SQL Server Data Types – Decimal, Numeric, Bit, Money, Date, Int

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

/*
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:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *