SQL知识汇编一：触发器、存储过程、表变量、临时表

一个触发器只能作用于一张表。它在触发事件发生后被激活，并且只能在表上创建。5 插入、更新、删除：激活触发器的三个操作可以同时执行，也可以选择其中之一。6 if update：表示操作是否对指定列有影响。因此，如果使用删除操作，则不能使用该语句。Deleted和Insert可以说是一种特殊的临时表，它是在触发触发器时由系统自动生成的。它的结构与触发器作用的表结构相同，但存储的数据不同。创建表变量的语法与临时表的语法类似。例如，@@Error 表示错误号，@@RowCount 表示受影响的行数。临时表分为两种类型：本地临时表和全局临时表。主键、唯一、空、检查。

扳机

触发器的基础知识

1 tr_name：触发器名称

2 on table/view：触发器作用的表。一个触发器只能作用于一张表

3 for 和 after：同义

4 after和instead of：sql 2000新建项目afrer和instead of的区别

后

它在触发事件发生后被激活，并且只能在表上创建。

代替

它代替相应的触发事件执行，并且可以构建在表或视图上。

5 插入、更新、删除：激活触发器的三个操作可以同时执行，也可以选择其中之一。

6 if update(col_name)：表示操作是否对指定列有影响。 如果是这样，请激活触发器。 此外，由于删除操作只影响行，

因此，如果使用删除操作，则不能使用该语句（虽然使用没有错误，但无法激活触发器，没有意义）。

7 触发器执行时使用的两个特殊表：deleted和inserted

Deleted和Insert可以说是一种特殊的临时表，它是在触发触发器时由系统自动生成的。 它的结构与触发器作用的表结构相同，但存储的数据不同。

8 解释删除数据和插入数据的区别

删除数据和插入数据的区别

Inserted存储插入和更新操作后的数据

Deleted 存储删除和更新操作之前的数据

注意：更新操作相当于先删除后插入。 因此，在执行更新操作时，会将修改前的数据复制到已删除的表中。 当修改的数据保存到应用触发器的表中时，同时也会生成一条数据。 复制到插入的表

存储过程

存储过程的优点

A. 存储过程允许标准组件编程

B.存储过程可以获得更快的执行速度

C. 存储过程减少网络流量

D. 可以充分利用存储过程作为安全机制

表变量

表变量定义：

创建表变量的语法与临时表的语法类似。 不同的是创建时必须命名。 表变量是变量的一种。 表变量也分为两种：局部变量和全局变量。 本地表变量的名称以“@”为前缀，只能在当前本地用户连接中访问。 全局表变量的名称以“@@”为前缀，一般是系统的全局变量。 例如，@@Error 表示错误号，@@RowCount 表示受影响的行数。

临时表

临时表定义：

临时表与永久表类似，只不过它是在 Tempdb 中创建的。 它只有在数据库连接结束或被SQL命令DROP删除后才会消失，否则它将一直存在。 临时表在创建时会生成SQL Server系统日志。 虽然它们反映在Tempdb中并分配在内存中，但它们也支持物理磁盘，但用户无法看到指定磁盘中的文件。

临时表分为两种类型：本地临时表和全局临时表。 本地临时表的名称以“#”为前缀。 它们仅在当前本地用户连接中可见，并在用户与实例断开连接时被删除。全局临时表的名称以“##”为前缀。 它们在创建后对任何用户都是可见的，并且当引用该表的所有用户断开连接时被删除。

表变量与临时表对比总结

特征

表变量

临时表

范围

当前批次

当前会话、嵌套存储过程、全局：所有会话

使用场景

自定义函数、存储过程、批处理

自定义函数、存储过程、批处理

如何创建

仅 DECLARE 语句。 只能通过DECLEARE语句创建

创建表语句

SELECT INTO 语句。

表名长度

最多 128 字节

最多 116 字节

立柱类型

可以使用自定义数据类型

可以使用 XML 集合

自定义数据类型和 XML 集合必须在 TempDb 中定义

整理

字符串排序规则继承自当前数据库

字符串排序规则继承自 TempDb 数据库

指数

定义表时必须创建索引

