page contents

About the Post

Author Information

SQL Server Data Types

I am going to start a series on different database types for various data programs. (Microsoft SQL Server, MY SQL and Access) My goal here is to document the different data types and categories that reflect basic information about them.

For more detailed information, please see this article Data Types (Transact-SQL)  and also Paul S. Randal did a great job as well with this article Inside the Storage Engine: Anatomy of a record

I am going to start with Microsoft SQL Server:

Data type Description Storage Category
char(n) Fixed-length character string. Maximum 8,000 characters n bytes Character Strings
varchar(n) Variable-length character string. Maximum 8,000 characters 1 byte per character + 2 bytes overhead Character Strings
varchar(max) Variable-length character string. Maximum 1,073,741,824 characters 1 byte per character + 2 bytes overhead Character Strings
text Variable-length character string. Maximum 2GB of text data 1 byte per character + 2 bytes overhead Character Strings

Please see red below

Data type Description Storage Category
nchar(n) Fixed-length Unicode data. Maximum 4,000 characters (2 x n bytes) Unicode Character Strings
nvarchar(n) Variable-length Unicode data. Maximum 4,000 characters   Unicode Character Strings
nvarchar(max) Variable-length Unicode data. Maximum 536,870,912 characters 2x characters stored + 2 bytes overhead Unicode Character Strings
ntext Variable-length Unicode data. Maximum 2GB of text data   Unicode Character Strings

Please see red below

Data type Description Storage Category
bit Allows 0, 1, or NULL 1 byte for each 8 columns Exact numerics
binary(n) Fixed-length binary data. Maximum 8,000 bytes n bytes Binary Strings
varbinary(n) Variable-length binary data. Maximum 8,000 bytes 1 byte per character + 2 bytes overhead Binary Strings
varbinary(max) Variable-length binary data. Maximum 2GB 1 byte per character + 2 bytes overhead Binary Strings
image Variable-length binary data. Maximum 2GB   Binary Strings

Please note: ntext , text, and image data types
will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Source: SQL Server 2012 Books Online [Data Types (Transact-SQL)]

Data type Description Storage Category
tinyint Allows whole numbers from 0 to 255 1 byte Exact numerics
smallint Allows whole numbers between -32,768 and 32,767 2 bytes Exact numerics
int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes Exact numerics
bigint Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 bytes Exact numerics
decimal(p,s) Fixed precision and scale numbers. 5-17 bytes Exact numerics
 
Allows numbers from -10^38 +1 to 10^38 –1.
 
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
 
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
numeric(p,s) Fixed precision and scale numbers. 5-17 bytes Exact numerics
 
Allows numbers from -10^38 +1 to 10^38 –1.
 
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
 
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes Exact numerics
money Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes Exact numerics
float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308. 4 or 8 bytes Approximate Numerics
 
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes Approximate Numerics

 

Data type Description Storage Category
datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes Date and Time
datetime2 From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes Date and Time
smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes Date and Time
date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes Date and Time
time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes Date and Time
datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes Date and Time
timestamp Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal counter and does not correspond to real time. Each table may have only one timestamp variable 8 bytes Other

Added for clarity: 

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter is the database timestamp. This tracks a relative time within a database, not an actual time that can be associated with a clock

SQL Server 2005 Books Online timestamp (Transact-SQL) 

Please note:

The timestamp syntax is deprecated. (It has been since SQL Server 2008 BOL) This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

SQL Server 2012 Books Online (rowversion (Transact-SQL))

Data type Description Storage Category
sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp 8,016 bytes Other
uniqueidentifier Stores a globally unique identifier (GUID) 16 bytes Other
xml Stores XML formatted data. Maximum 2GB Up to 2 GB Other
cursor Stores a reference to a cursor used for database operations N/A Other
table Stores a result-set for later processing Depends on number of rows stored and table definition Other
hierarchyid Stores a variable length, system data type to represent position in a hierarchy. 1-892 bytes + 2 bytes overhead Other

 

Tags: , ,

7 Responses to “SQL Server Data Types”

  1. Nice… But one suggestion, maybe mark/indicate those datatypes that are deprecated? For example Text/Ntext/Image datatypes shouldn’t be used anymore…

    Maybe just an asterisk/footnote or snip in the description. Just a little thing but might help explain the difference between them and their replacement datatypes.

    January 14, 2013 at 7:14 AM
    • Scott Mattie #

      Hey Greg, thanks for pointing that out and I am sorry for the delay. I needed to confirmed some of the information between the different versions of Books Online. I have updated the chart to reflect these changes.

      January 22, 2013 at 9:15 PM
      • Prefect! 🙂

        January 23, 2013 at 6:00 AM
        • Scott Mattie #

          Again, thanks for pointing it out 🙂

          January 23, 2013 at 10:32 PM
  2. Bob Beauchemin #

    The list is missing geometry and geography, which have been in SQL Server since 2008. I would also timestamp is based on an internal “counter” rather than an internal “clock”, but that’s my opinion.

    January 14, 2013 at 6:43 PM
    • Scott Mattie #

      Bob,

      I updated the chart to reflect the better choice of words after reading BOL and I agree with you that clock was a poor choice of words and counter is more appropriate for timestamp.

      In regards to Geometry and Geography, I did not reflect Spatial Types at all (http://msdn.microsoft.com/en-us/library/ff848797.aspx) and it was due to it not being listed in BOL as a data type in the link I provided and also I was unable to find the Description, Storage and Category information.

      January 22, 2013 at 9:19 PM

Trackbacks/Pingbacks

  1. Microsoft Access Data Types | Scott Mattie's Blog - January 16, 2013

    […] stated on Monday, I have started a series on different database types for various data programs. (Microsoft SQL […]

Copy Protected by Chetan's WP-Copyprotect.