SQL Server database Field Data Type

Bit
Integer
The BIT data type is an integer, and its value can only be 0, 1, or null. This data type is used to store data with only two possible values, such as yes, no, true, or fa lse.
, On, or off

Int
Integer
Int data types can be stored from-231 (-2147483648) to 231 (2147483
An integer between 647 and. This type can be used for almost all numeric data stored in the database. This data type occupies 4 bytes in the database.

Smallint
Integer
Smallint
Data types can be stored from-
An integer between 215 (-32768) and 215 (32767. This type of data is useful for storing numeric data that is often limited to a specific range. This data type occupies 2 in the database
Byte Space

Tinyint
Integer
Tinyint data can be stored from 0 to 255.
An integer. It is useful when you only plan to store a limited number of values. This data type occupies 1 in the database
Bytes

Numeric
Exact numeric type
Numeric data type and decimal
Same type

Decimal
Exact numeric type
Decimal
Data types can be used to store numeric data with fixed precision and range from-1038-1 to 1038-1. When using this data type, you must specify the range and precision. The range is the total number of digits that can be stored around the decimal point. Precision is the number of digits stored on the right of the decimal point.

Money
Currency type
Money
The data type is used to indicate money and currency values. This data type can be stored from-922 billion to 9220
Billions of data, accurate to one thousandth of the currency unit

Smallmoney
Currency type
Smallmoney
The data type is used to indicate money and currency values. This data type can be stored from-214748.3648 to 214748.3647
The data is accurate to one thousandth of the currency unit.

Float
Approximate numeric type
Float
The data type is an approximate value type for floating point numbers. Floating Point Numbers are similar because not all numbers in the range can be accurately expressed. Floating Point number can be from-1.79e + 308 to 1.79e + 308
Any number

Real
Approximate numeric type
Real
The data type is similar to a floating point. It indicates the floating point number between-3.40e + 38 and 3.40e + 38.

Datetime
Datetime type
The datetime data type is used to represent the date and time. This data type was stored in
1. All Date and Time data during the day, accurate to 1/300 seconds or 3.33 milliseconds

Smalldatetime
Datetime type
Smalldatetime
The data type is used to indicate the date and time from January 1, January 1-20, 1900 to January 1, June 6, accurate to one minute.

Cursor
Special Data Type
Cursor
A data type is a special data type that contains a reference to a cursor. This type of data is used in the stored procedure and cannot be used when creating a table.

Timestamp
Special Data Type
Timestamp
A data type is a special data type used to create a unique digital data within the database range. A table can have only one timestamp column. The value of the timestamp column changes each time a row is inserted or modified. Although its name contains "time", the timestamp column is not a date that people can recognize. In a database, the timestamp value is unique.

Uniqueidentifier
Special Data Type
The uniqueidentifier data type is used to store a globally unique identifier (guid. GUID is indeed globally unique. This number has almost no chance of being rebuilt in another system. Newid can be used
Function or convert a string to a unique identifier to initialize a column with a unique identifier

Char
Character Type
The Char data type is used to store non-uniform encoded data with a fixed length. When defining a column of this type, you must specify the column length. This data type is useful when you always know the length of the data to be stored. For example, when you store data in zip code and 4 character format, you know that it always takes 10 characters. This data type has a maximum column width of 8000.
Characters

Varchar
Character Type
The varchar data type, same as the char type, is used to store non-uniform encoding character data. And char
Type is not the same, this data type is longer. When defining a column as the data type, you must specify the maximum length of the column. The biggest difference between it and char data types is that the storage length is not the column length, but the data length.

Text
Character Type
Text
Data types are used to store a large amount of non-uniform encoding character data. This data type can contain a maximum of 2 billion to characters

Nchar
Unified encoding type
Nchar
The data type is used to store fixed-length, uniform-encoding data. Unified encoding uses a dual-byte structure to store each character, rather than a single byte (in common text ). It allows a large number of extended characters. This data type can store 4000 characters, doubling the byte space used

Nvarchar
Unified encoding type
Nvarchar
The data type is used as variable-length, uniform-encoding data. This data type can store 4000 characters, and the byte space is doubled.

Ntext
Unified encoding type
Ntext
The data type is used to store a large amount of uniform encoding data. This data type can store 230
-1 or nearly 1 billion characters, doubling the byte space used

Binary
Binary data type
Binary data types are used to store up to 8000
Bytes of binary data with a fixed length. When the content of the input table is close to the same length, you should use this data type.

Varbinary
Binary data type
Varbinary
Data types are used to store up to 8000
Bytes of long binary data. This type of data should be used when the content size of the input table is variable.

Image
Binary data type
Image
The data type is used to store variable-length binary data up to 2 billion-1-1 or approximately bytes.

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

Compared with the preceding three data types, the names only contain letters "N", which indicate that the characters of the Unicode data type are stored. Friends who have written programs should be familiar with Unicode. It is enough to store only one byte of 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.

(2) datetime and smalldatetime
Datetime: Date and Time data from January 1, 3% to seconds.
Smalldatetime: Date and Time data from January 1, January 1-20, 1900 to January 1, June 6, 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: an integer of 1 or 0.

(4) decimal and numeric
The two data types are equivalent. There are two parameters: P (precision) and S (number of decimal places ). P specifies the maximum number of decimal digits that can be stored on the left and right of the decimal point. P must be from
The value between 1 and 38. S specifies the maximum number of decimal digits that can be stored on the right of the decimal point. s must be a value ranging from 0 to P. The default decimal place is 0.

(5) float and real
Float: floating point data from-1.79 ^ 308 to 1.79 ^ 308.
Real: floating point data from-3.40 ^ 38 to 3.40 ^ 38. In SQL
In server, the synonym for real is float (24 ).

When the database defines fields of the char type, I wonder if you will hesitate to select which of the following char, nchar, varchar, nvarchar, text, and ntext fields? There are two possible results. One is the choice of frugal people: it is best to use a fixed length to save some space for the change, and the process will be faster, if you cannot set the length, you have to set the length as small as possible. The other is that it doesn't matter. Try to use a variable type and enlarge the length as much as possible.

In view of the current favorable situation where hardware is as cheap as radish, it makes little sense to be entangled in such a small problem. However, if you do not find out about it, you will always feel sorry for the overworked CPU and hard disk.

(The following description is only valid for sqlserver ):

1. Use the following queries with caution when using non-UNICODE:
Select
F from t where f =
N 'xx'

Cause: the index cannot be used, because the database will convert F to Unicode first and then compare it with N 'xx '.

2. Char
The processing speed is similar to that of varchar with the same length (as described later)

3. The length of varchar does not affect the processing speed !!! (See the following explanation)

4. The total length of a column in an index can be up to 900 bytes. Therefore, nchar cannot create an index for varchar, Char, and nvarchar with a length greater than 900.

5. indexes cannot be created on text or ntext.

6. O/R
The attribute types of corresponding entities in mapping are generally string type, and char [] is rarely used. Therefore, if mapping is reasonable, the type of variable length is more consistent.

7. Generally, names in basic data tables basically use like in actual queries.
'% XX %', which means indexes cannot be used. Therefore, if the index is created for this field

8. Other fields like remark do not need to be queried at all, so no index is required.

9. The storage of varchar is the same as that of string, that is, length.
{Block} is used in this way, so the length of the varchar is irrelevant to the actual occupied space.

10. For a fixed-length field, extra space is required to store the null identifier. Therefore, if a char field contains many null values, unfortunately, your occupied space is larger than that without null (but this is not too large, because the null mark is stored in bits, but if you have only one null in a row, you need to identify it, in this case, you will waste 1 byte of space and sin !), At this time, you can use special identifiers for storage, such as 'nv'

11. Same as above. Therefore, indexes cannot take effect for such null queries. If you use a null marker instead, congratulations, you can use the index.

12. The comparison costs of char and varchar are the same. Now the key is to look at the cost of their index search. Because the search policies are the same, we should compare who occupies less space. If the number of characters is small, the char length is smaller than that of varchar. However, if the number is small, varchar may be less than char, it also depends on the enrichment of the actual filling value, such as varchar (3) and char (3). In theory, it should be char faster, but if it is Char (10) and varchar (10). If the enrichment is only 30%, it should be varchar in theory. Because varchar requires extra space to store the block length, as long as the length (1-fillfactor) is greater than the storage space (as if it is 2 bytes), it will be faster than char with the same length.

13. nvarchar is slower than varchar, and it will take up double space for non-Unicode characters. Why is this type introduced? Yes, it is for internationalization. For Unicode data, sorting rules do not work for them, rather than when Unicode characters process data in different languages, the sorting rule must be specified to work normally, so n type is of this benefit.

Summary:
1. If the data size is very large, and the length can be determined by 100% and the storage is only ANSI characters, then Char
2. Determine whether the length is not necessarily an ANSI character or nchar;
3. You are not sure about the length. If you want to query and use indexes, set them to 400 in nvarchar format;