SQL2000里的数据类型

(1)char、varchar、text和nchar、nvarchar、ntext 
char和varchar的长度都在1到8000之间，它们的区别在于char是定长字符数据，而varchar是变长字符数据。所谓定长就是长度固定的，当输入的数据长度没有达到指定的长度时将自动以英文空格在其后面填充，使长度达到相应的长度；而变长字符数据则不会以空格填充。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。

后面三种数据类型和前面的相比，从名称上看只是多了个字母"n"，它表示存储的是Unicode数据类型的字符。写过程序的朋友对Unicode应该很了解。字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较：nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

(2)datetime和smalldatetime 
datetime：从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒。 
smalldatetime：从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。

(3)bitint、int、smallint、tinyint和bit 
bigint：从-2^63(-9223372036854775808)到2^63-1(9223372036854775807)的整型数据。 
int：从-2^31(-2,147,483,648)到2^31-1(2,147,483,647)的整型数据。 
smallint：从-2^15(-32,768)到2^15-1(32,767)的整数数据。 
tinyint：从0到255的整数数据。 
bit：1或0的整数数据。

(4)decimal和numeric 
这两种数据类型是等效的。都有两个参数：p（精度）和s（小数位数）。p指定小数点左边和右边可以存储的十进制数字的最大个数，p必须是从 1到38之间的值。s指定小数点右边可以存储的十进制数字的最大个数，s必须是从0到p之间的值，默认小数位数是0。

(5)float和real 
float：从-1.79^308到1.79^308之间的浮点数字数据。 
real：从-3.40^38到3.40^38之间的浮点数字数据。在SQL Server中，real的同义词为float(24)。

数据库定义到char类型的字段时，不知道大家是否会犹豫一下，到底选char、nchar、varchar、nvarchar、text、ntext中哪一种呢？结果很可能是两种，一种是节俭人士的选择：最好是用定长的，感觉比变长能省些空间，而且处理起来会快些，无法定长只好选用定长，并且将长度设置尽可能地小；另一种是则是觉得无所谓，尽量用可变类型的，长度尽量放大些。

　　鉴于现在硬件像萝卜一样便宜的大好形势，纠缠这样的小问题实在是没多大意义，不过如果不弄清它，总觉得对不起劳累过度的CPU和硬盘。

下面开始了(以下说明只针对SqlServer有效)：

1、当使用非unicode时慎用以下这种查询： 
select f from t where f = N'xx'

原因：无法利用到索引，因为数据库会将f先转换到unicode再和N'xx'比较

2、char 和相同长度的varchar处理速度差不多（后面还有说明）

3、varchar的长度不会影响处理速度！！！（看后面解释）

4、索引中列总长度最多支持总为900字节，所以长度大于900的varchar、char和大于450的nvarchar,nchar将无法创建索引

5、text、ntext上是无法创建索引的

6、O/R Mapping中对应实体的属性类型一般是以string居多，用char[]的非常少，所以如果按mapping的合理性来说，可变长度的类型更加吻合

7、一般基础资料表中的name在实际查询中基本上全部是使用like '%xx%'这种方式，而这种方式是无法利用索引的，所以如果对于此种字段，索引建了也白建

8、其它一些像remark的字段则是根本不需要查询的，所以不需要索引

9、varchar的存放和string是一样原理的，即length {block}这种方式，所以varchar的长度和它实际占用空间是无关的

10、对于固定长度的字段，是需要额外空间来存放NULL标识的，所以如果一个char字段中出现非常多的NULL，那么很不幸，你的占用空间比没有NULL的大（但这个大并不是大太多，因为NULL标识是用bit存放的，可是如果你一行中只有你一个NULL需要标识，那么你就白白浪费1byte空间了，罪过罪过！），这时候，你可以使用特殊标识来存放，如：'NV'

11、同上，所以对于这种NULL查询，索引是无法生效的，假如你使用了NULL标识替代的话，那么恭喜你，你可以利用到索引了

12、char和varchar的比较成本是一样的，现在关键就看它们的索引查找的成本了，因为查找策略都一样，因此应该比较谁占用空间小。在存放相同数量的字符情况下，如果数量小，那么char占用长度是小于varchar的，但如果数量稍大，则varchar完全可能小于char，而且要看实际填充数值的充实度，比如说varchar(3)和char(3)，那么理论上应该是char快了，但如果是char(10)和varchar(10)，充实度只有30%的情况下，理论上就应该是varchar快了。因为varchar需要额外空间存放块长度，所以只要length(1-fillfactor)大于这个存放空间（好像是2字节)，那么它就会比相同长度的char快了。

13、nvarchar比varchar要慢上一些，而且对于非unicode字符它会占用双倍的空间，那么这么一种类型推出来是为什么呢？对，就是为了国际化，对于unicode类型的数据，排序规则对它们是不起作用的，而非unicode字符在处理不同语言的数据时，必须指定排序规则才能正常工作，所以n类型就这么一点好处。

总结陈词： 
1、如果数据量非常大，又能100%确定长度且保存只是ansi字符，那么char 
2、能确定长度又不一定是ansi字符或者，那么用nchar； 
3、不确定长度，要查询且希望利用索引的话，用nvarchar类型吧，将它们设到400； 
4、不查询的话没什么好说的，用nvarchar(4000) 
5、性格豪爽的可以只用3和4，偶尔用用1，毕竟这是一种额外说明，等于告诉别人说，我一定需要长度为X位的数据

ＳＱＬ 语法参考手册

    DB2 提供了关连式资料库的查询语言 ＳＱＬ (Structured Query Language)，是一种非常口语化、既易学又易懂的语法。 此一语言几乎是每个资料库系统都必须提供的，用以表示关连式的操作，包含了资料的定义（ＤＤＬ）以及资料的处理（ＤＭＬ）。SQL原来拼成SEQUEL，这语言的原型以“系统 R“的名字在 IBM 圣荷西实验室完成，经过IBM内部及其他的许多使用性及效率测试，其结果相当令人满意，并决定在系统R 的技术基础发展出来 IBM 的产品。而且美国国家标准学会（ANSI）及国际标准化组织（ISO）在1987遵循一个几乎是以 IBM SQL 为基础的标准关连式资料语言定义。

一、资料定义 ＤＤＬ（Data Definition Language)
    资料定语言是指对资料的格式和形态下定义的语言，他是每个资料库要建立时候时首先要面对的，举凡资料分哪些表格关系、表格内的有什麽栏位主键、表格和表格之间互相参考的关系等等，都是在开始的时候所必须规划好的。

１、建表格：
        CREATE TABLE table_name(
        column1 DATATYPE [NOT NULL] [NOT NULL PRIMARY KEY],
        column2 DATATYPE [NOT NULL],
        ...）
        说明：　
            DATATYPE --是资料的格式，详见表。
            NUT NULL --可不可以允许资料有空的（尚未有资料填入）。
            PRIMARY KEY --是本表的主键。

２、更改表格　
        ALTER TABLE table_name
        ADD COLUMN column_name DATATYPE
        说明：增加一个栏位（没有删除某个栏位的语法。
        ALTER TABLE table_name
        ADD PRIMARY KEY (column_name)
        说明：更改表得的定义把某个栏位设为主键。
        ALTER TABLE table_name
        DROP PRIMARY KEY (column_name)
        说明：把主键的定义删除。

３、建立索引　
        CREATE INDEX index_name ON table_name (column_name)
        说明：对某个表格的栏位建立索引以增加查询时的速度。

４、删除　
        DROP table_name
        DROP index_name

二、的资料形态 DATATYPEs
    smallint
                16 位元的整数。
    interger
                32 位元的整数。
    decimal(p,s)
                p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数
                点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
    float
                32位元的实数。
    double
                64位元的实数。
    char(n)
                n 长度的字串，n不能超过 254。
    varchar(n)
                长度不固定且其最大长度为 n 的字串，n不能超过 4000。
    graphic(n)
                和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为
                了支援两个字元长度的字体，例如中文字。
    vargraphic(n)
                可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。
    date
                包含了 年份、月份、日期。
    time
                包含了 小时、分钟、秒。
    timestamp
                包含了 年、月、日、时、分、秒、千分之一秒。

三、资料操作 ＤＭＬ （Data Manipulation Language)
    资料定义好之後接下来的就是资料的操作。资料的操作不外乎增加资料（insert)、查询资料（query）、更改资料（update) 、删除资料（delete）四种模式，以下分 别介绍他们的语法：

１、增加资料：
        INSERT INTO table_name (column1,column2,...)
        VALUES ( value1,value2, ...)
         说明：
            1.若没有指定column 系统则会按表格内的栏位顺序填入资料。
            2.栏位的资料形态和所填入的资料必须吻合。
            3.table_name 也可以是景观 view_name。

        INSERT INTO table_name (column1,column2,...)
        SELECT columnx,columny,... FROM another_table
        说明：也可以经过一个子查询（subquery）把别的表格的资料填入。

