SQL Server 2005 常用数据类型详解

SQL Server 2005 常用数据类型详解

1. 字符串数据类型

char

此数据类型可存储1~8000个定长字符串，字符串长度在创建时指定；如未指定，默认为char(1)。每个字符占用1byte存储空间。

nchar

此数据类型可存储1~4000个定长Unicode字符串，字符串长度在创建时指定；如未指定，默认为nchar(1)。每个字符占用2bytes存储空间。

varchar

此数据类型可存储最大值为8000个字符的可变长字符串。可变长字符串的最大长度在创建时指定，如varchar(50)，每个字符占用1byte存储空间。

nvarchar

此数据类型可存储最大值为4000个字符可变长Unicode字符串。可变长Unicode字符串的最大长度在创建时指定，如nvarchar(50)，每个字符占用2bytes存储空间。

text

此数据类型可存储最大值为2147483647个字符的变长文本，并且无需指定其初始值，每个字符占用1byte存储空间，一般用来存储大段的文章。text数据类型实际上是一个Large Object 数据类型，默认情况下，此类型的数据不是存储在数据行内，而是存储于独立的Large Object 数据页上。另外，text数据类型不能做为函数、存储过程或触发器中的参数来用。

ntext

同text数据类型，只不过存储的是最大值为1073741823个字符的Unicode变长文本，每个字符占用1byte存储空间。

说明：无论使用哪种字符串数据类型，字符串值必须放在引号内，推荐使用单引号。

2. 数值数据类型

bit

此数据类型存储值为0或1的二进制字段。占用1byte存储空间。

tinyint

此数据类型存储0~255的整数，占用1byte存储空间。

smallint

此数据类型存储-32768~32767的整数，占用2bytes存储空间。

int

此数据类型存储-2147483648~2147483647的整数，占用4bytes存储空间。

bigint

此数据类型存储-9223372036854775808~9223372036854775807的整数，占用8bytes存储空间。

decimal/numeric

这两个数据类型功能相同，均为存储精度可变的浮点值。但推荐采用decimal，因其存储的数据“更有说明性”。此种数据类型由两个值来确定decimal(p,s)，p为精度，s为标量，如decimal(3,2)，其中数值2为小数的位数，那么decimal(3,2)可用来存储如1.28这样的浮点数。此种数据类型占用的存储空间取决于精度值p。p为1~9，占用5bytes存储空间；p为10~19，占用9bytes存储空间；p为20~28，占用13bytes存储空间；p为29~38，占用17bytes存储空间。

float

此数据类型存储1~53的可变精度的浮点值，精度表示为float(n)，n表示科学记数法的尾数，取值范围为-1.79E+308~-2.23E-308的负数和2.23E-308~1.79E+308的正数。其存储空间由精度值决定，n为1~24，占用4bytes存储空间；n为25~53，占用8bytes存储空间。

real

此数据类型存储-3.40E+38~-1.18E-38的负数和1.18E~3.40E+38的正数。占用4bytes存储空间。

smallmoney

此数据类型存储-214748.3648~214748.3647的货币值，精确到小数后4位。占用4bytes存储空间。

money

此数据类型存储-922337203685477.5808~922337203685477.5807的货币值，精确到小数后4位。占用8bytes存储空间。

3. 日期和时间数据类型

smalldatetime

此数据类型存储从1900年1月1日到2079年6月6日的日期。占用4btyes存储空间。

datetime

此数据类型存储从1753年1月1日到9999年12月31日的日期。占用8bytes存储空间。

4. 二进制数据类型

binary

此数据类型存储1~8000个字符的二进制数据，其指定长度即为占用的存储空间。

varbinary

此数据类型存储可变长的二进制数据，可在创建时指定其具体长度，也可不指定。

5. 其它数据类型

rowversion/timestamp

