Convert data types in SQL Server databases

Every time I read every time I forget .. turn from http://bbs.nankai.edu.cn/

"

When designing a database, we are used to setting the Data Type of the field storing strings to Char, varchar, nchar, and nvarchar. The text and ntext types are often ignored and can be used to store strings, these data types have their own strengths and weaknesses, and there are also great differences in performance and functionality. Therefore, you need to select an appropriate type to store data during design. The following is a brief comparison of these data types. 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 not, it is automatically filled with spaces. 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. nchar and nvarchar. In terms of name, there is an "N" more than the first two types ". 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. 3. Text and ntext. Since SQL server stores data on data pages, each data page is 8 KB, and each data page has some management information, each data page is used to store 8060 bytes of table record data, because a record can only be stored on one data page and cannot be displayed across pages, the total length of a single record in each table cannot exceed 8060 bytes. Therefore, for the previous data types, the maximum length is 8000 bytes. Therefore, when some long strings are stored, the previous several types are insufficient. Text stores variable-length non-Unicode data. The maximum length is 2 ^ 31-1 (2,147,483,647) characters. It can be said that this length can meet our arbitrary needs for String Length. The relationship between ntext and text is the same as that between char and nchar.

"