Data Types

As we all know the selection of proper data types is very important for a SQL Server Table objects. Without proper data types the performance is going down and it is un manageable for SQL developer.

In this article I am trying to gather and provide a chart that contains most useful data types used in Microsoft SQL Server Version 2000/2005/2008.

Datatype	Min	Max	Storage	Type	Notes
Bigint	-2^63	2^63-1	8 bytes	Exact numeric
Int	-2,147,483,648	2,147,483,647	4 bytes	Exact numeric
Smallint	-32,768	32,767	2 bytes	Exact numeric
Tinyint	0	255	1 bytes	Exact numeric
Bit	0	1	1 to 8 bit columns in the same table requires a total of 1 byte, 9 to 16 bits = 2 bytes, etc...	Exact numeric
Decimal	-10^38+1	10^38–1	Precision 1-9 = 5 bytes, precision 10-19 = 9 bytes, precision 20-28 = 13 bytes, precision 29-38 = 17 bytes	Exact numeric	Decimal and numeric data type is exactly the same. Precision is the total number of digits. Scale is the number of decimals. For both the minimum is 1 and the maximum is 38.
Numeric	no
Money	-2^63 / 10000	2^63-1 / 10000	8 bytes	Exact numeric
Smallmoney	-214,748.3648	214,748.3647	4 bytes	Exact numeric
Float	-1.79E + 308	1.79E + 308	4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53	Approximate numeric	Precision is specified from 1 to 53.
Real	-3.40E + 38	3.40E + 38	4 bytes	Approximate numeric	Precision is fixed to 7.
Datetime	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	8 bytes	Date and time	If you are running SQL Server 2008 and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Smalldatetime	1900-01-01 00:00	2079-06-06 23:59		Date and time
Date  (SQL2008)	0001-01-01	9999-12-31		Date and time
Time (SQL2008)	00:00:00.0000000	23:59:59.9999999		Date and time	Specifying the precision is possible. TIME(3) will have milliseconds precision. TIME(7) is the highest and the default precision. Casting values to a lower precision will round the value.
Datetime2 (SQL2008)	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	Presicion 1-2 = 6 bytes precision 3-4 = 7 bytes precision 5-7 = 8 bytes	Date and time	Combines the date data type and the time data type into one. The precision logic is the same as for the time data type.
Datetimeoffset (SQL2008)	0001-01-01 00:00:00.0000000 -14:00	9999-12-31 23:59:59.9999999 +14:00	Presicion 1-2 = 8 bytes precision 3-4 = 9 bytes precision 5-7 = 10 bytes	Date and time	Is a datetime2 data type with the UTC offset appended.
Char	0 chars	8000 chars	Defined width	Character string	Fixed width
Varchar	0 chars	8000 chars	2 bytes + number of chars	Character string	Variable width
Varchar(max) (SQL2005/2008)	0 chars	2^31 chars	2 bytes + number of chars	Character string	Variable width
Text	0 chars	2,147,483,647 chars	4 bytes + number of chars	Character string	Variable width
Nchar	0 chars	4000 chars	Defined width x 2	Unicode character string	Fixed width
Nvarchar	0 chars	4000 chars		Unicode character string	Variable width
Nvarchar(max) (SQL2005/2008)	0 chars	2^30 chars		Unicode character string	Variable width
Ntext	0 chars	1,073,741,823 chars		Unicode character string	Variable width
Binary	0 bytes	8000 bytes		Binary string	Fixed width
Varbinary	0 bytes	8000 bytes		Binary string	Variable width
Varbinary(max) (SQL2005/2008)	0 bytes	2^31 bytes		Binary string	Variable width
Image	0 bytes	2,147,483,647 bytes		Binary string	Variable width
Sql_variant				Other	Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
Timestamp				Other	Stores a database-wide unique number that gets updated every time a row gets updated.
Uniqueidentifier				Other	Stores a globally unique identifier (GUID).
Xml (SQL2005/2008)				Other	Stores XML data. You can store xml instances in a column or a variable.
Cursor				Other	A reference to a cursor.
Table				Other	Stores a result set for later processing.

Hope you like it.

Posted by: MR. JOYDEEP DAS