这两种数据类型功能一样，但Microsoft建议在任何情况下尽可能地指明rowversion而不是timestamp，因为rowversion更加准确地反应了数据类型的真实性质。timestamp数据类型跟时间完全无关，它表明数据库中的数据修改发生的相对顺序。不要在键（尤其是主键）中使用timestamp列，因为每次修改行时，timestamp值都会更改。当指定数据类型为rowversion 或timestamp，那么SQL Server会在对表的插入或删除等更新操作时自动生成一个新值，并把这个新值放在合适的字段里。此类型数据占用8bytes存储空间。

uniqueidentifier

此类型数据存储二进制值，其作用与全局唯一标识符(GUID)一样。GUID 主要用于在有多个节点、多台计算机的网络中，分配必须具有唯一性的标识符。占用16bytes存储空间。

SQL Server 2005 数据类型

SQL 2005数据类型说明

自动编号：设字段类型为：int ，然后列属性中（是标识）选是，标识种子选1。

用varchar(max)代替text。varchar的最大长度为8000，但是varchar(max)则可以存储多达2G 的数据，因此其作用相当于SQL 2000中的text。但是微软可能会后续的SQL Server版本中移除text类型，从现在就应该用varchar(max) 来代替text。

用nvarchar(max)代替ntext，用binary(max)代替image.

为XML数据选择xml类型。在SQL Server 2005中，为XML数据添加了相应的数据类型，因此存储XML数据的列不需要用varchar(max)或nvarchar(max)，而应当用xml数据类型，以利用T-SQL中专门针对xml数据列的新命令，以及针对xml列的索引。

易混淆的数据类型

(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)。

关于SQL SERVER 2005 与SQL SERVER 2000比较

这个题目太大了,这里只能大体介绍一下，细节方面还需大家共同研究！希望能使大家对SQL SERVER 2005 快速入手,另外sql server 2005 的界面风格有越来越像.net了,赶快安装一个感受一下吧!进去之后不要再去找查询分析器了,它和企业管理器都被集成在”Microsoft SQL Server Management studio”了,这个工具占内存较大80M多(再加上sql server 的实例进程80M,就160M了),而以前的企业管理器只需20M左右,加上sql server 的实例进程10M多一共才30M多.有得必有失~!

hacker at 2006/9/29

一、数据库设计方面

1、字段类型。

SQL Server 2005引入了一系列新的被称为MAX的数据类型。这是VARCHAR，NVARCHAR和VARBINARY类型的扩展，这几种类型以前被限制在8000字节以下。MAX可以容纳高达2GB 的数据，与TEXT和IMAGE一样。

可以使用字符串函数对CLOB类型进行操作。但是这就引发了对varchar和char效率讨论的

老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？是否碎片会引发效率问题？这都是需要进一步探讨的东西。

数据类型

Sql server2000 Sql server2005

text 最大2GB varchar(max) 最大2GB(相当于oracle中的CLOB类型)

ntext 最大2GB nvarchar(max) 最大2GB

image 最大2GB varbinary(max) 最大2GB（代替image也让SQL Server的字段类型更加简洁统一）

无XML XML 数据被作为二进制大型对象(BLOB) 存储于内部，可有效地进行重新分析和压缩

其它数据类型保持不变。

2、外键的级联更能扩展

新版本中外键级联加入了SET NULL 和SET DEFAULT 属性，能够提供能好的级联设置。(有点像oracle了)语法如下(引用sql server 2005 help来说明):

CREATE TABLE 和ALTER TABLE 语句的REFERENCES 子句支持ON DELETE 和ON UPDATE 子句：

? * ON DELETE , NO ACTION | CASCADE | SET NULL | SET DEFAULT - +

? * ON UPDATE , NO ACTION | CASCADE | SET NULL | SET DEFAULT - +

如果没有指定ON DELETE 或ON UPDATE，则默认为NO ACTION。

NO ACTION

指定如果试图删除/修改某一行，而该行的键被其他表的现有行中的外键所引用，则产生错误并回滚DELETE/UPDATE语句。

CASCADE、SET NULL 和SET DEFAULT

