Shulou(Shulou.com)05/31 Report--

This article introduces what common data types there are in SQL Server, the content is very detailed, interested friends can refer to, hope to be helpful to you.

I. 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 (- 2,147,483,648) to the 31st power of 2-1 (2,147,483647).

Data of each INT type is stored in 4 bytes, where 1 bit represents the sign of the integer value, and the other 31 bits represent the length and size of the integer value.

2 、 SMALLINT

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

Each SMALLINT type data takes up 2 bytes of storage space, where 1 bit represents the sign of the integer value, and the other 15 bits represent 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 of data takes up 1 byte of storage space.

4 、 BIGINT

The BIGINT data type stores all positive and negative integers from-2 ^ 63 (- 9,223,372,036,854,775,807) to 2 ^ 63-1 (9,223,372,036,854,775,807).

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

2. Floating point data type

The floating-point data type is used to store decimal decimals. Floating-point data is stored in SQL Server by rounding up (Round up or rounding only).

The so-called rounding 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 out the necessary rounding. If a number is rounded, its absolute value will not decrease.

For example, 3.14159265358979 is rounded by 2 bits and 12 bits respectively, and the results are 3.15 and 3.141592653590.

1. REAL data type

The REAL data type can be accurate to the seventh decimal place, ranging 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 can be accurate to the 15th decimal place, ranging from-1.79E-308 to 1.79E + 308. Each FLOAT type of data takes up 8 bytes of storage space.

The FLOAT data type can be written as FLOAT [n]. N specifies the precision of the FLOAT data. N is an integer value between 1 and 15. When n takes 1 to 7, it actually defines a data of type REAL, and the system stores it in 4 bytes; when n takes 8 to 15:00, the system thinks it is of type FLOAT and stores it in 8 bytes.

3 、 DECIMAL

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

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

For example: decimal (155), which means a total of 15 digits, including 10 integer digits and 5 decimal places.

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, SQL Server supports a maximum of 28 bits of data precision by default. However, you can use the command to execute the sqlserver.exe program to start SQL Server, which can change the default precision.

The command syntax is as follows:

SQLSERVR [/ D master_device_path] [/ P precisim_leve1]

Example 4-4: start SQL Server with the most big data precision 38

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

/ * if no specific precision value is specified after the / P parameter is used, it defaults to 38 bits. / *

III. Binary data types

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, with values ranging from 1 to 8000.

You must specify the size of the BINARY type data when using it, which should be at least 1 byte. BINARY type data takes up 4 bytes of storage space.

When entering data, you must precede the data with the character "0X" as a binary identity. For example, to enter "abc", you should enter "0xabc".

If the input data is too long, the excess will be truncated. If the number of data bits entered is odd, a 0 will be added after the starting symbol "0X". For example, the above "0xabc" will be automatically changed to "0x0abc" by the system.

2 、 VARBINARY

The VARBINARY data type is defined as VARBINARY (n). It is similar to the BINARY type, with a value of 1 to 8000, and if the input data is too long, the excess will be truncated.

The difference is that the VARBINARY data type has a variable length, because the storage length of the VARBINARY data type is the actual numeric length + 4 bytes. When the BINARY data type allows NULL values, it is considered the VARBINARY data type.

In general, because the length of the BINARY data type is fixed, it is faster than the VARBINARY type.

IV. Logical data types

BIT: the BIT data type takes up 1 byte of storage and has a value of 0 or 1. If you enter a value other than 0 or 1, it is considered 1. The BIT type cannot be defined as a NULL value (the so-called NULL value is a null or meaningless value).

Character data types

The character data type is the most frequently used data type. It can be used to store all kinds of letters, numeric symbols and special symbols. In general, character type data must be preceded by single or double quotation marks.

1 CHAR

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

It can hold 8000 ANSI characters. If you do not specify an n value, the system default value is 1. If the number of characters in the input data is less than n, the system automatically adds a space after it to fill the set space. If the input data is too long, the excess will be truncated.

2 、 NCHAR

The NCHAR data type is defined as NCHAR [(n)]. It is similar to the CHAR type. The difference is that the value of the NCHAR data type n is 1 to 4000. Because the NCHAR type uses the UNICODE standard character set (CharacterSet).

The UNICODE standard requires two bytes of storage per character, so it takes up twice as much storage space as non-UNICODE standard data types. The advantage of using the UNICODE standard is that because it uses two bytes as a storage unit, the capacity of one storage unit is greatly increased, which can include all languages all over the world, and Chinese, English, French, German and so on can appear at the same time in a data column without coding conflicts.

3 、 VARCHAR

The VARCHAR data type is defined as VARCHARn). It is similar to the CHAR type, with a value of 1 to 8000, and if the input data is too long, the excess will be truncated. The difference is that the VARCHAR data type has a variable length, because the storage length of the VARCHAR data type is the actual numeric length, and if the number of characters in the input data is less than n, the system will not add spaces to fill the set space. In general, because the length of the CHAR data type is fixed, it is faster than the VARCHAR type.

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 uses the UNICODE standard character set (Character Set), with n values ranging from 1 to 4000.

VI. Text and graphic data types

These data types are used to store large amounts of character or binary data.

1 、 TEXT

TEXT data type is used to store a large amount of text data, its capacity is theoretically 1 to 2 to the 31st power-1 (2,147,483,647) bytes, in practical application depends on the storage space of the hard disk.

In previous versions of SQL Server 2000, a TEXT object in the database actually stored a pointer to individual data pages (Data Page) in 8KB (8192 bytes).

These data pages are dynamically added and logically linked. In SQL Server 2000, data of TEXT and IMAGE types are stored directly in the data rows of the table, rather than in different data pages. This reduces the amount of space used to store TEXT and IMA- GE types, and correspondingly reduces the number of Icano disks that process this type of data.

2 、 NTEXT

NTEXT data type is similar to TEXT. The difference in type similarity is that the NTEXT type uses the UNICODE standard character set (Character Set), so its theoretical capacity is 230-1 (1,073,741,823) bytes.

3 、 IMAGE

The IMAGE data type is used to store large amounts of binary data Binary Data. Its theoretical capacity is 2 to the 31st power-1 (2147483647) bytes. It stores data in the same mode as the TEXT data type.

Commonly used to store graphics and other OLE Object Linking and Embedding, object connection and embedding) objects. When entering data, like the BINARY data type, you must precede the data with the character "0X" as a binary identity

VII. Date and time data types

1 DATETIME

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

About which common data types in SQL Server are shared here, I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.