SQL Server Tempdb技术手册

1、SQL Server Tempdb技术手册一、基础介绍Tempdb数据库类似于操作系统的分页文件。它用于存储用户创建的临时对象、数据库引擎需要的临时对象和行版本信息。Tempdb数据库是在每次启动SQL Server时创建的，当SQL Server停止运行时，该数据库将被重新创建为其原始大小，所以不必对其进行数据备份。SQL在对Tempdb数据库中的对象做数据修改时，只写入最少的信息到日志文件中，结合Tempdb数据库每次重建的特性，SQL不支持对该数据库的还原及日志还原操作。二、特别之处1、每次重启都会删除,然后从model系统数据库中复制一份，并继承某些model数据库的属性，然而有些属性

2、并不能继承，比如“恢复模式”属性，无论model数据库是什么恢复模式，Tempdb只能是“简单恢复模式”，以便始终自动回收 Tempdb 日志空间，且我们尝试修改其恢复模式时，也会抛出错误如下，提示你不能修改。备注：数据库恢复模式参见附录一。2、SQL Server 2005以上版本默认Tempdb初始大小为8MB只能有一个文件组放置数据库文件，不能创建和使用新的文件组。也就是说Tempdb只能使用默认的PRIMARY文件组，但可以为Tempdb创建多个数据文件和日志文件。3、用户可以在Tempdb数据库创建和使用表，也可以使用事务和回滚事务。在用户数据库中，事务具有ACID属性，但是在Tem

3、pdb的事务中，并不具有。备注：SQL Server事务的ACID属性详见附录二。4、SQL Server使用Tempdb存储一些内部对象，比如查询的中间结果集。大部分的这些内部操作不会生成日志记录，因为它们不需要回滚，所以这些操作是非常快的。5、在Tempdb上也存在一些有别于用户数据库的限制。例如上面提到的修改恢复模型、日志收缩、DBCC check等。三、空间使用SQL中有三种类型的数据会存放在Tempdb数据库中，分别是内部对象、版本存储信息、用户对象信息，关于这三种类型的具体介绍，参加附录三。1、内部对象：1）排序(order by 、group by、row_number、rank

4、、dense_rank、ntile等)。详见附录四SQL Server2005四种排序函数。2）hash join(inner join、left outer join、right outer join、full outer join等)。3）hash aggregate(dinstinct等)：哈希聚合，可以通过执行SQL语句的执行计划(Ctrl+L)查看是否使用了聚合操作。关于聚合方面的知识，参加附录五。4）Instead of触发器：目前我们使用的较少。关于instead of触发器和after/for 触发器介绍，详见附录六。5）cursor结果：游标。6）DBCC check：数据库

5、检查语句。7）Service Broker：数据库邮件、事件通知、查询通知等。8）大型的数据，如：XML、text、image、varchar(max)等。9）大型查询或中间查询结果，如：UNION，嵌套子查询等。2、版本存放区:1）事务的snapshot隔离级别或者read committed snapshot。2）多数据结果集multiple active result sets。3）在线生成索引。4）after 触发器。3、自定义对象:1）Global：全局变量、全局临时表。2）local temporary table：临时表。3）数据表变量。四、容量监控可以使用下面的SQL语句查看T

6、empdb的动态使用情况。SELECTSUM (user_object_reserved_page_count)*8.0/1024 as 用户对象(mb),SUM (internal_object_reserved_page_count)*8.0/1024 as 内部对象(mb),SUM (version_store_reserved_page_count)*8.0/1024 as 记录版本空间(mb),SUM (unallocated_extent_page_count)*8.0/1024 as 可用空间(mb),SUM (mixed_extent_page_count)*8.0/1024

7、as mixedextent(mb)FROM sys.dm_db_file_space_usage说明：上述语句是从系统数据表中查找SQL系统记录的对应各对象所占数据页，所以：数据页*8（kb）/1024（kb/mb）=MB有些网络资料直接使用数据页数除以128(128=1024/8)，容易让人困惑。五、应用测试说明：以下测试均是在上述容量监控T-SQL语句的监控下执行并查看执行效果的。1、临时表create table #t1(c1 int primary key identity(1,1),c2 nvarchar(10) default 'hello t1')create

