How can we indicate a max size for the type int, integer or number in a SQL Server database?
For example, with varchar we can indicate the max size: varchar(40).
What's the equivalent for the numbers?
-
One solution could be to create a Trigger as shown here: stackoverflow.com/questions/12868213/…Robin– Robin2013-06-28 09:21:13 +00:00Commented Jun 28, 2013 at 9:21
-
A check constraint limits the acceptable range of values, but the only influence over the amount of storage used is which of the built in types you select - so there's no way, for instance, to get an integral type that will only use 6 bytes of storage.Damien_The_Unbeliever– Damien_The_Unbeliever2013-06-28 09:23:52 +00:00Commented Jun 28, 2013 at 9:23
-
if not an integral , is there a way to specify this max Size for a numeric value in general ?marcAntoine– marcAntoine2013-06-28 09:31:01 +00:00Commented Jun 28, 2013 at 9:31
Add a comment
|
2 Answers
You define a CHECK constraint:
CREATE TABLE dbo.YourTable
(
SomeIntColumn INT
CONSTRAINT CHK_YourTable_SomeIntColumn
CHECK (SomeIntColumn >= 0 AND SomeIntColumn <= 9999),
.......
)
You can define a range of possible values for your numeric column that way. With this CHECK constraint in my sample, you can limit your INT column to a maximum of 4 digits.
Update
For the INT style datatypes, you cannot define a number of digits in SQL Server directly - there's no INT(4) or anything like that. You'll need to use a CHECK constraint as I showed.
The types decimal (and numeric) do support precision and scale:
decimal(p, s)
where p is the precision (total number of digits) and s is the scale (number of digits after the decimal point).
So a decimal(10,4) gives you 10 digits in total, 4 of which are after the decimal point, so you can have values like
0.0000
123456.1234
and so on
3 Comments
marcAntoine
yes , thank you , but how do i refer to this max number 9999 with the ColumnSize of the schematable in my code later ? because i must use the max Size for some verifications in my code
marc_s
@manu: you cannot set a "max size" for an
INT column - it's always 4 bytes in size.marcAntoine
then if not for an int , can i set a max size for other numeric types ? ( numbers )
They are already defined by that type you use, for bigint that would be 8 bytes. See reference
2 Comments
marcAntoine
yes , but i meant when i want an int that contains no more than 4 numbers , for example an attribute in database Key1 that should be a numeric and its max Size of numbers is 4 when i enter 11158 it won't accept it
whastupduck
Oh, check marc_s 's answer