２、查询资料：
    基本查询
        SELECT column1,columns2,...
        FROM table_name
        说明：把table_name 的特定栏位资料全部列出来
        SELECT *
        FROM table_name
        WHERE column1 = xxx
        [AND column2 > yyy] [OR column3 <> zzz]
        说明：
            1.'*'表示全部的栏位都列出来。
            2.WHERE 之後是接条件式，把符合条件的资料列出来。

        SELECT column1,column2
        FROM table_name
        ORDER BY column2 [DESC]
        说明：ORDER BY 是指定以某个栏位做排序，[DESC]是指从大到小排列，若没有指明，则是从小到大
              排列

    组合查询
        组合查询是指所查询得资料来源并不只有单一的表格，而是联合一个以上的
        表格才能够得到结果的。
        SELECT *
        FROM table1,table2
        WHERE table1.colum1=table2.column1
        说明：
            1.查询两个表格中其中 column1 值相同的资料。
            2.当然两个表格相互比较的栏位，其资料形态必须相同。
            3.一个复杂的查询其动用到的表格可能会很多个。

    整合性的查询：
        SELECT COUNT (*)
        FROM table_name
        WHERE column_name = xxx
        说明：
            查询符合条件的资料共有几笔。
        SELECT SUM(column1)
        FROM table_name
        说明：
            1.计算出总和，所选的栏位必须是可数的数字形态。
            2.除此以外还有 AVG() 是计算平均、MAX()、MIN()计算最大最小值的整合性查询。
        SELECT column1,AVG(column2)
        FROM table_name
        GROUP BY column1
        HAVING AVG(column2) > xxx
        说明：
            1.GROUP BY: 以column1 为一组计算 column2 的平均值必须和 AVG、SUM等整合性查询的关键字
              一起使用。
            2.HAVING : 必须和 GROUP BY 一起使用作为整合性的限制。

    复合性的查询
        SELECT *
        FROM table_name1
        WHERE EXISTS (
        SELECT *
        FROM table_name2
        WHERE conditions )
        说明：
            1.WHERE 的 conditions 可以是另外一个的 query。
            2.EXISTS 在此是指存在与否。
        SELECT *
        FROM table_name1
        WHERE column1 IN (
        SELECT column1
        FROM table_name2
        WHERE conditions )
        说明：　
            1. IN 後面接的是一个集合，表示column1 存在集合里面。
            2. SELECT 出来的资料形态必须符合 column1。

    其他查询
        SELECT *
        FROM table_name1
        WHERE column1 LIKE 'x%'
        说明：LIKE 必须和後面的'x%' 相呼应表示以 x为开头的字串。
        SELECT *
        FROM table_name1
        WHERE column1 IN ('xxx','yyy',..)
        说明：IN 後面接的是一个集合，表示column1 存在集合里面。
        SELECT *
        FROM table_name1
        WHERE column1 BETWEEN xx AND yy
        说明：BETWEEN 表示 column1 的值介於 xx 和 yy 之间。

３、更改资料：
        UPDATE table_name
        SET column1='xxx'
        WHERE conditoins
        说明：
            1.更改某个栏位设定其值为'xxx'。
            2.conditions 是所要符合的条件、若没有 WHERE 则整个 table 的那个栏位都会全部被更改。

４、删除资料：
           DELETE FROM table_name
           WHERE conditions
           说明：删除符合条件的资料。

说明：关于WHERE条件后面如果包含有日期的比较，不同数据库有不同的表达式。具体如下：
  (1)如果是ACCESS数据库，则为：WHERE mydate>#2000-01-01#    
  (2)如果是ORACLE数据库，则为：WHERE mydate>cast('2000-01-01' as date)
                           或：WHERE mydate>to_date('2000-01-01','yyyy-mm-dd')
     在Delphi中写成：
     thedate= '2000-01-01';
     query1.SQL.add('select * from abc where mydate>cast('+''''+thedate+''''+' as date)');

Introduction

Binary datatypes

Character datatypes

Date and Time datatypes

Numeric datatypes

Integer datatypes

Monetary datatypes

Special datatypes

Text and image datatypes

Unicode Character datatypes

User-Defined datatypes

Introduction

There are three new datatypes in SQL Server 2000 in comparison with SQL Server 7.0.

These datatypes were added:

• bigint
• sql_variant
• table

In this article, I want to tell you about built-in SQL Server 2000 datatypes, about user-defined datatypes and what datatype is generally used in a particular situation.

Binary datatypes

Binary data is similar to hexadecimal data and consists of the characters 0 through 9 and A through F, in groups of two characters each. You should specify 0x before binary value when input it.

There are two binary datatypes:

• binary[(n)]
• varbinary[(n)]

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

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

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

Character datatypes

Character datatypes are used to store any combination of letters, symbols, and numbers. You should enclose character data with quotation marks, when enter it.

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 accuracy 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

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.

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.

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.

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.

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.

This is the syntax of sp_addtype stored procedure:

sp_addtype [ @typename = ] type, [ @phystype = ] system_data_type [ , [ @nulltype = ] 'null_type' ] [ , [ @owner = ] 'owner_name' ]

where

typename - is the User-Defined datatype name.

phystype - is the SQL Server 2000 datatype, on which the User-Defined datatype is based.

nulltype - is the NULL or NOT NULL.

owner - is the owner of the User-Defined datatype.