8、table #t2(c1 int primary key identity(1,1),c2 int,c3 nvarchar(10) default 'hello t2',c4 int default rand(datepart(ms,getdate()*10000)insert #t1 default valuesset nocount ondeclare i intset i=0while i<20begin insert #t1(c2) select c2 from #t1 set i=i+1end -为了要让随机数乱，所以逐条增加.while i<100000

9、0begin insert #t2(c2) values (i) set i=i+1end-要占用Tempdb 上大量的内部对象select * from #t1 join #t2 on #t1.c1=#t2.c4 order by c4结论：1）临时表及临时变量均占用Tempdb用户对象。2）对临时表的连接操作占用Tempdb内部对象，查询结果全部显示后自动释放内部对象空间。3）Tempdb中的用户对象空间在查询结束后仍然占用，需等关闭查询、删除临时表、删除变量或删除其中数据后才释放。4）将上述SQL中的临时表改为局部临时表后发现：全局临时表和局部临时表对于Tempdb的使用情况一样。2、实

10、体表order byselect * from ybxj_cdxx order by ybrq desc结论：1）Tempdb最大值比查询数据表实体大小稍大些；查询开始显示数据时，Tempdb即开始释放空间，当客户端显示完数据，全部释放，但硬盘空间不释放，直到重启服务。2）此处测试order by，不再测试group by，理论上同理。3）上述实体表使用的是Tempdb内部对象。3、视图及实体表关联查询-新建两个视图create view ybxx1 as select * from ybxj_ybxx where ybrq>'2013/01/01'结论：直接从实体表中查

11、数据不使用Tempdb。create view ybxx2 asselect a.cjdw,a.gzzxcode,a.ybjth,a.ybrq,a.yblsh,a.dh,a.zbcj,a.zlcj,a.khcode ,a.khmc,b.cjid,b.cdmc,b.id from ybxj_ybxx a,ybxj_cdxx b where a.ybjth=b.ybjth and a.ybrq=b.ybrq and a.lsh=b.lsh结论：查询连接视图时使用Tempdb。4、聚集函数select MAX(ybrq) from ybxj_ybxxgoselect SUM(jldcm) from

12、ybxj_ybxx结论：执行聚集函数并不使用Tempdb。5、distinctselect distinct ybrq from ybxj_ybxxCtrl+L结果如下：结论：Distinct 没有使用Tempdb，此结论与前面资料提到的使用Tempdb情况不一致。6、unionselect apptype,rightsort,rightname from sys_userrightunionselect apptype,rightsort,rightname from sys_groupright结论：数据表太小，没有消耗Tempdbselect ybjth,ybrq,lsh from yb

13、xj_cdxxunionselect ybjth,ybrq,lsh from ybxj_ybxx结论：数据量增大后，使用Tempdb的内部对象空间。select ybjth,ybrq,lsh from ybxj_cdxxunion allselect ybjth,ybrq,lsh from ybxj_ybxx结论：union all两头都使用的是实体表，不需要进行数据重组，所以不使用Tempdb空间。8、内存与Tempdbselect * from sys_appdb order by apptype 数据表大小：0.031Mselect * from sys_oplog order by a

14、pptype 数据表大小：152M从前面的测试已经看出order by 消耗Tempdb，这次测试先找一个小于Tempdb初始大小的小表进行测试，观察Tempdb没有明显变化，又找了个大于Tempdb初始大小的表，结果Tempdb仍然没有变化。结论：1）如果数据量超过了系统内存容量，SQL数据库引擎就会在Tempdb中创建工作表格，否则系统优先使用系统内存容量。2）在正式服务器测试后发现：即使在系统内存足够大的情况下，排序操作仍然会使用Tempdb数据库。9、重建索引-删除表中原有索引后测试重建索引-drop index ybxj_ybxx.ybxj_ybxx1create index ybx