创建表后即可创建索引

约束

可以使用PRIMARY KEY、UNIQUE、NULL、CHECK约束，但必须在创建表时声明

主键、唯一、空、检查。 可以使用约束，可以随时添加，但不能有外键约束

创建表后使用DDL（索引、列）

不允许

允许。

数据插入方式

INSERT 语句（SQL 2000：无法使用 INSERT/EXEC）。

INSERT语句，包括INSERT/EXEC。

SELECT INTO 语句。

将显式值插入标识列（SET IDENTITY_INSERT）。

不支持 SET IDENTITY_INSERT 语句

支持SET IDENTITY_INSERT语句

截断表

不允许

允许

销毁方法

批处理结束后自动销毁

显式调用 DROP TABLE 语句。

当前会话结束时自动销毁（全局临时表：还包括其他会话语句不再引用该表时。）

事务

只有更新表时才会有事务，持续时间比临时表短。

正常事务长度，比表变量长

存储过程重新编译

不

会导致重新编译

回滚

不受回滚影响

会受到回滚的影响

统计数据

没有创建统计信息，因此所有估计的行数都是1，因此生成的执行计划将不准确。

创建统计数据并生成具有实际行数的执行计划。

作为参数传递到存储过程

仅在SQL Server 2008中，并且必须是预定义的用户定义表类型。

不允许

显式命名的对象（索引、约束）。

不允许

允许，但要注意多用户问题

动态SQL

表变量必须在动态 SQL 中定义

可以在调用动态SQL之前定义临时表

用途：没有表关联操作，仅作为数据处理的中间集。 推荐使用表变量； 当存在表关联且无法确定数据大小时，建议使用临时表。

非结构化数据、大数据、云存储无疑已成为信息技术发展趋势和热点。 作为核心基础的分布式文件系统已经被推到了风口浪尖，受到工业界和学术界的广泛推广。 现代分布式文件系统一般具有高性能、高扩展性、高可用性、高性能、易用、易管理的特点。 架构设计的复杂性使得系统测试变得非常复杂。 从商业产品ISILON、IBRIX、SONAS、Filestore、NetApp GX、Panasas、StorNext、BWFS、Loongestor到开源系统Lustre、Glusterfs、Moosefs，如何测试和评估这些分布式文件系统并选择最适合数据应用的产品系统呢绒？ 这里我们从功能测试和非功能测试两个方面简单介绍分布式文件系统的测试方法，并对主要测试工具进行简单说明，为产品选型或者产品开发提供依据。

分布式文件系统测试方法

(1) 功能测试（手动+自动）

文件系统功能主要涉及系统实现的POSIX API，包括文件读取和访问控制、元数据操作、锁定操作等功能和API。 文件系统的POSIX语义不同，实现的文件系统API也不同。 功能测试必须能够覆盖文件系统设计和实现的API和功能点。 功能测试工作量较大，应以自动化测试为主，临时手工测试为辅。 自动化测试工具可以使用 LTP、fstest 和 locktest。

(2)非功能测试

(2.1) 数据一致性测试（手动+自动）

这里的数据一致性是指文件系统中的数据与外部写入之前的数据一致，即写入的数据和读取的数据始终一致。 数据一致性可以表明文件系统能够保证数据的完整性，不会造成数据丢失或数据错误。 这是文件系统最基本的功能。 这部分测试可以使用 diff 和 md5sum 脚本自动化。 LTP还提供数据一致性测试工具。 此外，我们还可以进行Adhoc手动测试，例如编译软件源代码和Linux内核来验证数据的完整性。

(2.2) POSIX语义兼容性测试（自动化）

POSIX（Portable Operating System Interface），全称可移植操作系统接口，由 IEEE 开发，并由 ANSI 和 ISO 标准化。 POSIX 的目的是提高应用程序在各种操作系统之间的可移植性。 符合 POSIX 标准的应用程序可以重新编译以在任何符合 POSIX 标准的操作系统上运行。 POSIX的本质是接口。 Linux遵守POSIX标准，VFS也必须遵守POSIX标准。 因此，只要文件系统满足VFS，就可以说符合POSIX标准，具有良好的可移植性、通用性和互操作性。 文件系统POSIX兼容性测试采用LTP（Linux Test Project）和PCTS（Posix Complicance Testing Suite）进行自动化测试，支持Linux90、Linux96、UNIX98 POSIX标准测试。

