In the next table you have a list of the system data types available in MS SQL Server 2005.
| >Data type name | >Number of bytes | >Comments | |
| Integer | tinyint | 1 | |
| smallint | 2 | ||
| int | 4 | ||
| bigint | 8 | ||
| Exact numeric | decimal[p[,s]] | 5-17 | |
| numeric[p[,s]] | 5-17 | ||
| Appropiate numeric | float[(n)[ | 8 | |
| real | 4 | ||
| Monetary | smallmoney | 4 | |
| money | 8 | ||
| Date and time | smalldatetime | 4 | |
| datetime | 8 | ||
| Characters | char[(n)] | 0-8000 | non-Unicode |
| varchar[(n)] | 0-8000 | non-Unicode | |
| varchar(max) | 0-2 GB | non-Unicode, 16 bytes pointer on row, ,preferred over text data type | |
| text | 0-2 GB | non-Unicode, 16 bytes pointer or in row, obsolete, varchar(max) prefered | |
| nchar[(n)] | 0-8000 | max 4000 unicode characters | |
| nvarchar[(n)] | 0-8000 | max 4000 unicode characters | |
| nvarchar(max) | 0-2 GB | 16 bytes pointer or in row, ,preferred over ntext data type | |
| ntext | 0-2 GB | 16 bytes pointer, obsolete, nvarchar(max) prefered | |
| Binary | binary[(n)] | 0-8000 | |
| varbinary[(n)] | 0-8000 | ||
| varbinary(max) | 0-2 GB | 16 bytes pointer or in row, ,preferred over image data type | |
| Image | image | 0-2 GB | 16 bytes pointer, obsolete, varbinary(max) prefered |
| Global identifier | uniqueidentifier | 16 | |
| XML | xml | 0-2GB | 16 bytes pointer |
| Special | bit | 1 | 1 byte for every 8 bit columns |
| cursor | 0-8 | ||
| timestamp | 8 | one column per table | |
| sysname | 256 | ||
| table | – | ||
| sql_variant | 0-8016 |
You should take care of some facts when you choose a data type for a variable or for a column.
When using exact numeric data types you can specifie the scale and precision (digits on the left and on the right side of the decimal point. Data will always be returned exactly as entered. In the case of the approxiamete numeric data types the MS SQL Server will tyy to keep the data as accurately as possible, For example 2/3 which is 0.(6) cannot be stored without loss so an approximation is stoted in the database. You should take care when using approximate numeric data types in WHERE clausebecause of the way data is stored because 1/3 + 2/3 = 3/3 = 1 but 0.(3) + 0.(6) = 0.(9) <> 1.
When you store characters in a table, if the length of the text is not always the same you should use varchar on nvarchar. Even if it use some extra processing power, the benefits of not wasting space on the harddisk and on the database page that is holding the row is greater then the loss.
The smalldatetime data type can be used to store dates between January 1st, 1900 and June 6th, 2079, values been kept in approximation to the nearest minute. The datetime data type can store dates between January 1st, 1753 and December 31, 9999 with an accuracy of one three-hundredth of a second.
The timestamp data type is used to version-stamp table rows. You can hava only one timestamp column per table and it is not necessarly to specify a name for the timestamp column when you create a table.
Arrao4u 