15、j_ybxx1 on ybxj_ybxx(yblsh)-初始大小：192.00.00.06848.01152.0-执行中：192.02752.00.04096.01152.0-执行结束：192.00.00.06848.01152.0结论：当数据量大时消耗系统内存和Tempdb内部对象空间，创建结束释放空间。alter index ybxj_ybxx1 on ybxj_ybxx rebuild with (online=on)结论：没有发现使用Tempdb情况。create index ybxj_ybxx1 on ybxj_ybxx(yblsh) with (drop_existing = on

16、)结论：没有发现使用Tempdb情况。10、触发器create table t1(a1 int not null,a2 int default rand(datepart(ms,getdate()*10000 not null)create table t2(a1 int null,a2 int null)create trigger insertt1 on t1 for insert asbegin declare i int set i = 0 while i < 100000 begin insert into t2 select * from inserted set i =i+

17、1 endendinsert into t1 (a1) values (100)说明：新建2个小表t1,t2，新建一个触发器实现往第一个表插入数据时自动往第二个表插入N条重复数据，为便于观测这里插入10完条，同时t1表增加一列随机数；为规避临时表影响，没有自定义临时表，只定义了一个临时变量用于实现循环插入数据。-原始：192.00.00.06848.01152.0-结束：192.064.064.06720.01152.0-再后：192.064.00.06784.01152.0结论：1）触发器的inserted临时表使用Tempdb的内部对象空间，同时使用了记录版本空间。2）语句结束，所占空间没

18、有及时释放；但过段时间后，记录版本空间释放，内部对象空间仍然占用。六、Tempdb迁移测试1、Tempdb迁移普通的用户数据库迁移方法如：分离/附加、备份/还原、导入/导出、修改文件指针等都不适用于Tempdb数据库，迁移Tempdb方法为：1.停止SQL服务2.复制Tempdb数据库的两个文件（.mdf/.ldf）到新文件夹，如（D:Tempdb）3.启动SQL服务4.打开SQL Server Management Studio，执行以下代码： USE masterGOALTER DATABASE Tempdb MODIFY FILE (NAME = tempdev, FILENAME =

19、'E:SQLTempdbTempdb.mdf')GOALTER DATABASE Tempdb MODIFY FILE (NAME = templog, FILENAME = 'E:SQLTempdbtemplog.ldf')GO 5.再次停止SQL服务。6.删除原Tempdb旧文件（清理空间）。7.再次启动SQL服务。备注：1、可以使用下面的SQL语句查看数据库文件存放路径及属性：SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE datab

20、ase_id = DB_ID(N'Tempdb')此语句也可以查看其它用户数据库。七、Tempdb调优建议若要获得最佳的 Tempdb 性能，我们建议在生产环境中对 Tempdb 进行如下配置：1、将 Tempdb 的恢复模式设置为 SIMPLE。此模式自动回收日志空间以保持较小的空间要求。2、使 Tempdb 文件的大小可以根据需要自动增大。这可以使文件的大小增大到磁盘变满为止。3、将文件增量设置为合理的大小以避免 Tempdb 数据库文件的增量过小。如果文件的增量与写入 Tempdb 的数据量相比过小，则 Tempdb 可能需要不断扩大。这将影响性能。建议为 Tempdb

21、文件设置 FILEGROWTH 增量时遵循以下通用原则。4、您可能必须基于 Tempdb 文件所在的 I/O 子系统的速度调整此百分比。为了避免潜在的闩锁超时，我们建议将自动增长操作限制在大约两分钟之内。例如，如果 I/O 子系统以每秒 50 MB 的速度初始化文件，则无论 Tempdb 文件的大小如何，FILEGROWTH 增量都应设置为最大值 6 GB。如果可能，请使用实例数据库文件初始化来提高自动增长操作的性能。5、通过将文件大小设置为足够容纳环境中典型工作负荷的值来预分配所有 Tempdb 文件的空间。这可以避免 Tempdb 因扩展得过于频繁而影响性能。6、根据需要创建足够多的文件以

22、使磁盘宽度最大化。使用多个文件可以减少 Tempdb 存储争用并获得更大的可伸缩性。但是，请勿创建过多的文件，因为此操作可能降低性能并增加管理开销。作为通用原则，为服务器中的每一个 CPU 创建一个数据文件（用于解释任何关联掩码设置），然后根据需要上下调整文件的数量。请注意，双核心 CPU 将被视为两个 CPU。7、使每个数据文件的大小相同，这样可以优化比例填充的性能。8、将 Tempdb 数据库放置在快速 I/O 子系统中。如果有许多直接连接的磁盘，则请使用磁盘条带化。9、将 Tempdb 数据库放置在用户数据库使用的磁盘以外的磁盘中。附录一、查看或更改数据库的恢复模式“恢复模式”是一种数据

23、库属性，它控制如何记录事务，事务日志是否需要（以及允许）备份，以及可以使用哪些类型的还原操作。 有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。 通常，数据库使用完整恢复模式或简单恢复模式。 数据库可以随时切换为其他恢复模式。 model 数据库将设置新数据库的默认恢复模式。注意：1、在从完整恢复模式或大容量日志恢复模式切换前，请备份事务日志。2、时点恢复在大容量日志模式下不可能进行。因此，如果在可能需要事务日志还原的大容量日志恢复模式下运行事务，这些事务可能会丢失数据。若要在灾难恢复方案中最大程度地恢复数据，建议仅在符合以下条件下切换到大容量日志恢复模式：1）数据库中当前不允

24、许存在用户。2）在大容量处理过程中进行的所有修改均不依靠日志备份就可恢复；例如，通过重新运行大容量处理。如果满足这两个条件，在大容量日志恢复模式下还原备份的事务日志时将不会丢失任何数据。3、如果在大容量操作过程中切换到完整恢复模式，则大容量操作的日志记录将从最小日志记录更改为最大日志记录，反之亦然。、使用 SQL Server Management Studio查看或更改恢复模式1.连接到相应的 SQL Server 数据库引擎实例之后，在对象资源管理器中，单击服务器名称以展开服务器树。2.展开“数据库”，然后根据数据库的不同，选择用户数据库，或展开“系统数据库”，再选择系统数据库。3.右键单

25、击该数据库，再单击“属性”，这将打开“数据库属性”对话框。4.在“选择页”窗格中，单击“选项”。5.当前恢复模式显示在“恢复模式”列表框中。6.也可以从列表中选择不同的模式来更改恢复模式。可以选择“完整”、“大容量日志”或“简单”。7.单击“确定”。、使用 Transact-SQL查看恢复模式SELECT name,recovery_model_desc FROM sys.databases WHERE name = '数据库'USE master ALTER DATABASE 数据库 SET RECOVERY 模式附录二、SQL Server事务的ACID属性当事务处理系统创

26、建事务时，将确保事务有某些特性。组件的开发者们假设事务的特性应该是一些不需要他们亲自管理的特性。这些特性称为ACID特性。 ACID就是：原子性(Atomicity )、一致性( Consistency )、隔离性( Isolation)和持久性(Durabilily)。 1. 原子性 原子性属性用于标识事务是否完全地完成，一个事务的任何更新要在系统上完全完成，如果由于某种原因出错，事务不能完成它的全部任务，系统将返回到事务开始前的状态。 让我们再看一下银行转帐的例子。如果在转帐的过程中出现错误，整个事务将会回滚。只有当事务中的所有部分都成功执行了，才将事务写入磁盘并使变化永久化。 为了提供回

27、滚或者撤消未提交的变化的能力，许多数据源采用日志机制。例如，SQL Server使用一个预写事务日志，在将数据应用于(或提交到)实际数据页面前，先写在事务日志上。但是，其他一些数据源不是关系型数据库管理系统(RDBMS)，它们管理未提交事务的方式完全不同。只要事务回滚时，数据源可以撤消所有未提交的改变，那么这种技术应该可用于管理事务。 2. 一致性 事务在系统完整性中实施一致性，这通过保证系统的任何事务最后都处于有效状态来实现。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。因为事务开 始时系统处

28、于一致状态，所以现在系统仍然处于一致状态。 再让我们回头看一下银行转帐的例子，在帐户转换和资金转移前，帐户处于有效状态。如果事务成功地完成，并且提交事务，则帐户处于新的有效的状态。如果事务出错，终止后，帐户返回到原先的有效状态。 记住，事务不负责实施数据完整性，而仅仅负责在事务提交或终止以后确保数据返回到一致状态。理解数据完整性规则并写代码实现完整性的重任通常落在开发者肩上，他们根据业务要求进行设计。 当许多用户同时使用和修改同样的数据时，事务必须保持其数据的完整性和一致性。因此我们进一步研究A C I D特性中的下一个特性：隔离性。 3. 隔离性 在隔离状态执行事务，使它们好像是系统在给定时

29、间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。 这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。 重要的是，在隔离状态执行事务，系统的状态有可能是不一致的，在结束事务前，应确保系统处于一致状态。但是在每个单独的事务中，系统的状态可能会发生变化。如果事务不是在隔离状态运行，它就可能从系统中访问数据，而系统可能处于不一致状态。通过提供事务隔离，可以阻止这类事件的发生。 在银行的示例中，这意味着在这个系统内，其他过程和事务在我们的事务完成前看不到我们

30、的事务引起的任何变化，这对于终止的情况非常重要。如果有另一个过程根据帐户余额进行相应处理，而它在我们的事务完成前就能看到它造成的变化，那么这个过程的决策可能建立在错误的数据之上，因为我们的事务可能终止。这就是说明了为什么事务产生的变化，直到事务完成，才对系统的其他部分可见。 隔离性不仅仅保证多个事务不能同时修改相同数据，而且能够保证事务操作产生的变化直到变化被提交或终止时才能对另一个事务可见，并发的事务彼此之间毫无影响。这就意味着所有要求修改或读取的数据已经被锁定在事务中，直到事务完成才能释放。大多数数据库，例如SQL Server以及其他的RDBMS，通过使用锁定来实现隔离，事务中涉及的各个

31、数据项或数据集使用锁定来防止并发访问。4. 持久性 持久性意味着一旦事务执行成功，在系统中产生的所有变化将是永久的。应该存在一些检查点防止在系统失败时丢失信息。甚至硬件本身失败，系统的状态仍能通过在日志中记录事务完成的任务进行重建。持久性的概念允许开发者认为不管系统以后发生了什么变化，完成的事务是系统永久的部分。 在银行的例子中，资金的转移是永久的，一直保持在系统中。这听起来似乎简单，但这，依赖于将数据写入磁盘，特别需要指出的是，在事务完全完成并提交后才写入磁盘的。 附录三、SQL Server中Tempdb存储内容首先，先介绍“Tempdb的空间使用”，有下面几种类型的对象会占用Tempdb

32、的空间：内部对象、版本存储、用户对象。内部对象内部对象是指由SQL Server系统内部自动创建的一些系统级别的对象，应用程序不能直接的从这些对象删除或者插入数据，系统对象的元数据存储在内存里面，并且不能通过象“sys.all_objects”这样的系统目录来进行查询，因此，我们可认为内部对象是一些隐藏对象。通常系统对象被用于：.存储排序的中间临时结果集。.存储HASH连接以及HASH聚合的中间结果集。.存储XML变量或者象LOB这样的大对象类型变量。LOB数据类型包括：text, image, ntext, varchar(max), varbinary(max)以及其他。.存储会利用到Sp

33、ool运算符(如CTE查询)保存的中间结果集。.存储键集驱动游标生成的工作表。.通过静态游标存储查询结果。.通过ServiceBroker存储传输中的消息。.通过INSTEAD OF触发器存储数据以及内部的处理。内部对象还可以用于下面这些的任何一个功能。比如，DBCC CHECK内部使用的查询需要利用到Spool运算符生成中间结果集，查询通知，以及事件通知，ServiceBroker，因此它们同样需要利用到Tempdb的空间。更新内部对象不会产生日志记录。除非涉及排序单位，否则内部对象上页分配是不会生成日志记录的。如果语句失败，这些对象将会被释放。每个Tempdb上的内部对象至少占用9个页（包

34、括1个IAM页和8个数据页的区）。版本存储版本存储用于存储新特性中通过事务产生的行版本。例如快照隔离级别，触发器，MARS（多个活动的结果集）以及联机索引创建。在SQL Server 2005中，有两个版本的存储区。关于更多的相关信息，请参见联机帮助：版本存储由append-only存储单位构成。对于连续的插入和随机的查找，append-only存储单位是非常高效的。它们不会显示在系统目录，比如sys.all_objects。插入版本存储不会产生日志记录。每个单位可以存储很多行的版本。如果有新的版本需要被存储，大约每1分钟就有1个新的存储单位被创建。由于版本存储涉及的内容比较多且复杂，在这里就

35、不再一一论述，更多内容，请网友们自行参考联机丛书关于“版本存储”的章节。用户对象可以在系统目录sys.all_objects找到用户对象。Sp_spaceused可以显示这些对象占用的空间。用户对象包括用户定义的表和索引以及系统表和索引。这两种类型的表在磁盘上的数据结构都完全一样。可以通过T-SQL语句对用户表进行操作，不能通过T-SQL语句直接的对系统表进行修改，它们对于使用系统目录是可见的。通常用户对象上的操作是可以被记录日志的，包括BCP，BULK INSERT，SELECT INTO以及索引重建操作，这些行为和其他的简单恢复模型的数据库的行为是完全一样的。用户定义的表包括全局临时表例如

36、#t，以及局部临时表例如#t.全局临时表会在其依赖的整个会话过期或者中止以后释放，而局部临时表只存在于其依赖的某种作用域。在另外一方面，也会伴随一个作用域的结束而被释放。（例如存储过程）局部临时表包括表变量t,表值函数的返回值，以及那些使用了带有sort_in_Tempdb选项的联机聚集索引创建所需要的Mapping索引。临时表的作用域实例：1我们先看下面的代码：use Tempdbgocreate proc p1asset nocount onselect top 1 * into #t from sys.objectsselect * from #t我们创建了个存储过程，先通过系统表插入1

37、行记录生成一个局部临时表#t,然后再通过SELECT语句查询这个临时表。然后，我们创建完以后，通过执行:exec p1我们可以看到结果集，但如果，我们再次通过SELECT查询语句对#t进行查询：select * from #t我们将会看到下面的错误信息，这是因为在存储过程的作用域完之后，这个局部临时表对象也随之被销毁了。Msg 208, Level 16, State 0, Line 1Invalid object name '#t'.如果想在执行完存储过程以后，还可以进行SELECT查询，把局部临时表替换为全局临时表就可以了。2还是利用上面提到的存储过程p1create pr

38、oc p1 asset nocount onselect top 1 * into #t from sys.objectsselect * from #t突然某天，需求改变，创建临时表的源表需要改变或者需要适应可变的需求，那么我们肯定会考虑传入一个表名参数，使用动态语句来实现，代码如下：alter proc p1table_name varchar(100)asset nocount ondeclare sql varchar(200)set sql = 'select top 1 * into #t from '+table_nameexec(sql)select * fro

39、m #t然后我们执行存储过程：exec p1 table_name='sys.objects'我们并未得到我们想要的结果，而是一个错误信息：Msg 208, Level 16, State 0, Procedure p1, Line 9Invalid object name '#t'.这也是因为，这里的局部临时表只存在于动态语句内的作用域，对于这样的需求我们可以通过下面两个方式来解决：第一种方式是通过将查询语句一并包含到动态语句里，代码如下：alter proc p1 table_name varchar(100) asset nocount ondeclare

40、 sql varchar(200)set sql = 'select top 1 * into #t from '+table_name + 'select * from #t'exec(sql)第二种方式则是将局部临时表修改为全局临时表#t。3我们来考虑全局临时表的作用域，我们做个测试，这个测试还是利用到最开始的那个存储过程P1，只不过代码我们把局部临时表调整为了全局临时表：alter proc p1 asset nocount onselect top 1 * into #t from sys.objectsselect * from #t我们创建完这个存储

41、过程以后，先在查询窗口1执行1次这个存储过程，那么按照我们之前说的，则会创建一个全局的的临时表，我们可以在执行完存储过程以后，依然可以使用SELECT语句对其进行查询。然后我们试着重新开启第2，第3个查询窗口，同样执行语句查询：select * from #t我们依然可以看到我们想要的结果集。我们再次把查询窗口1关闭，然后再次在窗口2，3执行上面这个查询代码，结果出错了。附录四、SQL Server 2005 四种排序函数SQL server 2005新增的几个函数,分别是row_number( )、rank( )、,DENSE_RANK( )、ntile( )下面以实例分别简单讲解。1.ro

42、w_number( )先来点数据,先建个表 SET NOCOUNT ONCREATE TABLE Person(FirstName VARCHAR(10),Age INT,Gender CHAR(1)INSERT INTO Person VALUES ('Ted',23,'M')INSERT INTO Person VALUES ('John',40,'M')INSERT INTO Person VALUES ('George',6,'M')INSERT INTO Person VALUES (&#

43、39;Mary',11,'F')INSERT INTO Person VALUES ('Sam',17,'M')INSERT INTO Person VALUES ('Doris',6,'F')INSERT INTO Person VALUES ('Frank',38,'M')INSERT INTO Person VALUES ('Larry',5,'M')INSERT INTO Person VALUES ('Sue',29,

44、'F')INSERT INTO Person VALUES ('Sherry',11,'F')INSERT INTO Person VALUES ('Marty',23,'F')直接用例子说明问题:SELECT ROW_NUMBER() OVER (ORDER BY Age) AS Row Number by Age,FirstName,AgeFROM Person出现的数据如下Row Number by Age FirstName Age- - -1 Larry 52 Doris 63 George 64 Mar

45、y 115 Sherry 116 Sam 177 Ted 238 Marty 239 Sue 2910 Frank 3811 John 40可以观察到,是根据年龄升序排列了，并且row_number()是给出了序列号了,这个序列号被重命名为Row Number by Age,与sql server2000对比:如果在sql server2000中实现相对麻烦一些，我们可以利用IDENTITY()函数实现，但IDENTITY()函数只能用在sql server2000临时表中，因此需要将数据检索到临时表里。select identity(int,1,1) as Row Number by Age

46、,FirstName,Age into #A from Person order by Ageselect * from #Adrop table #a如果不想按年龄排序,可以这样写SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1) AS Row Number by Record Set,FirstName,AgeFROM Person另外一个例子SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS Partition by Gender,FirstName,Age,GenderFR

47、OM Person这里是按性别划分区间了，同一性别再按年龄来排序,输出结果如下Partition by Gender FirstName Age Gender- - - -1 Doris 6 F2 Mary 11 F3 Sherry 11 F4 Sue 29 F1 Larry 5 M2 George 6 M3 Sam 17 M4 Ted 23 M5 Marty 23 M6 Frank 38 M7 John 40 M注意,姓名M开始,序号又从1,2,3开始了 2.RANK( )函数 先看例子SELECT RANK() OVER (ORDER BY Age) AS Rank by Age,Firs

48、tName,AgeFROM Person输出如下:Rank by Age FirstName Age- - -1 Larry 52 Doris 62 George 64 Mary 114 Sherry 116 Sam 177 Ted 237 Marty 239 Sue 2910 Frank 3811 John 40看到了么,同年岭的话,将有相同的顺序,顺序成1,2,2,4了。与sql server2000对比:出现了RANK()函数实在是方便,在sql server2000里实现排序并列的问题麻烦很多。select Rank by Age=isnull(select count(*) from

49、 person where Age>A.Age),0)+1,FirstName,Age from Person A order by Rank by Age SELECT RANK() OVER(PARTITION BY Gender ORDER BY Age) AS Partition by Gender,FirstName, Age, Gender FROM Person输出为Partition by Gender FirstName Age Gender- - - -1 Doris 6 F2 Mary 11 F2 Sherry 11 F4 Sue 29 F1 Larry 5 M2

50、George 6 M3 Sam 17 M4 Ted 23 M4 Marty 23 M6 Frank 38 M7 John 40 M可以看到,按性别分组了,每个性别分组里,继续是用了rank( )函数3.DENSE_RANK( )函数SELECT DENSE_RANK() OVER (ORDER BY Age) AS Dense Rank by Age, FirstName, AgeFROM Person输出结果为:Dense Rank by Age FirstName Age- - -1 Larry 52 Doris 62 George 63 Mary 113 Sherry 114 Sam 175 Ted 235 Marty 236 Sue 297 Frank 388 John 40看到了么,和rank函数区别是,顺序始终是连续的,Doris 和George同年,都是排第2位,但之后的mary不象rank函数那样排第4,而是排第3位了4.ntile( )函数SELECT FirstName,Age,NTILE(3) OVER (ORDER BY Age) AS Age GroupsFROM Person输出结果：First