Programmer's Rules

SQL Server

Data Types in SQL Server

Concise specification of SQL Server data types

SQL Serverjob interview70-761

In SQL Server, data types can be devided into the following categories:

a) Exact numeric - store data with precision. They include:

  - integers (grouped taking into account the total number of digits stored):

    * tinyint - stored in 1 byte, the range of values from 0 to 255,

    * smallint - stored in 2 bytes, the range of values from -32768 to 32768,

    * int - stored in 4 bytes, the range of values from -231 to 231-1,

    * bigint - stored in 8 bytes, the range of values from -263 to 263-1.

  - decimal data types (you can flexibly specify the total number of digits to be stored and the number of digits to the right of the decimal place):

    * decimal - stored in the range of 5 to 17 bytes, the range of values from -1038 to 1038 + 1,

    * numeric - thre is no functional difference between numeric and decimal. That data type is kept in order to be compatible with older versions of SQL Server.

  - data types for storing monetary or currency values with a scale of up to 4 decimal places:

    * money - stored in 8 bytes, the range of values from -263 to 263-1,

    * smallmoney - stored in 4 bytes, the range of values from -231 to 231-1.

  - boolean data type - for storing values true or false;

    * bit - stored in 1 byte. Value 1 responds to true, 0 responds to false.

b) Approximate numeric (data is stored in the form of so called scientific notation: x = M * 10E, see Wikiedia, which causes thet inacact values are stored):

    * float - takes the optional parameter, that specifies the number of bytes to store so called mantisa (M - in the above equation). THe size of matisa determines the size in disk. If the mantisa is between 1 and 24, float require 4 bytes. If mantisa is in the range of 25 to 53, float requires 8 bytes.

    * real - is the same as float with mantisa equal 24 (float(24)).

c) Binary data types - serve to keep binary data, such as serialized files, pictures, data streams. Thre are binary data types with fixed or variable length:

    * binary - fixed-length binary data up to 8000 bytes,

    * varbinary - variable-length binary data up to 8000 bytes,

    * image - fixed-length binary data up to 231-1 bajtów

d) Character data types - besides different ranges of storage they allow to cope with many languages, sorting and collation types.

    * char - fixed-length string data - max 8000 chars,

    * varchar - variable-length string data - max 8000 chars,

    * nchar - fixed-length Unicode string data - max 8000 chars,

    * nvarchar - variable-length Unicode string data - max 8000 chars,

    * text - is deprecated and is replaced with varchar(max),

    * ntext - is deprecated and is replaced with nvarchar(max),

e) Date and time data types:

    * date - stored in 3 bytes, the range of dates only from 0001-01-01 to 9999-12-31.

    * datetime - stored in 8 bytes, the range of dates with 24 hour time only from 1753-01-01 to 9999-12-31.

    * datetime2 - stored in variable number of bytes: from 6 to 8 bytes, stores date with 24 hour time from 0001-01-01 to 9999-12-31, the precision can be set up to 100 ns.

    * datetimeoffset - stored in 10 bytes with default precision of 100ns, stores date together with 24 hour time, allows writing time with time zone offset, e.g. 2018-08-20 22:12:15 +01:00.

    * smalldatetime - stored in fixed-length 4 bytes, contains 24 hour time with the precision of 1 minute, the range of dates from 1900-01-01 to 2079-06-06

    * time - stored in fixed-length 4 bytes, stores 24 hour time only, the precision of milisecods can be set in the range of 0 to 7 digits.

f) Other data types:

    * xml - stores data in XML format up to 2GB,

    * uniqueidentifier - stores so called GlobalUnique Identifiers (GUID) in 16 bytes.

    * hierarchyid - represents hierarchical order between data in a table. Internally is stored as varbinary of length up to 900 bytes.

    * rowversion - stores automatically generated 8 byte binary data tnat are incremented each time when a row is updated or inserted.

    * geometry - stores data in a two-dimentional coordinate system.

    * geography - stores data in a geographical coordinate system.

    * sql_variant - is a special data type to store embedded data types. Allows to for example store in one column types of integer, decimal and float.

    * cursor - is used to refer to cursor objects that allow to view tables row-by-row.

    * table - data type that allows storing of table variables - used to store temeprary query results.
Tutorial statistics