sqlserver(T_SQL):
DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)
DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)
DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)


Oracle SQL(P_SQL) 语句可以分为以下几类:
1.数据操作语言语句[Data manipulation language，DML]
2.数据定义语言语句[Data definition language，DDL]
3.事务控制语句[transaction control statement]
4.会话控制语句[session control statement]

openQuery

openQuery：对给定的链接服务器执行指定的传递查询。该服务器是 OLE DB 数据源。OPENQUERY 可以在查询的 FROM 子句中引用，就好象它是一个表名。OPENQUERY 也可以作为 INSERT、UPDATE 或 DELETE 语句的目标表进行引用。但这要取决于 OLE DB 访问接口的功能。尽管查询可能返回多个结果集，但是 OPENQUERY 只返回第一个。

语法：OPENQUERY ( linked_server ,'query' )

OPENQUERY 不接受其参数的变量。

在 SQL Server 2000 和更高版本中，OPENQUERY 不能用于对链接服务器执行扩展存储过程。但是，通过使用四部分名称，可以在链接服务器上执行扩展存储过程。例如：

EXEC SeattleSales.master.dbo.xp_msver

权限

EXEC sp_addlinkedserver 'OracleSvr', 
   'Oracle 7.3', 
   'MSDAORA', 
   'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles') 
Go

B. 执行 UPDATE 传递查询

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101') 
SET name = 'ADifferentName';

C. 执行 INSERT 传递查询

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')
VALUES ('NewTitle');

D. 执行 DELETE 传递查询

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

Bulk Insert 语法

 BULK INSERT 语句会将名为newdata.txt 的数据文件中的数据大容量导入到AdventureWorks 数据库的SalesOrderDetail 表。此数据文件驻留在系统computer2
的salesforce 网络共享目录下的\dailyorders 共享文件夹中。

BULK INSERT

   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]

      FROM 'data_file'

     [ WITH

        (

   [ [ , ] BATCHSIZE = batch_size ] --BATCHSIZE指令来设置在单个事务中可以插入到表中的记录的数量

   [ [ , ] CHECK_CONSTRAINTS ] --指定在大容量导入操作期间，必须检查所有对目标表或视图的约束。若没有 CHECK_CONSTRAINTS 选项，则所有 CHECK 和 FOREIGN KEY 约束都将被忽略，并且在此操作之后表的约束将标记为不可信。

   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] --指定该数据文件中数据的代码页

   [ [ , ] DATAFILETYPE =

      { 'char' | 'native'| 'widechar' | 'widenative' } ] --指定 BULK INSERT 使用指定的数据文件类型值执行导入操作。

   [ [ , ] FIELDTERMINATOR = 'field_terminator' ] --标识分隔内容的符号

   [ [ , ] FIRSTROW = first_row ] --指定要加载的第一行的行号。默认值是指定数据文件中的第一行

   [ [ , ] FIRE_TRIGGERS ] --是否启动触发器

   [ [ , ] FORMATFILE = 'format_file_path' ]

   [ [ , ] KEEPIDENTITY ] --指定导入数据文件中的标识值用于标识列

   [ [ , ] KEEPNULLS ] --指定在大容量导入操作期间空列应保留一个空值，而不插入用于列的任何默认值

   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]

   [ [ , ] LASTROW = last_row ] --指定要加载的最后一行的行号

   [ [ , ] MAXERRORS = max_errors ] --指定允许在数据中出现的最多语法错误数，超过该数量后将取消大容量导入操作。

   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ] --指定数据文件中的数据如何排序

   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]

   [ [ , ] ROWTERMINATOR = 'row_terminator' ] --标识分隔行的符号

   [ [ , ] TABLOCK ] --指定为大容量导入操作持续时间获取一个表级锁

   [ [ , ] ERRORFILE = 'file_name' ] --指定用于收集格式有误且不能转换为 OLE DB 行集的行的文件。

        )]

BULK INSERT AdventureWorks2012.Sales.SalesOrderDetail
   FROM '\\computer2\salesforce\dailyorders\neworders.txt';
GO

BCP命令使用

• 将 SQL Server 表中的数据大容量导出到数据文件中。

• 从查询中大容量导出数据。

• 将数据文件中的数据大容量导入到 SQL Server 表中。

bcp 实用工具可通过 bcp 命令进行访问。使用bcp 命令大容量导入数据时，除非使用已有的格式化文件，否则必须了解表的架构及其各列的数据类型。

bcp 实用工具可将 SQL Server 表中的数据导出到数据文件，以供其他程序使用。此实用工具还可将其他程序（通常为另一数据库管理系统 (DBMS)）中的数据导入 SQL Server 表。数据首先从源程序导出到数据文件，然后再通过单独的操作将数据文件中的数据复制到 SQL Server
表中。

bcp 命令具有可指定数据文件的数据类型和其他信息的开关。如果未指定这些开关，则此命令会提示您指定格式信息，例如数据文件中数据字段的类型。然后此命令会询问您是否要创建包含交互式响应的格式化文件。如果希望在以后的大容量导入或大容量导出操作中具有灵活性，格式化文件通常会很有用。可以在稍后对同等数据文件使用bcp
命令时指定该格式化文件。有关详细信息，请参阅在使用 bcp 时指定数据格式以获得兼容性 (SQL Server)。

bcp 实用工具通过使用 ODBC 大容量复制进行编写

• bcp 实用工具

             bcp 实用工具可以在 Microsoft SQL Server 实例和用户指定格式的数据文件间大容量复制数据。使用bcp 实用工具可以将大量新行导入 SQL Server 表，或将表数据导出到数据文件。除非与queryout
