SQL2000 field type description

Data Type
In Microsoft SQL Server, each column, local variable, expression, and parameter has a related data type, which specifies the data type (integer, character, money, etc.) that an object can hold). SQL Server provides a system data type set that defines all data types that can be used with SQL Server. The following lists the data type sets provided by the system.
You can define a user-defined data type, which is the alias of the Data Type provided by the system. For more information about user-defined data types, see sp_addtype and create user-defined data types.
When two expressions with different data types, sorting rules, precision, decimal places, or lengths are combined using operators:
Apply the Data Type precedence rule to the Data Type of the input expression to determine the Data Type of the obtained value. For more information, see Data Type precedence.
If the data type of the result is Char, varchar, text, nchar, nvarchar, or ntext, the sorting rule of the result value is determined by the priority rule of the sorting rule. For more information, see sorting rule priority.
The precision, decimal places, and length of the result depend on the precision, decimal places, and length of the input expression. For more information, see precision, decimal places, and length.
SQL Server provides a synonym for data types for SQL-92 compatibility. For more information, see Data Type synonyms. Fcpw ~ O ^
G [1 $ nwww.tzsy.cn
Exact number
Integer
Int, bigint, smallint, and tinyint
Use the exact numeric data type of integer data.
Bigint
Integer Data from-2 ^ 63 (-9223372036854775808) to 2 ^ 63-1 (9223372036854775807) (All numbers ). The storage size is 8 bytes.
Int
Integer Data from-2 ^ 31 (-2,147,483,648) to 2 ^ 31-1 (2,147,483,647) (All numbers ). The storage size is 4 bytes. The SQL-92 synonym for the Int Is integer.
Smallint
Integer Data from-2 ^ 15 (-32,768) to 2 ^ 15-1 (32,767. The storage size is 2 bytes.
Tinyint
Integer Data from 0 to 255. The storage size is 1 byte.
Note
The bigint data type is supported where integer values are supported. However, bigint is used in some special cases. When the integer value exceeds the range supported by the int data type, bigint can be used. In SQL Server, the int data type is the main integer data type.
In the data type precedence table, bigint is located between smallmoney and Int.
The function returns bigint only when the parameter expression is of the bigint data type. SQL Server does not automatically upgrade other integer data types (tinyint, smallint, and INT) to bigint.
Bit
Integer Data 1, 0, or null.
Note
You cannot use indexes for bit columns.
Microsoft SQL Server optimizes bit column storage. If a table contains no more than eight bit columns, these columns are stored as one byte. If the table contains 9 to 16 bit columns, these columns are stored as two bytes. This applies to more columns.
Decimal and numeric
Numeric data type with fixed point precision and decimal places.
Decimal [(P [, S])] and numeric [(P [, S])]
The number of fixed point precision and decimal places. When the maximum precision is used, the valid values are from-10 ^ 38 + 1 to 10 ^ 38-1. SQL-92 synonyms for decimal are DEC and Dec (P, S ).
P (precision)
Specifies the maximum number of decimal digits that can be stored on the left and right of the decimal point. The precision must be between 1 and the maximum precision. The maximum precision is 38.
S (decimal places)
Specifies the maximum number of decimal digits that can be stored on the right of the decimal point. The number of decimal places must be between 0 and P. The default number of decimal places is 0, so 0 <= S <= P. The maximum storage size varies based on precision.
Precision storage bytes
1-9 5
10-19 9
20-28 13
29-38 17
Money and smallmoney
The currency data type that represents the currency or cash value.
Money
The value of the currency data is between-2 ^ 63 (-922,337,203,685,477.5808) and 2 ^ 63-1 (+ 922,337,203,685,477.5807), accurate to 10 thousandth of the currency unit. The storage size is 8 bytes.
Smallmoney
The value of the currency data is between-214,748.3648 and + 214.748, and 3647, accurate to the tenth of the monetary unit. The storage size is 4 bytes.

Approximate Number
Float and real
Indicates the approximate numeric data type of floating point numeric data. Floating point data is an approximate value; not all data within the data type range can be accurately expressed.

Syntax
Float [(n)]
Floating Point Data from-1.79e + 308 to 1.79e + 308. N is the number of digits used to store the ending number of scientific records float, indicating its accuracy and storage size. N must be a value between 1 and 53.

Precision storage size of N range
1-24 7-digit 4-byte
25-53 15-digit 8-byte

Microsoft SQL Server float [(n)] the data type complies with the SQL-92 standards for all N values from 1 to 53. The synonym for double precision is float (53 ).

Real
Floating Point Data from 3.40e + 38 to 3.40e + 38. The storage size is 4 bytes. In SQL Server, the synonym for real is float (24 ).

Datetime and smalldatetime
Represents the date and time data types of the date and the time within the day.

Datetime
From the date and time data of January 1, 3% to 3.33 seconds (equivalent to 0.00333 milliseconds or seconds ). As shown in the following table, adjust the value to an increment of. 000,. 003, or. 007 seconds.
Example adjusted
01/01/98 23:59:59. 999 00:00:00. 000
01/01/98 23:59:59. 995,
01/01/98 23:59:59. 996,
01/01/98 23:59:59. 997, or
01/01/98 23:59:59. 998 23:59:59. 997
01/01/98 23:59:59. 992,
01/01/98 23:59:59. 993,
01/01/98 23:59:59. 994 23:59:59. 993
01/01/98 23:59:59. 990 or
01/01/98 23:59:59. 991 23:59:59. 990

Microsoft SQL Server rejects all values that cannot be recognized as between January 1, 1753 and January 1, 9999.

Smalldatetime
The date and time data from January 1, January 1-20, 1900 to January 1, June 6 are accurate to minutes. The smalldatetime value of 29.998 s or lower is rounded down to the nearest minute, And the smalldatetime value of 29.999 s or higher is rounded up to the nearest minute.

-- Returns time as 12: 35
Select cast ('2017-05-08 12:35:29. 2000 'As smalldatetime)
Go
-- Returns time as 12: 36
Select cast ('2017-05-08 12:35:29. 2000 'As smalldatetime)
Go

Note
Microsoft SQL Server uses two 4-byte Integers to store values of the datetime data type. The number of days before or after the first 4-byte storage base date (January 1, 1900. The base date is the system reference date. Datetime values earlier than January 1, January 1, 1753 are not allowed. The other 4-byte storage represents the daily time in milliseconds after midnight.
The smalldatetime data type stores the date and the daily time, but the accuracy is lower than datetime. SQL Server stores the smalldatetime value as two 2-byte integers. The first 2-byte is stored in the number of days after January 1, January 1, 1900. The other two bytes store the minute after midnight. The date range is from January 1, January 1-20, 1900 to January 1, June 6, accurate to minutes.

String

Char and varchar
Data Types of fixed-length (char) or variable-length (varchar) characters.

Char [(n)]
Character data with a fixed length of n Bytes and is not Unicode. N must be a value between 1 and 8,000. The storage size is n Bytes. The synonym for char in the SQL-92 is character.

Varchar [(n)]
Variable-length and non-UNICODE character data with a length of n Bytes. N must be a value between 1 and 8,000. The storage size is the actual length of the input data bytes, rather than n Bytes. The length of the input data can be zero. The synonym for varchar in the SQL-92 is Char varying or character varying.

Note
If n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the cast function, the default length is 30.
The default database sorting rules will be assigned to objects using Char or varchar unless a specific sorting rule is assigned to the Collate clause. This sorting rule controlsCodePage.
For websites that support multiple languages, Unicode nchar or nvarchar data types should be considered to minimize character conversion issues. If Char or varchar is used:
If you want the data values in the column to be close to the same size, use char.
If you want the data values in the column to be significantly different, use varchar.
If set ansi_padding is off when you execute create table or alter table, a char column defined as null will be processed as varchar.
When the collation code page uses double-byte characters, the storage size is still n Bytes. Depending on the string, the storage size of n Bytes may be less than n characters.

Ntext
Unicode data with a variable length. The maximum length is 2 ^ 30-1 (1,073,741,823) characters. (See ntext, text, and image .)

Ntext, text, and image
It is used to store the fixed-length and variable-length data types of large non-Unicode, Unicode, and binary data. Unicode data uses the Unicode UCS-2 character set.

Ntext
The maximum length of Unicode data with a variable length is 230-1 (1,073,741,823) characters. The storage size is twice the number of input characters (in bytes ). Ntext is synonymous with national text in the SQL-92.

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

Image
The variable-length binary data is between 0 and 231-1 (2,147,483,647) bytes.

 

Binary string

Binary and varbinary
Binary data type of a fixed length (Binary) or variable length (varbinary.

Binary [(n)]
A fixed length of n Bytes of binary data. N must be from 1 to 8,000. The storage space is n + 4 bytes.

Varbinary [(n)]
N Bytes of variable-length binary data. N must be from 1 to 8,000. The size of the bucket is the length of the actual input data plus 4 bytes, rather than n Bytes. The input data length may be 0 bytes. The synonym for varbinary in the SQL-92 is binary varying.

Image
A variable-length binary data with a maximum length of 2 ^ 31-1 (2,147,483,647) bytes. (See ntext, text, and image .)

Note
If n is not specified in the data definition or variable declaration statement, the default length is 1. If n is not specified by the cast function, the default length is 30.
Use binary when the column data items are of the same size.
Varbinary is used when the column data item sizes are inconsistent.

Other data types

Cursor
This is a data type of the output parameter of a variable or stored procedure. These parameters include references to the cursor. Variables created using the cursor data type can be empty.
Some operations can reference variables and parameters with the cursor data type. These operations include:
Declare @ local_variable and set @ local_variable statements.
Open, fetch, close, and deallocate cursor statements.
Stored Procedure output parameters.
Cursor_status function.
Sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns system stored procedures.
# The cursor data type cannot be used for columns in the create table statement.

SQL _variant
A data type that stores various data types supported by SQL Server (except for text, ntext, image, timestamp, and SQL _variant.
SQL _variant can be used in columns, parameters, and variables and return user-defined function values. SQL _variant allows these database objects to support values of other data types.

Syntax
SQL _variant

Note
SQL _variant columns can contain rows of different data types. For example, columns defined as SQL _variant can store int, binary, and char values. Values stored in SQL _variant cannot be text, ntext, image, timestamp, or SQL _variant.

SQL _variant can be up to 8016 bytes in length.

SQL _variant data type must be projected into its basic data type value before being involved in addition and subtraction.

You can assign a default value to SQL _variant. This data type can also use null as its basic value, but the null value is not associated with the basic type. In addition, SQL _variant can not use other SQL _variant as its basic type.

A unique, primary key, or foreign key can contain columns of the SQL _variant type, but the total length of data values containing the given row key should not be greater than the maximum length of the index (currently 900 bytes ).

The table can have any number of SQL _variant columns.

SQL _variant cannot be used in containstable or freetexttable.

ODBC does not fully support SQL _variant. Therefore, when Microsoft ole db for ODBC is usedProgram(Msdasql), The SQL _variant column query is returned as binary data. For example, the SQL _variant column containing string data 'ps2091' is returned as 0x505332303931.

Table
A special data type used to store result sets for subsequent processing. This data type is mainly used to temporarily store a group of rows. These rows are returned as the result set of the Table value function.

Note
You can declare functions and variables as table types. Table variables can be used in functions, stored procedures, and batch processing.
Try to use table variables instead of temporary tables. Table variables have the following advantages:
The behavior of table variables is similar to that of local variables and has a clearly defined scope. This scope is the function, stored procedure, or batch processing that declares the variable.
In its scope, table variables can be used as regular tables. This variable can be applied to tables or table expressions in select, insert, update, and delete statements. However, table cannot be used in the following statements:
Insert into table_variable exec stored procedure.
Select select_list into table_variable statement.
When the function, stored procedure, or batch processing of the table variable is defined, the table variable is automatically cleared.
Using table variables in a stored procedure reduces the amount of recompilation in a stored procedure compared to using a temporary table.

Transactions involving table variables only exist during table variable update. This reduces the need for table variables to lock and record resources.
Table variables cannot be assigned values. In addition, because the table variable scope is limited and is not part of the persistent database, it is not affected by transaction rollback.

Timestamp
Timestamp indicates the number of binary values automatically generated, which is unique in the database. Timestamp is generally used to add version stamps to table rows. The storage size is 8 bytes.

Note
The Transact-SQL timestamp data type is different from the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the transact-SQL datetime data type.
In future versions, Microsoft SQL Server may modify the behavior of the transact-SQL timestamp data type to make it consistent with the behavior defined in the standard. By that time, the current timestamp data type will be replaced by the rowversion data type.
Microsoft SQL Server 2000 introduces the rowversion synonym of the timestamp data type. Try to use rowversion instead of timestamp in DDL statements. Rowversion is restricted by the behavior of Data Type synonyms. For more information, see Data Type synonyms.

In the create table or alter table statement, you do not need to provide a column name for the timestamp data type:
Create Table exampletable (prikey int primary key, timestamp)
If no column name is provided, SQL Server generates a timestamp column name. Rowversion data type synonyms do not have such behavior. The column name must be provided when rowversion is specified.
A table can have only one timestamp column. Values in the timestamp column are updated each time a row containing the timestamp column is inserted or updated. This attribute makes the timestamp column not suitable for use as a key, especially not as a primary key. Any update to the row changes the timestamp value to change the key value. If the column belongs to the primary key, the old key value is invalid, and the foreign key that references the old value is no longer valid. If the table is referenced in a dynamic cursor, all updates change the row position of the cursor. If this column belongs to the index key, all updates to the data row will also lead to index updates.
The timestamp column that cannot be null is semantically equivalent to the binary (8) column. The empty timestamp column can be semantically equivalent to the varbinary (8) column.

Uniqueidentifier
Guid ).

Note
The uniqueidentifier data type column or local variable can be initialized to a value in two ways:
Use the newid function.

Convert a String constant to the following format (XXXXXXXX-XXXX-xxxxxxxxxxxx, where each X is a hexadecimal number in the range of 0-9 or a-f ). For example, 6f9619ff-8b86-d011-b42d-00c04fc964ff is a valid uniqueidentifier value.
The comparison operator can be used with the uniqueidentifier value. However, the arrangement is not achieved by comparing the bit modes of two values. Only the operations that can be performed on the uniqueidentifier value can be compared (=, <>,<, >,<=, >=) and checked null (is null and is not null ). Other arithmetic operators are not allowed. All column constraints and attributes (except identity) can be used for the uniqueidentifier data type.