Sql Server DataTypes

SQL Server Data Types Reference

Introduction

 There are plenty of data types to use in SQL Server. Knowing the limitations and benefits of each one will soon pay off. For instance, choosing the tinyint datatype instead of the int datatype for the "ProductType" column with values ranging from 1 to 10 will save three bytes per record. With 100,000 records you will save 300,000 bytes. That's not much in terms of disc space ("storage is cheap, bla bla") but you'll probably have indexes containing that column and if that index takes less memory the engine will process that index much faster in every "join" and "where" etc. So those queries will perform better and use less system resources (memory) which will make the whole server perform better as there will be more resources available for other things. Once learned the datatypes available and spending a few extra minutes when designing your schema will result in faster query execution and a better performing database.

This sheet provides an easy reference to look up limitations and benefits for each SQL Server data type.

The Data types reference sheet

The columns 8, 9, 10 shows what versions of SQL Server that supports the data type

• 8 = SQL Server 2000
• 9 = SQL Server 2005
• 10 = SQL Server 2008

Datatype	Min	Max	Storage	8	9	10	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 booth 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 numerics	Precision is specified from 1 to 53.
Real	-3.40E + 38	3.40E + 38	4 bytes				Approximate numerics	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	0001-01-01	9999-12-31		no	no		Date and time
Time	00:00:00.0000000	23:59:59.9999999		no	no		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	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	no	no		Date and time	Combines the date datatype and the time datatype into one. The precision logic is the same as for the time datatype.
Datetimeoffset	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	no	no		Date and time	Is a datetime2 datatype 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)	0 chars	2^31 chars	2 bytes + number of chars	no			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)	0 chars	2^30 chars		no			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)	0 bytes	2^31 bytes		no			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				no			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.