SQL Server Data Types

A datatype ensures which type of data our column or variable can store in SQL Server. When we create any table or variable, it is required to specify the type of data it will store in addition to the name of each column. For example, in SQL Server, each table has many columns and contains specific data types for each column that should be integer, float, varchar, Boolean, etc.

How to use datatypes?

We are required to specify the data type in advance for each column of a table to ensure which type of data it can store. The data type specification also prevents the user from entering any unexpected or invalid information.
When we assign the appropriate data type to a column, we can make efficient use of memory by allocating only the amount of system memory required for the data in the relevant column.

We can use the following characteristics to define data type in SQL Server:

The type of values (fixed or variable) it represents.
The storage space depends on the values, which are a fixed-length or variable length.
Its storage value can be indexed or not.
How SQL Server performs a comparison of values of a particular data type.

Why use data types?

We can understand the importance of data type in SQL Server by taking a simple Sign-up page to create a new google account (only for reference purposes). This page shows the following input fields: First Name, Last Name, User Name, and Password.

These fields require the following data type characteristics:

First name and Last name: These fields should require only alphabets.
Username: This field can accept letters, numbers, and periods.
Password: This field must contain alphabets, numbers, and special characters.

Some other examples are:

Contact Number: This field must be numeric.
Pin Code: It can take both alphabets and numeric fields.

SQL Server supports a broad category of SQL standard data types based on the user's needs. These data types can be broken into the following categories:

Exact numeric
Approximate numeric
Date and time
Character strings
Unicode character strings
Binary strings
Other data types

Exact numeric data type

This data type will store exact numbers such as integer, decimal, and money. They have their own lower, upper limit, and memory requirements. They have their own memory needs, as well as a lower and upper limit. It is also necessary to use the lowest data type to save memory requirements. For example, the bit data type can be used to store true (1) or false (0) values. SQL Server categorizes this data type into nine types, which are given below:

Data Type	Descriptions	Lower Range	Upper Range	Storage
bit	It is an integer type that allows us to store 0, 1, and NULL values.	0	1	1 byte
tinyint	It allows us to store whole numbers from 0 to 255.	0	255	1 byte
smallint	It allows us to store whole numbers between -32768 to 32767.	-2^15 (-32,768)	2^15-1 (32,767)	2 byte
int	It allows to store whole numbers between -2,147,483,648 and 2,147,483,647	-2^31 (-2,147,483,648)	2^31-1 (2,147,483,647)	4 byte
bigint	It allows to store whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807	-2^63 (-9,223,372,036,854,775,808)	to 2^63-1 (9,223,372,036,854,775,807)	8 byte
Decimal(p, s)	It is used for scale and fixed precision numbers. The p parameter indicates the maximum total number of digits that can be stored (both to the left and the right of the decimal point). By default, it is 18 but can be in a range of 1 to 38. The s parameter indicates the maximum number of digits stored to the right of the decimal point. By default, its value is 0 but can be from 0 to p.	?10^38+1	10^381?1	5 to 17 bytes
Numeric(p, s)	It is similar to the decimal data type because both are synonyms.	?10^38+1	10^38?1	5 to 17 bytes.
smallmoney	It allows storing monetary or currency values.	-214,748.3648	214,748.3647	4 byte
money	It allows to store monetary or currency values.	-922,337,203,685,477.5808	922,337,203,685,477.5807	8 byte

Approximate numeric data types

This data type is used to store floating-point and real values. It is mainly used in scientific calculations.

Data Type	Lower range	Upper Range	Storage	Precision
float(n)	?1.79E+308	1.79E+308	It depends on n. 4 or 8 bytes	7 digit
real	?3.40E+38	3.40E+38	4 byte	15 digit

Date and Time data types

We use these data types to hold the temporal values such as date and time, including time offset in a column. We can categories this data type into six types, which are summarized in below table:

