SQL Server Data Types Quick Reference Guide


By:   |   Updated: 2020-09-25   |   Comments (2)   |   Related: More > Data Types


Problem

SQL Server has many different data types and it is not always a given as which data type to use, so this outline gives you a quick overview of the different data types you can use in SQL Server.

Solution

Following are commonly used data types organized by category with a brief description, range of values, storage size, and an example.

Exact Numerics SQL Server Data Types

Int Data Type

  • Int is used to store a whole number and is the primary integer data type
  • Range of values: -2,147,483,648 to 2,147,483,647
  • Storage size: 4 Bytes
-- declare an int variable, assign it a value, and increment it
DECLARE @MyInt int = 0
SET @MyInt += 1
SELECT @MyInt AS [MyInt] 
Int

BigInt Data Type

  • Bigint is used to store a whole number that is outside of the range of an int
  • Range of values:  -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • Storage size: 8 Bytes
-- make the variable a bigint  
DECLARE @MyBigInt bigint = 2147483648
SELECT @MyBigInt AS [MyBigInt]
Bigint

Smallint Data Type

  • Smallint takes less space than an INT and can be used when a whole number is guaranteed to be between -32,768 to 32,767
  • Range of values: -32,768 to 32,767
  • Storage size: 2 Bytes
-- smallint example
DECLARE @MySmallInt smallint = 32000
SELECT @MySmallInt AS [MySmallInt]
SmallInt

Tinyint Data Type

  • Tinyint can be used when a whole number is guaranteed to be between 0 and 255, i.e. number of years of a mortgage
  • Range of values: 0 to 255
  • Storage size: 1 Byte
-- tinyint example of length of a mortgage in years
DECLARE @MortgageLength tinyint = 30
SELECT @MortgageLength AS [MortgageLength]
Tinyint

Bit Data Type

  • Bit can be used to represent a Boolean value such as a switch is either on or off, a process is done or it isn’t, etc.
  • Range of values: 0 or 1, or a NULL
  • Storage size = 1 Byte per every 8-bit column in a table
-- BIT example as an on / off value of a light switch
DECLARE @LightSwitch bit = 0
 
IF @LightSwitch = 1 PRINT 'LightSwitch is On'
ELSE PRINT 'LightSwitch is Off'

SET @LightSwitch = 1 -- turn on
IF @LightSwitch = 1 PRINT 'LightSwitch is On'
ELSE PRINT 'LightSwitch is Off'
Bit

Decimal Data Type

  • Decimal is a fixed precision and scale type
  • Range of values: 10^38 +1 to 10^38
  • Storage size:
    • Precision 1-9       5 Bytes
    • Precision 10-19    9 Bytes
    • Precision 20-28   13 Bytes
    • Precision 29-38   17 Bytes
  • Functionally the same as numeric
/*
decimal (p,s) 
p = precision = maximum total number of digits to be stored including both sides of decimal point - must 1 thru 38 - default = is 18
s = scale = number of digits to the right of the decimal point - default = 0
*/
 
DECLARE @MyDecimal decimal(8,4) = 123.1 -- 8 total digits with 4 to the right of the decimal
SELECT @MyDecimal AS MyDecimal
Decimal

Numeric Data Type

  • Numeric is a fixed precision and scale type
  • Range of values: 10^38 +1 to 10^38
  • Storage size:
    • Precision 1-9      5 Bytes
    • Precision 10-19   9 Bytes
    • Precision 20-28  13 Bytes
    • Precision 29-38  17 Bytes
  • Functionally the same as decimal
/*
numeric (p,s) 
p = precision = maximum total number of digits to be stored including both sides of decimal point - must 1 thru 38 - default = is 18
s = scale = number of digits to the right of the decimal point - default = 0
*/
 
DECLARE @MyNumeric numeric(8,4) = 123.1 -- 8 total digits with 4 to the right of the decimal
SELECT @MyNumeric AS MyNumeric
Numeric

Money Data Type

  • Money represents a monetary value
  • Range of values: -922,337,203,685,477.5808 to 922,337,203,685,477.5807
  • Storage size: 8 Bytes
