SQL Server more common data types detailed _mssql

There are data types for each variable, parameter, expression, and so on in SQL Server. The data types provided by the system are grouped into several broad categories, as shown in table 4-2.

Among them, BIGINT, sql_variant, and table are the newly added 3 data types in SQL Server 2000. The following categories describe the various data types.

First, Integer data types

An 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 between 31 (-2, 147, 483, 648) to 2 of 31 (1, 2, 147) from 2.

data for each int type is stored in 4 bytes, where 1 bits represent the positive 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 of 15 (-32, 768) to 2 15 (1, 32).

Each smallint type of data occupies 2 bytes of storage space, where 1 bits represent the positive 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 consumes 1 bytes of storage space.

4, BIGINT

BIGINT data types store 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 consumes 8 bytes of storage space.

Second, floating-point data types

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

Rounding is the value plus 1 on the lowest significant bit of the number part that is reserved, and the necessary rounding, when (and only if) the number to be rounded is a zero. If a number is a rounded number, its absolute value is not reduced.

For example, 2-bit and 12-bit rounding were performed on 3.14159265358979, with the result 3.15 and 3.141592653590.

1. Real data type

The real data type can be accurate to a 7th decimal place, ranging from -3.40E-38 to 3.40E +38. Data for each real type consumes 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. Data for each float type occupies 8 bytes of storage space.

Float data type can be written in the form of float[N]. n Specifies the precision of float data. n is an integer value from 1 to 15. When n takes 1 to 7 o'clock, it actually defines a real type of data that the system stores with 4 bytes;
When N takes 8 to 15 o'clock, the system thinks it is a float type and stores it in 8 bytes.

3, DECIMAL

The decimal data type can provide the actual storage space needed for decimals, but there are limits to which you can store values between 1 and 10 from 38 to 38 from 10 to 2 to 17 bytes.

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

For example: Decimal (15 5), representing a total of 15 digits, where integers 10 digits, and decimal 5.

4, NUMERIC

The numeric data type is exactly the same as the decimal data type.
Note: SQL Server, in order to work with the front-end development tools, supports the maximum data precision of 28 digits. However, you can change the default precision by using a command to execute the Sqlserver.exe program to start SQL Server.

The command syntax is as follows:

SQLSERVR[/D master_device_path][/p Precisim_leve1]

Example 4-4: Starting SQL Server with maximum data precision 38

SQLSERVR/D C:\ Mssql2000\data\master.dat/p38

/* After using the/p parameter, the default is 38 digits if no specific precision value is specified thereafter./*

Three or two binary data types

1, BINARY

The BINARY data type is used to store binary data. It is defined as binary (n), and n represents the length of the data, with a value of 1 to 8000.

You must specify the size of the binary type data when you use it, at least 1 bytes. BINARY type Data occupies n+4 bytes of storage space.

When you enter data, you must precede the data with the character "0X" as the binary ID, such as: "ABC" should be entered "0XABC".

If the input data is too long, it will intercept the excess. If you enter an odd number of digits, you will add a 0 after the start sign "0X", such as "0XABC" which is automatically changed to "0X0ABC" by the system.

2, VARBINARY

The VARBINARY data type is defined in the form VARBINARY (n). It is similar to the binary type, and the value of N is 1 to 8000, and if the input data is too long, it will truncate its excess portion.

The difference is that the varbinary data type has variable length attributes, 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 treated as a varbinary data type.

Generally, because the binary data type length is fixed, it is faster to handle than the varbinary type.

Four, logical data types

The Bit:bit data type occupies 1 bytes of storage space, with a value of 0 or 1. If you enter a value other than 0 or 1, it will be treated as 1.
The BIT type cannot be defined as a null value (the so-called null value is a null value or a meaningless value).

V. Character data types

Character data types are the most frequently used data types. It can be used to store all kinds of letters, digital symbols, special symbols. In general, use character type data with a single quotation mark or double quotation mark before and after it.

1 CHAR

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

Can hold 8,000 ANSI characters. If you do not specify an n value, the system defaults to 1. If the number of characters entered in the data is less than n, the system automatically adds spaces to fill the set space. If the data you enter is too long, it will truncate its excess portion.

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 stipulates that each character occupies two bytes of storage space, so it takes up one more storage space than a non-Unicode standard data type. The advantage of using the Unicode standard is that it uses two bytes as a storage unit. A large increase in the capacity of one storage unit, the world's language can be included, in a data column can be in Chinese, English, French, German, etc., without coding conflicts.

3, VARCHAR

The varchar data type is defined in the form of Varcharn). It is similar to the char type, and the value of N is 1 to 8000, and if the input data is too long, it will truncate its excess portion.
The difference is that the varchar data type has an attribute of 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, then the system does not add spaces to fill the set space.
Typically, because the char data type is fixed in length, it is faster to handle 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), and N takes a value of 1 to 4000.

Vi. text and graphic data types

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

1, TEXT

The text data type is used to store a large amount of text data, and its capacity is theoretically 1 to 2 of 31-1 (2, 147, 483, 647) bytes, depending on the storage space of the hard disk when applied to the real world.

In previous versions of SQL Server 2000, a text object in a database was actually a pointer that pointed to a data page in 8KB (8,192 bytes).

These data pages are dynamically added and logically linked. In SQL Server 2000, data for text and image types are stored directly in the table's data rows, rather than in a different data page.
This reduces the amount of space used to store the text and Ima-ge types, and correspondingly reduces the number of I/O to the disk processing data.

2, NTEXT

The ntext data type is similar to the text. Type, and 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. Its theoretical capacity is 2 of 31 times 1 (2,147,483,647) bytes. It stores data in the same mode as the text data type.

Typically used to store objects such as OLE Object linking and embedding, object concatenation and embedding, such as graphics. As with binary data types when entering data, you must precede the data with the character "0X" as the binary identity

Seven, date and time data types

1 DATETIME

DATETIME data types are used to store a combination of dates and times. It can be stored between 59 seconds from the ad January 1, 1753 0 o'clock to the A.D. December 31, 9999 23:59.

The above is the entire narrative of this article, I hope you like.