Data Type	Descriptions	Lower Range	Upper Range	Storage
date	It is used to store dates in SQL Server. By default, its format is YYYY-MM-DD, and its value is 1900-01-01.	0001-01-01	9999-12-31	3 bytes
datetime	It is used to store date and time with fractional seconds. Its default value is 1900-01-01 00:00:00. Its accuracy is in the increment of .000, .003, or .007 seconds. It is recommended to avoid this data type and use datetime2 instead.	1753-01-01	9999-12-31	8 bytes
datetime2	It specifies date and time with fractional seconds and an accuracy of 100 nanoseconds. It provides precision from 0 to 7 digits. By default, its precision is 7, and the format is YYYY-MM-DD hh:mm: ss[.fractional seconds].	0001-01-01 00:00:00	9999-12-31 23:59:59.9999999	6 to 8 bytes
datetimeoffset	It is the same as datetime2 with the addition of a time zone offset. Its timezone offset value is between -14:00 through +14:00.	0001-01-01 00:00:00	9999-12-31 23:59:59.9999999	10 bytes
smalldatetime	It specifies a date along with the time of day and an accuracy of 1 minute. Here time is calculated on a 24-hour clock, with seconds starting at zero (:00) and no fractional seconds.	1900-01-01 00:00:00	2079-06-06 23:59:59	4 bytes
time	It specifies time data only with an accuracy of 100 nanoseconds. It is based on a 24-hour clock without time zone awareness. By default, its format is hh:mm:ss[.nnnnnnn].	00:00:00.0000000	23:59:59.9999999	3 to 5 bytes

Character string data type

This data type allows us to define the character data type only, which can be fixed or variable in length. The following table illustrates all character string data type support in SQL Server:

Data Type	Descriptions	Lower Range	Upper Range	Storage
char(n)	It is used to store fixed-length non-Unicode character data.	0 characters	8000 characters	n bytes
varchar(n)	It is used to store variable-length non-Unicode character data.	0 characters	8000 characters	n bytes + 2 bytes
varchar(max)	It is a data type that stores variable-length data. It is recommended to avoid this data type unless required because of its huge memory storage.	0 characters	2^31 characters	n bytes + 2 bytes
text	It is a variable-length character string. It is also recommended to avoid this data type because it would be deprecated in future releases.	0 characters	2,147,483,647 chars	n bytes + 4 bytes

Unicode character string data types

This data type allows us to define the full range of Unicode character sets encoded in the UTF-16 character set. The following table illustrates all Unicode character string data type support in SQL Server:

Data Type	Descriptions	Lower Range	Upper Range	Storage
nchar It is used to store fixed-length Unicode character data.	0 characters	4000 characters	2 times n bytes
nvarchar	It is used to store variable-length Unicode character data.	0 characters	4000 characters 2 times	n bytes + 2 bytes
ntext	It is used to store variable-length Unicode string data. It is recommended to avoid this data type because it would be deprecated in future releases.	0 characters	1,073,741,823 characters	2 times the string length

Binary data types

This data type allows storing image, audio, and video files of fixed and variable length into a database location. It stores information in 0 and 1 format. The following table illustrates all binary string data type support in SQL Server:

Data Type	Descriptions	Upper Range	Storage
binary	It is used to store fixed-length binary strings.	8000 bytes	n bytes
varbinary	It is used to store variable-length binary string.	8000 bytes	The actual length of data entered + 2 bytes
image	It is similar to the varbinary data type that can store up to 2 GB. It is recommended to avoid this data type because it would be deprecated in future releases.	2,147,483,647 bytes

Special data types

SQL Server also provides some special data types that can be used according to our requirements. The below table illustrates all special datatypes:

Data Type	Descriptions
cursor	It is an object data type used for variable and stored procedure OUTPUT parameters containing a reference to a cursor. In addition, it stores temporary table information.. Its output is a column of sp_cursor_list and sp_describe_cursor that returns the name of the cursor variable.
rowversion	It's a data type that returns automatically generated unique binary numbers within a database. It is commonly used for version-stamping table rows. Its memory storage is 8 bytes. It's an increasing number that doesn't preserver time and date.
hierarchyid	It is a system data type with variable length representing a position in a hierarchy. Its value represents the position in a tree hierarchy.
uniqueidentifier	This data type indicates the 16 byte GUID. It is used to convert a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Here, x is a hexadecimal digit (0-9 and a-f).
XML	This data type is used to store XML data in a SQL Server table. Its storage limit is 2 GB.
Spatial Geometry Types	It is a spatial planar data type used to represents data in a flat (Euclidean) coordinate system.
Spatial Geography Types	It is a geography spatial data type used for storing ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates.
sql_variant	This data type is used to store values of other SQL Server data types.
table	It is a spatial data type used to store the temporary result in a table-valued function. This data is used for later processing. We can use it by using the syntax as DECLARE @local_variable. It can also be used in functions, stored procedures, and batches.

Conclusion

This article gives a brief overview of all data types supported in SQL Server. After knowing these data types, we will be aware of using the appropriate data type for better storage, query processing, and data retrieval.

Next TopicSQL Server Login Database

← prev next →