Easily confusing data types in SQL Server

(1) Char, varchar, text and nchar, nvarchar, ntext

The length of char and varchar is between 1 and 8000, the difference being that char is a fixed-length character data, and varchar is variable-length character data. The so-called fixed-length is constant, when the input data length does not reach the specified length will be automatically filled with English spaces behind it, so that length to the appropriate length, and variable length character data will not be filled with space. Text stores variable-length non-Unicode data with a maximum length of 2^31-1 (2,147,483,647) characters.

The next three data types, compared to the preceding ones, are just a few more letters "n" from the name, which means that the characters stored are Unicode data types. A friend who has written a program should know a lot about Unicode. Character, English characters only need one byte of storage is sufficient, but the number of Chinese characters, the need for two bytes of storage, English and Chinese characters at the same time can cause confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, The English character is also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared to char and varchar: nchar, nvarchar stores up to 4,000 characters, both English and Chinese, while char and varchar can store up to 8,000 English and 4,000 Chinese characters. You can see that the use of nchar, nvarchar data types do not have to worry about the input characters are English or Chinese characters, more convenient, but in the number of storage in English some loss.

(2) datetime and smalldatetime

DateTime: Date and Time data from January 1, 1753 to December 31, 9999, accurate to 3% seconds.

smalldatetime: Date and time data from January 1, 1900 to June 6, 2079, accurate to minutes.

(3) bitint, int, smallint, tinyint and bit

bigint: Integer data from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807).

int: integer data from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).

smallint: Integer data from -2^15 (-32,768) to 2^15-1 (32,767).

Tinyint: Integer data from 0 to 255.

Bit:1 or 0 of integer data.

(4) Decimal and numeric

The two types of data are equivalent. have two parameters: P (Precision) and S (scale). p Specifies the maximum number of decimal digits that can be stored to the left and right of the decimal point, and P must be a value from 1 to 38. s specifies the maximum number of decimal digits that can be stored to the right of the decimal point, and S must be a value between 0 and P, and the default scale of digits is 0.

(5) Float and real

float: Floating-point numeric data from -1.79^308 to 1.79^308.

Real: Floating-point numeric data from -3.40^38 to 3.40^38. In SQL Server, the synonym for Real is float (24).