软件测试SQL2000里的数据类型(5)

　　Cursor datatype is used for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. The variables created with the cursor data type are nullable. You cannot use this datatype for a column in a CREATE TABLE statement.

　　sql_variant datatype is used to store values of various SQL Server supported data types, except text, ntext, timestamp, and sql_variant. The maximum length of sql_variant datatype is 8016 bytes. You can store in one column of type sql_variant the rows of different data types, for example int, char, and varchar values.

　　This is the example of using sql_variant datatype:

　　SET NOCOUNT ON GO if object_id('tbTest') is not null drop table tbTest GO CREATE TABLE tbTest ( id int primary key, sql_v sql_variant ) GO INSERT INTO tbTest VALUES (1, 1) INSERT INTO tbTest VALUES (2, 1.0) INSERT INTO tbTest VALUES (3, '1') INSERT INTO tbTest VALUES (4, 0x01) GO SELECT sql_v FROM tbTest GO

　　Here is the result set:

　　sql_v ------------------------ 1 1.0 1 0x01

　　Table datatype is used to store a result set for later processing. You cannot use this datatype for a column in a CREATE TABLE statement. You should use DECLARE @local_variable to declare variables of type table. Table variables should be used instead of temporary tables, whenever possible, because table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined, and table variables require less locking and logging resources.

　　This is the example of using table datatype:

　　DECLARE @tbl table (id int) INSERT INTO @tbl VALUES (1) INSERT INTO @tbl VALUES (2) SELECT * FROM @tbl

　　Here is the result set:

　　id ----------- 1 2

　　Timestamp datatype is stored in 8 bytes as binary(8) datatype. The timestamp value is automatically updated every time a row containing a timestamp column is inserted or updated.

　　Timestamp value is a monotonically increasing counter whose values will always be unique within a database and can be selected by queried global variable @@DBTS.

　　Uniqueidentifier is a GUID (globally unique identifier). A GUID is a 16-byte binary number that is guaranteed to be unique in the world. This datatype is usually used in replication or as primary key to unique identify rows in a table.

　　You can get the new uniqueidentifier value by calling the NEWID function.

　　Note You should use IDENTITY property instead of uniqueidentifier, if global uniqueness is not necessary, because the uniqueidentifier values are long and more slowly generated.

　　Text and image datatypes

　　Text and image data are stored on the Text/Image pages, not on the Data pages as other SQL Server 2000 data.

　　There are three datatypes in this category:

　　text

　　ntext

　　image

　　Text datatype is a variable-length datatype that can hold up to 2147483647 characters. This datatype is used when you want to store the character values with the total length more than 8000 bytes.

　　ntext datatype is a variable-length unicode datatype that can hold up to 1073741823 characters. This datatype is used when you want to store the variable-length unicode data with the total length more than 4000 bytes.

　　Image datatype is a variable-length datatype that can hold up to 2147483647 bytes of binary data. This datatype is used when you want to store the binary values with the total length more than 8000 bytes. This datatype is also used to store pictures.

　　Unicode Character datatypes

　　A column with unicode character datatype can store all of the characters that are defined in the various character sets, not only the characters from the particular character set, which was chosen during SQL Server Setup. Unicode datatypes take twice as much storage space as non-Unicode datatypes.

　　The unicode character data, as well as character data, can be used to store any combination of letters, symbols, and numbers. You should enclose unicode character data with quotation marks, when enter it.

　　There are two unicode character datatypes:

　　nchar[(n)]

　　nvarchar[(n)]

　　nchar[(n)] datatype can store up to 4000 bytes of fixed-length unicode character data. You can specify the maximum byte length with n.

　　nvarchar[(n)] datatype can store up to 4000 bytes of variable-length unicode character data. You can specify the maximum byte length with n. Variable-length means that character data can contain less than n bytes, and the storage size will be the actual length of the data entered.

　　You should use nvarchar datatype instead of nchar datatype, when you expect null values or a variation in data size.

　　User-Defined datatypes

　　You can create your own User-Defined datatypes by executing sp_addtype system stored procedure. Once a User-Defined datatype is created, you can use it in the CREATE TABLE or ALTER TABLE statements, as built-in SQL Server 2000 datatypes.

原文转自：http://www.ltesting.net