SQL Server common data types

Time:2020-10-30

In SQL server, every variable, parameter, expression, etc. has a data type. The data types provided by the system can be divided into several categories, as shown in Table 4-2.

Among them, bigint, SQL_ Variant and table are three new data types in SQL Server 2000. The following classification describes the various data types.

1、 Integer data type

The integer data type is one of the most commonly used data types.

    1、INT (INTEGER)

The int (or integer) data type stores all positive and negative integers from the 31st power of – 2 (- 2147483648) to the 31st power of 2 (2147483647).

Each int type data is stored in 4 bytes, of which 1 bit represents the positive and negative sign of the integer value, and the other 31 bits indicate the length and size of the integer value.

    2、SMALLINT

The smallint data type stores all positive and negative integers from the 15th power of – 2 (- 32768) to the 15th power of 2 (32767).

Each smallint type data takes up 2 bytes of storage space, in which 1 bit represents the positive and negative sign of the integer value, and the other 15 bits indicate the length and size of the integer value.

    3、TINYINT

The tinyint data type stores all positive integers from 0 to 255. Each tinyint type data takes up 1 byte of storage space.

    4、BIGINT

The bigint data type stores all positive and negative integers from – 2 ^ 63 (- 9223372036854775807) to 2 ^ 63-1 (9223372036854775807).

Each bigint type data takes up 8 bytes of storage space.  

2、 Floating point data type

The floating point data type is used to store decimal numbers. The floating-point data is stored in SQL server by round up (round up or rounding only).

The so-called rounding up means that when (and only if) the number to be rounded is a non-zero number, add 1 to the value on the least significant bit of the reserved number part, and carry the necessary carry. If a number is rounded up, its absolute value does not decrease.

For example: round 3.14159265358979 to 2 and 12 bits respectively, the results are 3.15 and 3.141592653590.

   1. Real data type

The real data type is accurate to the seventh decimal place and ranges from – 3.40e – 38 to 3.40e + 38. Each real type of data takes up 4 bytes of storage space.

    2、FLOAT

The float data type is accurate to the 15th decimal place and ranges from – 1.79e – 308 to 1.79e + 308. Each float type data takes up 8 bytes of storage space.  

The float data type can be written in the form of float [n]. N specifies the precision of float data. N is an integer value between 1 and 15. When n is taken from 1 to 7, a real type data is defined, and the system stores it with 4 bytes;
When n takes 8 to 15, the system considers it to be float type and stores it in 8 bytes.

    3、DECIMAL

The decimal data type can provide the actual storage space required for decimals, but there are limitations. You can use 2 to 17 bytes to store values from – 10 to the 38th power – 1 to the 38th power – 1 of 10.

It can be written in the form of decimal [P [S]], and P and s determine the exact scale and number of digits. Where p is the total number of digits (excluding the decimal point) of the value available for storage, the default value is 18; s is the number of digits after the decimal point, and the default value is 0.

For example, decimal (155) means there are 15 digits in total, including 10 integers and 5 decimals.    

    4、NUMERIC

The numeric data type is exactly the same as the decimal data type.
Note: in order to cooperate with the front-end development tools, the maximum data precision supported by SQL server is 28 bits by default. But it can be done by using commands sqlserver.exe Program to start SQL server, you can change the default precision.

The command syntax is as follows:

        SQLSERVR[/D master_device_path][/P precisim_leve1]

Example 4-4: starting SQL Server with maximum data accuracy 38

        sqlservr /d c:\ Mssql2000\data\master.dat /p38

/ * after using the / P parameter, if no specific precision value is specified, the default value is 38 bits. / *

3、 Binary data type

    1、BINARY

The binary data type is used to store binary data. It is defined as binary (n), where n represents the length of the data, ranging from 1 to 8000.

The size of binary type data must be specified when using, and it should be at least 1 byte. Binary data takes up n + 4 bytes of storage space.

When inputting data, the character “0x” must be added before the data as the binary identification. For example, if you want to input “ABC”, you should enter “0xabc”.

If the input data is too long, the excess part will be cut off. If the input data bits are odd, a 0 will be added after the start symbol “0x”. For example, “0xabc” above will be automatically changed to “0x0abc”.

    2、VARBINARY

