SQL Server common data types, SQL Server

SQL Server common data types, SQL Server

Each variable, parameter, and expression in SQL Server has a data type. The data types provided by the system are classified into several categories, as shown in Table 4-2.

BIGINT, SQL _VARIANT, and TABLE are the three new data types added in SQL Server 2000. The following categories describe various data types.

I. Integer Data Type

Integer data is one of the most common data types.

1. INT (INTEGER)

INT (or INTEGER) Data Type stores all positive and negative integers from the 31 Power (-2,147,483,648) of-2 to the 31 Power-1 (2,147,483,647) of 2.

Data of each INT type is stored in four bytes. One digit indicates the positive and negative signs of the integer, And the other 31 digits indicate the length and size of the integer.

2. SMALLINT

SMALLINT stores all positive and negative integers from the 15th power (-32,768) of-2 to the 15th power-1 (32,767) of 2.

Each SMALLINT data occupies 2 bytes of storage space. One digit indicates the positive and negative signs of the integer, and the other 15 digits indicate the length and size of the integer.

3. TINYINT

TINYINT data type stores all positive integers from 0 to 255. Each TINYINT data occupies 1 byte of storage space.

4. BIGINT

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 data occupies 8 bytes of storage space.

Ii. Floating Point Data Type

The floating point data type is used to store decimal places. The data of floating point values is stored in SQL Server in the Round-robin (Round up) mode.

When (and only if) the number to be rounded is a non-zero number, add 1 to the value in the lowest valid bit of the reserved number, and carry out the necessary carry. If a number is rounded up, its absolute value is not reduced.

For example, round 3.14159265358979 by two or 12 digits, respectively. The result is 3.15 and 3.141592653590.

1. REAL Data Type

The REAL data type can be accurate to 7th decimal places in the range of-3.40E-38 to 3.40E + 38. Each REAL data occupies 4 bytes of storage space.

2. FLOAT

The FLOAT data type can be precise to 15th decimal places, ranging from-1.79E-308 to 1.79E + 308. Each FLOAT data occupies 8 bytes of storage space.

FLOAT data can be written as FLOAT [n. N specifies the FLOAT data precision. N is an integer between 1 and 15. When n is 1 to 7, a REAL data type is defined, and the system stores it in four bytes;
When n is 8 to 15, the system considers it as a FLOAT type and stores it in 8 bytes.

3. DECIMAL

The DECIMAL data type can provide the actual storage space required by decimals, but it also has certain limitations, you can use 2 to 17 bytes to store values between the power-38 and the power-1 from-10 to the power-1 from-10.

It can be written as DECIMAL [p [s]. p and s determine the exact ratio and number. P indicates the total number of digits (excluding the decimal point) of the value that can be stored. The default value is 18. s indicates the number of digits after the decimal point. The default value is 0.

For example, decimal (15 5) indicates a total of 15 digits, with an integer of 10 digits and a decimal of 5 digits.

4. NUMERIC

The NUMERIC data type is exactly the same as the DECIMAL data type.
Note: To work with front-end development tools, SQL Server supports a maximum of 28 data precision by default. However, you can use the command to execute the sqlserver.exe program to start SQL Server and 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 maximum data precision 38

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

/* If no specific precision value is specified after the/P parameter is used, the default value is 38 BITs ./*

Iii. binary data type

1. BINARY

The BINARY data type is used to store BINARY data. The format is BINARY (n). n indicates the data length. The value ranges from 1 to 8000.

The size of BINARY data must be specified. The value must be at least one byte. BINARY data occupies n + 4 bytes of storage space.

The character "0X" must be added before data input as a binary identifier. For example, to enter "abc", enter "0 xabc ".

If the input data is too long, the excess part is truncated. If the number of digits entered is an odd number, a 0 value will be added after the starting symbol "0X". For example, the above "0 xabc" 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. n ranges from 1 to 8000. If the input data is too long, the excess part is 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 value Length + 4 bytes. If the BINARY data type allows NULL, it is considered as the VARBINARY data type.

Generally, BINARY data is faster than VARBINARY because of its fixed length.

Iv. Logical Data Types

BIT: the BIT data type occupies 1 byte of storage space. Its value is 0 or 1. If you enter a value other than 0 or 1, it is regarded as 1.
BIT type cannot be defined as NULL value (so-called NULL value refers to a NULL value or meaningless value ).

V. Character Data Types

Character data types are the most commonly used data types. It can be used to store various types of letters, numbers, and special symbols. Generally, single quotation marks or double quotation marks must be added before and after character data ".

1 CHAR

The CHAR data type is defined as CHAR [(n ). Each character and symbol stored as CHAR occupies one byte of storage space. N indicates the storage space occupied by all characters. n ranges from 1 to 8000,

It can contain 8000 ANSI characters. If n is not specified, the default value is 1. If the number of characters in the input data is less than n, the system automatically adds spaces to fill the set space. If the input data is too long, the excess part is 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 NCHAR Data Type n is 1 to 4000. Because the NCHAR type adopts the UNICODE standard character set (CharacterSet ).

The UNICODE standard requires that each character occupies two bytes of storage space, so it occupies twice the storage space than the non-UNICODE standard data type. The advantage of using the UNICODE standard is that it uses two bytes as the storage unit, and the capacity of one storage unit is greatly increased. It can include all the languages and texts in the world, in a data column, Chinese, English, French, and German can appear at the same time without encoding conflicts.

 3. VARCHAR

The VARCHAR data type is defined as VARCHARn ). It is similar to the CHAR type, and the value of n is also 1 to 8000. If the input data is too long, the excess part is truncated.
The difference is that the VARCHAR data type has a variable length feature, because the storage length of the VARCHAR data type is the actual value length, if the number of characters of the input data is less than n, the system does not add spaces to the backend to fill the set space.
Generally, because the CHAR data type has a fixed length, it is faster than the VARCHAR type.

4. NVARCHAR

The NVARCHAR data type is defined as NVARCHAR [(n)]. It is similar to VARCHAR. The difference is that the NVARCHAR data type adopts the UNICODE Character Set (Character Set), and the value of n is 1 to 4000.

Vi. text and graphic data types

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

1. TEXT

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

In versions earlier than SQL Server 2000, a TEXT object in the database is actually a pointer that points to Data pages in 8 KB (8192 bytes ).

These data pages are dynamically added and logically linked. In SQL Server 2000, data of the TEXT and IMAGE types is directly stored in the data rows of the table, rather than on different data pages.
This reduces the space used to store TEXT and IMA-GE types, and correspondingly reduces the number of I/O operations on the disk to process such data.

2. NTEXT

The NTEXT data type is similar to that of TEXT. The NTEXT type adopts the UNICODE Character Set (Character Set). Therefore, its theoretical capacity is 230-1 (1,073,741,823) bytes.

3. IMAGE

The IMAGE Data type is used to store a large amount of Binary Data. The theoretical capacity is 31 to the power of 2-1 (2,147,483,647) bytes. The data storage mode is the same as the TEXT data type.

It is usually used to store OLE Object Linking and Embedding such as graphics, Object connection and Embedding) objects. The input data is the same as the BINARY data type. The character "0X" must be added before the data as the binary id.

VII. Date and Time Data Types

1 DATETIME

The DATETIME data type is used to store the combination of date and time. It can store data between on January 1, January 1, 1753 AD and on January 9, December 31, 9999 AD.

All of the above are described in this article. I hope you will like it.