Integers include bigint, int, smallint and tinyint. As can be seen from the meaning of identifiers, their representation scope is gradually narrowing.
L bigint: a big integer with the range of-263 (-9223372036854775808) ~ 263-1(9223372036854775807), the precision is 19, the number of decimal places is 0, and the length is 8 bytes.
L int: integer, the numerical range is -23 1 (-2,147,483,648) ~ 231-1(2,147,483,647.
Lsmall: short integer with the range of-215 (-32768) ~ 215-1(32767), precision of 5, decimal places of 0 and length of 2 bytes.
L tinyint: a short integer with a numerical range of 0 ~ 255, a length of 1 byte, a precision of 3, a decimal place of 0 and a length of 1 byte.
(2) exact integer type
Accurate integer data consists of integer part and decimal part, and its digits are all significant, which can completely and accurately store decimals. Exact integer types include decimal and number. Functionally, they are completely equivalent, the only difference is that decimal cannot be used for columns with the identity keyword.
The format of declaring exact integer data is numeric | decimal(p[, s]), where p is precision, s is decimal place, and the default value of s is 0. For example, if a column is designated as an exact integer with a precision of 6 and a decimal of 3, that is, decimal (6,3), then if 56.342689 is assigned to the recorded column, the column actually stores 56.3427.
Decimal and numeric can store digital data with fixed precision, and the number of decimal places ranges from-1038+1 to1038–1. Their storage length varies with accuracy, ranging from 5 bytes to 17 bytes.
L When the precision is 1 ~ 9, the length of storage bytes is 5;
L When the precision is 10 ~ 19, the length of storage bytes is 9;
L When the precision is 20 ~ 28, the length of storage bytes is13;
L when the precision is 29 ~ 38, the length of storage bytes is 17.
For example, if Numeric (8,3) is declared, 5 bytes are needed to store this type of data, while if Numeric (22,5) is declared, 13 bytes are needed to store this type of data.
Note: When declaring accurate integer data, its decimal places must be less than the precision; When assigning a value to precise integer data, the number of digits of the integer part of the assigned data must not be greater than the length of the integer part of the column.
(3) floating-point type
Floating-point type is also called approximate numerical type. As the name implies, this type cannot provide the accuracy of accurately representing data. When storing some numerical values with this type, some precision may be lost, so it can be used to deal with numerical values with very large range and low precision requirements, such as some statistics.
There are two approximate numerical data types: float[(n)] and real. Both types usually use scientific counting methods to represent data, that is, the form is mantissa E-order, such as 5.6432E20, -2.98E 10, 1.287659E-9 and so on.
L real: 4 bytes are used to store data, with the range of table number from-3.40e+38 to 3.40e+38, and the data accuracy is 7 significant digits.
L float: The value range of n in the definition is 1 ~ 53, which is used to indicate its accuracy and storage size. When n is between 1 ~ 24, it actually defines a real data with a storage length of 4 bytes and an accuracy of 7 significant digits. When n is between 25 and 53, the storage length is 8 bytes and the accuracy is 15 significant digits. When the default value is n, it means that n is between 25 and 53. The number range of floating-point data is-1.79E+308 to 1.79E+308.
(4) Currency type
SQL Server provides two data types specially used to handle money: money and smallmoney, which represent monetary values with decimal numbers.
L Money: The numerical range of the data is-263 (-922337203685477.5808) ~ 263-1(922337203685477.5807), the precision is 19, the number of decimal places is 4, and the length is 8 bytes. The number range of money is the same as bigint's, except that the money type has 4 decimal places. In fact, money is operated by integers, but the decimal point is fixed at the last 4 digits.
L smallmoney: the numerical range is–231(-2, 147, 48.3648) ~ 23 1- 1 (2, 147, 48.3647), and its accuracy is high. It can be seen that the relationship between smallmoney and int is just like that between money and bigint.
When inserting a value of type money or smallmoney into a table, you must add a currency symbol ($) in front of the data, and there cannot be a comma (,) in the middle of the data; If the currency value is negative, you need to add a minus sign (-) after the symbol $. For example, $ 15000.32, $680 and $-20000.9088 are all correct representations of monetary data.
(5) Configuration
Bit data in SQL Server is equivalent to logical data in other languages. It only stores 0 and 1, and its length is one byte. It should be noted, however, that SQL Server optimizes the storage of columns in a table: if there are no more than 8 columns in a table, these columns will be stored as one byte; If there are 9 to 16 columns in the table, these columns will be stored as two bytes, and so on for more columns.
When bit type data is assigned 0, its value is 0, and when it is not assigned 0 (for example, 100), its value is 1.
If a column in a table is bit data, then the column is not allowed to be empty (the concept of null value will be introduced later in this section) and is not allowed to be indexed.
(6) Personality type
Character data is used to store character strings, which can include letters, numbers and other special symbols (such as #, @,&; Wait). When entering a string, you need to enclose the symbols in the string with single or double quotation marks, such as "abc" and "ABC".
SQL Server character types include two types: fixed-length (char) or variable-length (varchar) character data types.
l char[(n)]
Fixed-length character data type, where n defines the length of character data, and n is between 1 and 8000. The default value is 1. When the column in the table is defined as char(n) type, if the actual length of the string to be stored is less than n, spaces are added at the end of the string to reach the length of n, so the length of char(n) is n ... For example, if the data type of the column is char(20) and the input string is "ahjm 1922", the character ahjm/kloc is stored. If the number of characters entered exceeds n, the excess will be truncated.
l varchar[(n)]
Variable-length character data type, where n is exactly the same as n in fixed-length character char, but here n represents the maximum length that a string can reach. The length of varchar(n) is the actual number of characters in the input string, not necessarily n. For example, if the data type of a column in the table is varchar( 100) and the input string is "ahjm 1922", the character ahjm 1922 is stored, and its length is 8 bytes.
Char can be used when the length of character data values in a column is close to the same, such as name; When the length of data values in a column is quite different, it is more appropriate to use varchar, which can save storage space.
(7) Unicode character types
Unicode is a "unified character coding standard", which is used to support the storage and processing of character data in international non-English languages. Unicode character types of SQL Server can store various characters defined by Unicode standard character set.
Unicode character types include nchar[(n)] and nvarchar[(n)]. Nchar is the data type of fixed-length Unicode data, and nvarchar is the data type of variable-length Unicode data, both of which use UNICODE UCS-2 character set.
Lnchar [(n)]: nchar [(n)] is fixed-length Unicode character data containing n characters, where the value of n is between 1 and 4000, and the default value is 1. The length is 2n bytes. If the length of the input string is less than n, it will be supplemented with blank characters.
L nvarchar [(n)]: nvarchar [(n)] is variable-length Unicode character data containing at most n characters, where the value of n is between 1 and 4000, and the default value is 1. The length is twice the number of characters entered.
In fact, the usage of nchar and nvarchar is very similar to that of char and varchar, but the character set is different (the former uses Unicode character set and the latter uses ASCII character set).
(8) Text type
When you need to store a lot of character data, such as long notes, log information, etc. The maximum length of character data is 8000 characters, which may make them unable to meet the requirements of this application, so text data can be used at this time.
Text types include text and ntext, which correspond to ASCII characters and Unicode characters respectively. A text type can represent a maximum length of 231-1(2147483647) characters, and its data storage length is several bytes of the actual characters. Ntext can represent Unicode characters with the maximum length of 230- 1 (1 073,741823), and the storage length of its data is twice the actual number of characters (in bytes).
(9) Binary types
Binary data types represent bit data streams, including binary (fixed length) and variable length (variable length).
L binary [(n)]: n bytes of fixed-length binary data. The value range of n is 1 to 8000, and the default value is 1. The storage length of binary (n) data is n+4 bytes. If the length of input data is less than n, the insufficient part is filled with 0; If the input data length is greater than n, the redundant part will be truncated.
When entering binary values, you can use 0-9 and a-f (letters can be uppercase or lowercase) to add 0x before the data. Therefore, binary data is sometimes called hexadecimal data. For example, 0xFF and 0x 12A0 represent values FF and 12A0 respectively. Because the maximum number of bytes is FF, every two bits of data in "0x" format account for 1 byte.
Lvarbinary [(n)]: n bytes of variable-length binary data. The value range of n is 1 to 8000, and the default value is 1. The storage length of varbinary(n) data is the actual input data length +4 bytes.
(10) Date and Time Type
Date and time type data is used to store date and time information, including datetime and smalldatetime.
Ldatetime: Datetime type can represent date and time data ranging from 1753 65438+ 10/to nine thousand nine hundred and ninety-nine 65438+February 3 1, with an accuracy of 3 seconds (3.33 milliseconds or 0.00333 seconds), such as/kloc.
The data length of datetime type is 8 bytes, and the date and time are stored in 4 bytes respectively. The first four bytes are used to store the number of days of 1900 65438+ 10 month 1 in date-time type data. A positive number means that the date is after1900 65438+10.65438, and a negative number means that the date is1900 65438+/0.65438. The last four bytes are used to store the number of milliseconds in date-time data starting from 12:00(24-hour clock).
Users input date and time data in the form of character strings, and the system also outputs date and time data in the form of character strings. The string form of date and time data input by users and output by the system is called "external form" of date and time data, while the storage form of date and time in the system is called "internal form". SQL Server is responsible for the conversion between the two representations of date and time data, including the legality check.
When the user gives the date and time data value, the date part and the time part are given separately.
The common format of the date part is as follows:
Year and month 2006 54 38+0 2006 54 38+0 1 month 20
Year 200 1 20 Jan
On [,]2006 1 20th, 5438+0.
Month, year, day, 2006 1 month 5438+0 20
Year, month, day, 2006 1 20th, 5438+0, 2006 1 20th, 5438+0.
Year, month, 20 200 1 Jan
Year (4 digits) 200 1 means 200 1 year 1 month 1 day.
Year and month 200 10 120, 010/20.
Month/day/year 0 1/20/0 1, 1/20/0 1, 0 1/20/200 1.
Month-day-year 0 1-20-0 1,1-20-0/,0 1-20-200 1,1-.
Month, day, year, 0 1.20.0 1 .20.01,0 1.20, 200065688.
Note: The year can be represented by 4 or 2 digits, and the month and day can be represented by 1 or 2 digits.
The commonly used representation formats of the time part are as follows:
Hours: 10:20, 08:05.
Hours: minutes: seconds 20:15:18,20:15:18.2.
Hours: minutes: seconds: milliseconds 20: 15: 18:200.
Time: AM | PM 10: AM 10, PM 10: 10.
Lsmall datetime: data of type smalldatetime can represent the date and time from 1900 1 to June 6, 2079. Data is accurate to minutes, that is, values of 29.998 seconds or less are rounded down to the nearest minute, and values of 29.999 seconds or higher are rounded up to the nearest minute. The storage length of Smalldatetime type data is 4 bytes, and the first 2 bytes are used to store the number of days after the date part in smalldatetime type data as 1900 1. The last two bytes are used to store the number of minutes from 12 noon in the time part of smalldatetime data.
The format of data of type smalldatetime entered by users is exactly the same as that of data of type datetime, but their internal storage may be different.
(1 1) timestamp type
The identifier is a timestamp. If the data type of a column is defined as timestamp type when the table is created, the counter value will be automatically added to the column whenever a new row is added to the table or an existing row is modified, that is, the original timestamp value will be incremented. The value of the timestamp column of a record actually reflects the relative (relative to other records) order in which the system modifies the record. A table can only have one timestamp column. The value of timestamp data is actually binary format data with a length of 8 bytes.
(12) image data type
The identifier is image, which is used to store pictures, photos, etc. Actually, variable-length binary data is stored, ranging from 0 to 23 1- 1 (2,147,483,647) bytes.
(13) Other data types
In addition to the above commonly used data types, SQL Server 2000 also provides several other data types: cursor, sql_variant, table and uniqueidentifier.
Cursor: is a cursor data type used to create cursor variables or define the output parameters of stored procedures.
Sql_variant: it is a data type that stores the values of various data types supported by SQL Server (except text, ntext, image, timestamp and sql_variant). The maximum length of Sql_variant can reach 80 16 bytes.
Table: is the data type used to store the result set and can be used for subsequent processing.
Uniqueidentifier: is a unique identifier type. The system will generate a unique identification value for this type of data, which is a binary data with a length of 16 bytes.