选项一起使用，否则使用该实用工具不需要了解 Transact-SQL 知识。若要将数据导入表中，必须使用为该表创建的格式文件，或者必须了解表的结构以及对于该表中的列有效的数据类型。

            如果使用 bcp 备份数据，请创建一个格式化文件来记录数据格式。bcp 数据文件不包括任何架构或格式信息，因此如果已删除表或视图并且不具备格式化文件，则可能无法导入数据。

bcp [database_name.] schema.{table_name | view_name | "query" {in data_file | out data_file | queryout data_file | format nul}

   [-a packet_size]
   [-b batch_size]
   [-c]
   [-C { ACP | OEM | RAW | code_page } ]
   [-d database_name]
   [-e err_file]
   [-E]
   [-f format_file]
   [-F first_row]
   [-h"hint [,...n]"] 
   [-i input_file]
   [-k]
   [-K application_intent]
   [-L last_row]
   [-m max_errors]
   [-n]
   [-N]
   [-o output_file]
   [-P password]
   [-q]
   [-r row_term]
   [-R]
   [-S [server_name[\instance_name]]
   [-t field_term]
   [-T]
   [-U login_id]
   [-v]
   [-V (80 | 90 | 100 )]
   [-w]
   [-x]
   /?

如果在命令指示符处指定的标识符或文件名包含空格或引号，则需用英文双引号 ("") 将该标识符引起来。

下面的 bcp out 命令创建了一个名为Currency Types.dat 的数据文件：

bcp AdventureWorks2012.Sales.Currency out "Currency Types.dat" -T -c

• 若要指定包含空格或引号的数据库名称，必须使用 -q 选项。

• 对于包含嵌入空格或引号的所有者、表或视图的名称，可以执行以下任一操作：

  • 指定 -q 选项，或者

  • 将所有者、表或视图的名称括在方括号 ([]) 中，并用引号引起来。

bcpout 操作要求对源表有 SELECT 权限。

bcpin 操作要求至少对目标表有 SELECT/INSERT 权限。此外，如果下列任一条件成立，则要求拥有 ALTER TABLE 权限：

• 存在约束，但没有指定 CHECK_CONSTRAINTS 提示。

  注意
  禁用约束是默认行为。 若要显式启用约束，请使用 -h 选项和 CHECK_CONSTRAINTS 提示。

• 存在触发器，但没有指定 FIRE_TRIGGER 提示。

  注意
  默认情况下，不激发触发器。 若要显式激发触发器，请使用 -h 选项和 FIRE_TRIGGERS 提示。

• 可以使用 -E 选项从数据文件导入标识值。

注意
要求对目标表具有 ALTER TABLE 权限是 SQL Server 2005 的新要求。如果用户帐户不具有对目标表的 ALTER TABLE 权限，这项新要求有可能导致不强制使用触发器和约束检查的bcp 脚本失败。

A.将表行复制到数据文件中（使用可信连接）

bcp AdventureWorks2012.Sales.Currency out Currency.dat -T -c

B.将表行复制到数据文件中（使用混合模式身份验证）

            bcp AdventureWorks2012.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
          

C.将文件中的数据复制到表中

USE AdventureWorks2012;
GO
SELECT * INTO AdventureWorks2012.Sales.Currency2 
FROM AdventureWorks2012.Sales.Currency WHERE 1=2;

bcp AdventureWorks2012.Sales.Currency2 in Currency.dat -T -c

USE AdventureWorks2012;
GO
SELECT * FROM Sales.Currency2

D.将特定的列复制到数据文件中

bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c

E.将特定的行复制到数据文件中

bcp "SELECT * FROM AdventureWorks2012.Person.Person WHERE FirstName='Jarrod' AND LastName='Rana' "  queryout "Jarrod Rana.dat" -T -c

F.将查询中的数据复制到数据文件中

bcp "SELECT FirstName, LastName FROM AdventureWorks2012.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c -T

G.创建非 XML 格式化文件

bcp AdventureWorks2012.Sales.Currency format nul -T -c  -f Currency.fmt

H.创建 XML 格式化文件

下面的示例为 AdventureWorks2012 数据库中的
Sales.Currency 表创建一个名为 Currency.xml 的 XML 格式化文件。该示例假定您使用 Windows 身份验证，并且与运行bcp 命令所针对的服务器实例之间具有可信连接。

在 Windows 命令提示符下，输入以下内容：

bcp AdventureWorks2012.Sales.Currency format nul -T -c -x -f Currency.xml

注意
若要使用 -x 开关，则必须使用 bcp 9.0 客户端。有关如何使用bcp 9.0 客户端的信息，请参阅“备注”。

有关详细信息，请参阅XML 格式化文件 (SQL Server)。

I.使用格式化文件进行 bcp 大容量导入

向 SQL Server 的实例中导入数据时，若要使用以前创建的格式化文件，请同时使用
-f 开关和 in 选项。
例如，以下命令通过使用以前创建的格式化文件 (Currency.xml)，将数据文件
Currency.dat 的内容大容量复制到 Sales.Currency 表的副本 (Sales.Currency2) 中。该示例假定您使用 Windows 身份验证，并且与运行bcp 命令所针对的服务器实例之间具有可信连接。

在 Windows 命令提示符下，输入以下内容：

bcp AdventureWorks2012.Sales.Currency2 in Currency.dat -T -f Currency.xml

• 创建格式化文件 (SQL Server)

当对 SQL Server 表进行大容量的数据导入或导出时，您可使用格式化文件提供一个灵活的系统，用于写入需要少量编辑或不需要编辑即可符合其他数据格式的数据文件，或用于从其他软件程序读取数据文件。

SQL Server 支持两种类型的格式化文件：非 XML 格式和 XML 格式。非 XML 格式是 SQL Server 早期版本支持的原始格式。

通常，XML 与非 XML 格式化文件可以互换。
但是，建议您为新的格式化文件使用 XML 语法，因为与非 XML 格式化文件相比，格式化文件具有多项优点。

注意
读取格式化文件所用的 bcp 实用工具 (Bcp.exe) 的版本必须与创建格式化文件所用的版本相同或更高。例如，SQL Server 2008bcp 可以读取由 SQL Server 2005bcp 生成的 9.0 版格式化文件，但 SQL Server 2005bcp 无法读取由 SQL Server 2008bcp 生成的 10.0 版格式化文件。

若要使用 bcp 命令创建格式化文件，请指定
format 参数并使用 nul 而不是数据文件路径。
format 选项还需要 -f 选项，例如：

bcptable_or_viewformat nul-fformat_file_name

A.为本机数据创建非 XML 格式化文件

bcp AdventureWorks2012.HumanResources.Department format nul -T -n -f Department-n.fmt

9.0
4
1       SQLSMALLINT   0       2       ""   1     DepartmentID                 ""
2       SQLNCHAR      2       100     ""   2     Name                         SQL_Latin1_General_CP1_CI_AS
3       SQLNCHAR      2       100     ""   3     GroupName                    SQL_Latin1_General_CP1_CI_AS
4       SQLDATETIME   0       8       ""   4     ModifiedDate                 ""

B.为字符数据创建非 XML 格式化文件

bcp AdventureWorks2012.HumanResources.Department format nul -c -f Department-c.fmt -T

9.0
4
1       SQLCHAR       0       7       "\t"     1     DepartmentID                 ""
2       SQLCHAR       0       100     "\t"     2     Name                         SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     "\t"     3     GroupName                    SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       24      "\r\n"   4     ModifiedDate                 ""

C.为 Unicode 本机数据创建非 XML 格式化文件

bcp AdventureWorks2012.HumanResources.Department format nul -T -N -f Department-n.fmt

D.为 Unicode 字符数据创建非 XML 格式化文件

bcp AdventureWorks2012.HumanResources.Department format nul -T -w -f Department-w.fmt

若要使用 bcp 命令创建格式化文件，请指定
format 参数并使用 nul 而不是数据文件路径。
format 选项始终要求指定 -f 选项；创建 XML 格式化文件时还必须指定
-x 选项，例如：

bcptable_or_viewformat nul-fformat_file_name-x

注意
为区分 XML 格式化文件，我们建议使用 .xml 作为文件扩展名，例如 MyTable.xml。

有关 XML 格式化文件的结构和字段的信息，请参阅 XML 格式化文件 (SQL Server)。

示例

本节包含了以下示例来演示如何使用 bcp 命令创建 XML 格式化文件：

• A. 为字符数据创建 XML 格式化文件

• B. 为本机数据创建 XML 格式化文件

本示例使用 AdventureWorks2012 示例数据库中的
HumanResources.Department 表。 HumanResources.Department 表包含四列：DepartmentID、Name、GroupName 和ModifiedDate。

注意
Adventure Works Cycles 是一家虚构的制造公司，用于演示数据库概念和方案。

A.为字符数据创建 XML 格式化文件

以下示例将为 AdventureWorks2012
HumanResources.Department 表创建 XML 格式化文件 Department.xml。格式化文件使用字符数据格式和非默认字段终止符 (,)。在命令之后显示生成的格式化文件的内容。

bcp 命令包含以下限定符。

限定符	说明
formatnul-fformat_file-x	指定 XML 格式化文件。
-c	指定字符数据。
-t,	指定逗号 (,) 作为字段终止符。 注意 如果数据文件使用默认的字段终止符 (\t)，则不需要-t 开关。
-T	指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。如果未指定-T，则必须指定-U 和-P 才能成功登录。

在 Windows 命令提示符下，输入以下 bcp 命令：

bcp AdventureWorks2012.HumanResources.Department format nul -c -x -f Department-c..xml –t, -T

生成的格式化文件 Department-c.xml 包含以下 XML 元素：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="24"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>
</BCPFORMAT>

有关此格式化文件语法的信息，请参阅 XML 格式化文件 (SQL Server)。有关字符数据的信息，请参阅使用字符格式导入或导出数据
(SQL Server)。

B.为本机数据创建 XML 格式化文件

以下示例将为 HumanResources.Department 表创建 XML 格式化文件Department-n.xml。格式化文件使用本机数据类型。在命令之后显示生成的格式化文件的内容。

bcp 命令包含以下限定符。

在 Windows 命令提示符下，输入以下 bcp 命令：

bcp AdventureWorks2012.HumanResources.Department format nul -x -f Department-n..xml -n -T

生成的格式化文件 Department-n.xml 包含以下 XML 元素：

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="NativeFixed" LENGTH="2"/>
  <FIELD ID="2" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="NCharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="NativeFixed" LENGTH="8"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="DepartmentID" xsi:type="SQLSMALLINT"/>
  <COLUMN SOURCE="2" NAME="Name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="3" NAME="GroupName" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="4" NAME="ModifiedDate" xsi:type="SQLDATETIME"/>
 </ROW>

恢复模式 (SQL Server)

SQL Server 备份和还原操作发生在数据库的恢复模式的上下文中。
恢复模式旨在控制事务日志维护。 “恢复模式”是一种数据库属性，它控制如何记录事务，事务日志是否需要（以及允许）备份，以及可以使用哪些类型的还原操作。有三种恢复模式：简单恢复模式、完整恢复模式和大容量日志恢复模式。通常，数据库使用完整恢复模式或简单恢复模式。数据库可以随时切换为其他恢复模式。

恢复模式	说明	工作丢失的风险	能否恢复到时点？
简单	无日志备份。 自动回收日志空间以减少空间需求，实际上不再需要管理事务日志空间。 有关简单恢复模式下数据库备份的信息，请参阅完整数据库备份 (SQL Server)。	最新备份之后的更改不受保护。 在发生灾难时，这些更改必须重做。	只能恢复到备份的结尾。 有关详细信息，请参阅完整数据库还原（简单恢复模式）。
完全	需要日志备份。 数据文件丢失或损坏不会导致丢失工作。 可以恢复到任意时点（例如应用程序或用户错误之前）。 有关完整恢复模式下数据库备份的信息，请参阅完整数据库备份 (SQL Server) 和完整数据库还原（完整恢复模式）。	正常情况下没有。 如果日志尾部损坏，则必须重做自最新日志备份之后所做的更改。	如果备份在接近特定的时点完成，则可以恢复到该时点。 有关使用日志备份还原到故障点的信息，请参阅将 SQL Server 数据库还原到某个时点（完整恢复模式）. 注意 如果有两个或更多必须在逻辑上保持一致的完整恢复模式数据库，则最好执行特殊步骤，以确保这些数据库的可恢复性。有关详细信息，请参阅包含标记的事务的相关数据库的恢复。
大容量日志	需要日志备份。 是完整恢复模式的附加模式，允许执行高性能的大容量复制操作。 通过使用最小方式记录大多数大容量操作，减少日志空间使用量。 有关尽量减少日志量的操作的信息，请参阅事务日志 (SQL Server)。 有关大容量日志恢复模式下数据库备份的信息，请参阅完整数据库备份 (SQL Server) 和完整数据库还原（完整恢复模式）。	如果在最新日志备份后发生日志损坏或执行大容量日志记录操作，则必须重做自该上次备份之后所做的更改。 否则不丢失任何工作。	可以恢复到任何备份的结尾。 不支持时点恢复。

Microsoft SQL Server 提供以下类型的复制以用于分布式应用程序：

• 事务复制。 有关详细信息，请参阅事务复制。

• 合并复制。 有关详细信息，请参阅合并复制。

• 快照复制。 有关详细信息，请参阅快照复制。

为应用程序选择的复制类型取决于多种因素，其中包括实际复制环境、要复制的数据类型和数量，以及是否在订阅服务器上更新数据等等。实际环境包括复制中所涉及的计算机数量和位置，以及这些计算机是客户端（工作站、便携式电脑或手持设备）还是服务器。

每种复制类型通常都开始于发布服务器和订阅服务器之间的已发布对象的初始同步。
此初始同步可以由带有“快照”的复制执行，该快照为发布所指定的所有对象和数据的副本。
快照在创建之后，便被传递到订阅服务器。 对于某些应用程序而言，只需快照复制即可。对于另一些类型的应用程序而言，后续数据更改应随着时间而增量式地传递到订阅服务器，这一点很重要。某些应用程序也需要更改从订阅服务器传递回发布服务器。事务复制和合并复制为这些类型的应用程序提供了若干选项。

不会跟踪快照复制的数据更改；每次应用快照时，都将完全覆盖现有数据。
事务复制通过 SQL Server 事务日志跟踪更改，而合并复制则通过触发器和元数据表跟踪更改

哈希联接

哈希联接有两种输入：生成输入和探测输入。查询优化器指派这些角色，使两个输入中较小的那个作为生成输入。

哈希联接用于多种设置匹配操作：内部联接；左外部联接、右外部联接和完全外部联接；左半联接和右半联接；交集；并集和差异。此外，哈希联接的某种变形可以进行重复删除和分组，例如 SUM(salary) GROUP BY department。这些修改对生成和探测角色只使用一个输入。

以下几节介绍了不同类型的哈希联接：内存中的哈希联接、Grace 哈希联接和递归哈希联接。

---

哈希联接先扫描或计算整个生成输入，然后在内存中生成哈希表。根据计算得出的哈希键的哈希值，将每行插入哈希存储桶。如果整个生成输入小于可用内存，则可以将所有行都插入哈希表中。生成阶段之后是探测阶段。一次一行地对整个探测输入进行扫描或计算，并为每个探测行计算哈希键的值，扫描相应的哈希存储桶并生成匹配项。

Grace 哈希联接

---

如果生成输入大于内存，哈希联接将分为几步进行。这称为“Grace 哈希联接”。每一步都分为生成阶段和探测阶段。首先，消耗整个生成和探测输入并将其分区（使用哈希键上的哈希函数）为多个文件。对哈希键使用哈希函数可以保证任意两个联接记录一定位于相同的文件对中。因此，联接两个大输入的任务简化为相同任务的多个较小的实例。然后将哈希联接应用于每对分区文件。

递归哈希联接

---

如果生成输入非常大，以至于标准外部合并的输入需要多个合并级别，则需要多个分区步骤和多个分区级别。如果只有某些分区较大，则只需对那些分区使用附加的分区步骤。为了使所有分区步骤尽可能快，将使用大的异步 I/O 操作以便单个线程就能使多个磁盘驱动器繁忙工作。

注意
如果生成输入仅稍大于可用内存，则内存中的哈希联接和 Grace 哈希联接的元素将结合在一个步骤中，生成混合哈希联接。

在优化过程中不能始终确定使用哪种哈希联接。因此，SQL Server 开始时使用内存中的哈希联接，然后根据生成输入的大小逐渐转换到 Grace 哈希联接和递归哈希联接。

如果优化器错误地预计两个输入中哪个较小并由此确定哪个作为生成输入，生成角色和探测角色将动态反转。哈希联接确保使用较小的溢出文件作为生成输入。这一技术称为“角色反转”。至少一个文件溢出到磁盘后，哈希联接中才会发生角色反转。

注意
角色反转的发生独立于任何查询提示或结构。角色反转不会显示在查询计划中；角色反转对于用户是透明的。

哈希援助

---

术语“哈希援助”有时用于描述 Grace 哈希联接或递归哈希联接。

注意
递归哈希联接或哈希援助会导致服务器性能降低。如果跟踪中显示许多哈希警告事件，请更新正在联接的列上的统计信息。

锁模式 描述
共享 (S) 用于不更改或不更新数据的操作（只读操作），如 SELECT 语句。
更新 (U) 用于可更新的资源中。防止当多个会话在读取、锁定以及随后可能进行的资源更新时发生常见形式的死锁。
排它 (X) 用于数据修改操作，例如 INSERT、UPDATE 或 DELETE。确保不会同时同一资源进行多重更新。
意向锁 用于建立锁的层次结构。意向锁的类型为：意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。
架构锁 在执行依赖于表架构的操作时使用。架构锁的类型为：架构修改 (Sch-M) 和架构稳定性 (Sch-S)。
大容量更新 (BU) 向表中大容量复制数据并指定了 TABLOCK 提示时使用。

共享锁
共享 (S) 锁允许并发事务读取 (SELECT) 一个资源。资源上存在共享 (S) 锁时，任何其它事务都不能修改数据。一旦已经读取数据，便立即释放资源上的共享 (S) 锁，除非将事务隔离级别设置为可重复读或更高级别，或者在事务生存周期内用锁定提示保留共享 (S) 锁。

更新锁
更新 (U) 锁可以防止通常形式的死锁。一般更新模式由一个事务组成，此事务读取记录，获取资源（页或行）的共享 (S) 锁，然后修改行，此操作要求锁转换为排它 (X) 锁。如果两个事务获得了资源上的共享模式锁，然后试图同时更新数据，则一个事务尝试将锁转换为排它 (X) 锁。共享模式到排它锁的转换必须等待一段时间，因为一个事务的排它锁与其它事务的共享模式锁不兼容；发生锁等待。第二个事务试图获取排它 (X) 锁以进行更新。由于两个事务都要转换为排它 (X) 锁，并且每个事务都等待另一个事务释放共享模式锁，因此发生死锁。

若要避免这种潜在的死锁问题，请使用更新 (U) 锁。一次只有一个事务可以获得资源的更新 (U) 锁。如果事务修改资源，则更新 (U) 锁转换为排它 (X) 锁。否则，锁转换为共享锁。

排它锁
排它 (X) 锁可以防止并发事务对资源进行访问。其它事务不能读取或修改排它 (X) 锁锁定的数据。

意向锁
意向锁表示 SQL Server 需要在层次结构中的某些底层资源上获取共享 (S) 锁或排它 (X) 锁。例如，放置在表级的共享意向锁表示事务打算在表中的页或行上放置共享 (S) 锁。在表级设置意向锁可防止另一个事务随后在包含那一页的表上获取排它 (X) 锁。意向锁可以提高性能，因为 SQL Server 仅在表级检查意向锁来确定事务是否可以安全地获取该表上的锁。而无须检查表中的每行或每页上的锁以确定事务是否可以锁定整个表。

意向锁包括意向共享 (IS)、意向排它 (IX) 以及与意向排它共享 (SIX)。

锁模式 描述
意向共享 (IS) 通过在各资源上放置 S 锁，表明事务的意向是读取层次结构中的部分（而不是全部）底层资源。
意向排它 (IX) 通过在各资源上放置 X 锁，表明事务的意向是修改层次结构中的部分（而不是全部）底层资源。IX 是 IS 的超集。
与意向排它共享 (SIX) 通过在各资源上放置 IX 锁，表明事务的意向是读取层次结构中的全部底层资源并修改部分（而不是全部）底层资源。允许顶层资源上的并发 IS 锁。例如，表的 SIX 锁在表上放置一个 SIX 锁（允许并发 IS 锁），在当前所修改页上放置 IX 锁（在已修改行上放置 X 锁）。虽然每个资源在一段时间内只能有一个 SIX 锁，以防止其它事务对资源进行更新，但是其它事务可以通过获取表级的 IS 锁来读取层次结构中的底层资源。

独占锁：只允许进行锁定操作的程序使用，其他任何对他的操作均不会被接受。执行数据更新命令时，SQL Server会自动使用独占锁。当对象上有其他锁存在时，无法对其加独占锁。
共享锁：共享锁锁定的资源可以被其他用户读取，但其他用户无法修改它，在执行Select时，SQL Server会对对象加共享锁。
更新锁：当SQL Server准备更新数据时，它首先对数据对象作更新锁锁定，这样数据将不能被修改，但可以读取。等到SQL Server确定要进行更新数据操作时，他会自动将更新锁换为独占锁，当对象上有其他锁存在时，无法对其加更新锁。

2. 从程序员的角度看：分为乐观锁和悲观锁。
乐观锁：完全依靠数据库来管理锁的工作。
悲观锁：程序员自己管理数据或对象上的锁处理。

MS-SQLSERVER 使用锁在多个同时在数据库内执行修改的用户间实现悲观并发控制

三 锁的粒度
锁粒度是被封锁目标的大小,封锁粒度小则并发性高,但开销大,封锁粒度大则并发性低但开销小

SQL Server支持的锁粒度可以分为为行、页、键、键范围、索引、表或数据库获取锁

资源 描述
RID 行标识符。用于单独锁定表中的一行。
键 索引中的行锁。用于保护可串行事务中的键范围。
页 8 千字节 (KB) 的数据页或索引页。
扩展盘区 相邻的八个数据页或索引页构成的一组。
表 包括所有数据和索引在内的整个表。
DB 数据库。

四 锁定时间的长短

锁保持的时间长度为保护所请求级别上的资源所需的时间长度。

用于保护读取操作的共享锁的保持时间取决于事务隔离级别。采用 READ COMMITTED 的默认事务隔离级别时，只在读取页的期间内控制共享锁。在扫描中，直到在扫描内的下一页上获取锁时才释放锁。如果指定 HOLDLOCK 提示或者将事务隔离级别设置为 REPEATABLE READ 或 SERIALIZABLE，则直到事务结束才释放锁。

根据为游标设置的并发选项，游标可以获取共享模式的滚动锁以保护提取。当需要滚动锁时，直到下一次提取或关闭游标（以先发生者为准）时才释放滚动锁。但是，如果指定 HOLDLOCK，则直到事务结束才释放滚动锁。

用于保护更新的排它锁将直到事务结束才释放。
如果一个连接试图获取一个锁，而该锁与另一个连接所控制的锁冲突，则试图获取锁的连接将一直阻塞到：

将冲突锁释放而且连接获取了所请求的锁。

连接的超时间隔已到期。默认情况下没有超时间隔，但是一些应用程序设置超时间隔以防止无限期等待

五 SQL Server 中锁的自定义

1 处理死锁和设置死锁优先级

死锁就是多个用户申请不同封锁,由于申请者均拥有一部分封锁权而又等待其他用户拥有的部分封锁而引起的无休止的等待

可以使用SET DEADLOCK_PRIORITY控制在发生死锁情况时会话的反应方式。如果两个进程都锁定数据，并且直到其它进程释放自己的锁时，每个进程才能释放自己的锁，即发生死锁情况。

2 处理超时和设置锁超时持续时间。

@@LOCK_TIMEOUT 返回当前会话的当前锁超时设置，单位为毫秒

SET LOCK_TIMEOUT 设置允许应用程序设置语句等待阻塞资源的最长时间。当语句等待的时间大于 LOCK_TIMEOUT 设置时，系统将自动取消阻塞的语句，并给应用程序返回"已超过了锁请求超时时段"的 1222 号错误信息

示例
下例将锁超时期限设置为 1,800 毫秒。
SET LOCK_TIMEOUT 1800

3) 设置事务隔离级别。

4 ) 对 SELECT、INSERT、UPDATE 和 DELETE 语句使用表级锁定提示。