允许通过删除或更新键值来影响指定具有外键关系的表，这些外键关系可追溯到在其中进行修改的表。如果为目标表也定义了级联引用操作，那么指定的级联操作也将应用于删除或更新的那些行。不能为具有timestamp 列的外键或主键指定CASCADE。

ON DELETE CASCADE

指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则也将删除所有包含那些外键的行。

ON UPDATE CASCADE

指定如果试图更新某一行中的键值，而该行的键值被其他表的现有行中的外键所引用，则组成外键的所有值也将更新到为该键指定的新值。(如果timestamp 列是外键或被引用键的一部分，则不能指定CASCADE。)

ON DELETE SET NULL

指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为NULL。目标表的所有外键列必须可为空值，此约束才可执行。ON DELETE SET NULL

指定如果试图更新某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为NULL。目标表的所有外键列必须可为空值，此约束才可执行。ON DELETE SET DEFAULT

指定如果试图删除某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。目标表的所有外键列必须具有默认值定义，此约束才可执行。如果某个列可为空值，并且未设置显式的默认值，则会使用NULL 作为该列的隐式默认值。因ON DELETE SET DEFAULT 而设置的任何非空值在主表中必须有对应的

值，才能维护外键约束的有效性。

ON UPDATE SET DEFAULT

指定如果试图更新某一行，而该行的键被其他表的现有行中的外键所引用，则组成被引用行中的外键的所有值将被设置为它们的默认值。目标表的所有外键列必须具有默认值定义，此约束才可执行。如果某个列可为空值，并且未设置显式的默认值，则会使用NULL 作为该列的隐式默认值。因ON UPDATE SET DEFAULT 而设置的任何非空值在主表中必须有对应的值，才能维护外键约束的有效性。

3、索引附加字段

即在索引中存储一些常用字段以提高查询速度,这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。在实验环境中会比映射到表中提高30%左右的效率。例:

CREATE INDEX ix_CustomerPostalcode

On Sales.Customer(PostalCode)

INCLUDE (AddressLine1,AddressLine2,City)

索引会提高查询(select)语句的性能,但建有大量索引会影响INSERT、UPDATE 和DELETE 语句的性能，因为在表中的数据更改时，所有索引都须进行适当的调整。

4、计算字段的持久化

原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update 的负担。OLTP慎用。OLAP可以大规模使用。

使用ORDER 排序和虚拟字段虚拟字段完成的是类似自增长ID 的任务

select identity(int,1,1) ID ,hymc into #temp

from hybm

order by hymc

(注: 在ORACLE中，语句: select rownum from USERTABLE order by USERNAME; 得到的rownum 还是没有排过序时的ROWNUM，根本不是已经排过序的ROWNUM。也就是说，有没有ORDER BY一个样。)

5、分区表

分区表是个亮点！从分区表也能看出微软要做大作强SQL Server的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQL Server2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。但是需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。如果你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

6、CLR类型

微软对CLR作了大篇幅的宣传，这是因为数据库产品终于融入.net体系中。最开始我们也是狂喜，感觉对象数据库的一些概念可以实现了。但是作了些试验，发现使用CLR的存储过程

或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！还不是性能有问题！否则面向对象的数据库早就实现了！

建议使用CLR的地方一般是和应用的复杂程度或操作系统环境有很高的耦合度的场景。如你想构建复杂的算法，并且用到了大量的指针和高级数据模型。或者是要和操作系统进行Socket通讯的场景。否则建议慎重！

7、索引视图

索引视图2k就有。但是2005对其效率作了一些改进但是schema.viewname的作用域真是太限制了它的应用面。还有一大堆的环境参数和种种限制都让人对它有点却步。

8、语句和事务快照

语句级快照和事务级快照终于为SQL Server的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

9、数据库快照

原理很简单，对要求长时间计算某一时间点的报表生成和防用户操作错误很有帮助。但是比起Oracle10g的闪回技术还是细粒度不够。可惜！

例子：

CREATE DATABASE demo2

GO

USE demo2

ALTER DATABASE demo2 SET allow_snapshot_isolation ON --启动快照功能

