SQL Server Data Types Quick Reference Guide

By:   |   Comments (2)   |   Related: > 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]
```

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]
```

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]
```

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]
```

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'
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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
```

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```
Next Steps

Following are some links with further info on data types:

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips