SQL Server Data Types

The SQL Server data types determine the kind and range of data that a column in a table can store (or a local variable, parameter, etc.). In this article, we will give a brief introduction to SQL Server data types, sizes & ranges. In subsequent tutorials, we look at each of these data types in more detail.

SQL Server provides several data types. We can divide them into five types.

  1. String Data Types
  2. Unicode string data types
  3. Exact Numeric Data Types
  4. Approximate Numeric Data Types
  5. Date/Time Data Types
  6. Binary Strings
  7. Other Data Types

String Data Types

The string data types allow us to store strings of one or more characters. You can store strings, numbers, symbols & special characters, etc. They allow us to store either fixed-length strings (char/nchar) or variable-length strings (varchar/nvarchar).

String Data Types

Char & varchar are the string or character data types in SQL Server. We use them to store the string data which includes letters. numbers, symbols, special characters, etc. Char is a fixed width data type while Varchar is a variable-length Data Type.

Data TypeLower limitUpper limitStorage
char0 bytes8000 bytesn bytes
varchar0 bytes8000 bytessize of the string in bytes + 2 bytes
varchar(max)0 bytes2^31 (2GB) bytessize of the string in bytes + 2 bytes

The text data type will be eventually discarded by SQL Server, Hence not listed above. Use varchar(max) instead.

Unicode String Data Types

nchar & nvarchar (nvarchar(max)) are the Unicode string data types in SQL Server. They are similar to char & varchar but stores the strings in Unicode encoding. nchar is a fixed width data type while nvarchar is a variable-length dataType. 

Data TypeLower limitUpper limitStorage
nchar0 bytes8000 bytes2 * n bytes
nvarchar0 bytes8000 bytes2* n bytes + 2 bytes
ntext0 bytes2,147,483,647 bytes2*n bytes

Numeric Data Types

The number data types used for storing numeric data types. You can use these data types in arithmetic operations.

Exact Numeric Data Types

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

Integer numbers are whole numbers. They do not contain any decimal places. The sql server data types have five integer data types. They are bit, tinyint, smallint, int & bigint.

The smallmoney & money are used to represent monetary or currency values. They store data up to four decimal places.

The decimal and numeric data types store numbers that have fixed precision and scale. Note that decimal and numeric are synonyms.

Data TypeRange fromRange toMemory
bit011 byte
If there are other bit columns in table, they are also merged in 1 byte (upto 8)
tinyint02551 byte
smallint−2^15 (−32,767)2^15 (−32,768)2 bytes
int−2^31 (−2,147, 483,648)2^31−1 (−2,147, 483,647)4 bytes
bigint−2^63 (−9,223,372, 036,854,775,808)2^63−1 (−9,223,372, 036,854,775,807)8 bytes
smallmoney−214,478.3648+214,478.36474 bytes
money−922,337, 203, 685,477.5808+922,337, 203, 685,477.58078 bytes
decimal−10^38+110^381−15 to 17 bytes
numeric−10^38+110^381−15 to 17 bytes

Approximate Numeric Data Types

Float & Real Data Types in SQL Server uses the floating-point number format. Real is a Single Precision Floating Point number, while Float is a Double Precision Floating Point number. The Floating point numbers can store very large or very small numbers than decimal numbers. But this trade-off comes at the cost of precision

Data TypeRange fromRange toValue of nsizePrecision
float−1.79E+3081.79E+3081-244 bytes7 Digit
25-538 bytes15 digits
real−3.40E+38 3.40E+384 bytes15 Digit

Date/Time Data Types

SQL Server supports several types to handle date and time.

The Date Data Type stores only date without time.

Use Time data type to store the only time of a day, without date & time zone and using 24 hours format

The DateTime2 Data Type stores both date & time together. The time part stores the fractional seconds Up to 7 decimal places. No of Decimal places is optional (Precision) and you can specify it while defining the DateTime2.

DateTimeOffset data type stores the date & time along with the Time Zone Offset. It is similar to DateTime2

The DateTime & SmallDateTime in SQL Server are the data types that store both date & time together. The time is based on the 24 hours clock.

Data TypeLower RangeUpper RangesizeAccuracy
date0001-01-019999-12-313 bytes, fixed1 day
time00:00:00.000000023:59:59.99999995 bytes100 nanoseconds
datetime20001-01-019999-12-316 bytes100 nanoseconds
datetimeoffset0001-01-019999-12-3110 bytes100 nanoseconds
datetime1753-01-019999-12-318 bytesRounded to increments of .000, .003, .007
smalldatetime1900-01-012079-06-064 bytes, fixed1 minute

Binary Strings

Binary, Varbinary & varbinary(max) are the binary string data types in SQL Server. These data types are used to store raw binary data up to a length of (32K – 1) bytes

Data TypeLower limitUpper limitMemory
binary0 bytes8000 bytesn bytes
varbinary0 bytes8000 bytesThe actual length of data entered + 2 bytes
image0 bytes2,147,483,647 bytes

Other Data Types

Apart from the above, there are few other data types that SQL Server provides. They are listed below

Data typeDescription
cursorThe cursor is the data type we use to store the reference to a set of data that we retrieve using a SELECT statement. We can use it to read one row at a time
rowversionThe rowversion is a special data type, which we can use as a mechanism for version-stamping table rows. The SQL Server automatically generates the values for this in the database, when we insert a new row.
hierarchyidThe hierarchyid data type is a variable-length, system data type. Use hierarchyid to represent the position in a hierarchy.
uniqueidentifierThe data type to store the 16-byte GUID column
sql_variantA data type that stores values of various SQL Server-supported data types.
xmlxml Is the data type that stores XML data. You can store xml instances in a column, or a variable of xml type.
Spatial Geometry TypesThe data type, geometry represents data in a Euclidean (flat) coordinate system.
Spatial Geography TypesThe geography spatial data type, geography represents data in a round-earth coordinate system
tableThe table data type is primarily used for temporarily storing a set of rows that are returned as the table-valued function result set.

References

Data Types

Read More

Leave a Comment

Your email address will not be published.

Scroll to Top