微软 SQL Server 的 10 大技巧

Posted on by

大家好！有很多关于不同 T-SQL 特性的信息。关于这种语言的各种生活技巧和最佳实践。我想告诉你关于使用这种语言的同样有用但不太受欢迎的技巧。

1.建表时要指定主键

没有主键的表在关系数据库中很大程度上无法使用，因为它们无法连接。应在创建表时指定主键，以保证其所有记录都有主键值。

不好的例子：

CREATE TABLE employee
(
  employee_id INTEGER NOT NULL,
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL
);

好的例子：

CREATE TABLE employee
(
  employee_id INTEGER NOT NULL PRIMARY KEY,
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL
);

或者

CREATE TABLE employee
(
  employee_id INTEGER NOT NULL,
  first_name VARCHAR(42) NOT NULL,
  last_name VARCHAR(42) NOT NULL,
  CONSTRAINT PK_Employee PRIMARY KEY (employee_id)
);

2.“NULL”不应该直接比较

以类似禅宗的方式，“NULL”永远不等于任何东西，甚至它本身。因此，使用相等运算符的比较将始终返回 False，即使该值实际上为 NULL。

出于这个原因，不应该使用比较运算符来与 NULL 进行比较。应改为使用 IS NULL 和 IS NOT NULL。

不好的例子：

UPDATE books
SET title = 'unknown'
WHERE title = NULL -- Noncompliant

好的例子：

UPDATE books
SET title = 'unknown'
WHERE title IS NULL

3. 可空子查询不应该用在“NOT IN”条件下

如果子查询返回 NULL，则对子查询使用 NOT IN 的 WHERE 子句条件将产生意外结果。另一方面，NOT EXISTS 子查询在相同条件下可靠地工作。

当 NOT IN 与子查询一起使用时，此规则会引发问题。此规则不检查所选列是否为可为空的列，因为规则引擎没有有关表定义的信息。如果该列可以为空，则由开发人员手动检查。

不好的例子：

SELECT *
FROM my_table
WHERE my_column NOT IN (SELECT nullable_column FROM another_table)  -- Noncompliant; "nullable_column" may contain 'NULL' value and the whole SELECT query will return nothing

好的例子：

SELECT *
FROM my_table
WHERE NOT EXISTS (SELECT 1 FROM another_table WHERE nullable_column = my_table.my_column)

或者

SELECT *
FROM my_table
WHERE my_column NOT IN (SELECT nullable_column FROM another_table WHERE nullable_column IS NOT NULL)

4.“COALESCE”、“IIF”和“CASE”输入表达式不应包含子查询

COALESCE 和 IIF（其计算结果为 CASE 表达式）以及 CASE 输入表达式不应与子查询一起使用，因为子查询将为表达式中的每个选项计算一次，并且每次计算可能返回不同的结果，具体取决于隔离级别。为确保结果一致，请使用 SNAPSHOT ISOLATION 隔离级别。为了确保一致的结果 和 更好的性能，请将子查询移出表达式。

请注意，使用 ISNULL 替换 COALESCE 也是一种选择。

不好的例子：

CASE
WHEN (SELECT COUNT(*) FROM A) > 0 THEN (SELECT COUNT(*) FROM A) + 42
...
ELSE otherExpression
END

好的例子：

SET @a = SELECT COUNT(*) FROM A

CASE
WHEN @a > 0 THEN @a + 42
...
ELSE otherExpression
END

或者

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
...
COALESCE((SELECT a FROM b WHERE c) , 1)
...

5. 应在“PROCEDURE”和“TRIGGER”定义上激活“NOCOUNT”

默认情况下，NOCOUNT 在服务器级别停用 (OFF)。这意味着默认情况下，服务器将向客户端发送受执行的 SQL 查询影响的行数，这在大多数情况下是无用的，因为没有人会读取此信息。

停用此功能将节省一些网络流量并提高存储过程和触发器的执行性能，这就是为什么建议在处理任何查询之前在 PROCEDURE 和 TRIGGER 定义的开头定义 SET NOCOUNT ON 的原因。

如果在 PROCEDURE（或 TRIGGER）定义的开头与不是 SET、IF 或 DECLARE 的第一个语句之间未设置 NOCOUNT 或将其设置为 OFF，则此规则会引发问题。

好的例子：

CREATE PROCEDURE dbo.MyProc(@debug  INT)
AS
BEGIN
  DECLARE @var INT;
  IF @debug = 0
    BEGIN
      SET NOCOUNT ON;
    END
  SELECT COUNT(*) FROM MY_TABLE
END;

或者

CREATE TRIGGER MyTrigger ON MyTable
AFTER INSERT
AS
BEGIN
  SET NOCOUNT ON;
  [...]
