CODEWITHSUNDEEP
sql-servertypes
wootscootinboogie
wootscootinboogie

Reputation: 8705

Specifying length of columns in SQL Server

Is there any danger in making a column say, an INT with length of 3 digits and some of the columns only containing one or two?

Upvotes: 0

Views: 187

Answers (4)

Tim Lehner
Tim Lehner

Reputation: 15261

You might choose to use a Decimal type as in the following:

create table #MyTable
(
    NotOneThousand decimal(3,0)
)

insert into #MyTable select 999
insert into #MyTable select 1000 -- fails
insert into #MyTable select -999
insert into #MyTable select -1000 -- fails

select * from #MyTable

drop table #MyTable

Or use a check constraint such as:

create table #MyTable
(
    NotOneThousand smallint check (NotOneThousand between 0 and 999)
)

insert into #MyTable select -1 -- fails
insert into #MyTable select 0
insert into #MyTable select null
insert into #MyTable select 999
insert into #MyTable select 1000 -- fails

select * from #MyTable

drop table #MyTable

Upvotes: 1

Jeff Cuscutis
Jeff Cuscutis

Reputation: 11637

An int has a predefined storage size. You can't change it. Data Types

You may be thinking of numeric or decimal where you can specify precision.

Upvotes: 1

Icarus
Icarus

Reputation: 63970

An INT in Sql Server is 4 bytes so you won't be able to do what you describe. The values can range from -2,147,483,648 to 2,147,483,647

If you only want to store numbers uf up to 3 digits, a smallint will suffice (2 bytes).

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280615

You can't make an INT with 3 digits. An INT goes from negative 2 billion to positive 2 billion, and takes 4 bytes unless you are (successfully) using compression.

You could use a SMALLINT which will save you two bytes, but in order to keep it to 3 digits, you'd need to add a check constraint where column < 1000. You still won't be able to save space on smaller values (e.g. single digits will still take 2 bytes) again, unless you are using compression.

Upvotes: 2

Related Questions