SQL Data Type Interpretation

SQL Data Type Interpretation

1. Differences between char, varchar, text, ntext, bigint, int, smallint, tinyint, and bit and data type of database computer cheats 2009-05-15 21:47:20 Read 265 reviews 4 font size: Big Medium and small foundation: The difference between char, varchar, text, and nchar, nvarchar, ntext 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, take up 10 bytes of space, insufficient to automatically fill with spaces.

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.

Further study:

Differences between char, varchar, text, ntext, bigint, int, smallint, tinyint, and bit, and data types of the database

VarChar occupies 2 bytes per English (ASCII) character, and only two bytes for a Chinese character char is 1 bytes for English (ASCII) characters, and the type of 2-byte varchar for a Chinese character does not fill with spaces, such as varchar (100), But its value is only "Qian", then its value is "Qian" and char is different, such as char (100), its value is "Qian", but in fact it is in the database is "Qian" (Qian after a total of 96 spaces, which is to fill it up to 100 bytes).

Since char is fixed-length, it will be much faster than varchar! But the program to deal with a little trouble, to use trim and other functions to remove the space on both sides!

ntext

The maximum length of a variable-length Unicode data is 230-1 (1,073,741,823) characters. The storage size is twice times the number of characters entered (in bytes). The synonym of ntext in SQL-92 is national text.

Text

The maximum length of a variable-length non-Unicode data in a server code page is 231-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. The storage size may be less than 2,147,483,647 bytes (depending on the string).

bigint: Integer data from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807) with a storage size of 8 bytes.

int: integer data from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) with a storage size of 4 bytes.

smallint: Integer data from -2^15 (-32,768) to 2^15-1 (32,767) with a storage size of 2 bytes.

Tinyint: Integer data from 0 to 255 with a storage size of 1 bytes.

Bit:1 or 0 of integer data with a storage size of 1 bytes.

Unicode data

In Microsoft SQL Server 2000, traditionally non-Unicode data types allow the use of characters defined by a specific character set. The character set is selected when you install SQL Server and cannot be changed. With Unicode data types, columns can store any character defined by the Unicode standard and contain all characters defined by different character sets. Unicode data types require storage space equal to twice times the non-Unicode data type.

Unicode data is stored using the nchar, varchar, and ntext data types in SQL Server. These data types are used for columns that store characters from multiple character sets. Use the nvarchar type when each item in the column contains a different number of Unicode characters (up to 4000). Use the nchar type when items in a column are of the same fixed length (up to 4,000 Unicode characters). Use the ntext type when any item in a column exceeds 4,000 Unicode characters.

Description: The Unicode data type for SQL Server is based on the national character data type in the SQL-92 standard. SQL-92 uses the prefix character n to identify these data types and their values.

Data type: Data class is a property of the data that represents the type of information represented by the data. Any computer language defines its own data type. Of course, different programming languages have different characteristics, and the categories and names of the data types defined are more or less different. SQL Server provides 25 types of data: • Binary [(N)] Varbinary [(N)] Char [(N)] varchar[(N)] nchar[(N)] nvarchar[(N)] DateTime smalldatetime decimal[(P[,s])] numeric[(P[,s])] float[(N)] Real int smallint tinyint Money SmallMoney Bit Cursor sysname Timestamp uniqueidentifier Text Image Ntext

(1) Binary data type the binary data includes binary, Varbinary, and Image. Binary data types can be either fixed-length (binary) or variable-length. binary[(N)] is a fixed N-bit binary data. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes. varbinary[(N)] is a binary data of n-bit variable length. where n is the range of values from 1 to 8000. The size of its storage scenting is n + 4 bytes, not n bytes. The data stored in the Image data type is stored as a bit string, not interpreted by SQL Server, and must be interpreted by the application. For example, applications can store data in the Image data type using BMP, Tief, GIF, and JPEG formats.

(2) Character data type character data types include Char,varchar and Text. Character data is data that is any combination of letters, symbols, and numbers. Varchar is a variable-length character data whose length does not exceed 8KB. Char is a fixed-length character data with a length of up to 8KB. ASCII data over 8KB can be stored using the text data type. For example, because Html documents are all ASCII characters and are typically longer than 8KB in length, these documents can be stored in SQL Server with the Text data type.

