10-AUG-2010, last improvement 15-AUG-2011
*I have to store numbers from 1 through 9999. Which data type should I choose?*

*I have to support different versions of SQL Server. Which data types are version safe?*

*How much space does a data type use?*

Below is one big table that answers all these questions!

Below is one big table that answers all these questions!

Precedence | Data Type | Class | Range | Space Used | 7.0 | 2000 | 2005 | 2008 | Remarks |
---|---|---|---|---|---|---|---|---|---|

^{*1} Using variable length columns will add some extra bytes to every row; 3 bytes for the first 8 variable length columns.^{*2} Use is discouraged; a better data type is available that serves the same purpose. |
|||||||||

18 | tinyint | Numeric data | 0 - 255 | 1 byte | Y | Y | Y | Y | |

17 | smallint | Numeric data | -32,768 - 32,767 | 2 bytes | Y | Y | Y | Y | |

16 | int | Numeric data | -2,147,483,648 - 2,147,483,647 | 4 bytes | Y | Y | Y | Y | |

14 | smallmoney | Numeric data | -214,748.3648 - 214,748.3647 | 4 bytes | Y | Y | Y | Y | Author recommends using decimal() instead |

12 | decimal(9,y) | Numeric data | -999,999,999 - 999,999,999 | 5 bytes | Y | Y | Y | Y | Precision up to 9 digits uses 5 bytes. Numeric is a synonym for Decimal. |

13 | money | Numeric data | -922,337,203,685,477.5808 - 922,337,203,685,477.5807 | 8 bytes | Y | Y | Y | Y | Author recommends using decimal() instead |

15 | bigint | Numeric data | -9,223,372,036,854,775,808 - 9,223,372,036,854,775,807 | 8 bytes | Y | Y | Y | ||

12 | decimal(19,y) | Numeric data | -9,999,999,999,999,999,999 - 9,999,999,999,999,999,999 | 9 bytes | Y | Y | Y | Y | Precision from 10 up to 19 digits uses 9 bytes |

12 | decimal(28,y) | Numeric data | -10^28+1 - 10^28-1 | 13 bytes | Y | Y | Y | Y | Precision from 20 up to 28 digits uses 13 bytes |

12 | decimal(38,y) | Numeric data | -10^38+1 - 10^38-1 | 17 bytes | Y | Y | Y | Y | Precision from 29 up to 38 digits uses 17 bytes |

28 | char(x) | Character data | 1 - 8,000 characters | 1 - 8000 bytes | Y | Y | Y | Y | char(1) uses 1 byte and can store 1 character |

27 | varchar(x) | Variable Length Character data | 0 - 8,000 characters | 2 - 8002 bytes | Y | Y | Y | Y | varchar(5) uses 2 bytes when empty and 7 bytes when occupied with 5 characters^{*1} |

27 | varchar(max) | Variable Length Binary data | 0 - 2,147,483,645 characters | 2 bytes - 2 gigabytes | Y | Y | varchar(max) uses 2 bytes when empty and 3 bytes when occupied with 1 character^{*1} |
||

21 | text | Variable Length Character data | 0 - 2,147,483,647 characters | 16 bytes - 2 gigabytes | Y | Y | Y^{*2} | Y^{*2} | text uses 16 bytes when empty and 17 bytes when occupied with 1 character |

26 | nchar(x) | Character data | 1 - 4,000 characters | 2 - 8000 bytes | Y | Y | Y | Y | nchar(5) uses 10 bytes and can store 5 character |

25 | nvarchar(x) | Variable Length Character data | 0 - 4,000 characters | 2 - 8002 bytes | Y | Y | Y | Y | nvarchar(5) uses 2 bytes when empty and 12 bytes when occupied with 5 characters^{*1} |

25 | nvarchar(max) | Variable Length Binary data | 0 - 1,073,741,822 characters | 2 bytes - 2 gigabytes | Y | Y | nvarchar(max) uses 2 bytes when empty and 4 bytes when occupied with 1 character^{*1} |
||

20 | ntext | Variable Length Character data | 0 - 1,073,741,823 characters | 16 bytes - 2 gigabytes | Y | Y | Y^{*2} | Y^{*2} | ntext uses 16 bytes when empty and 18 bytes when occupied with 1 character |

8 | date | Temporal data | 0001-01-01 - 9999-12-31 | 3 bytes | Y | ||||

9 | time(2) | Temporal data | 00:00:00.00 - 23:59:59.99 | 3 bytes | Y | Precision up to 2 digits uses 3 bytes | |||

7 | smalldatetime | Temporal data | 1900-01-01 00:00 - 2079-06-06 23:59 | 4 bytes | Y | Y | Y | Y | |

9 | time(4) | Temporal data | 00:00:00.0000 - 23:59:59.9999 | 4 bytes | Y | Precision of 3 or 4 digits uses 4 bytes | |||