5) 配置索引的锁定粒度
可以使用 sp_indexoption 系统存储过程来设置用于索引的锁定粒度

六 查看锁的信息

1 执行 EXEC SP_LOCK 报告有关锁的信息
2 查询分析器中按Ctrl+2可以看到锁的信息

七 使用注意事项

如何避免死锁
1 使用事务时，尽量缩短事务的逻辑处理过程，及早提交或回滚事务；
2 设置死锁超时参数为合理范围，如：3分钟-10分种；超过时间，自动放弃本次操作，避免进程悬挂；
3 优化程序，检查并避免死锁现象出现；
4 .对所有的脚本和SP都要仔细测试，在正是版本之前。
5 所有的SP都要有错误处理（通过@error）
6 一般不要修改SQL SERVER事务的默认级别。不推荐强行加锁

解决问题 如何对行 表 数据库加锁

八 几个有关锁的问题

1 如何锁一个表的某一行

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM table ROWLOCK WHERE id = 1 

2 锁定数据库的一个表

SELECT * FROM table WITH (HOLDLOCK) 

加锁语句：

sybase:
update 表 set col1=col1 where 1=0 ;
MSSQL:
select col1 from 表 (tablockx) where 1=0 ;
oracle:
LOCK TABLE 表 IN EXCLUSIVE MODE ； 

加锁后其它人不可操作，直到加锁用户解锁，用commit或rollback解锁

几个例子帮助大家加深印象
设table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

1）排它锁
新建两个连接
在第一个连接中执行以下语句

begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran 

在第二个连接中执行以下语句

begin tran
select * from table1
where B='b2'
commit tran 

若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒

2）共享锁
在第一个连接中执行以下语句

begin tran
select * from table1 holdlock -holdlock人为加锁
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran 

在第二个连接中执行以下语句

begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran 

若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个事务释放共享锁转为排它锁后才能执行 即要等待30秒

3）死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句

begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran 

在第二个连接中执行以下语句

begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran 

同时执行，系统会检测出死锁，并中止进程

补充一点:
Sql Server2000支持的表级锁定提示

HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别

NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别

PAGLOCK 在使用一个表锁的地方用多个页锁

READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁

ROWLOCK 强制使用行锁

TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表

UPLOCK 强制在读表时使用更新而不用共享锁

应用程序锁:
应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁

处理应用程序锁的两个过程

sp_getapplock 锁定应用程序资源

sp_releaseapplock 为应用程序资源解锁

注意: 锁定数据库的一个表的区别

SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除

SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除

优先级表

表达式中的圆括号改变由运算符优先级确定的计算顺序。这就是说，先计算完圆括号内的表达式，然后再将它的值用于表达式的其余部分。

