SQLServerCentral Article

SQL Server 2008 R2 Data Types

,

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

Rate

4.7 (27)

You rated this post out of 5. Change rating

Share

Share

Rate

4.7 (27)

You rated this post out of 5. Change rating