SQL Server Data Types

Summary: in this tutorial, you will learn about SQL Server data types including numeric, character string, binary string, date & time, and other data types.

SQL Server data types Overview

In SQL Server, a column, variable, and parameter holds a value that associated with a type, or also known as a data type. A data type is an attribute that specifies the type of data that these objects can store. It can be an integer, character string, monetary, date and time, and so on.

SQL Server provides a list of data types that define all types of data that you can use e.g., defining a column or declaring a variable.

The following picture illustrates the SQL Server data types system:

SQL Server Data Types

Notice that SQL Server will remove ntext, text, and image data types in its future version. Therefore, you should avoid using these data types and use nvarchar(max), varchar(max), and varbinary(max) data types instead.

Exact numeric data types

Exact numeric data types store exact numbers such as integer, decimal, or monetary amount.

  • The bit store one of three values 0, 1, and NULL
  • The int, bigint, smallint, and tinyint data types store integer data.
  • The decimal and numeric data types store numbers that have fixed precision and scale. Note that decimal and numeric are synonyms.
  • The money and smallmoney data type store currency values.

The following table illustrates the characteristics of the exact numeric data types:

Data TypeLower limitUpper limit Memory
bigint−2^63 (−9,223,372, 036,854,775,808)2^63−1 (−9,223,372, 036,854,775,807)8 bytes
int−2^31 (−2,147, 483,648)2^31−1 (−2,147, 483,647)4 bytes
smallint−2^15 (−32,767)2^15 (−32,768)2 bytes
tinyint02551 byte
bit011 byte/8bit column
decimal−10^38+110^381−15 to 17 bytes
numeric−10^38+110^381−15 to 17 bytes
money−922,337, 203, 685,477.5808+922,337, 203, 685,477.58078 bytes
smallmoney−214,478.3648+214,478.36474 bytes

Approximate numeric data types

The approximate numeric data type stores floating point numeric data. They are often used in scientific calculations.

Data TypeLower limitUpper limitMemoryPrecision
float(n)−1.79E+3081.79E+308Depends on the value of n7 Digit
real−3.40E+383.40E+384 bytes15 Digit

Date & Time data types

The date and time data types store data and time data, and the date time offset.

Data Type Storage size AccuracyLower RangeUpper Range
datetime8 bytesRounded to increments of .000, .003, .0071753-01-019999-12-31
smalldatetime4 bytes, fixed1 minute1900-01-012079-06-06
date3 bytes, fixed1 day0001-01-019999-12-31
time5 bytes100 nanoseconds00:00:00.000000023:59:59.9999999
datetimeoffset10 bytes100 nanoseconds0001-01-019999-12-31
datetime26 bytes100 nanoseconds0001-01-019999-12-31

If you develop a new application, you should use the timedatedatetime2 and datetimeoffset data types. Because these types align with the SQL Standard and more portable. In addition, the timedatetime2 and datetimeoffset have more seconds precision and datetimeoffset supports time zone.

Character strings data types

Character strings data types allow you to store either fixed-length (char) or variable-length data (varchar). The text data type can store non-Unicode data in the code page of the server.

Data Type Lower limitUpper limit Memory
char0 chars8000 charsn bytes
varchar0 chars8000 charsn bytes + 2 bytes
varchar (max)0 chars2^31 charsn bytes + 2 bytes
text0 chars2,147,483,647 charsn bytes + 4 bytes

Unicode character string data types

Unicode character string data types store either fixed-length (nchar) or variable-length (nvarchar) Unicode character data.

Data Type Lower limitUpper limit Memory
nchar0 chars4000 chars2 times n bytes
nvarchar0 chars4000 chars2 times n bytes + 2 bytes
ntext0 chars1,073,741,823 char2 times the string length

Binary string data types

The binary data types stores fixed and variable length binary data.

Data Type Lower limitUpper limit Memory
binary0 bytes8000 bytesn bytes
varbinary0 bytes8000 bytesThe actual length of data entered + 2 bytes
image0 bytes2,147,483,647 bytes

Other data types

Data Type Description
cursorfor variables or stored procedure OUTPUT parameter that contains a reference to a cursor
rowversionexpose automatically generated, unique binary numbers within a database.
hierarchyidrepresent a tree position in a tree hierarchy
uniqueidentifier16-byte GUID
sql_variantstore values of other data types
XMLstore XML data in a column, or a variable of XML type
Spatial Geometry typerepresent data in a flat coordinate system.
Spatial Geography typestore ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
tablestore a result set temporarily for processing at a later time

In this tutorial, you have learned about the brief overview of SQL Server data types. We will examine each data type in detail in the next tutorials.

Was this tutorial helpful?