9 | time(7) | Temporal data | 00:00:00.0000000 - 23:59:59.9999999 | 5 bytes | Y | Precision from 5 up to 7 digits uses 5 bytes | |||

5 | datetime2(2) | Temporal data | 0001-01-01 00:00:00.00 - 9999-12-31 23:59:59.99 | 6 bytes | Y | Precision up to 2 digits uses 6 bytes | |||

5 | datetime2(4) | Temporal data | 0001-01-01 00:00:00.0000 - 9999-12-31 23:59:59.9999 | 7 bytes | Y | Precision of 3 or 4 digits uses 7 bytes | |||

6 | datetime | Temporal data | 1753-01-01 00:00:00.000 - 9999-12-31 23:59:59.997 | 8 bytes | Y | Y | Y | Y | |

5 | datetime2(7) | Temporal data | 0001-01-01 00:00:00.0000000 - 9999-12-31 23:59:59.9999999 | 8 bytes | Y | Precision from 5 up to 7 digits uses 8 bytes | |||

4 | datetimeoffset(2) | Temporal data | 0001-01-01 00:00:00.00 - 9999-12-31 23:59:59.99 | 8 bytes | Y | Precision up to 2 digits uses 8 bytes | |||

4 | datetimeoffset(4) | Temporal data | 0001-01-01 00:00:00.0000 - 9999-12-31 23:59:59.9999 | 9 bytes | Y | Precision of 3 or 4 digits uses 9 bytes | |||

4 | datetimeoffset(7) | Temporal data | 0001-01-01 00:00:00.0000000 - 9999-12-31 23:59:59.9999999 | 10 bytes | Y | Precision from 5 up to 7 digits uses 10 bytes | |||

19 | bit | Binary data | 1 bit | 1 byte | Y | Y | Y | Y | bit uses 1 byte and can store 1 bit, 8 bits use 1 byte and can collectively store 8 bits. Because of nullability, 8 bit columns require more space than 1 binary(1) column |

30 | binary(x) | Binary data | 8 - 64,000 bits | 1 - 8000 bytes | Y | Y | Y | Y | As of SQL Server 2005 binary(1) uses 1 byte and can store 8 bits. SQL Server 2000 and earlier uses 4 more bytes |

29 | varbinary(x) | Variable Length Binary data | 0 - 64,000 bits | 2 - 8002 bytes | Y | Y | Y | Y | As of SQL Server 2005 varbinary(5) uses 2 bytes when empty and 7 bytes when occupied with 33 bits or more. SQL Server 2000 and earlier uses 2 more bytes^{*1} |

29 | varbinary(max) | Variable Length Binary data | 0 - 17,179,869,160 bits | 2 bytes - 2 gigabytes | Y | Y | varbinary(max) uses 2 bytes when empty and 3 bytes when occupied with up to 8 bits ^{*1} |
||

22 | image | Variable Length Binary data | 0 - 2,147,483,647 bytes | 16 bytes - 2 gigabytes | Y | Y | Y^{*2} | Y^{*2} | image uses 16 bytes when empty and 17 bytes when occupied with 1 byte |

24 | uniqueidentifier | Globally Unique Identifier | 1 GUID | 16 bytes | Y | Y | Y | Y | |

23 | timestamp | Database-wide Unique Number | 1 number | 8 bytes | Y | Y | Y | Y | |

11 | float(24) | Approximate Numeric data | -3.40E+38 - 3.40E+38 | 4 bytes | Y | Y | Y | Y | Mantissa up to 24 bits uses 4 bytes. Real is a synonym for Float(24). Author recommends never to use this data type for money amounts |

10 | float(53) | Approximate Numeric data | -1.79E+308 - 1.79E+308 | 8 bytes | Y | Y | Y | Y | Mantissa from 25 up to 53 bits uses 8 bytes. Author recommends never to use this data type for money amounts |

3 | xml | XML data | 2 bytes - 2 gigabyes | Y | Y | The "stored representation" cannot exceed 2 gigabytes in size | |||

2 | sql_variant | Depends | Depends | 16 - 8,016 bytes | Y | Y | Y | Author recommends avoiding this data type | |

1 | User defined data type | UDT | Depends | unknown | Y | Y | Author recommends avoiding this data type | ||

1 | geography(NULL) | Spatial data | empty | 2 bytes | Y | ^{*1} |
|||

1 | geography('Point') | Spatial data | 1 point | 24 bytes | Y | ^{*1} |
|||

1 | geography('Line') | Spatial data | LineString with 1 line | 40 bytes | Y | ^{*1} |
|||

1 | geography('LineString') | Spatial data | LineString with multiple lines | 82 - ? bytes | Y | Uses 34 bytes plus 16 bytes for every point in the LineString^{*1} |
|||

1 | geography('Polygon') | Spatial data | 82 - ? bytes | Y | Uses 34 bytes plus 16 bytes for every point in the Polygon^{*1} |

Back to SQL Server main menu. Mail your comments to gertjans@xs4all.nl.