例如：

z = 78 * (96 - 3 + 45)

数据库三种模式

简介

　　美国家标准协会(American National Standard Institute, ANSI)的数据库管理系统研究小组于1978年提出了标准化的建议，将数据库结构分为3级：面向用户或应用程序员的用户级、面向建立和维护数据库人员的概念级、面向系统程序员的物理级。

编辑本段三级模式

1、逻辑模式

　　模式又称概念模式或逻辑模式，对应于概念级。它是由数据库设计者综合所有用户的数据，按照统一的观点构造的全局逻辑结构，是对数据库中全部数据的逻辑结构和特征的总体描述，是所有用户的公共数据视图(全局视图)。它是由数据库管理系统提供的数据模式描述语言(Data Description Language，DDL)来描述、定义的，体现、反映了数据库系统的整体观。

2、外模式

　　外模式又称子模式或用户模式，对应于用户级。它是某个或某几个用户所看到的数据库的数据视图，是与某一应用有关的数据的逻辑表示。外模式是从模式导出的一个子集，包含模式中允许特定用户使用的那部分数据。用户可以通过外模式描述语言来描述、定义对应于用户的数据记录(外模式)，也可以利用数据操纵语言(Data Manipulation Language，DML)对这些数据记录进行。外模式反映了数据库的用户观。

