SQL Server 2008 R2 Data Types

By Kenneth Fisher, 2012/02/22

I’ve always had a hard time calculating table sizes. So when I first started studying for the 70-450 exam I made a point of printing out the information in books online on the subject. Of course one of the most important parts of calculating table sizes is knowing the size of the different data types and SQL Server 2008 added a large number of new data types. I decided to print out a list of the data types and their storage sizes. Imagine my surprise when I was unable to find a complete list in books on-line. Each data type could be found individually, and there was a general list of data types (with special data types missing). But no single list of the data types, a general description, and a storage size for each. Imagine my further surprise when I couldn’t find a list after several hours of searching on the web. Finally I decided to make my own.

In order to save others some time and grief I am posting my list here. If I’ve missed anything, made any mistakes, or could have included something useful please comment and let me know. I’m still studying and would hate to miss anything in my own documentation, let alone mess up anyone else’s.

Data Types
Exact Numerics		p = Precision (maximum total digits), s = Scale (maximum digits to the right of the decimal)
	bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	8 Bytes
	int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	4 Bytes
	smallint	-2^15 (-32,768) to 2^15-1 (32,767)	2 Bytes
	tinyint	0 to 255	1 Byte
	bit	TRUE(1) or FALSE(0)	1-8 bit columns in a table 1 Byte, 9-16 bit columns 2 Bytes etc
	money	Similar but smaller than decimal(19,4) -922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 Bytes
	smallmoney	Similar but smaller than decimal (10,4) -214,748.3648 to 214,748.3647	4 Bytes
	decimal(p,[s])	Based on precision. Max -10^38+1 - 10^38+1 Default (18,0)	p 1-9 (5 Bytes), p 10-19 (9 Bytes), p 20-28 (13 Bytes)
	numeric(p,[s])	Based on precision. Max -10^38+1 - 10^38+1. Functionally equivalent to decimal Default (18,0)	p 1-9 (5 Bytes), p 10-19 (9 Bytes), p 20-28 (13 Bytes)

Approximate Numerics		n = Number of bits used to store the mantissa of the float number in scientific notation
	float(n)	n is 1-24 precision is 7 digits, n is 25 to 53 precision is 15 digits. Regardless of actual number n is treated as either 24 or 53 Default (53) - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308	n 1-24 (4 Bytes), n 25-53 (8 Bytes)
	real	The ISO synonym for real is float(24). - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.	4 Bytes

Date and Time		p = Fractional Seconds Precision with a range 0-7 decimal places and a default of 7.
	date	0001-01-01 through 9999-12-31	3 Bytes
	datetime2(p)	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999	p 0-2 (6 Bytes), p 3-4 (7 Bytes), p 5-7 (8 Bytes)
	datetime	1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997	8 Bytes
	datetimeoffset(p)	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)	p 0-2 (8 Bytes), p 3-4 (9 Bytes), p 5-7 (10 Bytes)
	smalldatetime	1900-01-01 00:00:00 through 2079-06-06 23:59:59	4 Bytes
	time(p)	00:00:00.0000000 through 23:59:59.9999999	p 0-2 (3 Bytes), p 3-4 (4 Bytes), p 5-7 (5 Bytes)

Character Strings		n = Number of characters with a range of 1-8,000 Default when using a declare is 1. Default when using CAST or CONVERT is 30.
	char(n)	Fixed-length non-Unicode character data.	Number of characters in Bytes
	text	Variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31-1 (2,147,483,647) characters.	Number of characters in Bytes
	varchar(n\|MAX)	MAX indicates a maximum storage of 2^31-1 Bytes	Number of characters actually stored in Bytes + 2 Bytes

Unicode CharacterStrings		n = Number of characters with a range of 1-4,000 Default when using a declare is 1. Default when using CAST or CONVERT is 30.
	nchar(n)	Fixed-length Unicode character data.	Number of characters * 2 in Bytes
	ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.	Storage size, in bytes, is two times the number of characters entered.
	nvarchar(n\|MAX)	MAX indicates a maximum storage of 2^31-1 Bytes	Number of characters actually stored * 2 in Bytes + 2 Bytes

Binary Strings		n = Number of Bytes with a range of 1-8,000 Default when using a declare is 1. Default when using CAST or CONVERT is 30.
	binary(n)	Fixed-length binary data.	n
	image	Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes.
	varbinary(n\|MAX)	MAX indicates a maximum storage of 2^31-1 Bytes	Number of Bytes actually stored + 2 Bytes

Spatial		Each coordinate is stored as double-precision binary floating-point number. The data is binary with a header then the list of coordinates. Ex: A point with only 2 coordinates is 22 bytes. A line between two points, and so having at least 4 coordinates is 38 bytes. The more points of data the larger the storage required.
	geometry	Planar coordinates	Variable length. Max 2GB
	geography	Ellipsoidal (round-earth) coordinates	Variable length. Max 2GB

Other
	cursor	More of a code device than a true data type.	N/A
	hierarchyid	Variable length system data type used to represent a hierarchy.	Variable max 892 Bytes
	sql_variant	A column of type sql_variant may contain data of a different datatype on a row by row basis.	Based on data and data type stored. Maximum is 8016 Bytes.
	table	A table variable is a table stored in memory	Based on the table definition. Not possible to use as a column.
	timestamp	Incrementing number used as a mechanism for version-stamping table rows. Not an actual time stamp. Comes up as "rowversion (Transact-SQL)" in help.	8 Bytes
	uniqueidentifier	A GUID, ex 6F9619FF-8B86-D011-B42D-00C04FC964FF	16 Bytes
	xml( [ CONTENT \| DOCUMENT ] xml_schema_collection )		Based on data stored. Max size 2GB.

Total article views: 3358 | Views in the last 30 days: 24

FORUM

SQL Server 2008 R2 Data Types

maximum length of Dimension Attribute

Extract Number From Alphanumeric Character

find out the customers having maximum number of unique orders.

Characters

Maximum Number of "When Then" lines in a CASE statement?

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

SQL Server 2008 R2 Data Types

maximum length of Dimension Attribute

Extract Number From Alphanumeric Character

find out the customers having maximum number of unique orders.

Characters

Maximum Number of "When Then" lines in a CASE statement?

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Already a member? Jump in: