Analysis of basic common types of database

int, bigint, smallint, and tinyint
An exact numeric data type that uses integer data.
bigint
Integer data (all numbers) from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807). The storage size is 8 bytes.
Int
Integer data (all numbers) from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). The storage size is 4 bytes. The SQL-92 synonymous word for 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 bytes.
Comments
Support for bigint data types 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 primary integer data type.
In the data type precedence table, bigint is located between smallmoney and Int.
The function returns bigint only if the parameter expression is a bigint data type. SQL Server does not automatically promote other integer data types (tinyint, smallint, and int) to bigint.

char, varchar, nchar, nvarchar type explained:

First, the basic difference:

1, char, varchar

CHAR (n): fixed-length data type, n is the maximum byte length that can be stored (value range 1~8000), when the number of characters less than N, the end of the space is insufficient. The character after the database phase n when the number of characters exceeds n is deposited.

VARCHAR (n): variable-length data type, n is the maximum byte length that can be stored (the value range 1~8000), when the storage character occupies less than n, the actual use is the quasi;

Why does this say n is to limit the bytes stored?

The reason for this is when a char or varchar is used to store the data: a character is a character, which occupies one byte; in character, a Chinese character takes up two bytes in general, and the characters to be stored are mixed in Chinese and English, calculated as the actual bytes used. such as the ' Chinese ABC ' storage occupies bytes: 2*2+3:. Here's an example:

DECLARE @var1 varchar (6), @var2 varchar (5);

Set @var1 = ' Chinese ab ';

Set @var2 = ' Chinese ab ';

Select @var1, @var2;

The results of the implementation are as follows:

|    Unknown column 1 | Unknown column 2 |

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

|    English AB | Chinese A |

The reason is that the "Chinese AB" uses the varchar type to store the bytes: 2*2+2=6, the second varchar (5) limits the maximum to 5 bytes, so it will break out of the partial truncation, both ' B ' lost.

2, Ncahr, nvarchar

NCHAR (n): fixed-length data type, stored characters are Unicode encoded type, each character accounts for two bytes, n is the maximum number of characters that can be stored (value range 1~4000 "Think about what is the maximum value of 4000?" "), when the number of characters is less than N, the trailing space is insufficient. The character after the database phase n when the number of characters exceeds n is deposited.

nvarchar (n): variable-length data type, stored characters are Unicode encoded type, each character occupies two bytes, n is the maximum number of characters that can be stored (value range 1~4000), when the storage character occupies less than n, the actual use of the quasi-when more than N, truncated.

Also, as an example, the n in nvarchar (n) brackets refers to the number of character qualifiers.

declare @var1 nvarchar (4), @var2 nvarchar (3);

Set @var1 = ' Chinese ab ';

Set @var2 = ' Chinese ab ';

Select @var1, @var2;

The results of the implementation are as follows:

|    Unknown column 1 | Unknown column 2 |

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

|    English AB | Chinese A |

Second, the use of:

"This paragraph is excerpted from http://www.cnblogs.com/ebaidu/archive/2007/08/14/854778.html for learning only"

Many developers in the database design often do not have much to consider char, varchar type, some are not pay attention to, because the storage price has become more and more cheap, forget the beginning of some basic design theory and principles, this reminds me of the present young people, Big hand a wave of renminbi from his hands slipped away, in fact, I think whether it is a person or do development, details of the grasp directly decided a lot of things. Of course, some people do not understand their differences at all, and choose one. Here I would like to make a simple analysis of them, of course, if there is no place to ask for advice.

1, CHAR. Char is convenient to store the fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store data reached 10 bytes, to take up 10 bytes of space, insufficient to automatically fill with spaces, so you may want to read the time to use trim ().

2, VARCHAR. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used. From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

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

4, NCHAR, NVARCHAR, NTEXT. These three kinds of names from the first three more than the previous "N". It represents a character stored in a Unicode data type. We know that characters, the English character only need a byte storage is enough, but the number of Chinese characters, need two bytes of storage, English and Chinese characters at the same time prone to confusion, the Unicode character set is to solve the character set this incompatibility problem, all of its characters are expressed in two bytes, That is, the English character is also represented in two bytes. The length of the nchar and nvarchar is between 1 and 4000. Compared to char and varchar, nchar and nvarchar store up to 4,000 characters, whether in English or Chinese characters, while char and varchar can store up to 8,000 English and 4,000 Chinese characters. It can be seen that the use of nchar, nvarchar data types without worrying about the input characters are English or Chinese characters, more convenient, but in the storage of English number of some losses.

Add: char (n) reads the length of the string, which can be read out at once. While varchar (n) is like a string in C, the end is denoted by ' + ' at the end, so it cannot be read at once, and it needs a character readout to know where to end. So the char type reads faster than the varchar type.

So generally, if it contains Chinese characters, use Nchar/nvarchar, if pure English and numbers, with Char/varchar
I summarize their differences as follows:
CHAR, NCHAR fixed length, high speed, occupy space, need to handle
Varchar,nvarchar,text variable length, small space, slow speed, no need to handle
NCHAR, NVARCHAR, ntext processing Unicode codes

"This paragraph is excerpted from http://wenku.baidu.com/view/eee97bf5f61fb7360b4c652b.html for learning only"

varchar uses a single byte to store data in SQL Server, and nvarchar uses Unicode to store the data. Chinese characters stored in SQL Server are saved as two bytes (typically with Unico encoding), English characters are saved to the database, and if the field's type is varchar, only one byte is consumed, and two bytes if the field is of type nvarchar.
Under normal circumstances, we can also store Chinese characters using varchar, but if the operating system is an English operating system and the support is not comprehensive, the text character in SQL Server store is varchar is garbled (shown as??). And under normal circumstances, the host will support the Chinese environment, so if the use of varchar to store data, in the development phase is not found. In most cases, there will be no problem at the time of deployment.
But! If the host computer is an English operating system and does not support the Chinese environment, then the problem comes out. All varchar fields are garbled when they are stored in Chinese (shown as??). ）． And generally you don't know this because you're using the wrong data type to store the resulting, you'll try to install the Chinese font, try to set the operating system's language environment ... None of this solves the problem, and the only solution is to nvarchar (or nchar) the type of database field. More familiar with the project management of friends should know, to the deployment stage to modify the database is a very scary thing.
Another good thing about using nvarchar is that you don't need to consider the difference between the two characters when judging a string.
Of course, using nvarchar to store English characters will increase storage space by one more times. But given the low cost of storage, prioritizing compatibility gives you more benefits.
Therefore, when design should try to use nvarchar to store data. Use varchar to store only if you are sure that the field is not saved in Chinese.

Add: varchar (n) and nvarchar (n) can handle Chinese, but some details need to be noted: when encountering mixed English and Chinese conditions, the varchar type reads each byte to determine whether it is an English character or wait and the next byte to parse into a middle character, Instead, nvarchar is parsed as a Unicode character two bytes at a time. Speed yourself to realize it.

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

Summarize:

1, varchar (n) in parentheses, n is the maximum number of bytes that can be stored, and nvarchar (n) is the maximum number of characters that can be stored in parentheses;

2, varchar and Nvarcahr use case: In the word is full English when using varchar, all Chinese is the use of nvarchar, in English and Chinese mixed recommended use nvarchar.

3. Char and varchar use case: use char when reading efficiency is used (note to remove the trailing space during use), and use varchar when using space.