3、内模式

　　内模式又称存储模式，对应于物理级，它是数据库中全体数据的内部表示或底层描述，是数据库最低一级的逻辑描述，它描述了数据在存储介质上的存储方式和物理结构，对应着实际存储在外存储介质上的数据库。内模式由内模式描述语言来描述、定义，它是数据库的存储观。 　　在一个数据库系统中，只有唯一的数据库， 因而作为定义 、描述数据库存储结构的内模式和定义、描述数据库逻辑结构的模式，也是唯一的，但建立在数据库系统之上的应用则是非常广泛、多样的，所以对应的外模式不是唯一的，也不可能是唯一的。

编辑本段三级模式间的映射

　　数据库的三级模式是数据库在三个级别 (层次)上的抽象，使用户能够逻辑地、抽象地处理数据而不必关心数据在计算机中的物理表示和存储。实际上 ，对于一个数据库系统而言一有物理级数据库是客观存在的，它是进行数据库操作的基础，概念级数据库中不过是物理数据库的一种逻辑的、抽象的描述(即模式)，用户级数据库则是用户与数据库的接口，它是概念级数据库的一个子集(外模式)。 　　用户应用程序根据外模式进行数据操作，通过外模式一模式映射，定义和建立某个外模式与模式间的对应关系，将外模式与模式联系起来，当模式发生改变时，只要改变其映射，就可以使外模式保持不变，对应的应用程序也可保持不变；另一方面，通过模式一内模式映射，定义建立数据的逻辑结构(模式)与存储结构(内模式)间的对应关系，当数据的存储结构发生变化时，只需改变模式一内模式映射，就能保持模式不变，因此应用程序也可以保持不变。