/*
numeric (p,s) 
p = precision = maximum total number of digits to be stored including both sides of decimal point - must 1 thru 38 - default = is 18
s = scale = number of digits to the right of the decimal point - default = 0
*/
 
--declare variable and set to $1.99
DECLARE @MyMoney money = 1.99
SELECT @MyMoney AS MyMoney
Money

Smallmoney Data Type

  • Smallmoney represents a monetary value
  • Range of values: - 214,748.3648 to 214,748.3647
  • Storage size: 4 Bytes
--declare variable and set to $1.99
DECLARE @MySmallMoney smallmoney = 1.99
SELECT @MySmallMoney AS MySmallMoney
Smallmoney

Approximate Numerics SQL Server Data Types

Float Data Type

  • Float is an approximate number data type used to store a floating-point number
  • float (n) - n is the number of bits that are used to store the mantissa in scientific notation
  • Range of values: - 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
  • Storage size: 4 Bytes if n = 1-9 and 8 Bytes if n = 25-53 – default = 53
-- float
DECLARE @MyFLoat float = 0.1111111111111111111111111111111111111  -- 15 digit precision output
SELECT @MyFLoat AS MyFloat
Float

Real Data Type

  • Real is an approximate number data type equivalent to a float(24) used to store a floating-point number
  • Range of values: - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
  • Storage size: 4 Bytes
-- real
DECLARE @MyReal real = 0.1111111111111111111111111111111111111  -- 7 digit precision output
SELECT @MyReal AS MyReal
Real

Date and Time SQL Server Data Types

Date Data Type

  • Defines a date in the format yyyy-mm-dd
  • Range of values: 0001-01-01 through 9999-12-31
  • Storage size: 3 Bytes
-- declare variable and set to Jan 1 2020
DECLARE @MyDate date = '2020-01-01'
SELECT @MyDate
Date

Time Data Type

  • Defines a time
  • Range of values: 00:00:00.0000000 through 23:59:59.9999999
  • Storage size: 5 Bytes
-- declare time variable and set to current time
DECLARE @MyTime time = GETDATE()
SELECT @MyTime AS MyTime
Time

Datetime Data Type

  • Define a date and time
  • Range of values: 0001-01-01 through 9999-12-31 - 00:00:00.0000000 through 23:59:59.9999999
  • Storage size: 8 Bytes
-- declare datetime variable and set to current time
DECLARE @MyDateTime datetime = GETDATE()
SELECT @MyDateTime AS MyDatetime
Datetime

Datetime2 Data Type

  • Define a date and time with higher precision than datetime
  • DECLARE @VarName(n) where n is a value from 1 to 7 defining precision with default of 7
  • Range of values: 1753-01-01 through 9999-12-31 - 00:00:00.0000000 through 23:59:59.9999999
  • Storage size:
    • precision < 3          6 Bytes
    • precision = 3 or 4    7 Bytes
    • precision > 4          8 Bytes
-- declare datetime2 variable and set to current time
DECLARE @MyDatetime2 datetime2(7) = GETDATE()
SELECT @MyDatetime2 AS MyDatetime2
Datetime2

Smalldatetime Data Type

  • Define a date and time without fractional seconds
  • Range of values: 1900-01-01 through 2079-06-06 - 00:00:00 through 23:59:59
  • Storage size: 4 Bytes
-- declare smalltime variable and set to current time
DECLARE @MySmalldatetime smalldatetime = GETDATE()
SELECT @MySmalldatetime AS MySmalldatetime 
Smalldatetime

Datetimeoffset Data Type

  • Defines a date and time with time zone awareness
  • DECLARE @VarName datetimeoffset(n) where n is a value from 1 to 7 defining precision with default of 7
  • Range of values: 0001-01-01 through 9999-12-31 - 00:00:00 through 23:59:59.9999999 - -14:00 through +14:00
  • Storage size: 10 Bytes