Varbinary data type is defined as varbinary (n). It is similar to the binary type. The value of n is between 1 and 8000. If the input data is too long, the excess part will be cut off.

The difference is that the varbinary data type has the property of variable length, because the storage length of varbinary data type is the actual value length + 4 bytes. When a binary data type allows null values, it is treated as a varbinary data type.

In general, the binary data type is faster than varbinary data type because of its fixed length.

4、 Logical data type

Bit: bit data type takes 1 byte of storage space, and its value is 0 or 1. If you enter a value other than 0 or 1, it is treated as 1.
Bit type cannot be defined as a null value (a null value is a null or meaningless value).

5、 Character data type

The character data type is the most used data type. It can be used to store a variety of letters, number symbols, special symbols. In general, character type data should be preceded by single quotation marks’ or double quotation marks “.

    1 CHAR

Char data type is defined as char [(n). Each character and symbol stored in char type takes up one byte of storage space. N represents the storage space occupied by all characters, and the value of n is from 1 to 8000

It can hold 8000 ANSI characters. If you do not specify a value of N, the system defaults to 1. If the number of characters in the input data is less than N, the system will automatically add a space after it to fill the set space. If the input data is too long, the excess part will be cut off.

    2、NCHAR

The nchar data type is defined as nchar [(n)]. It is similar to the char type. The difference is that nchar data type N takes values from 1 to 4000. Because the nchar type uses the Unicode standard character set.  

Therefore, Unicode takes up more than two bytes of storage space for non-standard data types. The advantage of using Unicode standard is that because it uses two bytes as the storage unit, the capacity of one storage unit will be greatly increased. It can include all languages in the world. In a data column, Chinese, English, French, German, etc. can appear at the same time without coding conflict.

    3、VARCHAR

Varchar data type is defined as varchar n). It is similar to char type, n value is also 1 to 8000, if the input data is too long, the excess part will be cut off.
The difference is that varchar data type has the feature of variable length, because the storage length of varchar data type is the actual numerical length. If the number of characters of input data is less than N, the system will not add spaces after it to fill the set space.
In general, char data type is faster than varchar data type because of its fixed length.

   4、NVARCHAR

The nvarchar data type is defined as nvarchar [(n)]. It is similar to the varchar type. The difference is that the nvarchar data type adopts the Unicode standard character set, and the value of n is from 1 to 4000.

6、 Text and graphic data types

This type of data type is used to store large amounts of character or binary data.

    1、TEXT

Text data type is used to store a large number of text data, and its capacity is theoretically 1 to 2 to the 31th power-1 (2147483647) bytes. In practical application, it depends on the storage space of hard disk.

In the version before SQL Server 2000, a text object in the database actually stores a pointer, which points to a data page in the unit of 8KB (8192 bytes).  

These data pages are dynamically added and logically linked. In SQL Server 2000, text and image data are directly stored in the data rows of the table, rather than in different data pages.
This reduces the space used to store text and ima-ge types, and correspondingly reduces the amount of I / O that the disk processes for this type of data.

    2、 NTEXT

Ntext data type is similar to text. The difference is that ntext uses Unicode standard character set, so its theoretical capacity is 230-1 (1, 073, 741, 823) bytes.

   3、 IMAGE

The image data type is used to store a large number of binary data. Its theoretical capacity is 2 to the 31th power – 1 (2147483647) bytes. It stores data in the same mode as the text data type.

It is usually used to store OLE objects such as graphics, linking and embedding objects. When inputting data, like binary data type, the character “0x” must be added before the data as binary identification

7、 Date and time data type

    1 DATETIME

The datetime data type is used to store a combination of date and time. It can be stored from 0:00 on January 1, 1753 to 23:59:59 on December 31, 9999.

The above is the whole description of this article, I hope you like it.

Recommended Today

JavaScript closure (from an interview question)

First look at a piece of code and come up with your own answer: for(var i=0;i<5;i++){ setTimeout(function(){ console.log(i); },1000) } console.log(i); The answer to this question is 5555555.One 5 is output immediately, and five 5 are output after 1 second. If we want to change the answer here into: 5 1 2 3 4 5.Well, […]