SQL BASIC
SQL JOINS
SQL ADVANCED
SQL REFERENCE
Advertisements

SQL Server Data Types

The following sections describe the data types supported by SQL Server.

SQL Server Data Types

The data types supported by the Microsoft SQL Server can be grouped into three main categories: string, numeric and date/time data types.

String Data Types

String data types are normally used to store names, addresses, descriptions or any value that contains letters and numbers including binary data, like image or audio files.

Data type Description
char(n) Stores fixed-length character string. Maximum length 8,000 characters.
varchar(n) Stores variable-length character string. Maximum length 8,000 characters.
varchar(max) Stores variable-length character string. Here, max indicates that the maximum storage size is 2 GB.
text Stores variable-length character string. Maximum storage size is 2 GB.
nchar Stores fixed-length Unicode string. Maximum length 4,000 characters.
nvarchar Stores variable-length Unicode string. Maximum length 4,000 characters.
nvarchar(max) Stores variable-length Unicode string. Here, max indicates that the maximum storage size is 2 GB.
ntext Stores variable-length Unicode string. Maximum storage size is 2 GB.
binary(n) Stores fixed-length binary data. Maximum storage size is 8,000 bytes.
varbinary(n) Stores variable-length binary data. Maximum storage size is 8,000 bytes.
varbinary(max) Stores variable-length binary data. Here, max indicates that the maximum storage size is 2 GB.
image Stores variable-length binary data. Maximum storage size is 8,000 bytes.

Numeric Data Types

Numeric data types are normally used to store data like price, salary etc.

Data type Description
bit Allows you to store a value 1, 0, or NULL.
tinyint Stores integer values in the range from 0 to 255.
smallint Stores integer values in the range from -32,768 to 32,767.
int Stores integer values in the range from -2,147,483,648 to 2,147,483,647.
bigint Stores integer values in the range from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
decimal(p,s) Stores fixed precision and scale numbers. Valid values are from 10^38 +1 through 10^38 - 1. See below for more details.
numeric(p,s) The numeric datatype is functionally equivalent to decimal.
smallmoney Allows you to store monetary or currency values accurately in the range from -214,748.3648 to 214,748.3647.
money Allows you to store monetary or currency values accurately in the range from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
float(n) Stores floating point numeric values. Valid values are from -1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308.
real Stores floating point numeric values. Valid values are from -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38.

When declaring a or decimal or numeric column, the precision and scale can be specified, like decimal(p,s) or numeric(p,s), where the p or precision indicates the maximum total number of digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 to 38. The default precision is 18.

Whereas, the s or scale indicates the maximum number of digits that can be stored to the right of the decimal point. This number is subtracted from p to determine the maximum number of digits to the left of the decimal point. Scale must be a value from 0 to p. The default scale is 0.

For example, the price decimal(6,2) column can store any value with six digits and two decimals i.e. in the range from -9999.99 to 9999.99.


Date and Time Data Types

Date and Time data types are normally used to store data like date of birth, hiring date, date and time when a record is created or updated inside table, etc.

Data type Description
date Stores a date value in the range from 0001-01-01 (January 1, 1) to
9999-12-31 (December 31, 9999).
time Stores time of a day with the accuracy of 100 nanoseconds. Valid values are from 00:00:00.0000000 to 23:59:59.9999999.
datetime Stores a combined date and time value with an accuracy of 3.33 milliseconds. Valid date range for datetime is from 1753-01-01 (January 1, 1753) to 9999-12-31 (December 31, 9999).
datetime2 The datetime2 is an extension of the datetime data type that has a larger date range. Valid date range for datetime2 is from 0001-01-01 (January 1, 1) to 9999-12-31 (December 31, 9999).
smalldatetime Stores a combined date and time value with an accuracy of 1 minute. Valid date range for smalldatetime is from 1900-01-01 (January 1, 1900) to
2079-06-06 (June 6, 2079).
datetimeoffset Same as datetime2 with the addition of a time zone offset. Default format is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. Valid range for time zone offset is from -14:00 to +14:00.
timestamp In SQL Server timestamp is the synonym for the rowversion data type which automatically generate, unique binary numbers within a database. The rowversion is generally used for version-stamping table rows.

Note: Every time that a row with a rowversion column is inserted or modified inside a table, the incremented database rowversion value is inserted in the rowversion column. A table can have only one rowversion column.

Advertisements
Bootstrap UI Design Templates