第 6 章 Transact-SQL 增 强 工 具

本 章 将 介 绍 Transact-SQL 增 强 工 具 的 内 容 ， Transact-SQL 增 强 工 具 是

M icrosoft SQL Server 7 版 本 的 组 成 部 分 。

6.1 增 强 的 存 储 过 程

存 储 过 程 是 Transact-SQL 语 句 编 译 而 成 的 对 象 ， 可 以 用 EXECUTE storedprocedurename 语 句 运 行 它 。 存 储 过 程 可 以 返 回 一 个 或 多 个 结 果 集 ， 整 型返 回 代 码 、 OUTPUT 自 变 量 或 参 数 。

存 储 程 序 有 许 多 优 点 ： 高 速 度 、 可 重 用 性 、 商 业 逻 辑 封 装 、 保 护 尚 未 优 化的 查 询 、 减 少 网 络 拥 挤 、 以 及 安 全 性 ( 因 为 只 需 对 存 储 过 程 授 予 许 可 ， 而 对 它 访问 的 对 象 不 需 授 予 许 可 ) 。 存 储 程 序 是 Transact-SQL 开 发 人 员 与 数 据 库 交 互 操作 、 提 高 数 据 库 性 能 和 可 靠 性 的 基 础 。

M icrosoft SQL Server 能 提 供 永 久 或 临 时 存 储 过 程 。 临 时 存 储 过 程 存 储 在

tempdb 系 统 数 据 库 中 。

存 储 过 程 中 的 延 迟 名 解 析 性 能 (Delayed Name Resolution Behavior)

由 于 允 许 SQL Server 开 发 人 员 创 建 在 Transact-SQL 代 码 中 并 不 存 在 的 存 储过 程 、 批 处 理 文 件 或 触 发 程 序 及 引 用 对 象 ， 延 迟 名 解 析 可 节 省 Transact-SQL 编程 人 员 的 大 量 时 间 和 精 力 。 在 以 前 的 版 本 中 ， Transact-SQL 编 程 人 员 必 须 按 特定 的 顺 序 创 建 对 象 。 例 如 ， 在 6.x 版 本 中 ， 当 创 建 存 储 过 程 时 ， 存 储 过 程 在 创建 脚 本 的 Transact-SQL 代 码 中 所 引 用 的 对 象 必 须 在 分 析 该 存 储 过 程 ( 即 句 法 分 析 ) 和 创 建 存 储 过 程 对 象 时 存 在 于 数 据 库 上 。 如 果 在 数 据 库 上 找 不 到 存 储 过 程 所 引用 的 对 象 ， 就 无 法 创 建 存 储 过 程 对 象 。 在 以 前 的 版 本 中 ， SQL Server 会 返 回“ 引用 对 象 不 存 在 ” 的 错 误 声 明 ， 为 了 创 建 引 用 某 个 对 象 的 存 储 过 程 ( 也 可 以 是 触 发程 序 )， 就 必 须 创 建 该 对 象 。 这 是 非 常 费 时 的 ， 也 不 符 合 逻 辑 的 循 环 。

在 M icrosoft SQL Server 7 中 ， 创 建 存 储 过 程 时 若 引 用 对 象 不 存 在 ， 当 分 析该 存 储 过 程 ( 分 析 语 句 的 合 法 性 ) 时 ， 即 使 在 数 据 库 上 找 不 到 存 储 过 程 或 触 发 程序 中 引 用 的 对 象 ， SQL Server 也 允 许 创 建 存 储 过 程 ， 并 将 它 放 到 数 据 库 上 。 在以 后 的 解 析 运 行 阶 段 会 解 析 名 称 ， 解 析 阶 段 是 另 一 个 合 法 性 分 析 阶 段 ， 它 发 生在 运 行 期 间 ， 而 不 是 对 象 创 建 期 间 。 如 果 存 储 过 程 在 运 行 期 间 引 用 了 对 象 （ 这可 能 是 几 天 之 后 的 事 情 ）， 而 被 引 用 的 对 象 仍 未 创 建 ， SQL Server 将 发 出 一 个 运行 错 误 的 信 息 ， 因 为 存 储 过 程 通 不 过 解 析 阶 段 。

这 样 会 使 Transact-SQL 的 开 发 更 加 容 易 ， 因 为 在 创 建 存 储 过 程 时 可 能 有 许多 对 象 不 存 在 ， 但 是 它 们 将 在 执 行 存 储 过 程 的 运 行 期 间 创 建 。 在 解 析 阶 段 的 运

行 期 间 ， 可 以 阐 明 查 询 计 划 (Query plan)。

M icrosoft SQL Server 用 表 中 的 数 据 量 、 表 中 出 现 的 索 引 等 信 息 来 建 立 查 询表 。 现 有 引 用 对 象 的 数 据 必 须 在 解 析 阶 段 存 在 ， 但 是 因 为 在 这 一 阶 段 并 不 实 际执 行 存 储 过 程 对 象 ， 所 以 引 用 对 象 名 不 必 存 在 于 对 象 创 建 语 法 验 证 阶 段 。

SQL Server Optimizer 必 须 有 关 于 现 有 (existing)对 象 的 信 息 ， 才 能 阐 明 该 查询 计 划 ， 并 把 该 计 划 放 入 procedure cache( 程 序 高 速 缓 存 ) 的 内 存 中 ( 参 见 图 6.1)。然 后 M icrosoft SQL Server 使 用 该 查 询 计 划 执 行 存 储 过 程 对 象 。

图 6.1 存储过程和触发器的新延迟名称解析

Alter Procedure 语 句

M icrosoft SQL Server 7 的 另 一 个 受 欢 迎 的 变 化 是 ： 它 能 改 变 存 储 过 程 对 象 ， 而 不 必 从 数 据 库 和 syscomments 系 统 表 中 删 去 它 来 改 变 存 储 过 程 对 象 。 改 变 存储 过 程 的 能 力 是 非 常 重 要 的 ， 因 为 存 储 过 程 和 触 发 程 序 都 带 有 其 他 引 用 和 许 可 ， 在 删 除 对 象 时 会 销 毁 它 们 。 只 改 变 对 象 而 不 是 删 除 它 将 保 留 与 该 对 象 相 关 的 引用 和 许 可 。 下 表 展 示 了 谁 能 使 用 Alter Procedure 语 句 ：

ALTER PROCEDURE 的 用 法

Stored Procedure Owner

Data Definition Language Administrators DBO

A LTER PROCEDURE 语 句 的 语 法 如 下 ：

A LTER PROC ［ EDURE ］ procedure name ［ ;number］

［ ({ @ argument_name } data_type ［ = default ］ ［ OUTPUT ］ )］

［ ,...］

W ITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }］

［ FOR REPLICATION ］ A S

Transact-SQL statements ［ ...］

表 6.1 给 出 并 解 释 了 A LTER PROCEDURE 的 每 个 参 数 。

表 6.1ALTER PROCEDURE 的参数

续 表

新 的 系 统 存 储 过 程

M icrosoft SQL Server 7 创 建 了 许 多 新 的 系 统 存 储 过 程 。 这 些 新 系 统 存 储 过程 分 为 如 下 类 别 ：

• Distributed Query Related

• Cursor Related

• Security Related

• Web Related

• Job Related

• Other

系 统 存 储 过 程 的 详 细 内 容 将 在 第 23 章 中 介 绍 。

新 分 布 式 查 询 相 关 系 统 存 储 过 程

新 分 布 式 查 询 相 关 系 统 存 储 过 程 可 用 于 分 布 式 查 询 ， 并 包 含 新 的 OLE DB

连 接 工 具 。 许 多 工 具 都 已 转 换 为 使 用 OLE DB 的 功 能 。 表 6.2 描 述 了 新 的 分 布

式 查 询 相 关 系 统 存 储 过 程 。

表 6.2 新分布式查询相关系统存储过程

新 的 游 标 相 关 系 统 存 储 过 程

表 6.3 描 述 了 新 的 游 标 相 关 存 储 过 程 的 功 能 。 当 需 要 以 每 次 一 行 的 方 式 处 理结 果 集 时 ， 可 使 用 游 标 。

表 6.3 新的游标相关系统存储过程