CREATE TABLE test

(

tid INT NOT NULL primary key,

tname VARCHAR(50) NOT NULL

)

INSERT INTO test VALUES(1,'version1')

INSERT INTO test VALUES(2,'version2')

--连接一

USE demo2

BEGIN TRAN

UPDATE test SET tname='version3' WHERE tid=2

SELECT * FROM test

--连接二

USE demo2

SET transaction isolation level snapshot

SELECT * FROM test

10、Mirror

Mirror可以算是SQL Server的Data guard了。具说它切换速度可以达到秒级,这个功能能否被用户认可还有待时间的考验. 这个操作起来比较简单不在详细介绍。

二、开发方面

参考页面：https://www.360docs.net/doc/8111792203.html,/china/msdn...e.mspx?mfr=true

1、Ranking函数集

其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQL Server2005的row_number比Oracle的更先进。因为它把Order by集成到了一起，不用像Oracle那样还要用子查询进行封装。但是大家注意一点。

如下面的例子：

USE demo

GO

CREATE TABLE rankorder

(

orderid INT,

qty INT

)

GO

INSERT rankorder VALUES(30001,10)

INSERT rankorder VALUES(10001,10)

INSERT rankorder VALUES(10006,10)

INSERT rankorder VALUES(40005,10)

INSERT rankorder VALUES(30003,15)

INSERT rankorder VALUES(30004,20)

INSERT rankorder VALUES(20002,20)

INSERT rankorder VALUES(20001,20)

INSERT rankorder VALUES(10005,30)

INSERT rankorder VALUES(30007,30)

INSERT rankorder VALUES(40001,40)

GO

SELECT orderid,qty,

ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,

RANK() OVER(ORDER BY qty) AS rank,

DENSE_RANK() OVER(ORDER BY qty) AS denserank

FROM rankorder

ORDER BY qty

还有一点要说明：

select ROW_NUMBER() OVER (order by aa)

from tbl

order by bb

会先执行aa的排序，然后再进行bb的排序。

可能有的朋友会抱怨集成的order by，其实如果使用ranking函数,Order by是少不了的。如果担心Order by会影响效率，可以为order by的字段建立聚集索引，查询计划会忽略order by

操作（因为本来就是排序的嘛）。

2、top

可以指定一个数字表达式，以返回要通过查询影响的行数或百分比，还可以根据情况使用变量或子查询。可以在DELETE、UPDATE和INSERT查询中使用TOP选项。可以动态传入参数，省却了动态SQL的拼写。

例:

--声明个变量

DECLARE @a INT

DECLARE @b INT

DECLARE @c INT

--赋值

SET @a = 10

SET @b = 5

SELECT @c = @a/@b

--使用计算表达式

SELECT TOP(@c) * FROM toptest

--使用SELECT语句作为条件

SELECT TOP(SELECT COUNT(*) FROM toptest2) *

FROM toptest

--指出top

DELETE TOP(2) toptest where column1>'t6'

--更新top

UPDATE TOP(2) toptest SET column1 = 'hi' where column1<='t2'

3、Apply

对递归类的树遍历很有帮助。

CROSS APPLY : 得到和FUNCTION 结果相匹配的记录

OUTER APPLY: All rows , regardless of matching function results

一个有代表性的例子：

USE demo

GO

CREATE TABLE Arrays

(

aid INT NOT NULL IDENTITY PRIMARY KEY,

array VARCHAR(7999) NOT NULL

)

GO

INSERT INTO Arrays VALUES('')

INSERT INTO Arrays VALUES('10')

INSERT INTO Arrays VALUES('20,40,30')

INSERT INTO Arrays VALUES('-1,-3,-5')

GO

CREATE FUNCTION function1(@arr AS VARCHAR(7999))

RETURNS @t TABLE(pos INT NOT NULL, value INT NOT NULL)

AS

BEGIN

DECLARE @end AS INT, @start AS INT, @pos AS INT

SELECT @arr = @arr + ',', @pos = 1,

