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