Quick reference for SQL Server data types
Applies to: SQL Server 2000, SQL Server 2005, SQL Server 2008
Saving disk space is not the only benefit of properly selected data types for SQL Server columns. In addition to disk space benefits, the right data types reduce backup times, improve SQL Server performance, and enhance the execution of queries and joins. Here’s a comprehensive list of all SQL Server datatypes, ranges, and limitations:
Exact numerics
| Type | From | To |
|---|---|---|
| bigint | -9,223,372,036,854,775,808 | 9,223,372,036,854,775,807 |
| int | -2,147,483,648 | 2,147,483,647 |
| smallint | -32,768 | 32,767 |
| tinyint | 0 | 255 |
| bit | 0 | 1 |
| decimal | -10^38 +1 | 10^38 -1 |
| numeric | -10^38 +1 | 10^38 -1 |
| money | -922,337,203,685,477.5808 | +922,337,203,685,477.5807 |
| smallmoney | -214,748.3648 | +214,748.3647 |
numeric and decimal are Fixed precision and scale data types and are functionally equivalent.
Approximate numerics
| Type | From | To |
|---|---|---|
| float | -1.79E + 308 | 1.79E + 308 |
| real | -3.40E + 38 | 3.40E + 38 |
datetime and smalldatetime
| Type | From | To |
|---|---|---|
| datetime (3.33 milliseconds accuracy) | Jan 1, 1753 | Dec 31, 9999 |
| smalldatetime (1 minute accuracy) | Jan 1, 1900 | Jun 6, 2079 |
| date (1 day accuracy. Introduced in SQL Server 2008) | Jan 1, 0001 | Dec 31, 9999 |
| datetimeoffset (100 nanoseconds accuracy. Introduced in SQL Server 2008) | Jan 1, 0001 | Dec 31, 9999 |
| datetime2 (100 nanoseconds accuracy. Introduced in SQL Server 2008) | Jan 1, 0001 | Dec 31, 9999 |
| time (100 nanoseconds accuracy. Introduced in SQL Server 2008) | 00:00:00.0000000 | 23:59:59.9999999 |
Character Strings
| Type | Description |
|---|---|
| char | Fixed-length non-Unicode character data with a maximum length of 8,000 characters. |
| varchar | Variable-length non-Unicode data with a maximum of 8,000 characters. |
| varchar(max) | Variable-length non-Unicode data with a maximum length of 2^31 characters (Introduced in SQL Server 2005). |
| text | Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters. |
Unicode Character Strings
| Type | Description |
|---|---|
| nchar | Fixed-length Unicode data with a maximum length of 4,000 characters. |
| nvarchar | Variable-length Unicode data with a maximum length of 4,000 characters. |
| nvarchar(max) | Variable-length Unicode data with a maximum length of 2^30 characters (Introduced in SQL Server 2005). |
| ntext | Variable-length Unicode data with a maximum length of 1,073,741,823 characters. |
Binary Strings
| Type | Description |
|---|---|
| binary | Fixed-length binary data with a maximum length of 8,000 bytes. |
| varbinary | Variable-length binary data with a maximum length of 8,000 bytes. |
| varbinary(max) | Variable-length binary data with a maximum length of 2^31 bytes (Introduced in SQL Server 2005). |
| image | Variable-length binary data with a maximum length of 2,147,483,647 bytes. |
Other Data Types
- sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
- timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
- uniqueidentifier: Stores a globally unique identifier (GUID).
- xml: Stores XML data. You can store xml instances in a column or a variable (Introduced in SQL Server 2005).
- cursor: A reference to a cursor.
- table: Stores a result set for later processing.
- hierarchyid: A variable length, system data type used to represent position in a hierarchy (Introduced in SQL Server 2008).
Beekeeper Studio Is A Free & Open Source SQL Server GUI
I’ve upgraded from SQL Server and it was a smooth change — Beekeeper Studio enhanced my own workstream, offering a cleaner experience and better performance, as well as flexibility. - Aman Bhattarai
Beekeeper Studio is a fast, intuitive and easy to use SQL Server client. It supports loads of other databases too, and works great on Windows, Mac and Linux.
What SQL Server Users Say About Beekeeper Studio
So far the most comprehensive DB admin GUI out there. The only reason I haven’t given 5 stars is I cannot claim to have used it exhaustively only having discovered it two weeks ago. But so far, mySQL, MSSQL and Postgres in one tool is simply incredible.
Planning to purchase once pay day hits.
I had been using the free version of Beekeeper studio for a while, but when I needed to work with some MongoDB databases I upgraded to the paid version. And honestly, I was up and running in no time. Beekeeper is slick, fast, and it just works. Very intuitive, very easy to use. I work with MongoDB, Postgresql, SQL Server, and other databases in my job, and having one tool that can talk to them all is just wonderful.
Thank you, Matthew and team!