Introduction to SQL Server data types

In the query process and query results for SELECT, each column, variable, expression, and parameter has a related data type. The data type is used to specify the type of data that an object can hold.

The data types of SQL Server systems are: numeric types, date and time types, character types, and other data types.

First, numeric type

Depending on the range of values, the numerical type can be divided into integer, non-Integer, Boolean. The numerical model can also be divided into precise digital type and approximate numeral type according to its precision.

1. Integer type

(1) bigint: Long integer, the value range from -2^63 to 2^63-1, occupies 8 bytes.

(2) int: integer with a value range from -2^31 to 2^31-1, taking 4 bytes.

(3) smallint: Short integer, the value range from -2^15 to 2^15-1, occupies 2 bytes.

(4) Tinyint: Micro-integer, the value range from 0 to 255, occupies 1 bytes.
2. Non-integer type

(1) decimal and numeric, data data types with fixed precision and decimal digits.

(2) Money: currency type, accounting for 8 bytes, can have 4 decimal places.

(3) smallmoney, accounting for 4 bytes, can have 4 decimal places.

(4) Float: multi-precision floating-point type, depending on the number of decimal places in which the bytes are occupied.

(5) Real: single-precision floating-point type, which occupies 4 bytes.

3. Boolean type

(1) bit, a value of 0, 1, null one of the three. A string value of true and FALSE can be converted to the following bit value: True converts to 1,false to 0.

Second, date and time type

1. Date and Time type

(1) DateTime: The date range is from January 1, 1753 to December 31, 9999, and is accurate to 3.33 milliseconds and occupies 8 bytes.

(2) smalldatetime: the date range is from January 1, 1900 to June 6, 2079, accurate to minutes and occupies 4 bytes.

(3) DateTimeOffset: Date range from January 1 to December 31, 9999 of the first year of the year, accurate to 100 nanoseconds, and can specify the time zone offset, occupy 10 bytes. Introduced from SQL Server 2008.

(4) DateTime2: Date range from January 1 to December 31, 9999 of the first year of A.D., accurate to 100 nanoseconds, occupy 6 to 8 bytes. Introduced from SQL Server 2008.

2. Date type

(1) Date: Dates range from January 1 to December 31, 9999 in the first year of the year, accurate to 1 days, occupying 3 bytes. Introduced from SQL Server 2008.

3. Time-based

(1) Time: Accurate to 100 nanoseconds, and can specify a timezone offset, which takes up 5 bytes. Introduced from SQL Server 2008.

Description : DateTime is actually composed of 2 parts. The 1th part is a 4-byte integer that holds the difference between dates since the base date, and the 2nd is a 4-byte integer that holds the cumulative number of milliseconds since midnight.

Three, character type

1. String type

(1) Char: fixed-length string with 1 bytes per character and a maximum of 8,060 bytes.

(2) VARCHAR: variable-length string.

(3) Text: variable-length character type.

2. Unicode String type

(1) NCHAR: fixed-length Unicode string with 2 bytes per Unicode character.

(2) Nvarchar: variable-length Unicode string.

(3) NTEXT: variable-length Unicode character type with a maximum of 2^31-1 bytes.

3. Binary String type

(1) Binary: fixed-length binary data type

(2) varbinary: A variable-length binary data type.

(3) Image: A variable-length binary data type.

Note : ntext, text, and image data types will be removed in future versions of SQL Server, using nvarchar (max), varchar (max), and varbinary (max) instead.

iv. Other data Types (1) cursor: cursor.

(2) Timestamp: Time stamp, occupies 8. Typically used for row versioning.

(3) Uniqueidentifier:guid type, accounting for 16 bytes.

There are HierarchyID, sql_variant, XML, table and other data types.
Note: Using the timestamp syntax is not recommended, and subsequent versions of Microsoft SQL Server will remove the feature.

From "The MSSQL We chased together" blog

Introduction to SQL Server data types