END;

6.“LIKE”子句不应以通配符开头

当 LIKE 子句的值以 ‘%’、'[…]’ 或 ‘_’ 开头时，搜索列上的索引将被忽略，而是执行全表扫描。

不好的例子：

SELECT FIRST_NAME, LAST_NAME FROM PERSONS
WHERE LAST_NAME LIKE '%PONT'

7.“CASE”输入表达式应该是不变的

在幕后，简单的 CASE 表达式被评估为搜索的 CASE 表达式。那是，

CASE @foo
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'

实际上被评估为

CASE
WHEN @foo = 1 THEN 'a'
WHEN @foo = 2 THEN 'b'

在大多数情况下，差异是无关紧要的，但是当输入表达式不固定时，例如如果涉及 RAND()，则可能会产生意想不到的结果。因此，最好对输入表达式求值一次，将其分配给一个变量，然后将该变量用作 CASE 的输入表达式。

当在 CASE 输入表达式中使用以下任何内容时，此规则会引发问题：RAND、NEWID、CRYPT_GEN_RANDOM。

不好的例子：

CASE CONVERT(SMALLINT, RAND()*@foo)  -- Noncompliant
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'

好的例子：

DECLARE @bar SMALLINT = CONVERT(SMALLINT, RAND()*@foo)
CASE @bar
WHEN 1 THEN 'a'
WHEN 2 THEN 'b'

8. 不应使用“@@IDENTITY”

@@IDENTITY 返回在连接上创建的最后一个标识列值，无论范围如何。这意味着它可以返回您生成的最后一个标识值，或者它可以返回由用户定义的函数或触发器生成的值，可能是由于您的插入而触发的。为了访问在您的范围内创建的最后一个身份值，请改用 SCOPE_IDENTITY()。

不好的例子：

INSERT ...

SET @id = @@IDENTITY  -- Noncompliant

好的例子：

INSERT ...

SET @id = SCOPE_IDENTITY()

9. 不应使用已弃用的功能

不推荐使用的语言特性是那些为了向后兼容而暂时保留但最终将从语言中删除的特性。实际上，弃用宣布了一个宽限期，以允许从旧功能平稳过渡到新功能。在此期间，不应将已弃用的功能添加到代码中，并且应逐步删除所有现有的使用。

以下功能已被弃用或（取决于您的版本）已从 TSQL 中删除：

  • 代替 WRITETEXT、UPDATETEXT、READTEXT – 使用 VARCHAR
  • 而不是 GROUP BY ALL – 使用 UNION 或派生表
  • 代替 FASTFIRSTROW – 使用 FAST n
  • 而不是 SETUSER – 使用 EXECUTE AS
  • 而不是 CREATE RULE – 使用 CHECK 约束
  • 在 BACKUP 或 RESTORE 中代替 PASSWORD、MEDIAPASSWORD – 对目录使用使用访问控制或 WITH RESTRICTED_USER 用于 RESTORE
  • 代替 DBCC DBREINDEX – 使用 ALTER INDEX REBUILD
  • 代替 DBCC INDEXDEFRAG – 使用 ALTER INDEX DEFRAG
  • 而不是 DBCC SHOWCONTIG – 使用 sys.dm_db_index_physical_stats
  • 代替 DBCC CONCURRENCYVIOLATION – 不使用替代品
  • 而不是 SET ROWCOUNT – 使用 TOP(n)
  • 而不是 SET REMOTE_PROC_TRANSACTIONS – 使用引用链接服务器的分布式查询 (sp_addlinkedserver)
  • 而不是 RAISERROR @Errno @ErrMsg – 使用带括号的 RAISERROR 语法
  • 而不是使用两部分名称的 DROP INDEX – 使用将表（和数据库）移动到 ON 子句
  • 而不是字符串文字作为列别名 – 使用删除别名周围的引号。如果需要转义，请使用方括号
  • 代替编号的过程 – 使用 Replace 与未编号的过程
  • 而不是系统存储过程 – 不使用替代品
  • 而不是 fn_virtualservernodes – 使用 sys.dm_os_cluster_nodes
  • 而不是 fn_servershareddrives – 使用 sys.dm_io_cluster_shared_drives
  • 而不是 GRANT / DENY / REVOKE ALL – 使用列出特定权限
  • 而不是 TORN_PAGE_DETECTION – 使用校验和
  • 代替 TAPE – 使用 DISK 或 URL
  • 而不是没有表提示 – 使用 WITH WITH(hint)

10. 不应使用“GOTO”语句

GOTO 语句是控制流中的非结构化更改。应该避免它们，并用结构化的结构代替它们。

推荐阅读：