新 的 安 全 性 相 关 系 统 存 储 过 程

表 6.4 描 述 了 新 存 储 过 程 的 功 能 。 这 些 程 序 可 用 于 M icrosoft SQL Server 7

中 新 的 安 全 特 性 ， 如 角 色 。

表 6.4 新的安全性相关存储过程

增 强 的 W eb 相 关 系 统 存 储 过 程

表 6.5 表 示 的 是 M icrosoft SQL Server 中 的 新 增 强 型 Web 相 关 的 存 储 过 程 。

表 6.5 新的 Web 相关的存储过程

新 的 作 业 相 关 系 统 存 储 过 程

SQL Server Agent 使 用 新 的 作 业 相 关 存 储 过 程 ， 该 程 序 带 有 大 大 扩 充 的 作 业处 理 调 度 工 具 。 使 用 SQL Server Agent 可 避 免 与 这 些 存 储 过 程 合 并 在 一 起 的 复杂 性 ， 这 在 本 书 第 4 章 通 过 SQL Server Agent 来 介 绍 。 表 6.6 描 述 了 新 的 作 业相 关 系 统 存 储 过 程 的 功 能 。

表 6.6 新的作业相关系统存储过程

续 表

续 表

续 表

其 他 新 系 统 存 储 过 程

表 6.7 描 述 了 Microsoft SQL Server 中 的 其 他 几 个 新 存 储 过 程 。

表 6.7 其他新存储过程

新 系 统 扩 展 存 储 过 程

新 系 统 扩 展 存 储 过 程 很 少 在 M icrosoft SQL Server 的 日 常 工 作 中 使 用 ， 除 非它 通 过 一 个 M icrosoft SQL Server GUI 工 具 来 使 用 。 下 述 列 表 中 的 扩 展 存 储 过 程可 由 以 前 称 为 SQL Trace 的 SQL Profiler GUI 工 具 来 使 用 :

xp_trace_addnewqueue xp_trace_pausequeue

xp_trace_deletequeuedefinition xp_trace_restartqueue xp_trace_destroyqueue xp_trace_savequeuedefinition xp_trace_enumqueuedefname xp_trace_setappfilter xp_trace_enumqueuehandles p_trace_setconnectionidfilter

xp_trace_eventclassrequired xp_trace_setcpufilter

xp_trace_getappfilter xp_trace_setdbidfilter xp_trace_getconnectionidfilter xp_trace_setdurationfilter xp_trace_getcpufilter xp_trace_seteventclassrequired

xp_trace_getdbidfilter xp_trace_seteventfilter

xp_trace_getdurationfilter xp_trace_sethostfilter

xp_trace_geteventfilter xp_trace_sethpidfilter

xp_trace_geteventnames xp_trace_setindidfilter

xp_trace_getevents xp_trace_setntdmfilter

xp_trace_gethostfilter xp_trace_setntnmfilter

xp_trace_gethpidfilter xp_trace_setobjidfilter

xp_trace_getindidfilter xp_trace_setowneridfilter

xp_trace_getntdmfilter xp_trace_setpercentfilter

xp_trace_getntnmfilter xp_trace_setqueueautostart

xp_trace_getobjidfilter xp_trace_setreadfilter

xp_trace_getowneridfilter xp_trace_setserverfilter

xp_trace_getpercentfilter xp_trace_setseverityfilter

xp_trace_getqueueautostart xp_trace_setspidfilter

xp_trace_getreadfilter xp_trace_setuserfilter

xp_trace_getseverfilter xp_trace_setwritefilter xp_trace_getseverityfilter xp_trace_setqueuedestination

xp_trace_getspidfilter xp_trace_getqueuedestination

xp_trace_getspidfilter xp_trace_setqueuedestination

xp_trace_getuserfilter xp_trace_startconsumer xp_trace_getwritefilter

xp_trace_loadqueuedefinition

这 些 扩 展 存 储 过 程 可 由 SQL Server Profiler 使 用 ， 下 一 章 介 绍 新 数 据 库 工 具时 将 论 述 SQL Server Profiler。

触 发 程 序

