Ms sql server data types(numeric, character, date and time, Unicode, binary and special data types)

Ms Sql server data types include numeric, character, date and time, Unicode data types, binary data types, and special data types. They include several sub-types, which are introduced one by one below.

Ms sql server data types

One, Ms sql server numeric data types

1. integer

tinyint: store an integer between 0 and 255, which only occupies one byte, usually used when there are not many(options) categories;

smallint: short integer, which stores an integer between -32678 and 32767, which occupies 2 bytes, and is usually used when the value range can be determined in between;

int: store an integer between -2,147,483,648 — 2,147,483,647, which only occupies 4 bytes, a common type;

2. floating point(decimal)

float: is an approximate numeric type, storing any number between -1.79E+308 — 1.79E+308, occupying 4 bytes, single precision;

real: is also an approximate numeric type, storing floating-point numbers between -3.40E+38-3.40E+38, occupying 4 bytes, single precision;

decimal: Exact numeric type, storing data with a fixed precision and range between -10³⁸-1 — 10³⁸-1, occupying 2-17 bytes, and having two parameters: p(precision) and s(number of decimal places);

numeric: same as decimal;

3. currency type

smallmoney: used to store money and currency values, ranging from -214748.3648 to 214748.3647, occupying 4 bytes, which can be accurate to one ten thousandth of a currency unit;

money: used to store money and currency values, ranging from -2⁶³(-922,337,203,685,477.5808) to 2⁶³-1(+922,337,203,685,477.5807), occupying 8 bytes, which can be accurate to one ten thousandth of a currency unit;

Two, Ms sql server boolean data type

Bit: used to store boolean values, it only occupies 1 bit, the value is 0(false) or 1(true);

Three, Ms sql server character data types

char: used to store non-unicode data of specified length, char[(n)], n must be a value between 1 and 8000, that is, it can store up to 8000 English letters and 4000 DBCS characters. If char(8), there are actually only 6 English letters, which also account for 8 bytes;

varchar: similar to char, the difference is that it stores variable-length characters, and how much space is allocated depends on how many characters there are. For example, when varchar(30) is defined, there are actually only 10 characters and only 10 characters are allocated;

Text: used to store a large number of variable-length non-Unicode characters, up to 2³¹-1 characters;

nchar: is used to store unicode data of a specified length. Unlike char, it takes up double storage space;

nvarchar: similar to nchar, the difference is that variable-length characters are stored, and how much space is allocated depends on how many characters there are;

ntext: used to store a large number of variable-length Unicode characters, which takes up double the storage space and can store up to 2³⁰-1 characters;

Four, Ms sql server date and time data types

datetime: used to store the date and time, the range is from January 1, 1753 to December 31, 9999, accurate to three hundredths of a second(or 3.33 milliseconds);

smalldatetime: used to store the date and time, the range is from January 1, 1900 to June 6, 2079, accurate to 1 minute;

Five, Ms sql server binary data types

binary: used to store fixed-length binary data, the maximum length is 8000 bytes;

varbinary: used to store variable-length binary data, the maximum length is 8000 bytes;

image: used to store variable-length binary data, the maximum length is 2³¹-1(2,147,483,647) bytes;

Six, Ms sql server special data types

cursor: cursor reference;

 timestamp: used to indicate the relative order of data modification in the database, expressed in binary projection format, there can only be one timestamp column in a table;

uniqueidentifier: used to store a globally unique identifier(GUID), which consists of 16-byte hexadecimal numbers;

table: used to store temporary result sets;

Sql_variant: used to store the values of data types(except text, ntext, timestamp and sql_variant) supported by MsSQL Server.