TSQL Datatypes

Exact Numbers

• bigint Range: -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)
  Space: 8 Bytes
• int Range: -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
  Space: 4 Bytes
• smallint Range: -2^15 (-32,768) to 2^15-1 (32,767)
  Space: 2 Bytes
• tinyint Range: 0 to 255
  Space: 1 Byte
• bit Range: 0 (FALSE) or 1 (TRUE)
  Space: 8 bit columns in a table, will be collectively stored as: 1 Byte
  9 - 16 bit columns in a table, will be collectively stored as: 2 Bytes, etc.
• decimal Declaration: decimal(p[,s]).
  p = Precision - total number of digits stored to both the left and right of the decimal point.
  s = Scale the maximum number of digits stored to the right of the decimal point (optional).
  
  Precision 1 - 9: Storage is 5 bytes
  Precision 10 - 19: Storage is 9 bytes
  Precision 20 - 28: Storage is 13 bytes
  Precision 29 - 38: Storage is 17 bytes
  
  Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18.
  Note: Decimal is equivalent to Numeric.
• numeric Declaration: numeric(p[,s]).
  p = Precision - total number of digits stored to both the left and right of the decimal point.
  s = Scale the maximum number of digits stored to the right of the decimal point (optional).
  
  Precision 1 - 9: Storage is 5 bytes
  Precision 10 - 19: Storage is 9 bytes
  Precision 20 - 28: Storage is 13 bytes
  Precision 29 - 38: Storage is 17 bytes
  
  Minimum Precision is 1 and Maximum Precision is 38. The Default Precision is 18.
  Note: Numeric is equivalent to Decimal.
• money Range: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
  Space: 8 bytes
• smallmoney Range: -214,748.3648 to 214,748.3647
  Space: 4 bytes

Approximate Numbers

• float Declaration: float(n).
  n = the number of bits used to store the floating point number.
  
  Range: -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
  
  n Value 1 - 24: Precision - 7 digits: Space - 4 bytes
  n Value 25 - 53: Precision - 15 digits: Space - 8 bytes
• real Range: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
  Space: 4 bytes
  
  Note: Real is equivalent to float(24).

Date and Time

• datetime Range: January 1, 1753, through December 31, 9999
  Accuracy: 3.33 ms Space: 8 bytes (two 4 byte integers). First 4 bytes represent the number of days before or after Jan. 1, 1900. The Second 4 bytes store the time of day as a number 1/3000-second units after 12:00 AM (00:00:00).
• smalldatetime Range: January 1, 1900, through June 6, 2079
  Accuracy: 1 min Space: 4 bytes (two 2 byte integers). First 2 bytes represent the number of days after Jan. 1, 1900. The Second 2 bytes store the number of minutes after 12:00 AM (00:00:00).

Character Strings

• char Definition: Fixed-Length character string.
  Declaration: char(n).
  n = the number of characters.
  
  Space: n number of bytes
  
  Valid lengths for a char datatype are 1 through 8,000.
• varchar Definition: Variable-Length character string.
  Declaration: varchar(n | max).
  n = the number of characters.
  
  Space: characters actually used in datatype (1 byte per character) + 2 additional bytes
  
  Valid lengths for a varchar datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 characters).
• text Definition: Variable-Length character string in the code page of the server.
  
  Maximum length is 2,147,483,647 characters.
  Note: will be removed in future versions. Use varchar(max) instead.

Unicode Character Strings

• nchar Definition: Fixed-Length Unicode character string.
  Declaration: nchar(n).
  n = the number of characters.
  
  Space: n * 2 number of bytes
  
  Valid lengths for a char datatype are 1 through 4,000.
• nvarchar Definition: Variable-Length Unicode character string.
  Declaration: nvarchar(n | max).
  n = the number of characters.
  
  Space: characters actually used in datatype (2 bytes per character) + 2 additional bytes
  
  Valid lengths for a varchar datatype are 1 through 4,000. Alternatively MAX indicates that the maximum storage size is much larger (2^31-1 bytes - 2,147,483,647 characters).
• ntext Definition: Variable-Length character string in the code page of the server.
  
  Maximum length is 1,073,741,823 characters.
  Note: will be removed in future versions. Use nvarchar(max) instead.

Binary Strings

• binary Definition: Fixed-Length binary data.
  Declaration: binary(n).
  
  Space: n number of bytes.
  Maximum length is 8000 bytes.
• varbinary Definition: Variable-Length binary data.
  Declaration: varbinary(n | max).
  n = the number of characters.
  
  Space: actual number of bytes stored in datatype + 2 additional bytes
  
  Valid lengths for a varbinary datatype are 1 through 8,000. Alternatively, MAX allows for a much larger maximum storage size (2^31-1 bytes - 2,147,483,647 bytes).
• image Definition: Variable-Length binary data.
  
  Maximum length is 2,147,483,647 bytes.
  
  Note: will be removed in future versions. Use varbinary(max) instead.

Other Data Types

• cursor
• sql_variant
• table
• timestamp
• uniqueidentifier
• xml