I am totally new in SQL server databases and I would like some help.
I would like to create a table with the following properties
key Field: requestID integer(5)
but the problem is that I was not able to find where I can specify the value of characters allowed to be entered!
is there any way in SQL server 2008 that I can tell to the database that the following field can be lets say an Integer and it will have only 5 digits? like int(5)??
Do you want to store data from a flat file in a table? As in The first 5 characters are numeric and hold the requestID, the next 5 characters hold the day, the next 2 are numeric and hold the period, ...?
SQL server comes with some predefined integer data types.
BIGINT: -2^63 (-9 e18) to 2^63-1 (+9 e18); 8 Bytes
INT: -2^31 (-2 billion) to 2^31-1 (+2 billion); 4 Bytes
SMALLINT: -2^15 (-32,768) to 2^15-1 (32,767); 2 Bytes
TINYINT: 0 to 255; 1 Byte
You must choose one that fits the range of possible values.
Or you can use a DECIMAL data type: DECIMAL(p ,s).
p is the precision, the maximum total number of decimal digits that can be stored, both before and after the decimal point. With values from 1 till 38, with default 18.
s is the scale, the number of decimal digits after the decimal point. The scale must be a value from 0 through p, default 0.
To store 12.3456, you need a DECIMAL(6, 4).
To store 123456, you need a DECIMAL(6, 0) or DECIMAL(6).
What values will the column "day" store? Strings? Like 'Mon' or 'Thurs'? And what about Period?
Best is to use the standard DATE data type. It will make your life a lot easier later on when you have to do date arithmetic (order by date, summarise per week or month or year, ... ).
By the way, VARCHAR(5) is perfect SQL syntax. INTEGER(2) isn't.
About naming a column, "day". You can do that, but using SQL reserved words for column names becomes a pain later on, as you will always have to surround them with "[" and "]": [day]. And it will obfuscate the meaning of your SQL scripts later on. Try to come up with a more descriptive name like StartDate or RequestDate ...
With kind regards . . . . . SQL Server 2000/2005/2012
Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2. Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages