Data type of the database

(1) Integer type

integers include bigint, int, smallint, and tinyint, which can be seen from the meaning of identifiers, and their range of representations is gradually shrinking.

L bigint: Large integer, number range is-263 (-9223372036854775808) ~263-1 (9223372036854775807), with a precision of 19, a scale of 0, and a length of 8 bytes.

L int: integer, number range is-231 (-2,147,483,648) ~231-1 (2,147,483,647), its precision is 10, the number of decimal digits is 0, and the length is 4 bytes.

L smallint: A short integer with a range of-215 (-32768) ~215-1 (32767) with a precision of 5, a scale of 0, and a length of 2 bytes.

L tinyint: Short integer, number range is 0~255, length is 1 bytes, its precision is 3, the scale is 0, the length is 1 bytes.

(2) Precise integer type

Exact integer data consists of integral and fractional parts, all of which are valid bits and can store decimal numbers with complete precision. The exact integer type consists of two types: decimal and numeric. Functionally, the two are completely equivalent, the only difference being that decimal cannot be used for columns with the IDENTITY keyword.

The format for declaring exact integer data is numeric | Decimal (P[,s]), where p is precision, S is the number of decimal digits, and S is the default value of 0. For example, if you specify a column as an exact integer, with a precision of 6 and a scale of 3, or decimal (6,3), then when you assign a value of 56.342689 to a record, the column actually stores 56.3427.

Decimal and numeric can store digital data of fixed precision and decimal digits from 1038 +1 to 1038–1, whose storage length varies with precision, with a minimum of 5 bytes and a maximum of 17 bytes.

When the accuracy is 1~9, the storage byte length is 5;

When the accuracy is 10~19, the storage byte length is 9;

When the accuracy is 20~28, the storage byte length is 13;

When the accuracy is 29~38, the storage byte length is 17.

For example, if you declare numeric (8,3), it takes 5 bytes to store that type of data, and if you have a declaration numeric (22,5), it takes 13 bytes to store that type of data.

Note: When declaring precise integer data, its scale must be less than precision, and when assigning a value to an exact integer, you must make the integer portion of the assigned data not greater than the length of the integer part of the column.

(3) floating-point type

Floating-point types are also called approximate numeric types. As the name implies, this type does not provide precise representation of the data, and it is possible to lose some precision when using this type to store certain values, so it can be used to handle values that are very large in scope and are not very high for accuracy requirements, such as some statistics.

There are two approximate numeric data types: float[(n)] and real, both of which are usually represented by scientific notation: the mantissa e-order, such as 5.6432e20,-2.98e10,1.287659e-9.

L Real: Use 4 bytes to store data, the number of tables is -3.40e + 38 to 3.40E + 38, the data precision is 7 digits.

L Float: The N range of values in the definition is 1~53, which indicates its precision and storage size. When n is between 1~24, a real data is actually defined, with a storage length of 4 bytes and a precision of 7 digits, and when n is between 25~53, the storage length is 8 bytes and the precision is 15 digits valid. When the default n, represents n between 25~53. The number of float data ranges from 1.79E+308 to 1.79E+308.

(4) Currency type

SQL Server provides two data types that are designed to handle currencies: money and smallmoney, which represent currency values in decimal numbers.

L Money: The number of data ranges from 263 (-922337203685477.5808) ~263-1 (922337203685477.5807), with a precision of 19, a scale of 4, and a length of 8 bytes. The range of money is the same as the bigint, except that the money type has 4 decimal places, in fact, money is calculated by integer, but the decimal point is fixed to the last 4 bits.

L SmallMoney: The number range is –231 ( -2,147,48.3648) ~231-1 (2,147,48.3647), with a precision of 10, a scale of 4, and a length of 4 bytes. It can be seen that the relationship between smallmoney and int is like the relationship between money and bigint.

When you insert a value of type money or smallmoney into a table, you must precede the data with a currency representation symbol ($), and the data cannot have a comma (,) in the middle, or a minus sign (-) after the symbol $ if the currency value is negative. For example: $15000.32,$680,$-20000.9088 are the correct representation of the currency data.

(5) Position type

Bit-type data in SQL Server is equivalent to logical data in other languages, it stores only 0 and 1, and is one byte in length. Note, however, that SQL Server optimizes storage for bit type columns in tables: If there are no more than 8 bit columns in a table, these columns will be stored as a byte, and if there are 9 to 16 bit columns in the table, the columns will be stored as two bytes, more columns, and so on.

When 0 o'clock is assigned to the bit type data, its value is 0, and the value is 1 if it is not 0 (for example, 100).

If a column in a table is a bit type of data, it is not allowed to be null (described in this section for null values) and is not allowed to be indexed.

(6) Character type

Character data is used to store strings, which can include letters, numbers, and other special symbols (such as #, @, &, and so on). When entering a string, enclose the symbol in a string in either single or double quotation marks, such as ' abc ', ' Abc<cde '.

SQL Server character types include two classes: fixed-length (char) or variable-length (varchar) character data types.

L char[(N)]

Fixed-length character data type, where n defines the length of the character data, n is between 1 and 8000 and defaults to 1. When a column in a table is defined as a char (n) type, if the actual string length to be stored is less than n, a space is added at the end of the string to reach the length n, so the length of char (n) is n. For example, if a column has a data type of char (20) and the string entered is "ahjm1922", the character ahjm1922 and 12 spaces are stored. If the number of characters entered exceeds N, the part that is exceeded is truncated.

L varchar[(N)]

Variable-length character data type where n is exactly the same as N in a fixed-length character char, but here n represents the maximum length a string can reach. The length of varchar (n) is the actual number of characters of the input string, not necessarily n. For example, the data type of a column in a table is varchar (100) and the string entered is "ahjm1922", then the character ahjm1922 is stored, and its length is 8 bytes.

When a character data value in a column is close to the same length, such as a name, you can use char at this point, and when the data value in the column is significantly different, varchar is more appropriate to save storage space.

(7) Unicode character type

Unicode is a "Unified character encoding standard" that supports the storage and processing of character data in non-English languages internationally. The Unicode character type of SQL Server can store the various characters defined by the Unicode standard character set.

Unicode character types include nchar[(n)] and nvarchar[(n)]. NChar are data types for fixed-length Unicode data, and nvarchar are data types for variable-length Unicode data, both using the Unicode UCS-2 character set.

L nchar[(N)]:nchar[(n)] is a fixed-length Unicode character data with n characters, the value of n is between 1 and 4,000, and the default is 1. Length is 2n bytes. If you enter a string that is less than n long, it will be filled with white space characters.

L nvarchar[(N)]:nvarchar[(n)] is a variable-length Unicode character data with a maximum of n characters, the value of n is between 1 and 4,000, and the default is 1. The length is twice times the number of characters entered.

In fact, the use of nchar, nvarchar and char, varchar is very similar, except that the character set is different (the former uses the Unicode character set, which uses the ASCII character set).

(8) Text type

When you need to store large amounts of character data, such as longer notes, log information, and so on, the limit of 8,000 characters for character data may make them not meet the needs of this application, and text data can be used at this time.

Text types include text and ntext, which correspond to ASCII characters and Unicode characters, respectively. The text type can represent a maximum length of 231-1 (2,147,483,647) characters, and the storage length of the data is the actual number of characters in bytes. ntext can represent a maximum length of 230-1 (1,073,741,823) Unicode characters, whose data is stored in twice times the length of the actual number of characters (in bytes).

(9) Binary type

The binary data type represents a bit data stream, including binary (fixed length) and varbinary (variable length).

l binary [(n)]: fixed-length n byte binary data. The n values range from 1 to 8,000, and the default is 1. Binary (n) data has a storage length of n+4 bytes. If the input data length is less than n, then the insufficient portion is filled with 0, and if the input data length is greater than n, the excess portion is truncated.

When entering a binary value, add 0x to the front of the data, and you can use a number symbol of 0-9, a-f (the letter case can be). Therefore, binary data is sometimes referred to as hexadecimal data. For example, 0xFF, 0x12a0 represent values FF and 12a0 respectively. Since the maximum number of bytes per byte is FF, the data in "0x" format takes up 1 bytes per two bits.

l varbinary [(N)]:n bytes of variable-length binary data. The n values range from 1 to 8,000, and the default is 1. The storage length of the varbinary (n) data is the actual input data length + 4 bytes.

(10) Date and Time type

Date-time type data is used to store date and time information, including both DateTime and smalldatetime classes.

The L Datetime:datetime type can represent date and time data from January 1, 1753 to December 31, 9999, with an accuracy of 3% seconds (3.33 milliseconds or 0.00333 seconds), for example, 1 to 3 milliseconds values are expressed as 0 milliseconds, 4 to 6 The value of milliseconds is expressed as 4 milliseconds.

The datetime type data length is 8 bytes, and the date and time are stored using 4 bytes, respectively. The first 4 bytes are used to store the number of days from January 1, 1900 in the DateTime type data, and the positive numbers indicate that the date is after January 1, 1900 and the negative is the date before January 1, 1900. The latter 4 bytes are used to store the number of milliseconds in the datetime type data from 12:00 (24-hour).

The user enters the datetime type data as a string, the system also outputs the DateTime type data as a string, and the string form of the DateTime type data entered by the user into the system and the system output is called the "external form" of the datetime type data. And the storage form of datetime in the system is called "internal form", SQL Server is responsible for the conversion between two representations of datetime type data, including the legality check.

When the user gives the datetime type data value, the date part and the time part are given separately.

The representation of the date part is commonly used in the following format:

Month Day 2001 Jan 20, 2001 Janary 20

Annual Sun and Moon 2001

Month Day [,] year Jan 20 2001, Jan 20,2001, Jan 20,01

Month-on-year-day Jan 2001 20

Sun Moon [,] year 2001, jan,2001

Day 2001 Jan

Year (4 digits) 2001 means January 1, 2001

Date 20010120, 010120

Month/day/year 01/20/01, 1/20/01, 01/20/2001, 1/20/2001

Month-day-year 01-20-01, 1-20-01, 01-20-2001, 1-20-2001

month. 01.20.01, 1.20.01, 01.20.2001, 1.20.2001

Description: The year can be expressed as either 4-bit or 2-bit, and the month and day can be expressed as 1-bit or 2-bit.

The typical presentation format for the time section is as follows:

Hours: minutes 10:20, 08:05

Hours: minutes: seconds 20:15:18, 20:15:18.2

Hours: minutes: seconds: milliseconds 20:15:18:200

Time: Minute am| PM 10:10AM, 10:10pm

L smalldatetime:smalldatetime type data can represent the date and time from January 1, 1900 to June 6, 2079, the data is accurate to the minute, that is, 29.998 seconds or less the value is rounded down to the nearest minute, 29.999 seconds, or higher Rounds up to the nearest minute. smalldatetime type data has a storage length of 4 bytes, and the first 2 bytes are used to store the number of days from the date portion of the smalldatetime type data after January 1, 1900 The following 2 bytes are used to store the minutes of the time portion of the smalldatetime type data from 12 o'clock noon.

The user enters smalldatetime type data in exactly the same format as the datetime type data, except that their internal storage may be different.

(11) Time stamp type

The identifier is timestamp. When you create a table that defines a column's data type as a timestamp type, each time you add a new row to the table or modify an existing row, the system automatically adds a counter value to the column, adding an increment to the original timestamp value. The value of the record timestamp column actually reflects the relative (relative to other records) order that the system modifies for that record. A table can have only one timestamp column. The value of the timestamp type data is actually binary format data, which is 8 bytes in length.

(12) Image data type

An identifier is an image that is used to store pictures, photos, and so on. The actual storage is variable-length binary data, between 0 and 231-1 (2,147,483,647) bytes.

(13) Other data types

In addition to the common data types described above, SQL Server 2000 provides several other data types: cursor, sql_variant, table, and uniqueidentifier.

Cursor: is a cursor data type that is used to create a cursor variable or to define the output parameters of a stored procedure.

Sql_variant: is a data type that stores the various data types supported by SQL Server (except text, ntext, image, timestamp, and sql_variant) values. The maximum length of the sql_variant can be up to 8016 bytes.

Table: Is the data type used to store the result set, and the result set is available for subsequent processing.

uniqueidentifier: is a unique identifier type. The system will generate a unique identity value for this type of data, which is a 16-byte long binary data.

Data type of the database