在 M icrosoft SQL Server 中 ， 对 触 发 程 序 的 功 能 和 特 性 进 行 了 改 进 。 触 发 程序 已 经 增 加 了 下 列 特 性 ：

• 改 变 触 发 程 序

• 可 递 归 调 用 本 身 的 触 发 程 序

• 多 个 触 发 程 序 对 应 一 个 触 发 行 为

  1. 改 变 触 发 程 序 语 句

现 在 用 户 能 改 变 触 发 程 序 对 象 ， 而 不 必 从 数 据 库 和 syscommands 系 统 表 中删 除 该 对 象 。 这 是 非 常 重 要 的 ， 因 为 触 发 程 序 携 带 了 在 删 除 对 象 时 被 销 毁 的 其他 引 用 和 许 可 。 改 变 该 对 象 而 不 是 删 除 它 ， 可 以 保 留 与 该 对 象 相 关 的 引 用 和 许可 。 如 果 使 用 W ITH APPEND 选 项 ， 则 对 每 个 Update 、 Insert 和 Delete 而 言 ， 可 存 在 多 个 触 发 程 序 ， 这 意 味 着 对 每 个 Update 、 Insert 和 Delete 而 言 ， 可 激 活多 个 触 发 程 序 。 W ITH APPEND 的 功 能 存 在 于 由 sp_dbcmptlevel 设 置 的 7 级 兼容 性 。 下 表 描 述 了 谁 可 使 用 A lter Trigger 语 句 ：

A lter Trigger 的 用 法

Table Owner

Data Definition Language Administrators DBO

其 语 法 如 下 ：

A LTER TRIGGER trigger_name ON table

［ W ITH ENCRYPTION ］

{FOR { ［ ,］ ［ INSERT ］［ ,］ ［ UPDATE ］ ［ ,］ ［ DELETE ］ }

［ NOT FOR REPLICATION ］ A S

sql_statement ［ ...n ］

}

|

{FOR { ［ ,］ ［ INSERT ］ ［ ,］ ［ UPDATE ］ }

［ NOT FOR REPLICATION ］ A S

IF UPDATE (column)

［ {AND | OR} UPDATE (column) ［ ,...n ］］ sql_statement ［ ...n ］

}

表 6.8 描 述 了 A lter Trigger 的 自 变 量 。

这 是 如 何 用 Alter Trigger 语 句 改 变 插 入 触 发 程 序 的 实 例 ：

A LTER TRIGGER mytrigger ON tablename

FOR INSERT

AS RAISERROR ( ′ mytrigger error′ , 1, 2)

表 6.8Alter Trigger Statement 的自变量

续 表

在 触 发 程 序 内 部 不 允 许 使 用 的 语 句

在 触 发 程 序 中 可 使 用 的 Transact-SQL 语 句 是 有 限 的 。 在 触 发 程 序 中 不 能 使用 下 列 Transact-SQL 语 句 ：

A LTER DATABASE LOAD LOG

A LTER TABLE RESTORE LOG

A LTER TRIGGER REVOKE

CREATE DATABASE SELECT INTO ( 因 为 它 可 创 建 表 格 )

CREATE DEFAULT TRUNCATE TABLE

CREATE PROCEDURE A LTER PROCEDURE

CREATE RULE ALTER VIEW

CREATE TABLE CREATE INDEX CREATE TRIGGER CREATE TABLE

DENY CREATE VIEW

DISK INIT D ISK RESIZE

DROP DATABASE DROP INDEX

DROP DEFAULT DROP TABLE

DROP PROCEDURE G R A N T

DROP RULE RESTORE DATABASE

DROP TRIGGER RECONFIGURE

DROP VIEW UPDATE STATISTICS LOAD DATABASE

使 触 发 程 序 能 递 归 调 用 它 本 身

如 果 设 置 了 sp_dboption 递 归 触 发 程 序 选 项 ， 并 且 更 新 了 触 发 程 序 内 部 的 表格 ， 该 触 发 程 序 就 可 以 递 归 调 用 自 身 。

下 面 是 一 个 实 例 ：

sp_dboption mydb ,′ recursive triggers ′ , true