(2.3) 部署模式测试（手动）

当前的分布式文件通常具有横向扩展的特性，可以构建大规模、高性能的文件系统集群。 对于不同的应用程序和解决方案，文件系统的部署方式会有很大差异。 部署方法测试需要测试不同场景下的系统部署方法，包括自动安装配置、集群规模、硬件配置（服务器、存储、网络）、自动负载均衡、高可用HA等，这部分测试不太可能可以自动化。 需要根据应用场景设计方案和具体部署，然后进行人工测试。

(2.4) 可用性测试（手动）

高可用性已经是分布式文件系统保证数据应用服务连续性不可或缺的特性之一。 分布式文件系统可用性主要包括元数据服务MDS和数据。 元数据服务MDS高可用通常采用Failover机制或者MDS集群。 数据可用性主要包括Replication、Self-heal、网络集群RAID、纠删码等机制。 文件系统高可用性对于许多应用程序至关重要，需要严格的测试和验证。 这部分测试是手动执行的。

(2.5)可扩展性测试（手动）

NIST给出的云计算权威定义：按需自助服务、广泛的网络访问、资源池、快速弹性、可衡量的服务。 云存储是云计算的一种形式，分布式文件系统是云存储的基础。 因此，弹性扩展能力对于云计算时代的文件系统显得尤为重要。 文件系统可扩展性测试主要包括测试系统的弹性扩展能力（包括扩展和收缩方面），以及扩展系统对性能的影响，验证是否具有线性扩展能力。 这部分测试也是手动完成的。

(2.6) 稳定性测试（自动化）

分布式文件系统一旦上线，通常会长时间不间断地运行。 稳定的重要性不言而喻。 稳定性测试主要验证系统在长期运行（7/30/180/365x24）下是否仍能正常运行并正常发挥功能。 稳定性测试通常以自动化方式进行。 可以使用LTP、Iozone、Postmark、fio等工具在测试系统上生成负载，同时使用功能测试方法来验证功能的正确性。

(2.7) 压力测试（自动化）

分布式文件系统的负载能力总是有上限的。 当系统过载时，系统可能会出现性能下降、功能异常、拒绝访问等问题。 压力测试是验证系统在高压力下（包括多数据客户端、高OPS压力、高IOPS/吞吐量压力）下是否仍能正常运行、功能正常以及系统资源消耗情况，从而为生产运营提供依据。 。 压力测试采用自动化方式进行，使用LTP、Iozone、Postmark、fio不断增加系统压力。 同时采用功能测试方法来验证功能的正确性，并利用top、iostat、sar、ganglia等工具对系统资源进行监控。

(2.8) 性能测试（自动化）

性能是评估分布式文件系统最关键的维度。 根据文件系统在不同场景下的性能表现，可以判断文件系统是否适合特定的应用场景，为系统性能调优提供依据。 文件系统性能主要包括三个指标：IOPS、OPS、吞吐量，分别代表小文件、元数据、大数据的处理能力。 性能测试以自动化的方式进行，测试系统在不同负载情况下的性能，主要包括OPS、IOPS、小文件、大文件、海量目录、邮件服务器、文件服务器、视频服务器、Web服务器等应用下的吞吐量，得出结果在IO加载工具中可以使用Iozone、Postmark、Fio、filebench等。

文件系统测试工具简介

(1) 长期计划 ()

LTP（Linux Test Project）是由SGI和IBM联合发起的项目。 它提供了一个测试套件来验证Linux系统的可靠性、健壮性和稳定性。 它还可以用于 POSIX 兼容性测试和功能测试。 LTP提供超过2000种测试工具，可以根据您的需求进行定制。 同时，LTP也是一个优秀的自动化测试框架。 基于它，可以通过设计测试用例和测试工具来实现更多功能的测试自动化。

(2) fstest()