-- compare times in 2 timezones
DECLARE @Edt datetimeoffset = CAST('2020-01-01 08:00:00.000 -00:00' AS datetimeoffset)
DECLARE @Pdt datetimeoffset = CAST('2020-01-01 05:00:00.000 -03:00' AS datetimeoffset)
SELECT DATEDIFF(minute, @Edt, @Pdt) as MinutesDifference
Datetimeoffset

Character Strings SQL Server Data Types

Char Data Type

  • Char is fixed size string used to store characters
  • Char(n | max) – where n is the fixed length of the string in Byte pairs from 1 through 8,000 and max is 8,000
  • Storage size: n
-- char
DECLARE @MyChar char(20) = 'abcdefgh' -- 20 bytes used
SELECT @MyChar AS MyChar
Char

Varchar Data Type

  • Varchar is a variable size string used to store characters
  • Varchar(n | max) – where n is the fixed length of the string in Byte pairs from 1 through 8,000 and max is 8,000
  • Storage size: up to n or max
-- char
DECLARE @MyChar char(20) = 'abcdefgh' -- 20 bytes used
SELECT @MyChar AS MyChar
Varchar

Text Data Type

  • Test is a variable length string up to 2,147,483,647 Bytes
  • Invalid for local variable
  • Storage size: to 2,147,483,647 Bytes
-- create a table with 1 field of type text
CREATE TABLE dbo.MyTable(MyText text NULL)
--insert value
INSERT dbo.MyTable VALUES('abcdefgh')
SELECT MyText
FROM dbo.MyTable
Text

Unicode Character Strings SQL Server Data Types

Nchar Data Type

  • Nchar is fixed size string
  • Use to store string of fixed length characters
  • Nchar(n | max) – where n is the fixed length of the string in Byte pairs from 1 through 4,000 and max is 4,000
  • Storage size: n or max
-- nchar
DECLARE @MyNchar char(10) = 'abcdefgh' -- 20 bytes used
SELECT @MyNchar AS MyNchar
Nchar

Nvarchar Data Type

  • Nvarchar is a variable size string
  • Can store text in multiple languages
  • Nvarchar(n | max) – where n is the fixed length of the string in Byte pairs from 1 through 4,000 and max is 4,000
  • Storage size: up to n or max
-- nvarchar
DECLARE @MyNvarchar char(10) = 'abcdefgh' -- 8 bytes used
SELECT @MyNvarchar AS MyNvarchar
Nvarchar

Binary Strings SQL Server Data Types

Binary Data Type

  • Binary is a fixed length data type
  • Can be used to store any kind of binary data of fixed length like files, etc.
  • Binary(n) – where n is from 1 to 8,000
  • Storage size: n
-- binary
DECLARE @MyBinary binary(2) = 100  
SELECT CAST(@MyBinary AS int) AS MyBinary
Binary

Varbinary Data Type

  • Varbinary is a variable length data type that can be used to store any kind of binary data of varied length like files, etc.
  • Varbinary(n | max) – where n is from 1 to 8,000 and max is 8,000
  • Storage size: n
-- varbinary
DECLARE @MyVarbinary varbinary(2) = 100  
SELECT CAST(@MyVarbinary AS int) AS MyVarbinary
Varbinary
Next Steps

Following are some links with further info on data types:



Last Updated: 2020-09-25


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips



Comments For This Article




Tuesday, September 29, 2020 - 5:15:39 PM - Joe Gavin Back To Top (86555)
Thanks M. And you're correct on the datetime range. I missed that. Thanks for catching it.

Friday, September 25, 2020 - 10:29:20 AM - M Back To Top (86534)
Thank you for the article, Joe.
Please check the Datetime information. I think the minimum date needs to be January 1, 1753 and the timestamp precision should be 00:00:00.000 through 23:59:59.997
This is a nice "one stop shop" for datatypes.


download





Recommended Reading

How to get length of Text, NText and Image columns in SQL Server

Comparing SQL Server and Oracle datatypes

Handling error converting data type varchar to numeric in SQL Server

SQL Server differences of char, nchar, varchar and nvarchar data types

SQL Server Bit Data Type














get free sql tips
agree to terms