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

　　There are two character datatypes:

　　char[(n)]

　　varchar[(n)]

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

　　Varchar[(n)] datatype can store up to 8000 bytes of variable-length 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 varchar datatype instead of char datatype, when you expect null values or a variation in data size.

　　Date and Time datatypes

　　There are two datetime datatypes:

　　datetime

　　smalldatetime

　　Datetime is stored in 8 bytes of two 4-byte integers: 4 bytes for the number of days before or after the base date of January 1, 1900, and 4 bytes for the number of milliseconds after midnight.

　　Datetime datatype can store dates from January 1, 1753, to December 31, 9999, with aclearcase/" target="_blank" >ccuracy of 3.33 milliseconds.

　　If you will not specify date portion of the datetime value, then January 1, 1900 is supplied; if you will not specify time portion of datetime value, then 12:00:00:000AM is supplied.

　　Smalldatetime is stored in 4 bytes of two 2-byte integers: 2 bytes for the number of days after the base date of January 1, 1900, and 2 bytes for the number of minutes after midnight.

　　Smalldatetime datatype can store dates from January 1, 1900, to June 6, 2079, with accuracy to the minute.

　　If you will not specify date portion of the datetime value, then January 1, 1900 is supplied; if you will not specify time portion of datetime value, then 12:00AM is supplied.

　　Numeric datatypes

　　There are two kinds of the numeric datatypes:

　　Exact Numeric Data

　　Approximate Numeric Data

　　The difference between Exact Numeric Data and Approximate Numeric Data in that Exact Numeric Datacan store all decimal numbers with complete accuracy, and Approximate Numeric Data cannot.

　　Exact Numeric Data are:

　　decimal[(p[, s])]

　　numeric[(p[, s])]

　　The decimal and numeric datatypes are synonyms in the SQL Server 2000. Exact Numeric Data holds values from 10^38 - 1 through - 10^38 - 1. The storage size varies based on the specified precision, and it ranges from a minimum of 2 bytes to a maximum of 17 bytes.

　　p - is a precision, that specify the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The maximum precision is 28 digits.

　　s - is a scale, that specify the maximum number of decimal digits that can be stored to the right of the decimal point, and it must be less than or equal to the precision.

　　Approximate Numeric Data are:

　　float[(n)]

　　real

　　Float[(n)] datatype is stored in 8 bytes and is used to hold positive or negative floating-point numbers. By default, this column has a 15-digit precision. Float[(n)] datatype can store positive values from 2.23E-308 to 1.79E308 and negative values from -2.23E-308 to -1.79E308.

　　Real datatype is stored in 4 bytes and is used as float datatype to hold positive or negative floating-point numbers. This column has a 7-digit precision. Real datatype can store positive values from 1.18E-38 to 3.40E38 and negative values from -1.18E-38 to -3.40E38.

　　Integer datatypes

　　There are four integer datatypes:

　　tinyint

　　smallint

　　int

　　bigint

　　Tinyint is stored in 1 byte and is used to hold integer values from 0 through 255.

　　Smallint is stored in 2 bytes and is used to hold integer values from -32768 through 32,767.

　　Int is stored in 4 bytes and is used to hold integer values from -2147483648 through 2147483647.

　　Bigint is stored in 8 bytes and is used to hold integer values from -9223372036854775808 through 9223372036854775807.

　　Monetary datatypes

　　Monetary datatypes are usually used to store monetary values. There are two monetary datatypes:

　　money

　　smallmoney

　　Money datatype is stored in 8 bytes and is used to hold monetary values from -922337203685477.5808 through 922337203685477.5807.

　　Smallmoney datatype is stored in 4 bytes and is used to hold monetary values from - 214748.3648 through 214748.3647.

　　Special datatypes

　　These are the special datatypes:

　　bit

　　cursor

　　sql_variant

　　table

　　timestamp

　　uniqueidentifier

　　Bit datatype is usually used for true/false or yes/no types of data, because it holds either 1 or 0. All integer values other than 1 or 0 are always interpreted as 1. One bit column stores in 1 byte, but multiple bit types in a table can be collected into bytes. Bit columns cannot be NULL and cannot have indexes on them.

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