fstest 是文件系统 POSIX 兼容性测试套件的简化版本。 它可以在 FreeBSD、Solaris、Linux 上运行，用于测试 UFS、ZFS、ext3、XFS 和 NTFS-3G 文件系统。 fstest目前有3601个回归测试用例，测试的系统调用涵盖chmod、chown、link、mkdir、mkfifo、open、rename、rmdir、symlink、truncate、unlink。

(3) 锁定测试 ()

locktest用于对fcntl锁定功能进行压力测试。 运行时，主进程首先在指定的文件区域设置字节范围记录锁，然后多个从进程尝试在该文件区域执行读、写和添加新的锁操作。 这些操作的结果是可以预测的（矩阵如下）。 如果运行结果与预期一致，则测试通过，否则测试失败。

从机类型 测试操作 主站建议锁定 强制锁定

读锁 写锁 读锁 写锁

线程设置读锁 允许 允许 允许 允许

设置写锁 允许 允许 允许 允许

读取 允许 允许 允许 允许

写入 允许 允许 允许 允许

进程设置读锁 允许拒绝 允许拒绝

设置写锁 Denied Denied Denied Denied

读取 允许 允许 拒绝 允许

写入 允许 允许 拒绝 拒绝

(4) PCTS ()

PCTS（Posix Complicance Testing Suite），POSIX一致性测试套件，是一款基于POSIX标准并通过严格、定量的测试来验证、评估和证明操作系统是否符合POSIX标准的测试软件。 IEEE std2003.1是PCTS的设计标准。 常见的PCTS主要包括三种实现：VSX-PCTS、NIST-PCTS和OPTS-PCTS。 上面提供的连接是 VSX-PCTS。

(5) 碘酮 ()

Iozone是一种广泛使用的文件系统测试标准工具。 它可以生成和测量各种操作性能，包括读、写、重读、重写、向后读、读跨步、fread、fwrite、随机读、pread、mmap、aio_read、aio_write等操作。 Iozone已被移植到各种架构和操作系统的计算机上，并作为文件系统性能测试、分析和评估的标准工具被广泛使用。

(6) 邮戳 ()

Postmark是由知名NAS提供商NetApp开发的，用于测试其产品的后端存储性能。 Postmark主要用于测试电子邮件系统或电子商务系统中文件系统的性能。 此类应用的特点是需要频繁、大量的小文件访问。 Postmark的测试原理是创建一个测试文件池。 文件数量和最大最小长度可设置，数据总量固定。 创建完成后，Postmark会对文件池进行一系列的事务操作。 根据实际应用的统计结果，设置每个事务包括创建或删除操作以及读取或添加操作。 在某些情况下， 文件系统的缓存策略可能会对性能产生影响，而 Postmark 可以通过修改创建/删除和读/添加操作的比例来抵消这种影响。 事务操作完成后，Post删除文件池，结束测试，输出结果。 Postmark使用随机数生成正在操作的文件的序列号，使测试更接近实际应用。 输出结果中比较重要的输出数据包括总测试时间、平均每秒完成的事务数、事务处理过程中每秒创建和删除的平均文件数以及读写的平均传输速度。

(7) 菲奥 ()

fio 是一个 I/O 标准测试和硬件压力验证工具，支持 13 种不同类型的 I/O 引擎（sync、mmap、libaio、posixaio、SG v3、splice、null、network、syslet、guasi、solarisio 等） 、I/O 优先级（对于较新的 Linux 内核）、I/O 速率、分叉或线程作业等。fio 可以支持块设备和文件系统测试，广泛应用于标准测试、QA、验证测试等。它支持Linux、FreeBSD、NetBSD、OS X、OpenSolaris、AIX、HP-UX、Windows 等操作系统。

(8) 文件台()

Filebench 是一个文件系统性能的自动化测试工具。 它通过快速模拟真实应用服务器的负载来测试文件系统性能。 它不仅可以模拟文件系统微操作（如copyfiles、createfiles、randomread、randomwrite），还可以模拟复杂的应用程序（如varmail、fileserver、oltp、dss、webserver、webproxy）。 Filebench更适合测试文件服务器性能，但它也是一个自动负载生成工具，也可以用于文件系统性能。