(3) Unicode data types Unicode data types include Nchar,nvarchar and ntext. In Microsoft SQL Server, traditional non-Unicode data types allow the use of characters defined by a specific character set. During SQL Server Setup, a character set is allowed to be selected. With Unicode data types, any character defined by the Unicode standard can be stored in a column. In the Unicode standard, all characters defined in various character sets are included. Using a Unicode data type, the space occupied is twice times the size of the space used by non-Unicode data types. In SQL Server, Unicode data is stored in Nchar, Nvarchar, and Ntext data types. Columns stored with this type of character can store characters in multiple character sets. When the length of a column changes, you should use the Nvarchar character type, where you can store up to 4,000 characters. When the length of a column is fixed, you should use the Nchar character type, and you can store up to 4,000 characters at this time. When you use the Ntext data type, the column can store more than 4,000 characters.

The

(4) Date and time data type date and time data types include Datetime and smalldatetime two types. Date and time data types are made up of valid dates and times. For example, valid date and time data includes "4/01/98 12:15:00:00:00 PM" and "1:28:29:15:01 AM 8/17/98". The previous data type is the date before, the time in the last data type is a moment before, and the date in the back. In Microsoft SQL Server, date and time data types include datetime and smalldatetime two types when the date range is stored starting January 1, 1753 and ending December 31, 9999 (each value requires 8 bytes of storage). When using the smalldatetime data type, the stored date range starts January 1, 1900 and ends on December 31, 2079 (each value requires 4 bytes of storage). The format of the date can be set. The command to format the date is as follows: Set DATEFORMAT {format | @format _var| where the format | @format_var is the order of the dates. Valid parameters include MDY, DMY, YMD, YDM, MYD, and DYM. By default, the date format is MDY. For example, when the set DateFormat YMD is executed, the date is formatted as a month and day form, and when the set DateFormat DMY is executed, the date is in the form of sun and moon years

(5) Numeric data type numeric data contains only numbers. Numeric data types include positive and negative numbers, decimals (floating-point numbers), and integers. Integers consist of positive and negative integers, such as 39, 25, 0-2, and 33967. In Micrsoft SQL Server, the data types stored by integers are int,smallint and Tinyint. The INT data type stores data in a range that is larger than the Smallint data type stores the data, while the Smallint data type stores the range greater than the Tinyint data type stores the data. The range of data that is stored using the INT data is from 2 147 483 648 to 2 147 483 647 (each value requires 4 bytes of storage space). When using the Smallint data type, the range of stored data ranges from 32 768 to 32 767 (each value requires 2 bytes of storage). When you use the Tinyint data type, the range of stored data is from 0 to 255 (each value requires 1 bytes of storage space). The exact data type for the data in SQL Server is Decimal and Numeric. The amount of storage space that this data occupies is determined by the number of bits in the data. In SQL Server, the data type of the approximate decimal data is Float and Real. For example, One-third of this score is recorded. 3333333, which can be expressed accurately when using approximate data types. Therefore, the data retrieved from the system may not be exactly the same as the data stored in the column.

(6) Currency data indicates the number of positive or negative currencies. In Microsoft SQL Server, the data type of the currency data is money and smallmoney. The Money data type requires 8 storage bytes, and the SmallMoney data type requires 4 bytes of storage.

(7) Special data types Special data types include data types that were not previously mentioned. There are 3 special data types, namely Timestamp, Bit and uniqueidentifier. The Timestamp is used to represent the sequence of SQL Server activities in a binary projection format. Timestamp data is not related to inserting data or date and time. The Bit is made up of 1 or 0. When representing true or false, on, or off, use the Bit data type. For example, ask if a client request for each visit can be stored in a column of this data type. The uniqueidentifier consists of 16-byte hexadecimal digits that represent a globally unique. GUIDs are useful when the table's record rows require uniqueness. For example, using this data type in the customer Identification Number column can differentiate between different customers.

SQL Data Type Interpretation