Microsoft Transact SQL Server 2008 Data Types

Datatype

Min

Max

Storage

Type

Notes

Bigint -2^63 2^63-1 8 bytes Exact

Int -2,147,483,648 2,147,483,647 4 bytes Exact

Smallint -32,768 32,767 2 bytes Exact

Tinyint 0 255 1 bytes Exact

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

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 The Decimal and the 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 same as Decimal same as Decimal same as Decimal Exact

Money -2^63 / 10000 2^63-1 / 10000 8 bytes Exact

Smallmoney -214,748.3648 214,748.3647 4 bytes Exact

Float -1.79E + 308 1.79E + 308 4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53 Approx Precision is specified from 1 to 53.

Real -3.40E + 38 3.40E + 38 4 bytes Approx Precision is fixed to 7.

Datetime 1753-01-01 00:00:00.000 9999-12-31 23:59:59.997 8 bytes Datetime If you are running SQL Server 2008 or later and need milliseconds precision, use datetime2(3) instead to save 1 byte.

Smalldatetime 1900-01-01 00:00 2079-06-06 23:59 Datetime

Date 0001-01-01 9999-12-32 3 bytes Datetime New in SQL Server 2008

Time 00:00:00.0000000 23:59:59:9999999 3 to 5 bytes Datetime New in SQL Server 2008

Datetime2 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 6 to 8 bytes Datetime 100 nanosecond accuracy. New in SQL Server 2008

DatetimeOffset 0001-01-01 00:00:00.0000000 9999-12-31 23:59:59.9999999 8 to 10 bytes Datetime Like DateTime2 but includes offset in +-hh:mm from UTC. New in SQL Server 2008

Char 0 chars 8000 chars Defined width String Fixed width

Varchar 0 chars 8000 chars 2 bytes + number of chars String Variable width

Varchar(max) 0 chars 2^31 chars 2 bytes + number of chars String Variable width

Text 0 chars 2,147,483,647 chars 4 bytes + number of chars String Variable width

Nchar 0 chars 4000 chars Defined width x 2 Unicode Fixed width

Nvarchar 0 chars 4000 chars Unicode Variable width

Nvarchar(max) 0 chars 2^30 chars Unicode Variable width

Ntext 0 chars 1,073,741,823 chars Unicode Variable width

Binary 0 bytes 8000 bytes Binary Fixed width

Varbinary 0 bytes 8000 bytes Binary Variable width

Varbinary(max) 0 bytes 2^31 bytes Binary Variable width

Image 0 bytes 2,147,483,647 bytes Binary Variable width. Prefer to use the varbinary(max) type as the image type will be removed in future versions.

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 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.

HierarchyID Tree Stores position of nodes of a hierarchal tree structure as defined by the user. New in SQL Server 2008.

FileStream Stream Stores VARBINARY(MAX) binary large objects (BLOBs) outside of the database in the NTFS file system. New in SQL Server 2008, but only available if the feature is enabled.

Geometry Spatial Stores XY locations. New in SQL Server 2008.

Geography Spatial Stores Latitude and Logitude locations. New in SQL Server 2008.

Datatype	Min	Max	Storage	Type	Notes
Bigint	-2^63	2^63-1	8 bytes	Exact
Int	-2,147,483,648	2,147,483,647	4 bytes	Exact
Smallint	-32,768	32,767	2 bytes	Exact
Tinyint	0	255	1 bytes	Exact
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
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	The Decimal and the 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	same as Decimal	same as Decimal	same as Decimal	Exact
Money	-2^63 / 10000	2^63-1 / 10000	8 bytes	Exact
Smallmoney	-214,748.3648	214,748.3647	4 bytes	Exact
Float	-1.79E + 308	1.79E + 308	4 bytes when precision is less than 25 and 8 bytes when precision is 25 through 53	Approx	Precision is specified from 1 to 53.
Real	-3.40E + 38	3.40E + 38	4 bytes	Approx	Precision is fixed to 7.
Datetime	1753-01-01 00:00:00.000	9999-12-31 23:59:59.997	8 bytes	Datetime	If you are running SQL Server 2008 or later and need milliseconds precision, use datetime2(3) instead to save 1 byte.
Smalldatetime	1900-01-01 00:00	2079-06-06 23:59		Datetime
Date	0001-01-01	9999-12-32	3 bytes	Datetime	New in SQL Server 2008
Time	00:00:00.0000000	23:59:59:9999999	3 to 5 bytes	Datetime	New in SQL Server 2008
Datetime2	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	6 to 8 bytes	Datetime	100 nanosecond accuracy. New in SQL Server 2008
DatetimeOffset	0001-01-01 00:00:00.0000000	9999-12-31 23:59:59.9999999	8 to 10 bytes	Datetime	Like DateTime2 but includes offset in +-hh:mm from UTC. New in SQL Server 2008
Char	0 chars	8000 chars	Defined width	String	Fixed width
Varchar	0 chars	8000 chars	2 bytes + number of chars	String	Variable width
Varchar(max)	0 chars	2^31 chars	2 bytes + number of chars	String	Variable width
Text	0 chars	2,147,483,647 chars	4 bytes + number of chars	String	Variable width
Nchar	0 chars	4000 chars	Defined width x 2	Unicode	Fixed width
Nvarchar	0 chars	4000 chars		Unicode	Variable width
Nvarchar(max)	0 chars	2^30 chars		Unicode	Variable width
Ntext	0 chars	1,073,741,823 chars		Unicode	Variable width
Binary	0 bytes	8000 bytes		Binary	Fixed width
Varbinary	0 bytes	8000 bytes		Binary	Variable width
Varbinary(max)	0 bytes	2^31 bytes		Binary	Variable width
Image	0 bytes	2,147,483,647 bytes		Binary	Variable width. Prefer to use the varbinary(max) type as the image type will be removed in future versions.
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				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.
HierarchyID				Tree	Stores position of nodes of a hierarchal tree structure as defined by the user. New in SQL Server 2008.
FileStream				Stream	Stores VARBINARY(MAX) binary large objects (BLOBs) outside of the database in the NTFS file system. New in SQL Server 2008, but only available if the feature is enabled.
Geometry				Spatial	Stores XY locations. New in SQL Server 2008.
Geography				Spatial	Stores Latitude and Logitude locations. New in SQL Server 2008.

Notes:

GEOMETRY and GEOGRAPHY data types are implemented as .NET CLR data types, which means they can support various properties and methods specific to the data. For example, a method can be used to calculate the distance between two GEOMETRY XY coordinates, or the distance between two GEOGRAPHY latitude and longitude coordinates. Another example is a method to see if two spatial objects intersect or not. Methods defined by the Open Geospatial Consortium standard, and Microsoft extensions to that standard, can be used. To take full advantage of these methods, you will have to be an expert in spatial data, a topic that well beyond the scope of this chapter.

Another feature of spatial data types is that they support special spatial indexes. Unlike conventional indexes, spatial indexes consist of a grid-based hierarchy in which each level of the index subdivides the grid sector that is defined in the level above. But like conventional indexes, the SQL Server query optimizer can use spatial indexes to speed up the performance of queries that return spatial data.