An Overview of SQL Server Data Types (Transact-SQL)

Summary: In this article, you will learn SQL Server Data Types such as various data type including character string, unicode character string, numeric, binary string, date & time, and other data types.

SQL Server Data Tpes Introduction

The data type of a column in SQL server defines that what value the column will hold such as integer, binary, character, money, date and time, and so on.

In SQL Server, a column, parameter and variable holds a value that related with a type, or also called as a data type. Furthermore, A data type is an attribute which specified the type of data that these objects can store. It can be store value such as an integer, monetary, character string, 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., declaring a variable or defining a column.

Categories of SQL Server Data Types

    There are the following SQL data types categories, which SQL Server supports:

  • Exact numeric: int, decimal, numeric, bit, tinyint, smallint, bigint, money and smallmoney
  • Approximate numeric: float and real
  • Date and time: date, time, smalldatetime, DateTime, datetime2, and datetimeoffset.
  • Character strings: text, char and varchar
  • Unicode character strings: Ntext, Nchar and Nvarchar
  • Binary strings: image binary and varbinary
  • Other data types: table, XML, Cursor, rowversion, hierarchyid, sql_variant, uniqueidentifier, geography and Spatial

The following given below Diagram illustrates the SQL Server data types system:
SQL Server Data Types

.
Note:Important Note: SQL Server will remove text, ntext and image data types in its future version. so, you must avoid using these data types and you can use the varbinary(max), varchar(max) and nvarchar(max), data types instead.

Character Strings Data Types

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

Data Types Lower Limit Upper Limit Memory
char 0 chars 8000 chars n bytes
varchar 0 chars 8000 chars n bytes + 2 bytes
varchar (max) 0 chars 2^31 chars n bytes + 2 bytes
text 0 chars 2,147,483,647 chars n bytes + 4 bytes

Unicode Character String Data Types

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

Data Types Lower Limit Upper Limit Memory
nchar 0 chars 4000 chars 2 times n bytes
nvarchar 0 chars 4000 chars 2 times n bytes + 2 bytes
ntext 0 chars 1,073,741,823 char 2 times the string length

Exact Numeric Data Types

Exact numeric data types store exact numbers like decimal, integer, bigint, bit or monetary amount and so on.

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

There are the following table illustrates the characteristics of the exact numeric data types:

Data Types Lower Limit Upper Limit Memory
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
bigint −2^63 (−9,223,372, 036,854,775,808) 2^63−1 (−9,223,372, 036,854,775,807) 8 bytes
tinyint 0 255 1 byte
bit 0 1 1 byte/8bit column
numeric −10^38+1 10^381−1 5 to 17 bytes
decimal −10^38+1 10^381−1 5 to 17 bytes
money −922,337, 203, 685,477.5808 +922,337, 203, 685,477.5807 8 bytes
smallmoney −214,478.3648 +214,478.3647 4 bytes

Approximate Numeric Data Types

The approximate numeric data type can stores the floating point numeric data. These data type are often used in scientific calculations.

Data Types Lower Limit Upper Limit Memory Precision
float(n) −1.79E+308 1.79E+308 Depends on the value of n 7 Digit
real −3.40E+38 3.40E+38 4 bytes 15 Digit

Binary String Data Types

The binary string data types can stores the fixed and variable length binary data.

Data Types Lower Limit Upper Limit Memory
binary 0 bytes 8000 bytes n bytes
varbinary 0 bytes 8000 bytes The actual length of data entered + 2 bytes
image 0 bytes 2,147,483,647 bytes

Date & Time Data Types

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

Data Types Storage Size Accuracy Lower Range Upper Range
date 3 bytes, fixed 1 day 0001-01-01 9999-12-31
time 5 bytes 100 nanoseconds 00:00:00.0000000 23:59:59.9999999
datetime 8 bytes Rounded to increments of .000, .003, .007 1753-01-01 9999-12-31
datetime2 6 bytes 100 nanoseconds 0001-01-01 9999-12-31
smalldatetime 4 bytes, fixed 1 minute 1900-01-01 2079-06-06
datetimeoffset 10 bytes 100 nanoseconds 0001-01-01 9999-12-31

If you develop a new application, then you should use the date, time, datetime2 and datetimeoffset data types. as a result of these types align with the SQL Standard and more portable. furthermore, in addition, the time, datetimeoffset and datetime2 have a lot seconds precision and datetimeoffset supports time zone.

Other Data Types

Data Types Storage Size
cursor for the variables or stored procedure OUTPUT parameter that contains a reference to a cursor
rowversion show up automatically generated, unique binary numbers within a database.
hierarchyid characterize a tree position in a tree hierarchy
uniqueidentifier 16-byte GUID
sql_variant store values of other data types
XML To store XML data in a column, or the variable of XML type
Spatial Geometry type represent data in a flat coordinate system.
Spatial Geography type store ellipsoidal (round-earth) data, like GPS latitude and longitude coordinates.
table store a result set short-term for processing at a later time

Conclusion: SQL Server Data Types

you have learned about the brief overview of SQL Server data types such as including character string, unicode character string, numeric, binary string, date & time, and other data types etc. I hope you will enjoy! this article.

About Author

admin