Length of SQL Server kanji

A few days ago changed the program of a small bug, that is, the length of the input check problem. Project is. NET, the database is SQL Server. Check, found that the previous personnel to the length of the control is small, the database allowed to enter a length of 256, and others in the ASP program only defined 64. It was very urgent, I found this problem, I took it for granted to change 64 to 256. The test can be saved successfully, should be able to. Later, the head told me that the length of 256 should not consider the Chinese character bar, should be set to 128, so that the input 256 characters into the database, the database to collapse. We are usually using Oracle, SQL Server database is not very familiar, I said no ah, just now I saved 256 Chinese characters successfully, and the database also saved in AH. The head says it's not going to automatically cut out the extra parts, right? I don't think so, it's going to be stored in. When you change the length to 257, the page will collapse.

Later I changed the properties of another field, obviously 256, but this is not the same, this can only enter 256 English letters, but only 128 characters can be entered. I look at this field of the test is actually JS, there is a LENGTHB () method, it is estimated that this will be the control of the number of bytes.

Later I checked the database definition, their properties are nvarchar, read the following I searched the information on the Internet to understand.

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

The next three data types, compared to the previous one, are just a few letters "n" from the name, which represents the characters that are stored in the Unicode data type. A friend who has written a program should know a lot about Unicode. Character, the English characters only need one byte storage is sufficient, but the Chinese character is numerous, requires two bytes of storage, English and Chinese characters are prone to confusion, the Unicode character set is to solve the problem of incompatible character sets, all of its characters are represented by 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, nvarchar stores 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.

(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) The two data types, decimal and numeric, are equivalent. have two parameters: P (Precision) and S (decimal digits). 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, s must be a value from 0 to P, and the default scale 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).