Differences between several field types in the database

Char, varchar, nchar, and nvarchar differences for string fields in the program, SQLServer has char, varchar, nchar, and nvarchar types to match (text and ntext are not considered for the time being ), during database creation, these four types are often vague. Here is a comparison. Fixed Length or variable length: the so-called fixed length is fixed length

Char, varchar, nchar, and nvarchar differences for string fields in the program, SQLServer has char, varchar, nchar, and nvarchar types to match (text and ntext are not considered for the time being ), during database creation, these four types are often vague. Here is a comparison. Fixed Length or variable length: the so-called fixed length is fixed length

Differences between char, varchar, nchar, and nvarchar

For string fields in the program, SQLServer contains char, varchar, nchar, and nvarchar types (text and ntext are not considered for the time being, these four types are often vague. Here is a comparison.

Fixed Length or length: the so-called fixed length is fixed. when the length of the input data does not reach the specified length, it is automatically filled with spaces behind it, make the length reach the corresponding length. If there is a var prefix, it indicates that the actual storage space is longer, for example, varchar and nvarchar variable-length data will not be filled with spaces, text storage is also variable length.

Unicode or non-Unicode: in a database, only one byte is required for English characters. However, two bytes are required for Chinese characters and many other non-English characters. If both English and Chinese characters exist, the occupied space may lead to confusion, leading to garbled characters. 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 prefix n indicates Unicode characters, such as nchar and nvarchar. These two types Use the Unicode Character Set.

Based on the above two points, let's look at the field capacity:

• Char and varchar can contain up to 8000 English letters and 4000 Chinese Characters
• Nchar and nvarchar can store 4000 characters, regardless of English or Chinese Characters

Usage (personal preference): if the data size is very large and the length can be 100% characters determined and saved as only ansi characters, then char; can be determined that the length is not necessarily ansi characters or, then nchar ;? For ultra-large data, such as article content, use nText; other general nvarchar.

Char, varchar, nchar, and nvarchar have the following features.

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.

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, defining it as VARCHAR (10) is the most cost-effective. 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.

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

NCHAR, NVARCHAR, and NTEXT: The three names are named N more than the first three ". 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 letters 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. Therefore, in general, if it contains Chinese characters, use nchar/nvarchar. If it contains pure English characters and numbers, use char/varchar.

Differences between int, smallint, and tinyint of the database

Unicode can solve this problem by encoding each character in two bytes. A single specification for converting the most common business language has a sufficient 2-byte mode (65,536 ). Because all Unicode systems use the same bit pattern to represent all characters, when switching from one system to another, there will be no problem of incorrect character conversion. By using Unicode data types throughout the system, you can minimize the problem of character conversion. Unicode data is stored using the nchar, varchar, and ntext Data Types in SQL Server.

• 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.
• Smallint integer data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767. The storage size is 2 bytes.
• Integer Data of tinyint from 0 to 255. The storage size is 1 byte.

Difference between date, datetime, and timestamp

• Date indicates year, month, and day, such as YY-MM-DD
• Datetime represents the year, month, day, and time information, such as YY-MM-DD HH: MM: SS
• Datestamp and datetime indicate the same information, but the time range is different.

Time Range:

• Date --> '2014-01-01 'to '2014-12-31 '.
• Datetime --> '2017-01-01 00:00:00 'to '2017-12-31 23:59:59 '.
• Datestamp --> '2017-01-01 00:00:01 'UTC to '2017-01-19 03:14:07' UTC

Storage settings:

Timestamp

1. 4-byte storage (Time stamp value is stored in 4 bytes)
2. The value is saved in UTC (it stores the number of milliseconds)
3. Time zone conversion: during storage, the current time zone is converted, and then retrieved to the current time zone.

Datetime

1. 8-byte storage)
2. Actual storage format (Just stores what you have stored and retrieves the same thing which you have stored .)
3. It has nothing to deal with the TIMEZONE and Conversion .)