重新组织和重新生成

　　修复索引碎片的方法有两种：重新组织索引或重新生成索引。重新组织索引会对最外层数据页里的数据进行重新排序，并压缩索引页。重新组织的过程中 不会添加任何额外的数据，所以索引可能还残留着一定程度的碎片。重新组织索引操作不会占用很多系统资源，在运行过程中外部进程也能够对该索引所在的数据表 进行查询，所以能够说是联机(online)执行。

　　重新生成索引操作基本上删除掉目标索引并创建一个新索引。旧索引中的任何碎片都会被删除，新索引的逻辑排序将和对象的物理排序相匹配。由于整个 过程需要删除索引并重新创建，所以外部进程无法访问数据表，而且访问性能也大受影响。

MWEGE 关键字使用语法，支持在sqlserver2008上使用

根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]    
;

<target_table> ::=
{ 
    [ database_name . schema_name . | schema_name . ]
  target_table
}

<merge_hint>::=
{
    { [ <table_hint_limited> [ ,...n ] ]
    [ [ , ] INDEX ( index_val [ ,...n ] ) ] }
}

<table_source> ::= 
{
    table_or_view_name [ [ AS ] table_alias ] [ <tablesample_clause> ] 
        [ WITH ( table_hint [ [ , ]...n ] ) ] 
  | rowset_function [ [ AS ] table_alias ] 
        [ ( bulk_column_alias [ ,...n ] ) ] 
  | user_defined_function [ [ AS ] table_alias ]
  | OPENXML <openxml_clause> 
  | derived_table [ AS ] table_alias [ ( column_alias [ ,...n ] ) ] 
  | <joined_table> 
  | <pivoted_table> 
  | <unpivoted_table> 
}

<merge_search_condition> ::=
    <search_condition>

<merge_matched>::=
    { UPDATE SET <set_clause> | DELETE }

<set_clause>::=
SET
  { column_name = { expression | DEFAULT | NULL }
  | { udt_column_name.{ { property_name = expression
                        | field_name = expression }
                        | method_name ( argument [ ,...n ] ) }
    }
  | column_name { .WRITE ( expression , @Offset , @Length ) }
  | @variable = expression
  | @variable = column = expression
  | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
  | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
  } [ ,...n ] 

<merge_not_matched>::=
{
    INSERT [ ( column_list ) ] 
        { VALUES ( values_list )
        | DEFAULT VALUES }
}

<clause_search_condition> ::=
    <search_condition>

<search condition> ::=
    { [ NOT ] <predicate> | ( <search_condition> ) } 
    [ { AND | OR } [ NOT ] { <predicate> | ( <search_condition> ) } ] 
[ ,...n ] 

<predicate> ::= 
    { expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } expression 
    | string_expression [ NOT ] LIKE string_expression 
  [ ESCAPE 'escape_character' ] 
    | expression [ NOT ] BETWEEN expression AND expression 
    | expression IS [ NOT ] NULL 
    | CONTAINS 
  ( { column | * } , '< contains_search_condition >' ) 
    | FREETEXT ( { column | * } , 'freetext_string' ) 
    | expression [ NOT ] IN ( subquery | expression [ ,...n ] ) 
    | expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } 
  { ALL | SOME | ANY} ( subquery ) 
    | EXISTS ( subquery ) } 

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
    [ OUTPUT <dml_select_list> ]
}

