# SQL Server 2008 R2 Data Types

By Kenneth Fisher,

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 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: 3703 | Views in the last 30 days: 1

Related Articles
FORUM

### maximum length of Dimension Attribute

maximum length of Dimension Attribute

FORUM

### Extract Number From Alphanumeric Character

Extract Number From Alphanumeric Character

FORUM

### find out the customers having maximum number of unique orders.

find out the customers having maximum number of unique orders.

BLOG

### Characters

No, I’m not talking about a Dickens novel. I’m talking about the number of characters in a string....

FORUM

### Remove Character -- a comma from a number

Remove characters from numbers

Tags
 datatypes sql server 2008 r2