在 这 个 实 例 中 ， 如 果 更 新 了 触 发 程 序 内 部 的 表 ， 就 把 recursive trigger 数 据库 选 项 设 置 为 真 ， 而 且 允 许 该 触 发 程 序 递 归 调 用 其 自 身 。

多 个 触 发 程 序 对 应 一 个 触 发 动 作

如 果 指 明 了 W ITH APPEND 或 把 sp_dbcmptlevel 的 兼 容 性 等 级 设 置 为 70 ， 就 有 多 个 触 发 程 序 对 应 于 数 据 库 修 改 操 作 Update 、 Insert 和 Delete 。

新 数 据 类 型

M icrosoft SQL Server 中 的 数 据 类 型 包 含 新 功 能 ， 以 支 持 新 的 增 强 性 能 。 表

6.9 描 述 了 数 据 类 型 的 新 功 能 。

表 6.9 新数据类型的功能

数 据 存 储 增 强 功 能

对 数 据 库 上 的 数 据 存 储 进 行 了 某 些 改 变 ， 其 中 最 大 的 改 变 是 能 用 ALTER TABLE 删 除 一 列 和 IDENTITY 属 性 的 NOT FOR REPLICATION 从 句 。 表 6.10

表 示 受 影 响 的 存 储 语 句 及 其 增 强 功 能 。

表 6.10 数据存储语句功能的增强

新 的 属 性 功 能

M icrosoft SQL Server 7 引 入 了 几 个 新 的 属 性 功 能 。 表 6.11 描 述 了 新 的 属 性功 能 。

表 6.11 新的属性功能

新 的 安 全 性 功 能

M icrosoft SQL Server 7 中 ，在 增 强 安 全 性 方 面 ，增 加 了 与 角 色 和 新 的 Security Identification(SID )数 有 关 的 新 安 全 性 功 能 。 表 6.12 描 述 了 新 的 安 全 性 功 能 。

表 6.12 新的安全性功能

续 表

其 他 新 功 能

M icrosoft SQL Server 7 版 本 中 与 新 功 能 相 关 的 其 他 新 特 性 如 表 6.13 所 示 。

表 6.13 其他新功能

续 表

查 询 的 增 强 功 能

M icrosoft SQL Server 7 还 对 查 询 的 处 理 进 行 了 一 些 改 变 ， 包 括 新 的 优 化 程序 提 示 、 查 询 的 并 行 处 理 和 SET 语 句 中 的 显 示 计 划 增 强 功 能 等 。

表 6.14 描 述 了 查 询 的 增 强 功 能 。

表 6.14 查询的增强功能

新 的 优 化 程 序 提 示

应 注 意 某 些 新 优 化 程 序 提 示 ， 它 们 可 用 于 SELECT 、 INSERT 、 UPDATE 和

DELETE 语 句 ， 并 可 与 Query Analyzer 进 行 通 信 ， 以 便 指 明 如 何 执 行 查 询 。 表

6.15 描 述 了 这 些 新 的 优 化 程 序 提 示 。

表 6.15 新的优化程序提示

事 务 处 理 的 增 强 功 能

在 M icrosoft SQL Server 的 事 务 领 域 中 也 增 加 了 一 些 新 功 能 。 现 在 COMMIT 和 ROLLBACK 后 可 以 带 用 户 定 义 的 事 务 名 称 ， 并 增 加 了 优 化 程 序 提 示 ， 详 细内 容 请 参 阅 上 一 节 。 表 6.16 描 述 了 各 个 事 务 处 理 的 增 强 功 能 。

表 6.16 事务处理的增强功能

系 统 表 的 增 强 功 能

M icrosoft SQL Server 7 增 加 了 新 的 系 统 表 以 支 持 其 中 的 新 功 能 ， 有 许 多 新系 统 表 都 与 版 本 7 的 新 作 业 功 能 相 关 。 表 6.17 描 述 了 新 的 系 统 表 。

表 6.17 新系统表

如 前 所 述 ， Transact-SQL 在 版 本 7 中 进 行 了 许 多 修 改 。 在 下 一 章 中 ， 将 回 顾一 下 M icrosoft SQL Server 7 中 这 些 由 旧 版 本 转 化 来 的 新 增 强 工 具 。