<dml_select_list>::=
    { <column_name> | scalar_expression } 
        [ [AS] column_alias_identifier ] [ ,...n ]

<column_name> ::=
    { DELETED | INSERTED | from_table_name } . { * | column_name }
    | $action

USE AdventureWorks2012;
GO
CREATE PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;
-- Update the row if it exists.    
    UPDATE Production.UnitMeasure
	SET Name = @Name
	WHERE UnitMeasureCode = @UnitMeasureCode
-- Insert the row if the UPDATE statement failed.	
	IF (@@ROWCOUNT = 0 )
	BEGIN
	    INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name)
	    VALUES (@UnitMeasureCode, @Name)
	END
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Test Value';
SELECT UnitMeasureCode, Name FROM Production.UnitMeasure
WHERE UnitMeasureCode = 'ABC';
GO

-- Rewrite the procedure to perform the same operations using the MERGE statement.
-- Create a temporary table to hold the updated or inserted values from the OUTPUT clause.
CREATE TABLE #MyTempTable
    (ExistingCode nchar(3),
     ExistingName nvarchar(50),
     ExistingDate datetime,
     ActionTaken nvarchar(10),
     NewCode nchar(3),
     NewName nvarchar(50),
     NewDate datetime
    );
GO
ALTER PROCEDURE dbo.InsertUnitMeasure
    @UnitMeasureCode nchar(3),
    @Name nvarchar(25)
AS 
BEGIN
    SET NOCOUNT ON;

    MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
    WHEN MATCHED THEN 
        UPDATE SET Name = source.Name
	WHEN NOT MATCHED THEN	
	    INSERT (UnitMeasureCode, Name)
	    VALUES (source.UnitMeasureCode, source.Name)
	    OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable;
END;
GO
-- Test the procedure and return the results.
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'New Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'XYZ', @Name = 'Test Value';
EXEC InsertUnitMeasure @UnitMeasureCode = 'ABC', @Name = 'Another Test Value';

SELECT * FROM #MyTempTable;
-- Cleanup 
DELETE FROM Production.UnitMeasure WHERE UnitMeasureCode IN ('ABC','XYZ');
DROP TABLE #MyTempTable;
GO

USE AdventureWorks2012;
GO
IF OBJECT_ID (N'Production.usp_UpdateInventory', N'P') IS NOT NULL DROP PROCEDURE Production.usp_UpdateInventory;
GO
CREATE PROCEDURE Production.usp_UpdateInventory
    @OrderDate datetime
AS
MERGE Production.ProductInventory AS target
USING (SELECT ProductID, SUM(OrderQty) FROM Sales.SalesOrderDetail AS sod
    JOIN Sales.SalesOrderHeader AS soh
    ON sod.SalesOrderID = soh.SalesOrderID
    AND soh.OrderDate = @OrderDate
    GROUP BY ProductID) AS source (ProductID, OrderQty)
ON (target.ProductID = source.ProductID)
WHEN MATCHED AND target.Quantity - source.OrderQty <= 0
    THEN DELETE
WHEN MATCHED 
    THEN UPDATE SET target.Quantity = target.Quantity - source.OrderQty, 
                    target.ModifiedDate = GETDATE()
OUTPUT $action, Inserted.ProductID, Inserted.Quantity, Inserted.ModifiedDate, Deleted.ProductID,
    Deleted.Quantity, Deleted.ModifiedDate;
GO

EXECUTE Production.usp_UpdateInventory '20030501'

USE AdventureWorks2012;
GO
-- Create a temporary table variable to hold the output actions.
DECLARE @SummaryOfChanges TABLE(Change VARCHAR(20));

MERGE INTO Sales.SalesReason AS Target
USING (VALUES ('Recommendation','Other'), ('Review', 'Marketing'), ('Internet', 'Promotion'))
       AS Source (NewName, NewReasonType)
ON Target.Name = Source.NewName
WHEN MATCHED THEN
	UPDATE SET ReasonType = Source.NewReasonType
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, ReasonType) VALUES (NewName, NewReasonType)
OUTPUT $action INTO @SummaryOfChanges;

-- Query the results of the table variable.
SELECT Change, COUNT(*) AS CountPerChange
FROM @SummaryOfChanges
GROUP BY Change;

USE AdventureWorks2012;
GO
CREATE TABLE Production.UpdatedInventory
    (ProductID INT NOT NULL, LocationID int, NewQty int, PreviousQty int,
     CONSTRAINT PK_Inventory PRIMARY KEY CLUSTERED (ProductID, LocationID));
GO
INSERT INTO Production.UpdatedInventory
SELECT ProductID, LocationID, NewQty, PreviousQty 
FROM
(    MERGE Production.ProductInventory AS pi
     USING (SELECT ProductID, SUM(OrderQty) 
            FROM Sales.SalesOrderDetail AS sod
            JOIN Sales.SalesOrderHeader AS soh
            ON sod.SalesOrderID = soh.SalesOrderID
            AND soh.OrderDate BETWEEN '20030701' AND '20030731'
            GROUP BY ProductID) AS src (ProductID, OrderQty)
     ON pi.ProductID = src.ProductID
    WHEN MATCHED AND pi.Quantity - src.OrderQty >= 0 
        THEN UPDATE SET pi.Quantity = pi.Quantity - src.OrderQty
    WHEN MATCHED AND pi.Quantity - src.OrderQty <= 0 
        THEN DELETE
    OUTPUT $action, Inserted.ProductID, Inserted.LocationID, Inserted.Quantity AS NewQty, Deleted.Quantity AS PreviousQty)
 AS Changes (Action, ProductID, LocationID, NewQty, PreviousQty) WHERE Action = 'UPDATE';
GO