SQL 2005 中的 TOP子句

衣明志 发表于 , 阅读 (15650)

在给客户解决一个小的技术问题时,发现一些技术人员对SQL2005的T-SQL还有些东西是不太熟悉的,这次我就通过与SQL2000对比着来说一下SQL2005的TOP子句吧。

首先说一下大家熟悉的SELECT语句。

SQL2000中我们主要类似这样写:SELECT TOP 10 * FROM [News] ORDER BY [Id] DESC。这样我就能获取News表按Id列倒序的前10条记录。那么一个小问题就来了,我们知道SQL2000的TOP后面只能跟常量的,不能跟变量的,如果我们把这个语句放在一个存储过程中,而由于业务原因一些地方需要取出同样条件的10条有些20条还有57条……,那么我们怎么办呢?很多人说“简单啊,存储过中声明一个 @Top AS INT 的参数,然后先写一行SET ROWCOUNT @Top;然后写一行SELECT * FROM [News] ORDER BY [Id] DESC;就行了。对了,最好在后面紧接着加上SET ROWCOUNT 0;以避免影响后面的查询语句”。嗯这个回答在SQL2000是比较对的,起码比回答重新组合SQL语句的要好一些,尤其最好不要忘记那个SET ROWCOUNT 0;但是我们看到这种方式在实际应用中,体验是不好的,一旦语句复杂到一定程度时,SET ROWCOUNT前后影响可能会让SQL编写人员撞墙。ok,那么我们在SQL2005中该如何处理呢?

SQL2005中SELECT语句的TOP则方便多了,因为TOP支持使用任意的独立表达式,而不仅仅是常量。所以我们上面的同样问题可以简化为:SELECT TOP (@Top) * FROM [News] ORDER BY [Id] DESC。人性化多了,而且语句表达的意思很明确,不会像使用SET ROWCOUNT那样让人郁闷不已。

再说一下INSERT、UPDATE和DELETE等修改语句吧。

对于这三种操作我们似乎依然可以使用SET ROWCOUNT来处理,但是我们知道这种方式的体验之差,对复杂语句尤甚。而且我们知道“对于在远程表和本地及远程分区视图上执行的 INSERT、UPDATE 和 DELETE 语句,将忽略 SET ROWCOUNT 选项设置”,也就是说在远程查询时这个东西就不管用了。我们还在SQL2008的联机丛书中还发现这样一段重要提示

在 SQL Server 的下一个版本中,使用 SET ROWCOUNT 将不会影响 DELETE、INSERT 和 UPDATE 语句。请不要在新的开发工作中将 SET ROWCOUNT 与 DELETE、INSERT 和 UPDATE 语句一起使用,并应准备修改当前使用它的应用程序。另外,对于当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句,建议您使用 TOP 语法重写它们。

正如上述提示中诉说的,我们应该选择更灵活、更人性化,而且还能在未来升级到新版本SQL数据库时减少麻烦的TOP子句。三种例句如下:

INSERT TOP(100) INTO [News] SELECT [Id],[Title],[Author] from [OldNews];
UPDATE TOP(500) [News] SET [Author] = N'qihangnet' WHERE Id >2009; 
DELETE TOP(100) FROM [News];

值得注意的是上述这三种修改数据表的语句中,我们均无法直接使用ORDER BY附加排序条件,所以上述三种语句带有一定的随机性,尤其是UPDATE和DELETE语句。当然这种随机性有其两面性,优点是比如在按量分配工作的业务中,可以很方便的使用UPDATE进行随机性修改一定量的数据;缺点是当我们想通过排序控制一定量的数据时就不好了。那么真对于这种缺点我们是否就没有办法了呢?

其实在SQL2005中,已经提供了可以轻松解决这个问题的方法,那就是利用WITH语句。具体方法在我们稍后的讨论WITH语句的博客文章中进行说明,并通过几个例子来详细说明一下WITH的妙用。

1 条评论
ashuai
ashuai 2015-04-03 22:07
With呢? 花儿也谢了