数据库知识学习，数据库设计优化攻略(三)

1.4.4 注意临时表和表变量的用法

在复杂系统中，临时表和表变量很难避免，关于临时表和表变量的用法，需要注意：

➢ 如果语句很复杂，连接太多，可以考虑用临时表和表变量分步完成。

➢ 如果需要多次用到一个大表的同一部分数据，考虑用临时表和表变量暂存这部分数据。

➢ 如果需要综合多个表的数据，形成一个结果，可以考虑用临时表和表变量分步汇总这多个表的数据。

➢ 其他情况下，应该控制临时表和表变量的使用。

➢ 关于临时表和表变量的选择，很多说法是表变量在内存，速度快，应该首选表变量，但是在实际使用中发现，这个选

择主要考虑需要放在临时表的数据量，在数据量较多的情况下，临时表的速度反而更快。

➢ 关于临时表产生使用 SELECT INTO 和 CREATE TABLE + INSERT INTO 的选择，我们做过测试，一般情况下，SELECT INTO

会比 CREATE TABLE + INSERT INTO 的方法快很多，但是 SELECT INTO 会锁定 TEMPDB 的系统表 SYSOBJECTS、SYSINDEXES、

SYSCOLUMNS，在多用户并发环境下，容易阻塞其他进程，所以我的建议是，在并发系统中，尽量使用 CREATE TABLE +

INSERT INTO，而大数据量的单个语句使用中，使用 SELECT INTO。

➢ 注意排序规则，用 CREATE TABLE 建立的临时表，如果不指定字段的排序规则，会选择 TEMPDB 的默认排序规则，而不

是当前数据库的排序规则。如果当前数据库的排序规则和 TEMPDB 的排序规则不同，连接的时候就会出现排序规则的冲

突错误。一般可以在 CREATE TABLE 建立临时表时指定字段的排序规则为 DATABASE_DEFAULT 来避免上述问题。

1.4.5 子查询的用法

子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达

式的地方都可以使用子查询。

子查询可以使我们的编程灵活多样，可以用来实现一些特殊的功能。但是在性能上，往往一个不合适的子查询用法会

形成一个性能瓶颈。

如果子查询的条件中使用了其外层的表的字段，这种子查询就叫作相关子查询。相关子查询可以用 IN、NOT IN、

EXISTS、NOT EXISTS 引入。

关于相关子查询，应该注意：

➢ NOT IN、NOT EXISTS 的相关子查询可以改用 LEFT JOIN 代替写法。比如：

➢ 如果保证子查询没有重复 ，IN、EXISTS 的相关子查询可以用 INNER JOIN 代替。比如：

➢ IN 的相关子查询用 EXISTS 代替，比如

➢ 不要用 COUNT()的子查询判断是否存在记录，最好用 LEFT JOIN 或者 EXISTS，比如有人写这样的语句：

1.4.6 慎用游标

数据库一般的操作是集合操作，也就是对由 WHERE 子句和选择列确定的结果集作集合操作，游标是提供的一个非集合

操作的途径。一般情况下，游标实现的功能往往相当于客户端的一个循环实现的功能，所以，大部分情况下，我们把游标功

能搬到客户端。

游标是把结果集放在服务器内存，并通过循环一条一条处理记录，对数据库资源（特别是内存和锁资源）的消耗是非

常大的，所以，我们应该只有在没有其他方法的情况下才使用游标。

另外，我们可以用 SQL SERVER 的一些特性来代替游标，达到提高速度的目的。

➢ 字符串连接的例子

这是论坛经常有的例子，就是把一个表符合条件的记录的某个字符串字段连接成一个变量。比如需要把 JOB_ID=10 的

EMPLOYEE 的 FNAME 连接在一起，用逗号连接，可能最容易想到的是用游标：

➢ 用 CASE WHEN 实现转换的例子

很多使用游标的原因是因为有些处理需要根据记录的各种情况需要作不同的处理，实际上这种情况，我们可以用 CASE

WHEN 语句进行必要的判断处理，而且 CASE WHEN 是可以嵌套的。

变量参与的 UPDATE 语句的例子

SQL ERVER 的语句比较灵活，变量参与的 UPDATE 语句可以实现一些游标一样的功能，比如：

在 SELECT A,B,C,CAST(NULL AS INT) AS 序号

INTO #T

FROM 表

ORDER BY A ,NEWID()

产生临时表后，已经按照 A 字段排序，但是在 A 相同的情况下是乱序的，这时如果需要更改序号字段为按照 A 字段分

组的记录序号，就只有游标和变量参与的 UPDATE 语句可以实现了，这个变量参与的 UPDATE 语句如下：

DECLARE @A INT

DECLARE @序号 INT

UPDATE #T SET

@序号=CASE WHEN A=@A THEN @序号+1 ELSE 1 END,

@A=A,

序号=@序号

➢ 如果必须使用游标，注意选择游标的类型，如果只是循环取数据，那就应该用只进游标（选项 FAST_FORWARD），一般

只需要静态游标（选项 STATIC）。

➢ 注意动态游标的不确定性，动态游标查询的记录集数据如果被修改，会自动刷新游标，这样使得动态游标有了不确定

性，因为在多用户环境下，如果其他进程或者本身更改了纪录，就可能刷新游标的记录集。