Data types in SQL Server

In this section we will go over the available and applicable data types in Transact-SQL divided by categories.

NUMERIC

  • INT: Integer, exact-number data type with a maximum storage capacity of 4 bytes, which encompasses a range between -2,147,483,648 and 2,147,483,647 It is the main and most used type data to store integer numbers.

  • BIGINT: Integer numeric data with a maximum storage capacity of 8 bytes, which encompasses a range between -9.223.372.036.854.775.808 and 9.223.372.036.854.775.807. In order to save resources and define a more efficient model, bigint should only be used to store integer values that exceed the maximum value of other integer numeric data types.

  • SMALLINT: Integer numeric data with a maximum storage capacity of 2 bytes, which encompasses a range between -32,768 and 32,767.

  • TINYINT: Integer numeric data with a maximum storage capacity of 1 bytes, which encompasses a range between 0 and 255. It is the most efficient but also the most limited, which means that it must be used in specific cases, for example, to store a person’s age since no person reaches the age of 255.

  • BIT: Data type that can only store one of this values 0 (FALSE), 1 (TRUE), or NULL. It works the same way as the boolean type of other independent systems.

  • DECIMAL: Numerical data with fixed precision and scales. The precision will be between 1 and 38 and the storage size will vary depending on the precision, with the minimum number of bytes being 5 and the maximum number being 17.

  • NUMERIC: Data type functionally synonymous with DECIMAL, and can be used interchangeably.

  • MONEY: Data type that represents monetary or currency values with a maximum capacity of 8 bytes, which encompasses a range between -922.337.203.685.477.5808 and 922.337.203.685.477.5807.

  • SMALLMONEY: Data type that represents monetary or currency values with a maximum capacity of 4 bytes, which encompasses a range between -214.748.3648 and 214.748.3647.

  • FLOAT: Approximate floating point numeric data. Where n of FLOAT(n) will depend on the precision and maximum storage capacity 4 - 8 bytes. The value of n will be between 1 and 53, with 53 being the default value.

  • REAL: Approximate floating point numerical data. The real type will be the equivalent of FLOAT(24) which will define a storage capacity of 4 bytes.

CHARACTER STRINGS

NON UNICODE

  • CHAR: For storage of fixed-size alphanumeric characters. Where n of CHAR(n) indicates the size of the byte string from 1 to 8000.

  • VARCHAR: For storage of variable-size alphanumeric characters. Where n of VARCHAR(n) indicates the size of the byte string from 1 to 8000.

  • TEXT: For storing large values of fixed and variable length character and non-Unicode binary data. The storage size will be 2,147,483,647 bytes.

The text data type will be removed in future versions of SQL-Server so it is recommended to discard its use.

UNICODE CHARACTER

  • NCHAR: For storage of fixed-size alphanumeric characters. Where n of NCHAR(n) indicates the size of the byte string from 1 to 4000.

  • NVARCHAR: For storing variable-size alphanumeric characters. Where n of NVARCHAR(n) indicates the size of the byte string from 1 to 4000.

  • NTEXT: For storing large values of fixed and variable length character and Unicode binary data. The storage size will be 1,073,741,823 bytes.

The ntext data type will be removed in future versions of SQL-Server so it is recommended to discard its use now.

BINARY STRINGS

  • BINARY: For storage of fixed-length binary data. Where n of BINARY(n) indicates the length size, ranging from 1 to 8000 bytes.

  • VARBINARY: For storing variable-length binary data. Where n of VARBINARY(n) indicates the length size, ranging from 1 to 8000 bytes.

  • IMAGE: For storing variable-length binary data with a storage size of 2,147,483,647 bytes.

The image data type will be removed in future versions of SQL-Server so it is recommended to discard its use.

DATE AND TIME

  • DATE: To store a date in default format: YYYY-MM-DD. This format may be modified using input masks.

  • DATETIME: To store a date and time with fractions of a second in the default format YYYY-MM-DD hh:mm:ss. This format may be modified using input masks.

  • SMALLDATETIME: To store a date and time without fractions of a second in the default format YYYY-MM-DD hh:mm:ss. Seconds will always be O (:00).

  • DATETIME2: To store a date and time with a range and precision greater than DATETIME, in default format YYYY-MM-DD hh:mm:ss[nnnnnnn]. Said format can be modified using input masks.

  • DATETIMEOFFSET: To store a date and time with a range and precision like DATETIME2, additionally adding time zone recognition based on UTC (Coordinated Universal Time or Greenwich Mean Time). The default format will be YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm.

  • TIME: To store a time in default format: hh:mm:ss[.nnnnnnn].

OTHER DATA TYPES

  • CURSOR: To scroll through records and results of a query. See in more detail in the CURSOR section.

  • ROWVERSION: To display automatically generated binary numbers usually used to indicate the version of rows in a table.

  • HIERARCHYID: To represent position in a tree hierarchy.

  • UNIQUEIDENTIFIER: To store globally unique identifiers (GUID).

  • SQL_VARIANT: To store values of various data types supported by SQL Server.

  • XML: To store XML data.

  • TABLE: To temporarily store a set of rows and return the result with values of table.

  • SPACE GEOMETRY: To store planar spatial data in a Euclidean coordinate system.

  • SPACE GEOGRAPHY: To store ellipsoidal spatial data such as GPS latitude and longitude coordinates.