Common Database types

Common Database types

Int, bigint, smallint, and tinyint
Use the exact numeric data type of integer data.
Bigint
Integer Data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807) (All numbers ). The storage size is 8 bytes.
Int
Integer Data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647) (All numbers ). The storage size is 4 bytes. The SQL-92 synonym for the int Is integer.
Mallint
Integer Data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767. The storage size is 2 bytes.
Tinyint
Integer Data from 0 to 255. The storage size is 1 byte.
Note
The bigint data type is supported where integer values are supported. However, bigint is used in some special cases. When the integer value exceeds the range supported by the int data type, bigint can be used. In SQL Server, the int data type is the main integer data type.
In the data type precedence table, bigint is located between smallmoney and int.
The function returns bigint only when the parameter expression is of the bigint data type. SQL Server does not automatically upgrade other integer data types (tinyint, smallint, and int) to bigint.

Description of char, varchar, nchar, and nvarchar types:

I. Basic differences:

1. char and varchar

Char (n): Specifies the length of the data type. n indicates the data type that can be stored.Maximum Byte Length(Value range: 1 ~ 8000). When the number of characters to be stored is less than n, there are insufficient spaces at the end. When the number of characters stored exceeds n, the number of characters after n in the database stage.

Varchar (n): variable-length data type. n indicates the data type that can be stored.Maximum byte length (Value Range: 1 ~ 8000). When the storage character occupies less than n Bytes, the actual usage prevails. If the storage character exceeds n, It is truncated.

Here, Why n is used to limit the storage bytes?

The reason is that when char or varchar is used to store data: the character is a byte because it occupies one character; when the character is a Chinese character, a Chinese character generally occupies two bytes; when the characters to be saved are both Chinese and English, the actual bytes are used. For example, the byte occupied by the 'Chinese abc' storage is 2*2 + 3 :. The following is an example:

Declare @ var1 varchar (6), @ Var2 varchar (5);

Set @ var1 = 'Chinese AB ';

Set @ var2 = 'Chinese AB ';

Select @ var1, @ var2;

The execution result is as follows:

| Unknown column 1 | unknown Column 2 |

---------------------------------------

| Chinese AB | A |

The reason is that "Chinese AB" uses the varchar type for storage and occupies 2*2 + 2 = 6 bytes, and the second varchar (5) if the maximum length is 5 bytes, the excess part is truncated and 'B' is lost.

 

2. ncahr and nvarchar

Nchar (n): Specifies the length of the data type. The stored characters are of the Unicode encoding type. Each character occupies two bytes. n indicates the data that can be stored.Maximum number of characters(Value range: 1 ~ 4000 [think about why the maximum value is 4000 ?]), When the number of characters to be saved is less than n, there are insufficient spaces at the end. When the number of characters stored exceeds n, the number of characters after n in the database stage.

Nvarchar (n): variable-length data type. The stored characters are of the Unicode encoding type. Each character occupies two bytes. n indicates the data that can be stored.Maximum number of characters(Value range: 1 ~ 4000). When the storage character occupies less than n Bytes, the actual usage prevails. If the storage character exceeds n, It is truncated.

In the same example, n in nvarchar (n) refers to the character limit.

Declare @ var1 nvarchar (4), @ Var2 nvarchar (3);

Set @ var1 = 'Chinese AB ';

Set @ var2 = 'Chinese AB ';

Select @ var1, @ var2;

The execution result is as follows:

| Unknown column 1 | unknown Column 2 |

---------------------------------------

| Chinese AB | A |

 

Ii. Usage:

[This section is taken from http://www.cnblogs.com/ebaidu/archive/2007/08/14/854778.htmlonly for learning and use]

Many developers often do not consider the char and varchar types when designing databases. Some developers do not pay attention to them because the storage price is getting cheaper and cheaper, I forgot some of the basic design theories and principles at the beginning. This reminds me of the young people who have moved away from their hands with a wave of RMB. In fact, I want to be a human or a human, development is good, and the grasp of details directly determines a lot of things. Of course, there are still some people who just don't figure out their differences, so they just choose one. Here I want to make a simple analysis on them. Of course, if there is something wrong with them, I hope you can give me some advice.

1. CHAR. It is very convenient for CHAR to store fixed-length data, and the indexing efficiency of CHAR fields is high. For example, if char (10) is defined, no matter whether the data you store reaches 10 bytes, it takes up 10 bytes of space. If the space is insufficient, it is automatically filled with spaces. Therefore, trim () may be used multiple times during reading ().

2. VARCHAR. Variable-length data is stored, but the storage efficiency is not as high as CHAR. If the possible value of a field is not fixed, we only know that it cannot exceed 10 characters. It is the most cost-effective to define it as VARCHAR (10. The actual length of the VARCHAR type is the actual length of its value plus 1. Why "+ 1? This byte is used to save the actual length. From the perspective of space, it is appropriate to use varchar; from the perspective of efficiency, char is suitable, and the key is to find a trade-off point based on the actual situation.

3. TEXT. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters.

4. NCHAR, NVARCHAR, and NTEXT. The three names are named N more than the first three ". It indicates that characters of the Unicode data type are stored. We know that only one byte is required for English characters, but there are many Chinese characters and two bytes are required for storage. It is easy to cause confusion when both English and Chinese characters exist, unicode Character Set is generated to solve the incompatibility problem of character sets. All its characters are expressed in two bytes, that is, English characters are also expressed in two bytes. The length of nchar and nvarchar is between 1 and 4000. Compared with char and varchar, nchar and nvarchar can store up to 4000 characters, whether in English or Chinese. char and varchar can store up to 8000 English and 4000 Chinese characters. It can be seen that when using nchar and nvarchar data types, you do not have to worry about whether the entered characters are English or Chinese characters, which is more convenient, but there is some loss in the amount of stored English hours.

 

Supplement: char (n) knows the length of the string during reading and can be read at one time. Varchar (n), like the string processing method in C language, uses '\ 0' to indicate the end at the end. Therefore, it cannot be read at once. You need to read each character to know where it ends. Therefore, reading speed of char type is faster than that of varchar type.

Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains English letters and numbers, use char/varchar.
I will summarize their differences:
CHAR, NCHAR fixed length, fast speed, large space, need to be processed
VARCHAR, NVARCHAR, and TEXT cannot be long, the space is small, and the speed is slow. No processing is required.
NCHAR, NVARCHAR, and NTEXT process Unicode codes

[This section is taken from http://wenku.baidu.com/view/eee97bf5f61fb7360b4c652b.htmlonly for learning and use]

In SQL Server, varchar stores data in a single byte. nvarchar uses Unicode to store data. when a Chinese character is stored in SQL Server, it is saved as two bytes (generally Unico encoding), and an English character is saved to the database. If the field type is varchar, only one byte is occupied, if the field type is nvarchar, it occupies two bytes.
Normally, we can use varchar to store Chinese characters. However, if the operating system is an English operating system and the Chinese font is not fully supported, if the Chinese character is varchar in SQL Server, garbled characters (displayed as?) are displayed ??). In addition, the host normally supports Chinese environments. Therefore, if varchar is used to store data, it cannot be found in the development stage. in most cases, there will be no problems during deployment.
But! If the deployed host is an English operating system and does not support the Chinese environment, the problem arises. All varchar fields are garbled when they are stored in Chinese ??). In general, you do not know that this is because you use the wrong data type to store the data. You will try to install Chinese fonts, try to set the language environment of the operating system... these cannot solve the problem. The only solution is to set the database field type to nvarchar (or nchar ). anyone familiar with project management should know that it is terrible to modify the database at the deployment stage.
Another advantage of using nvarchar is that you do not need to consider the differences between Chinese and English characters when judging strings.
Of course, using nvarchar to store English characters will increase by a factor of storage space. However, given the low storage cost, compatibility will bring you more benefits.
Therefore, you should try to use nvarchar to store data during Design. varchar is used only when you ensure that this field does not save Chinese characters.

Note: Both varchar (n) and nvarchar (n) can process Chinese characters, but note the following details, when reading the varchar type, each byte is used to determine whether it is an English character or to wait for it to be resolved to a Chinese character together with the next byte, while nvarchar does not, resolve two bytes to a Unicode character at a time. The speed is easy to understand.

 

Bytes ------------------------------------------------------------------------------------------------------------------------

Summary:

1. In varchar (n) brackets, n indicates the maximum number of bytes that can be stored. In nvarchar (n) brackets, n indicates the maximum number of characters that can be stored;

2. varchar and nvarcahr usage: varchar is used when all characters are in English. nvarchar is used when all Chinese characters are in Chinese. nvarchar is recommended when both Chinese and English are used.

3. Usage of char and varchar: Use char when focusing on reading efficiency (note that trailing spaces should be removed during use) and use varchar when using space.