关系数据库知识总结
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
任何用户都可以执行 OPENQUERY。用于连接到远程服务器的权限是从为链接服务器定义的设置中获取的。
A. 执行 SELECT 传递查询
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 实用工具可以在 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.将表行复制到数据文件中（使用可信连接）
下面的示例阐释了 AdventureWorks2012.Sales.Currency 表中的out 选项。此示例创建一个名为Currency.dat 的数据文件，并使用字符格式将表数据复制到该文件中。该示例假定您使用 Windows 身份验证，并且与运行bcp 命令所针对的服务器实例之间具有可信连接。
在命令提示符处输入以下命令：
bcp AdventureWorks2012.Sales.Currency out Currency.dat -T -c
B.将表行复制到数据文件中（使用混合模式身份验证）
下面的示例阐释了 AdventureWorks2012.Sales.Currency 表中的out 选项。此示例创建一个名为Currency.dat 的数据文件，并使用字符格式将表数据复制到该文件中。
该示例假定您使用混合模式身份验证，您必须使用 -U 开关指定登录 ID。并且，除非您连接到本地计算机上 SQL Server 的默认实例，否则请使用-S 开关指定系统名称和实例名称（可选）。
bcp AdventureWorks2012.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
系统将提示您输入密码。
C.将文件中的数据复制到表中
下面的示例使用上一个示例中创建的文件 (Currency.dat) 来阐释in 选项。但是，此示例将首先创建一个AdventureWorks2012 Sales.Currency 表的空副本Sales.Currency2，数据将被复制到该副本中。该示例假定您使用 Windows 身份验证，并且与运行bcp 命令所针对的服务器实例之间具有可信连接。
若要创建空表，可在查询编辑器中输入以下命令：
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.将特定的列复制到数据文件中
若要复制特定列，可以使用 queryout 选项。下面的示例仅将Sales.Currency 表中的Name 列复制到数据文件中。该示例假定您使用 Windows 身份验证，并且与运行 bcp 命令所针对的服务器实例之间具有可信连接。
在 Windows 命令提示符下，输入以下内容：
bcp "SELECT Name FROM AdventureWorks.Sales.Currency" queryout Currency.Name.dat -T -c
E.将特定的行复制到数据文件中
若要复制特定行，可以使用 queryout 选项。下面的示例仅将名为Jarrod Rana 的联系人的行从AdventureWorks2012.Person.Person 表复制到数据文件 (Jarrod Rana.dat)中。该示例假定您使用 Windows 身份验证，并且与运行bcp 命令所针对的服务器实例之间具有可信连接。
在 Windows 命令提示符下，输入以下内容：
bcp "SELECT * FROM AdventureWorks2012.Person.Person WHERE FirstName='Jarrod' AND LastName='Rana' " queryout "Jarrod Rana.dat" -T -c
F.将查询中的数据复制到数据文件中
若要将 Transact-SQL 语句的结果集复制到数据文件中，请使用 queryout 选项。 下面的示例将 AdventureWorks2012.Person.Person 表中的姓名复制到 Contacts.txt 数据文件中；这些姓名先按姓排序，再按名排序。该示例假定您使用 Windows 身份验证，并且与运行bcp 命令所针对的服务器实例之间具有可信连接。
在 Windows 命令提示符下，输入以下内容：
bcp "SELECT FirstName, LastName FROM AdventureWorks2012.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c -T
G.创建非 XML 格式化文件
下面的示例为 AdventureWorks2012 数据库中的 Sales.Currency 表创建一个非 XML 格式化文件 Currency.fmt。该示例假定您使用 Windows 身份验证，并且与运行bcp 命令所针对的服务器实例之间具有可信连接。
在 Windows 命令提示符下，输入以下内容：
bcp AdventureWorks2012.Sales.Currency format nul -T -c -f Currency.fmt
有关详细信息，请参阅非 XML 格式化文件 (SQL Server)。
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 支持两种类型的格式化文件：非 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 格式化文件
下面的示例为 AdventureWorks2012 HumanResources.Department 表创建 XML 格式化文件 Department-n.xml。格式化文件使用本机数据类型。在命令之后显示生成的格式化文件的内容。
bcp 命令包含以下限定符。
|
限定符 |
说明 |
|---|---|
|
formatnul-fformat_file |
指定非 XML 格式化文件。 |
|
-n |
指定本机数据类型。 |
|
-T |
指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。如果未指定-T，则必须指定-U 和-P 才能成功登录。 |
在 Windows 命令提示符下，输入以下 bcp 命令：
bcp AdventureWorks2012.HumanResources.Department format nul -T -n -f Department-n.fmt
生成的格式化文件 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 ""
有关详细信息，请参阅非 XML 格式化文件 (SQL Server)。
B.为字符数据创建非 XML 格式化文件
下面的示例为 AdventureWorks2012 HumanResources.Department 表创建 XML 格式化文件 Department.fmt。格式化文件使用字符数据格式和非默认字段终止符 (,)。在命令之后显示生成的格式化文件的内容。
bcp 命令包含以下限定符。
|
限定符 |
说明 |
|---|---|
|
formatnul-fformat_file |
指定非 XML 格式化文件。 |
|
-c |
指定字符数据。 |
|
-T |
指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。如果未指定-T，则必须指定-U 和-P 才能成功登录。 |
在 Windows 命令提示符下，输入以下 bcp 命令：
bcp AdventureWorks2012.HumanResources.Department format nul -c -f Department-c.fmt -T
生成的格式化文件 Department-c.fmt 包含以下信息：
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 ""
有关详细信息，请参阅非 XML 格式化文件 (SQL Server)。
C.为 Unicode 本机数据创建非 XML 格式化文件
若要为 HumanResources.Department 表中的 Unicode 本机数据创建非 XML 格式化文件，请使用以下命令：
bcp AdventureWorks2012.HumanResources.Department format nul -T -N -f Department-n.fmt
有关如何使用 Unicode 本机数据的详细信息，请参阅使用 Unicode 本机格式导入或导出数据 (SQL Server)。
D.为 Unicode 字符数据创建非 XML 格式化文件
若要为 HumanResources.Department 表中使用默认终止符的 Unicode 字符数据创建非 XML 格式化文件，请使用以下命令：
bcp AdventureWorks2012.HumanResources.Department format nul -T -w -f Department-w.fmt
有关如何使用 Unicode 字符数据的详细信息，请参阅使用 Unicode 字符格式导入或导出数据 (SQL Server)。
若要使用 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 |
指定 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 命令包含以下限定符。
|
限定符 |
说明 |
|---|---|
|
formatnul-fformat_file-x |
指定 XML 格式化文件。 |
|
-n |
指定本机数据类型。 |
|
-T |
指定 bcp 实用工具通过使用集成安全性的可信连接连接到 SQL Server。如果未指定-T，则必须指定-U 和-P 才能成功登录。 |
在 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 哈希联接和递归哈希联接。
哈希联接先扫描或计算整个生成输入，然后在内存中生成哈希表。根据计算得出的哈希键的哈希值，将每行插入哈希存储桶。如果整个生成输入小于可用内存，则可以将所有行都插入哈希表中。生成阶段之后是探测阶段。一次一行地对整个探测输入进行扫描或计算，并为每个探测行计算哈希键的值，扫描相应的哈希存储桶并生成匹配项。
如果生成输入非常大，以至于标准外部合并的输入需要多个合并级别，则需要多个分区步骤和多个分区级别。如果只有某些分区较大，则只需对那些分区使用附加的分区步骤。为了使所有分区步骤尽可能快，将使用大的异步 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) 其他事务不能读取表,更新和删除
SQL Server数据仓库相关概念及构建流程
基本概念：
1．多维数据集：多维数据集是联机分析处理 (OLAP) 中的主要对象，是一项可对数据仓库中的数据进行快速访问的技术。多维数据集是一个数据集合，通常从数据仓库的子集构造，并组织和汇总成一个由一组维度和度量值定义的多维结构。
2．维度：是多维数据集的结构性特性。它们是事实数据表中用来描述数据的分类的有组织层次结构（级别）。这些分类和级别描述了一些相似的成员集合，用户将基于这些成员集合进行分析。
3.度量值：在多维数据集中，度量值是一组值，这些值基于多维数据集的事实数据表中的一列，而且通常为数字。此外，度量值是所分析的多维数据集的中心值。即，度量值是最终用户浏览多维数据集时重点查看的数字数据。您所选择的度量值取决于最终用户所请求的信息类型。一些常见的度量值有 sales、cost、expenditures 和 production count 等。
4．元数据：不同 OLAP 组件中的数据和应用程序的结构模型。元数据描述 OLTP 数据库中的表、数据仓库和数据集市中的多维数据集这类对象，还记录哪些应用程序引用不同的记录块。
5．级别：级别是维度层次结构的一个元素。级别描述了数据的层次结构，从数据的最高（汇总程度最大）级别直到最低（最详细）级别。
6．数据挖掘：数据挖掘使您得以定义包含分组和预测规则的模型，以便应用于关系数据库或多维 OLAP 数据集中的数据。之后，这些预测模型便可用于自动执行复杂的数据分析，以找出帮助识别新机会并选择有获胜把握的机会的趋势。
7．多维 OLAP (MOLAP)：MOLAP 存储模式使得分区的聚合和其源数据的复本以多维结构存储在分析服务器计算机上。根据分区聚合的百分比和设计，MOLAP 存储模式为达到最快查询响应时间提供了潜在可能性。总而言之，MOLAP 更加适合于频繁使用的多维数据集中的分区和对快速查询响应的需要。
8．关系 OLAP (ROLAP)：ROLAP 存储模式使得分区的聚合存储在关系数据库的表（在分区数据源中指定）中。但是，可为分区数据使用 ROLAP 存储模式，而不在关系数据库中创建聚合。
9．混合 OLAP (HOLAP)：HOLAP 存储模式结合了 MOLAP 和 ROLAP 二者的特性。
10．粒度：数据汇总的层次或深度。
11．聚合|聚集：聚合是预先计算好的数据汇总，由于在问题提出之前已经准备了答案，聚合可以改进查询响应时间。
12．切块：由多个维的多个成员限定的分区数据，称为一个切块。
13．切片：由一个维的一个成员限定的分区数据，称为一个切片。
14．数据钻取：最终用户从常规多维数据集、虚拟多维数据集或链接多维数据集中选择单个单元，并从该单元的源数据中检索结果集以获得更详细的信息，这个操作过程就是数据钻取。
15．数据挖掘模型：数据挖掘使您得以定义包含分组和预测规则的模型，以便应用于关系数据库或多维 OLAP 数据集中的数据。之后，这些预测模型便可用于自动执行复杂的数据分析，以找出帮助识别新机会并选择有获胜把握的机会的趋势。
|
优先级 |
计算顺序 |
运算符 |
说明 |
|---|---|---|---|
|
15 |
从左到右 |
., [], () |
字段访问、数组索引、函数调用和表达式分组 |
|
14 |
从右到左 |
++, --, -, ~, !, delete, new, typeof, void |
一元运算符、返回数据类型、对象创建、未定义的值 |
|
13 |
从左到右 |
*, /, % |
相乘、相除、求余数 |
|
12 |
从左到右 |
+, - |
相加、字符串串联、相减 |
|
11 |
从左到右 |
<<, >>, >>> |
移位 |
|
10 |
从左到右 |
<, <=, >, >=, instanceof |
小于、小于或等于、大于、大于或等于、是否为特定类的实例 |
|
9 |
从左到右 |
==, !=, ===, !== |
相等、不相等、全等，不全等 |
|
8 |
从左到右 |
& |
按位“与” |
|
7 |
从左到右 |
^ |
按位“异或” |
|
6 |
从左到右 |
| |
按位“或” |
|
5 |
从左到右 |
&& |
逻辑“与” |
|
4 |
从左到右 |
|| |
逻辑“或” |
|
3 |
从右到左 |
?: |
条件 |
|
2 |
从右到左 |
=, OP= |
赋值、复合赋值 |
|
1 |
从左到右 |
,（逗号） |
多个计算 |
表达式中的圆括号改变由运算符优先级确定的计算顺序。这就是说，先计算完圆括号内的表达式，然后再将它的值用于表达式的其余部分。
例如：
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
- WITH <common_table_expression>
-
指定在 MERGE 语句作用域内定义的临时命名结果集或视图，也称为公用表表达式。该结果集派生自一个简单查询，并由 MERGE 语句引用。有关详细信息，请参阅 WITH common_table_expression (Transact-SQL)。
- TOP ( expression ) [ PERCENT ]
-
指定受影响的行数或行百分比。 expression 可以为行数，也可以为行百分比。 在 TOP 表达式中引用的行不是以任意顺序排列的。 有关详细信息，请参阅 TOP (Transact-SQL)。
在联接整个源表和整个目标表并且删除了不符合插入、更新或删除操作条件的联接行之后，应用 TOP 子句。TOP 子句将联接行的数量进一步减少为指定值，并且以一种无序方式对其余联接行应用插入、更新或删除操作。也就是说，在 WHEN 子句中定义的操作中，这些行是无序分布的。例如，如果指定 TOP (10)，将会影响 10 行；在这些行中，可能会更新 7 行而插入 3 行，或者可能删除 1 行、更新 5 行并且插入 4 行，等等。
由于 MERGE 语句对源表和目标表都进行完全表扫描，因此在使用 TOP 子句通过创建多个批处理来修改大型表时，I/O 性能可能会受到影响。在这种情况下，一定要确保所有连续批处理都以新行作为处理目标。
- database_name
-
target_table 所在数据库的名称。
- schema_name
-
target_table 所属架构的名称。
- target_table
-
表或视图，<table_source> 中的数据行将根据 <clause_search_condition> 与该表或视图进行匹配。target_table 是由 MERGE 语句的 WHEN 子句指定的任何插入、更新或删除操作的目标。
如果 target_table 为视图，则针对它的任何操作都必须满足更新视图所需的条件。有关详细信息，请参阅通过视图修改数据。
target_table 不能是远程表。target_table 不能具有针对它定义的任何规则。
- [ AS ] table_alias
-
用于引用表的替代名称。
- USING <table_source>
-
指定基于 <merge_search condition> 与 target_table 中的数据行进行匹配的数据源。 此匹配的结果指出了要由 MERGE 语句的 WHEN 子句采取的操作。<table_source> 可以是一个远程表，或者是一个能够访问远程表的派生表。
<table_source> 可以是一个派生表，它使用 Transact-SQL 表值构造函数通过指定多行来构造表。
有关此子句的语法和参数的详细信息，请参阅 FROM (Transact-SQL)。
- ON <merge_search_condition>
-
指定在 <table_source> 与 target_table 进行联接以确定它们的匹配位置时所遵循的条件。
注意重要的是，应仅指定目标表中用于匹配目的的列。 也就是说，指定与源表中的对应列进行比较的目标表列。 不要尝试通过在 ON 子句中筛选掉目标表中的行来提高查询性能，例如，指定AND NOT target_table.column_x = value。这样做可能会返回意外和不正确的结果。
- WHEN MATCHED THEN <merge_matched>
-
指定 target_table 中与 <table_source> ON <merge_search_condition> 返回的行匹配并满足所有其他搜索条件的所有行均应根据 <merge_matched> 子句进行更新或删除。
MERGE 语句最多可以有两个 WHEN MATCHED 子句。 如果指定了两个子句，则第一个子句必须同时带有一个 AND <search_condition> 子句。 对于任何给定的行，只有在未应用第一个 WHEN MATCHED 子句的情况下，才会应用第二个 WHEN MATCHED 子句。 如果有两个 WHEN MATCHED 子句，那么其中的一个必须指定 UPDATE 操作，而另一个必须指定 DELETE 操作。 如果在 <merge_matched> 子句中指定了 UPDATE，并且根据 <merge_search_condition>，<table_source> 中的多个行与target_table 中的某一行匹配，则 SQL Server 将返回错误。MERGE 语句无法多次更新同一行，也无法更新和删除同一行。
- WHEN NOT MATCHED [ BY TARGET ] THEN <merge_not_matched>
-
指定对于 <table_source> ON <merge_search_condition> 返回的每一行，如果该行与target_table中的行不匹配但是满足其他搜索条件（如果存在），则在 target_table 中插入一行。要插入的值是由 <merge_not_matched> 子句指定的。MERGE 语句只能有一个 WHEN NOT MATCHED 子句。
- WHEN NOT MATCHED BY SOURCE THEN <merge_matched>
-
指定 target_table 中与 <table_source> ON <merge_search_condition> 返回的行不匹配但满足所有其他搜索条件的所有行均应根据 <merge_matched> 子句进行更新或删除。
MERGE 语句最多可以有两个 WHEN NOT MATCHED BY SOURCE 子句。如果指定了两个子句，则第一个子句必须同时带有一个 AND <clause_search_condition> 子句。对于任何给定的行，只有当未应用第一个 WHEN NOT MATCHED BY SOURCE 子句时才会应用第二个子句。如果有两个 WHEN NOT MATCHED BY SOURCE 子句，那么其中的一个必须指定 UPDATE 操作，而另一个必须指定 DELETE 操作。在 <clause_search_condition> 中只能引用目标表中的列。
当 <table_source> 未返回任何行时，无法访问源表中的列。 如果 <merge_matched> 子句中指定的更新或删除操作引用了源表中的列，将返回错误 207（无效列名）。 例如，由于无法访问源表中的 Col1，因此 WHEN NOT MATCHED BY SOURCE THEN UPDATE SET TargetTable.Col1 = SourceTable.Col1 子句可能导致该语句失败。
- AND <clause_search_condition>
-
指定任何有效的搜索条件。 有关详细信息，请参阅搜索条件 (Transact-SQL)。
- <table_hint_limited>
-
为由 MERGE 语句执行的每个插入、更新或删除操作指定对目标表应用的一个或多个表提示。需要有 WITH 关键字和括号。
不允许使用 NOLOCK 和 READUNCOMMITTED。 有关表提示的详细信息，请参阅表提示 (Transact-SQL)。
对作为 INSERT 语句目标的表指定 TABLOCK 提示与指定 TABLOCKX 提示具有相同的效果。对表采用排他锁。如果指定了 FORCESEEK，会将其应用于与源表联接的目标表的隐式实例。
注意指定带有 WHEN NOT MATCHED [ BY TARGET ] THEN INSERT 的 READPAST 可能会导致违反 UNIQUE 约束的 INSERT 操作。
- INDEX ( index_val [ ,...n ] )
-
指定目标表上的一个或多个索引的名称或 ID，以执行与源表的隐式联接。 有关详细信息，请参阅表提示 (Transact-SQL)。
- <output_clause>
-
不按照任何特定顺序为 target_table 中更新、插入或删除的每一行返回一行。有关此子句的参数的详细信息，请参阅OUTPUT 子句 (Transact-SQL)。
- OPTION ( <query_hint> [ ,...n ] )
-
指定使用优化器提示来自定义数据库引擎处理语句的方式。 有关详细信息，请参阅查询提示 (Transact-SQL)。
- <merge_matched>
-
指定更新或删除操作，这些操作应用于 target_table 中与 <table_source> ON <merge_search_condition> 返回的行不匹配但满足所有其他搜索条件的所有行。
- <merge_not_matched>
-
指定要插入到目标表中的值。
有关此子句的详细信息，请参阅 INSERT (Transact-SQL)。
- <search condition>
-
指定用于指定 <merge_search_condition> 或 <clause_search_condition> 的搜索条件。有关此子句的参数的详细信息，请参阅搜索条件 (Transact-SQL)。
必须指定三个 MATCHED 子句中的至少一个子句，但可以按任何顺序指定。 不能在同一个 MATCHED 子句中多次更新一个变量。
由 MERGE 语句指定的目标表中的任何插入、更新或删除操作都受为它定义的任何约束的限制，包括任何级联引用完整性约束。如果 IGNORE_DUP_KEY 对于目标表中的任何唯一索引都设置为 ON，则 MERGE 将忽略此设置。
MERGE 语句需要一个分号 (;) 作为语句终止符。 如果运行没有终止符的 MERGE 语句，将引发错误 10713。
如果在 MERGE 之后使用，@@ROWCOUNT (Transact-SQL) 会返回为客户端插入、更新和删除的行的总数。
在数据库兼容级别设置为 100 时，MERGE 为完全保留的关键字。 MERGE 语句可用于设置为 90 和 100 的数据库兼容级别；但是，在将数据库兼容级别设置为 90 时，MERGE 为非完全保留的关键字。
触发器的实现
对于在 MERGE 语句中指定的每个插入、更新或删除操作，SQL Server 都会激发针对目标表定义的任何对应的 AFTER 触发器，但不保证哪个操作最先或最后激发触发器。为相同操作定义的触发器会遵循您指定的顺序进行触发。有关设置触发器激发顺序的详细信息，请参阅指定第一个和最后一个触发器。
对于由 MERGE 语句执行的插入、更新或删除操作，如果目标表具有针对自己定义的已启用 INSTEAD OF 触发器，那么对于在 MERGE 语句中指定的所有操作，它都必须具有已启用的 INSTEAD OF 触发器。
如果对 target_table 定义了任何 INSTEAD OF UPDATE 或 INSTEAD OF DELETE 触发器，则不会执行更新或删除操作，而是会激发触发器并相应地填充inserted 和deleted 表。
如果对 target_table 定义了任何 INSTEAD OF INSERT 触发器，则不会执行插入操作，而是会激发触发器并相应地填充inserted 表。
A.使用 MERGE 在单个语句中对表执行 INSERT 和 UPDATE 操作
一种常见的应用场景是在存在匹配行时更新表中的一个或多个列，或在不存在匹配行时将数据作为新行插入。此操作一般通过将参数传递给包含相应 UPDATE 和 INSERT 语句的存储过程来执行。借助 MERGE 语句，将可以在单个语句中一起执行这两项任务。下面的示例显示了一个同时包含 INSERT 语句和 UPDATE 语句的存储过程。随后，此示例对该过程进行了修改，以使用单个 MERGE 语句执行等效的操作。
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
B.使用 MERGE 在单个语句中对表执行 UPDATE 和 DELETE 操作
下面的示例使用 MERGE 根据 SalesOrderDetail 表中已处理的订单，每天更新AdventureWorks 示例数据库中的ProductInventory 表。通过减去每天对SalesOrderDetail 表中的每种产品所下的订单数，更新ProductInventory 表的Quantity 列。如果某种产品的订单数导致该产品的库存量下降到 0 或更少，则会从ProductInventory 表中删除该产品对应的行。
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'
C.借助派生的源表，使用 MERGE 对目标表执行 UPDATE 和 INSERT 操作
下面的示例使用 MERGE 以更新或插入行的方式来修改 SalesReason 表。 当源表中的 NewName 值与目标表 (SalesReason) 的 Name 列中的值匹配时，就会更新此目标表中的 ReasonType 列。 当 NewName 的值不匹配时，就会将源行插入到目标表中。 此源表是一个派生表，它使用 Transact-SQL 表值构造函数指定源表的多个行。 有关在派生表中使用表值构造函数的详细信息，请参阅表值构造函数 (Transact-SQL)。该示例还说明了如何在表变量中存储 OUTPUT 子句的结果，并且说明存储结果之后如何通过执行返回已插入和更新的行的计数的简单选择操作来汇总 MERGE 语句的结果。
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;
D.将 MERGE 语句的执行结果插入到另一个表中
下面的示例捕获从 MERGE 语句的 OUTPUT 子句返回的数据，并将该数据插入到另一个表中。MERGE 语句根据在SalesOrderDetail 表中处理的订单更新ProductInventory 表的Quantity 列。本示例捕获已更新的行并将这些行插入到用于跟踪库存变化的另一个表中。
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