@start = 1, @end = CHARINDEX(',', @arr, @start)

WHILE @end > 1

BEGIN

INSERT INTO @t VALUES(@pos, SUBSTRING(@arr, @start, @end - @start))

SELECT @pos = @pos + 1,

@start = @end + 1, @end = CHARINDEX(',', @arr, @start)

END

RETURN

END

--测试

SELECT * FROM function1('200,400,300')

GO

SELECT A.aid, F.*

FROM Arrays AS A

CROSS APPLY function1(array) AS F

GO

SELECT A.aid, F.*

FROM Arrays AS A

OUTER APPLY function1(array) AS F

GO

查询结果:

4、CTE(Common Table expression_r 通用表达式)

它是一个可以由定义语句引用的临时表命名的结果集，要用到WITH 关键字。例：WITH SalesCTE(ProductID,SalesOrderID)

AS

(SELECT ProductID,COUNT(SalesOrderid)

FROM Sales.SalesOrderDetail GROUP BY ProductID)

SELECT * FROM SalesCTE

5、try/catch

先说明一下事务相关语法:

BEGIN TRAN[SACTION] [transaction_name]

COMMIT [TRAN[SACTION]] [transaction_name]

ROLLBACK [TRAN[SACTION] [transaction_name]

SAVE TRAN[SACTION] [savepoint_name]

上面这几很简单不解释了,下面看SET XACT_ABORT ON/OFF,当SET XACT_ABORT 为ON 时，如果Transact-SQL 语句产生运行时错误，整个事务将终止并回滚。为OFF 时，只回滚产生错误的Transact-SQL 语句，而事务将继续进行处理。所以在sql server 2k里使用事务就必须SET XACT_ABORT ON或者设置保存点。有了try/catch sql server2005 就不一样了!! 演示代码如下表。

USE demo

GO

CREATE TABLE student --创建工作表

( stuid INT NOT NULL PRIMARY KEY,

stuname VARCHAR(50) )

CREATE TABLE score

( stuid INT NOT NULL REFERENCES student(stuid),

score INT )

GO

INSERT INTO student VALUES (101,'zhangsan')

INSERT INTO student VALUES (102,'wangwu')

INSERT INTO student VALUES (103,'lishi')

INSERT INTO student VALUES (104,'maliu')

2K事务的打操作2005 使用TRY...CATCH构造扩充了错误处理能力

--调用一个运行时错误

SET XACT_ABORT ON(若为OFF 事务不会回滚)

BEGIN TRAN

INSERT INTO score VALUES (101,90)

INSERT INTO score VALUES (102,78)

INSERT INTO score VALUES (107,76)

INSERT INTO score VALUES (103,81)

INSERT INTO score VALUES (104,65)

COMMIT TRAN

GO

SET XACT_ABORT OFF

BEGIN TRY

BEGIN TRAN

INSERT INTO score VALUES (101,90)

INSERT INTO score VALUES (102,78)

INSERT INTO score VALUES (107,76)

INSERT INTO score VALUES (103,81)

INSERT INTO score VALUES (104,65)

COMMIT TRAN

PRINT '事务提交'

END TRY

BEGIN CATCH

ROLLBACK

PRINT '事务回滚'

SELECT ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_MESSAGE() as ErrorMessage;

END CATCH

GO

6、pivot/unpivot

实现行列互转。2K 中这个功能用case实现，不过好像没有case 直观。而且默认的第三字段（还可能更多）作为group by字段很容易造成新手的错误。（有图我就不说了）

7、OUTPUT 关键字

在下面的例子中只看蓝色的部分就是正常的insert into … values 语句,通过output 可以将中间值输出到临时表,也就是不能过触发器也可以实现对中间数据的处理。例：

DECLARE @InsertDetails TABLE (ProductID int, Insertedby sysname)

INSERT INTO Stock.ProductList

OUTPUT inserted.ProductID, suser_name() INTO @InsertDetails

VALUES (‘Racing Bike’,412.99)