SQLServer 2005范例代码查询辞典+

zxmlove 贡献于2011-07-08

作者 微软用户  创建于2008-09-25 01:28:00   修改者微软用户  修改于2008-09-26 02:23:00字数92629

文档摘要:第8章SQL Server函数。在本章中,我会演示如何在T-SQL代码中使用SQL Server 2005内建函数。不要把SQL Server 2005内建函数和第11章介绍的用户定义函数混淆,它能让我们实现聚合、数学运算、字符串操作、行排序等等。8.1 聚合函数聚合函数用于对一个或多个值进行计算,最后得出一个值。经常使用的聚合函数的一个例子是SUM,它用来返回一组数字值的总值。表8-1列出了SQL Server 2005提供的一些经常使用的聚合函数。
关键词:

第8章 SQL Server函数 在本章中,我会演示如何在T-SQL代码中使用SQL Server 2005内建函数。不要把SQL Server 2005内建函数和第11章介绍的用户定义函数混淆,它能让我们实现聚合、数学运算、字符串操作、行排序等等。 8.1 聚合函数 聚合函数用于对一个或多个值进行计算,最后得出一个值。经常使用的聚合函数的一个例子是SUM,它用来返回一组数字值的总值。表8-1列出了SQL Server 2005提供的一些经常使用的聚合函数。 表8-1 聚合函数 函 数 名 描  述 AVG AVG聚合函数计算一个组中非NULL值的平均值 COUNT COUNT聚合函数返回一个显示组中行数的整数值 COUNT_BIG 和COUNT功能一样,只是COUNT_BIG返回bigint数据类型值 MAX MAX聚合函数返回一组非NULL值中的最大值 MIN MIN聚合函数返回一组非NULL值中的最小值 SUM SUM聚合函数返回一个表达式中所有非NULL值的总和 STDEV STDEV函数根据数据母体的样本返回由表达式提供的所有值的标准差 STDEVP STDEVP函数也返回由表达式提供的所有值的标准差,只是它计算整个数据母体 VAR VAR函数根据数据母体的样本返回由表达式提供的值的方差 VARP VARP函数也返回表达式整个数据母体提供的值的方差 接下来的几个技巧会演示这些聚合函数。 8.1.1 返回平均值 AVG聚合函数计算组中非NULL值的平均值。 第一个示例演示如何使用AVG聚合函数返回组中非NULL值的平均值: 产品平均等级   这个查询返回: 第二个示例演示对StrandardCost列的DISTINCT值取平均——也就是说只有唯一的StandardCost值才会被平均: 对DISTINCT StandardCost求平均   这个查询返回: 解析 在这个技巧中,第一个示例返回由ProductID分组的平均产品等级。 第二个示例对DISTINCT StandardCost进行平均——也就是说只有唯一的StandardCost才会被平均。没有DISTINCT关键字,AVG聚合函数默认的行为会对包括重复值在内的所有值取平均。 8.1.2 返回行总数 COUNT聚合函数返回一个显示组中行的总数的整数数据类型。 这个示例演示了使用COUNT聚合函数来返回组中行的总数: 这个查询返回如下的(部分)结果: 如果在COUNT函数圆括号内包含DISTINCT关键字,你会得到那列的不重复值的总数。例如: 这个查询返回: 解析 在本技巧的第一个示例中,计算了每个货架上的货物数。COUNT是唯一一个不忽略NULL值的聚合函数,如果ProductID是NULL的话,它还是会在总数中包括。第二个示例演示了计算Production. ProductInventory表中不重复的货架总数。 如果你需要计算一个比整数数据类型所能存放的更大的值,可以使用COUNT_BIG聚合函数,它返回bigint数据类型的值。 8.1.3 找出表达式中的最小和最大值 MAX聚合函数返回组中非NULL值的最大值而MIN聚合函数返回最小值。MIN和MAX能用于数字、字符和datetime列。字符数据类型的最小和最大值由ASCII字母排序决定。datetime值的MIN和MAX基于最早的日期和最近的日期。 在本例中,我会演示如何使用MIN和MAX函数来找出Production.ProductReview表的Rating数字列中的最小和最大值: 这个查询返回: 解析 这个技巧演示获取Production.ProductReview表中Rating值的最小和最大值。和其他聚合函数一样,如果在SELECT子句中引用非聚合列,那么它们必须包含在GROUP BY子句中。 8.1.4 返回值的和 SUM聚合函数返回表达式中非NULL值的总和。 这个示例演示了如何使用SUM聚合函数来返回每个AccountNumber中TotalDue列值的总和: 这个查询返回下面的部分结果: 解析 在这个技巧中,根据AccountNumber来计算TotalDue列的和。因为AccountNumber本身不参与聚合而是包含在GROUP BY子句中。它也包含在ORDER BY子句中以根据分组的结果进行排序。 8.1.5 使用统计聚合函数 在这个技巧中,我会演示使用统计函数VAR、VARP、STDEV和STDEVP。 VAR函数根据数据母体的样本返回由表达式提供的值的方差(VARP函数也返回表达式整个数据母体提供的值的方差)。 第一个示例返回Sales.SalesOrderHeader表中所有行的TaxAmt值的方差: 这个查询返回: STDEV函数根据数据母体的样本返回由表达式提供的所有值的标准差。STDEVP函数也返回由表达式提供的所有值的标准差,只是它计算整个数据母体。 这个示例返回Sales.SalesOrderDetail表中所有行对于UnitPrice值的统计标准差: 这个查询返回: 解析 尽管统计函数各不相同,但是实现还是相近的。特别是在本例中,每个函数都接受一个值表达式,使用表的一个列名。然后函数使用SELECT子句中指定的列对一组数据(0个或多个行)进行操作并返回一个值。 8.2 数学函数 SQL Server 2005提供了几个能用于T-SQL代码的数学函数,如表8-2描述的那样。 表8-2  数学函数 函  数 描  述 ABS 计算绝对值 ACOS 计算其余弦是所指定参数的角(弧度) ASIN 计算其正弦是所指定参数的角(弧度) ATAN 计算其正切是所指定参数的角(弧度) ATN2 计算其正切介于两个浮点表达式之间的角(弧度) CEILING 计算大于或等于指定参数的最小整数 COS 计算余弦 COT 计算余切 DEGREES 把弧度转化为度 EXP 计算指定参数的指数值 FLOOR 返回小于或等于指定参数的最大整数 (续) 函  数 描  述 LOG 计算自然对数 LOG10 计算以10为底的对数 PI 返回Pi常量 POWER 返回第一个参数对第二个参数的幂的值 RADIANS 把度转化为弧度 RAND 生成0到1之间的随机float类型值 ROUND 把指定参数的值舍入到指定精度 SIGN 如果参数是负数返回-1,如果是0返回0,如果是正数返回1 SIN 计算指定角(弧度为单位)的正弦值 SQUARE 计算指定表达式的平方 SQRT 计算平方根 TAN 计算正切 下面的技巧会以示例来演示数学函数。 使用数学函数 这个技巧会演示4个不同的数学函数,包括POWER、SQRT、ROUND以及RAND。 第一个示例计算10的2次方: 这个查询返回: 下面的示例计算了100的平方根: 这个查询返回: 这个示例四舍五入一个数字到小数点后的第三位: 这个示例返回一个0到1之间的随机浮点值: 这个查询返回: 本技巧的最后一个示例根据提供的整数值返回一个固定的、浮点数据类型的值: 这个查询返回: 解析 在这个技巧中,我演示了4个不同的数学函数,包括POWER、SQRT、ROUND以及RAND。每一个函数根据它的操作接受不同的参数。对于一些诸如RAND的数学函数,输入值是可选的。 8.3 字符串函数 下面的一组技巧会演示SQL Server 2005的字符串函数。字符串函数对T-SQL编程非常有用,能让我们进行字符串清除、ASCII和普通字符之间的转换、模式搜索、移除尾部空格等等。 表8-3列出了SQL Server 2005中提供的各种字符串函数。 表8-3  字符串函数 函 数 名 描    述 ASCII和CHAR ASCII函数接受字符表达式最左边的字符并返回ASCII码。CHAR函数把ASCII码的整数值转化为字符值 CHARINDEX和PATINDEX CHARINDEX函数用于返回一个字符串在另外一个字符串中的起始位置。PATINDEX函数和CHARINDEX相似,只是PATINDEX允许在指定要搜索的字符串的时候使用通配符 DIFFERENCE和SOUNDEX DIFFERENCE和SOUNDEX都用于计算发音相似的字符串。SOUNDEX为字符串分配一个4位数字码,DIFFERENCE计算两个独立字符串的SOUNDEX输出的相似级别 LEFT和RIGHT LEFT函数返回字符串从左起指定个字符数的一部分字符串。RIGHT函数和LEFT函数相似,只是返回字符串从右起指定个字符数的一部分字符串 LEN和DATALENGTH LEN函数返回字符串表达式的字符个数,不包括最后一个字符后面的任何空格(尾部空格)。而DATALENGTH返回表达式使用的字节数 LOWER和UPPER LOWER函数返回字符表达式的小写形式,UPPER函数返回字符表达式的大写形式 LTRIM和RTRIM LTRIM函数移除前导空格,RTRIM函数移除尾部空格 NCHAR和UNICODE UNICODE函数返回字符表达式或输入表达式的第一个字符的Unicode整数值。NCHAR函数接受一个表示Unicode字符的整数值并把它转化为等价的字符 QUOTENAME QUOTENAME函数为Unicode输入字符串增加分隔符使之成为一个有效的分隔标识符 REPLACE REPLACE函数替换某个字符串中的一个指定字符串的所有示例,并把它替换为新的字符串 REPLICATE REPLICATE函数将某个字符表达式重复指定次 REVERSE REVERSE函数接受一个字符表达式并且以逆序的字符位置输出表达式 SPACE SPACE函数根据为输入参数指定的整数值返回重复空格的字符串 STR STR函数把数字数据转化为字符数据 STUFF STUFF函数删除指定长度的字符并在指定的起始位置插入指定字符串 SUBSTRING SUBSTRING函数返回某个表达式中定义的一块 接下来的几个技巧会演示如何使用这些字符串函数。 8.3.1 把字符值转化为ASCII以及把ASCII转回字符 ASCII函数接受字符表达式最左边的字符并返回ASCII码,而CHAR函数将代表ASCII的整数值转化为字符值。再次强调,ASCII只使用字符串的第一个字符。如果字符串是空或NULL,ASCII会返回NULL值(但空格值返回32)。 第一个示例演示如何把字符转化为整数的ASCII值: 这个查询返回: 接着,使用CHAR函数来把整数值再转化回字符: 这个查询返回: 解析 在这个技巧中,我们把“Hello”这个单词拆解成一个一个字符然后使用ASCII函数把它转化成数字的ASCII值。在第二个T-SQL语句中,又使用CHAR函数把ASCII值再转回字符。 8.3.2 返回整数和字符Unicode值 UNICODE函数返回字符或输入表达式的第一个字符的Unicode整数值。NCHAR函数接受一个表示Unicode字符的整数值然后把它转化为等价的字符。如果你需要使用Unicode标准与外部处理程序交换数据,那么这些函数就很有用。 第一个示例把单个字符转化为表示Unicode标准字符代码的整数值: 这个查询返回: 接着,把Unicode整数值转回字符 : 这个查询返回: 解析 在这个技巧中,我们把单词“Good!”拆分成一个一个字符然后使用UNICODE函数把它转化成整数值。在第二个T-SQL语句中,使用NCHAR函数把整数值转回字符。 8.3.3 获取字符串中另一个字符串的起始位置 CHARINDEX函数用于返回一个字符串中另外一个字符串的起始位置。语法如下: expression1参数是要寻找的字符串。expression2参数是要在其中搜索的字符串。可选的start_ location值指示你希望开始查找的字符位置。 这个示例演示了如何查找一个字符串中另外一个字符串的起始位置: 这个查询返回: 8.3.4 使用通配符找到一个字符串中另一个字符串的起始位置 PATINDEX函数和CHARINDEX类似,只是PATINDEX允许在要搜索的字符串中使用通配符。PATINDEX语法如下: PATINDEX返回搜索模式第一次出现的起始位置,但是和CHARINDEX不同,它没有起始位置选项。 在本例中,返回Person.Address表中AddressLine1包含单词片断“Olive”的行: 这个查询返回下面部分结果: 解析 本例返回所有AddressLine1列包含单词“Olive”的行。在单词左右都有通配符(之间没有空格),这里的“Olive”单词还能嵌入在其他单词中。这个模式还能使用不同的通配符。有关通配符的完整信息请参阅1.3.5节。 8.3.5 检测字符串相似度 DIFFERENCE和SOUNDEX两个函数都是和字符串配合使用,用于根据英语发音规则来评价字符串的发音相似度。SOUNDEX为字符串分配4个数字的代码,然后DIFFERENCE计算两个字符串的SOUNDEX输出之间的相似级别。 DIFFERENCE返回0到4的值,4表示最相近的匹配。 本例演示了如何识别发音相似的字符串——首先独立运算字符串,然后成对进行比较: 这个查询返回: 然后使用DIFFERENCE比较字符串对: 然后,比较另外一对: 这个查询返回: 解析 在第一个示例中,使用SOUNDEX来评价4个发音相似的单词。查询返回了一个4位代码,“Fleas”和“Fleece”等于F420,而“Peace”和“Peas”等于P200。 在第二个示例中,用DIFFERENCE来评价“Fleas”和“Fleece”以及“Fleece”和“Peace”。结果得出“Fleas”和“Fleece”的值为4,“Fleece”和“Peace”的值为2,前者比后者更相似。 8.3.6 获取字符串最左和最右部分 LEFT函数返回字符串左起指定字符个数的一部分。RIGHT函数和LEFT函数相似,只是它返回字符串右起指定字符个数的一部分。 这个技巧演示如何返回字符串最左和最右部分的子集。而且,还演示了使用这些函数进行的常见的字符串填充技巧。 在第一个示例中,从字符串获取最左边的10个字符: 这个查询返回: 接着,获取字符串最右边的10个字符: 这个查询返回: 下面的示例演示使用0填充ListPrice列的值: 为了业务目的需要填充数字   这个查询返回: 解析 这个技巧演示了使用LEFT和RIGHT的3个示例。前两个示例演示了返回字符串值的最左边和最右边的一些字符。 第三个示例演示了填充字符串以符合某个期望的业务格式。如果要把数据展现给终端用户或者把数据导出到外部系统,有的时候你可能需要保留或者增加前导值,比如前导0来得到固定长度的数字。要为ListPrice填充0首先需要为转换为varchar(10)的ListPrice值在前面连接10个0。然后,在这个连接的外面,使用RIGHT来提取连接后字符串中最后10个字符(因此,如果ListPrice不到10个数字,在左边就填充了前导的0): 8.3.7 检测字符串中的字符数或者字节数 LEN函数返回字符串表达式中的字符个数,不包含最后一个字符后的任何空格(尾部空格)。而DATALENGTH返回表达式使用的字节数。在本技巧中,我会演示如何测量字符串中的字符数和字节数。 第一个示例返回字符串中的字符数: 这个查询返回: 下面的示例返回字符串中的字节数。 这个查询返回: 解析 这个技巧使用了Unicode字符串,它通过给字符串加前缀N来定义,如下所示: 根据LEN,字符串中的字符数为38,然而由于它是Unicode字符串,DATALENGTH返回76个字节。Unicode数据的每个字符占2个字节,而非Unicode只占1个字节。 8.3.8 把字符串的一部分替换成另一个字符串 REPLACE函数将某个字符串中的指定字符串的所有实例替换为一个新的字符串。REPLACE的一个好处在于它不像PATINDEX和CHARINDEX只返回找到模式的某个位置,REPLACE能找到某个字符串中的一个模式的多个实例。 REPLACE的语法如下: 第一个字符串表达式参数是需要修改的字符串。第二个字符串表达式是需要从第一个字符串参数中移除的字符串。第三个字符串表达式是要插入到第一个参数中的字符串。 这个示例演示了如何将指定字符串的所有示例替换为一个新串: 这个查询返回: 解析 在这个技巧中,第一个字符串表达式是需要搜索的字符串“Zenon is our major profit center. Zenon leads the way”。第二个表达式是要替换的表达式(“Zenon”)。第三个表达式是替代“Zenon”的值“Xerxes”。 8.3.9 把字符串填充到字符串中 STUFF函数删除某个长度的字符并在指定的起始位置插入指定的字符串。语法如下: 这个函数的第一个参数是要修改的字符表达式。第二个参数是要插入字符串的起始位置。length是要在字符表达式中删除的字符数。第四个参数是你希望插入的实际字符表达式。 本例替换了字符串的一部分并在字符串内插入一个新的表达式: 这个查询返回: 解析 本技巧中的字符表达式是“My cat’s name is X. Have you met him?”。起始值是18,也就是说替换会发生在字符串中的第18个位置(在这里是X)。长度值是1,也就是从位置18开始的1个字符会被删除。最后一个字符表达式是Edgar,它就是要填充到字符串中的值。 8.3.10 把字符值转化为小写、大写以及合适的大小写 LOWER函数返回字符表达式的小写形式,UPPER函数返回字符表达式的大写形式。没有内建的合适大小写的函数,所以在本技巧中会演示一个用户定义函数。 在看这些不同的函数之前,我写了一个查询来显示Production.Document表中某行的Document- Summary值: 这个查询返回下面的句子大小写值: 第一个示例演示了把值设置为小写: 这个查询返回: 现在是大写形式: 这个查询返回: 要把字符串设置成合适形式(字符串中每个单词的首字母大写),可以使用用户定义函数。在第11章中会演示CREATE FUNCTION的语法使用细节: 一旦创建了用户定义函数,就能在函数的参数中指定要修改为合适大小写的字符串: 这个查询返回: 解析 第一个示例演示了LOWER函数,它返回字符表达式的小写形式。第二个示例演示了UPPER函数,它返回了字符表达式的大写形式。 没有内建的合适大小写函数,所以在这个技巧中创建了用户定义函数。 CREATE FUNCTION定义的第一行定义了期望的名字和参数——在这里是varchar(max)数据类型的参数: RETURNS关键字定义了应用逻辑后由函数返回的数据类型: 接着,使用LOWER函数把传入函数的变量修改成小写形式: 新的整数局部变量@C设置成了字母‘a’的ASCII值: 使用一个WHILE循环来遍历字母表的每一个字母,搜索每一个前面有空格的字母,然后把它替换为字符的大写形式: 转换成大写形式是通过让小写字符的ASCII整数值减去32来实现的。例如,小写‘a’的ASCII是97,大写的A是65。 最终由函数返回合适大小写的字符串值@UnCased: 8.3.11 移除前导和尾部空格 LTRIM函数移除前导空格而RTRIM函数移除尾部空格。 第一个示例演示移除字符串的前导空格: 这个查询返回: 第二个示例演示移除字符串的尾部空格: 这个查询返回: 解析 LTRIM和RTRIM都接受一个参数——要移除前导或尾部空格的字符表达式。我们注意到,没有可以用于移除前导和尾部空格的TRIM函数(在其他编程语言中可以看到)。我们必须通过在一个表达式中使用LTRIM和RTRIM来实现。 8.3.12 重复一个表达式N次 REPLICATE函数将某个字符表达式重复指定的次数。 语法是: 第一个参数是要重复的字符表达式。第二个参数是字符表达式要重复的次数的整数值。 这个示例演示了如何使用REPLICATE函数来重复字符表达式几次: 这个查询返回: 解析 在这个技巧的示例中,字符表达式中的字母Z重复了30次。使用REPLICATE就可以不用手动编码这些字符来重复这些值。最大返回值是8 000字节。 8.3.13 重复一个空格N次 SPACE函数根据为输入参数指定的整数,返回重复空格的字符串。 本例演示了如何将空格重复指定次数: 这个查询返回: 解析 在这个技巧中,我们在两个字符串的中间连接了6个空格。最大返回值是8 000个字节。 8.3.14 逆序输出表达式 REVERSE函数接受一个字符表达式并以逆序的字符位置输出表达式。 本例演示了如何反转一个字符串表达式: 这个查询返回: 解析 这个技巧演示了使用REVERSE函数以逆序输出字符串的字符。 8.3.15 返回表达式的一块 SUBSTRING函数返回某个表达式定义的一块。 语法如下: 函数的第一个参数是你希望使用其来返回其中一块的字符表达式。第二个参数定义了块的起始位置,第三个参数是你希望提取的块的长度。 在本例中,假设你的应用程序接收到了一个客户的银行账号。按照公司的规定只能以隐藏形式保存银行账号,只保留中间的4位: 这个查询返回: 解析 在这个技巧中,我们使用SUBSTRING函数来获取长串银行账号的中间4位。SUBSTRING函数中的表达式就是银行账号,后面跟了起始位置和要提取的字符数。 8.4 处理NULL 由于NULL值是未知的,它可能会弄乱相关代码。SQL Server 2005提供了函数来处理代码中的NULL值,如表8-4描述的那样。 表8-4  NULL函数 函 数 名 描  述 ISNULL ISNULL检测表达式是否为NULL,如果是的话替换NULL值为另外一个值 COALESCE COALESCE函数返回指定表达式列表的第一个非NULL值 NULLIF 当指定的两个表达式有相同值的时候NULLIF返回NULL值,否则返回第一个表达式的值 下面的几个技巧会演示这些函数。 8.4.1 替换NULL为替代值 ISNULL验证表达式是否为NULL,如果是的话,替换NULL值为一个替代值。 在本例中,任何NULL值都会被替换为另外的值: 这个查询返回下面的(部分)结果: 解析 在这个示例中,LastName列为Samant的Title列的值是NULL值。ISNULL函数在查询结果中把NULL值替换为UNKNOWN字符串表达式。 8.4.2 使用ISNULL进行灵活的搜索 在这个技巧中,我会演示如何在变量可能有或没有填充的情况下在查询中进行灵活、动态的搜索。这个技巧声明了3个局部搜索变量ProductID、StartDate和StandardCost。通过使用这个技术,你的查询可以根据所有、一些或者0个参数来返回结果。在这个示例中,只指定了ProductID: 只使用了@ProductID   用于搜索的局部变量   这个查询返回: 在第二个示例中,通过最小和最大的StandardCost范围来进行搜索: 用于搜索的局部变量   这个查询返回下面的(部分)结果: 解析 本技巧中演示的方法的优势在于代码很灵活,允许我们以无数种方式搜索数据,并且能让每个搜索条件为可选的。这个技巧的关键在于WHERE子句。每一个搜索条件都使用ISNULL和局部变量名,后面跟列名本身: 如果参数没有设置,它就会是NULL,因此每列的搜索条件会对列本身的值进行运算——总是返回TRUE。只有指定的参数才会用来过滤结果。 8.4.3 返回表达式列表中第一个非NULL值 COALESCE函数返回提供的表达式列表中第一个非NULL值。语法是: 这个技巧演示了如何使用COALESCE来返回第一个出现的非NULL值: 这个查询返回: 解析 在这个技巧中,我们创建了3个局部变量:@Value1、@Value2和@Value3。只有@Value2和@Value3被设置为整数值。没有设置值的变量@Value1是NULL。在COALESCE中,从@Value1到@Value3一次检查3个值。由于@Value2变量是第一个非NULL值的变量,所以返回了“22”。 8.4.4 当两个表达式相等的时候返回NULL值,否则返回第一个表达式 当提供的两个表达式有相同值的时候NULLIF返回NULL值,否则返回第一个表达式。 本例演示了如何使用NULLIF来运算两个表达式。如果两个表达式相等,NULL值会被返回,否则会返回第一个运算的表达式: 这个查询返回: 下面的示例测试两个表达式相等时返回的值: 这个查询返回: 解析 在这个技巧中,开始的批处理有两个不同的值:55和955。由于@Value1先运算并且两个值不同,因此NULLIF条件是FALSE,并返回了先运算的值。在第二个批处理中,@Value1和@Value2相等,所以NULLIF返回了NULL值。 8.5 日期函数 在本书的开始我们已经提到过,SQL Server 2005有两种数据类型来存储日期和时间数据:datetime和smalldatetime。datetime数据类型保存介于1753年1月1日和9999年12月31日之间的数据,测量时间最高达到3.33毫秒。smalldatetime数据类型保存更小的日期范围,从1900年1月1日到2079年6月6日,时间精度为1分钟。SQL Server 2005提供了一些使用这些数据类型操作的函数,如表8-5。 表8-5  日期函数 函 数 名 描    述 DATEADD DATEADD根据指定的间隔和数字返回一个增加或减少后的新日期 DATEDIFF DATEDIFF从第二个日期减去第一个日期并生成一个指定的datepart码格式的值 DATENAME DATENAME返回datepart码指定的一部分日期的字符串值 DATEPART 这个函数返回datepart码指定的一部分日期的整数值 DAY、MONTH和YEAR DAY返回表示日的整数值,MONTH返回表示月的整数而YEAR返回表示计算日期的年的整数 GETDATE、GETUTCDATE和CURRENT_TIMESTAMP GETDATE和CURRENT_TIMESTAMP都返回当前的日期和时间。GETUTCDATE返回格林尼治平时(协调世界时) 下面的一些技巧会演示这些日期函数。 8.5.1 返回当前日期和时间 GETDATE和CURRENT_TIMESTAMP都返回当前的日期和时间。GETUTCDATE返回格林尼治平时(协调世界时)。 这个示例演示如何返回当前的日期和时间以及协调世界时(格林尼治平时): 协调世界时或格林尼治平时   当前日期和时间   当前日期和时间   这个查询返回: 解析 这个技巧演示了获取当前日期和时间的3种方法。这3个函数都能在表的列定义中用作日期数据类型的DEFAULT值。 8.5.2 增加或减少日期值 DATEADD返回已经增加或减少的另外一个日期表达式的结果。语法是: datepart部分用于指定要修改的时间或日期的单位,如表8-6描述的那样: 表8-6  depart代码 代  码 描  述 yy或者yyyy 年 qq或q 季度 mm或m 月 dy或y 年中第几天 dd或d 天 wk或ww 周 dw或w 星期几 hh 小时 mi或n 分钟 ss或s 秒 ms 毫秒 DATEADD函数的第二个参数是要增加或减少日期的数字值(正数或负数)。第三个参数是要修改的日期。 第一个示例根据年减少日期: 这个查询返回: 下面的示例根据季度增加日期: 这个查询返回: 这个示例为日期减少6个月: 这个查询返回: 这个示例为日期增加50天: 这个查询返回: 这个示例为日期和时间减少30分钟: 这个查询返回: 解析 这个技巧演示了使用DATEADD函数根据不同的粒度来修改日期。对于前面的每个示例来说DATEADD的第三个参数都是文本日期值。然而,你也可以引用datetime数据类型表列或有效的日期表达式。 你会在下面的技巧中看到,第一个参数detepart也能在不同的日期函数中使用。 8.5.3 找出两个日期的差 DATEDIFF让第二个日期减去第一个日期,产生一个指定的datepart代码格式的值。DATEDIFF的语法是: 第一个datepart代码使用和DATEADD相同的datepart代码。第二个和第三个参数是参与减法的日期值。 本例演示了如何使用DATEDIFF函数来找出两个日期的差: 找出当前时间和EndDate之间的差   这个查询返回下面的(部分)结果: 解析 在这个技巧中,我们计算ProductCostHistory表的EndDate和当前日期的差,以月返回这个差。 8.5.4 显示日期一部分的字符串值 DATENAME返回的字符串是datepart代码指定的日期中的一部分。语法是: 第二个参数指定了字符串值基于的日期。 在这个技巧中,我会演示如何使用DATENAME返回datetime值的指定部分的字符串值: 显示EndDate这天为星期几   这个查询返回下面的(部分)结果: 解析 在这个技巧中,datepart参数设置成dw(星期几),结果根据EndDate列日期返回了星期几的值。 8.5.5 使用DATEPART显示日期一部分的整数值 这个函数根据datepart指定的部分日期返回整数值。DATEPART语法是: 第二个参数date指定了要计算整数值的日期。 这个示例演示了如何根据选择的日期部分返回整数值。第一个示例返回年值: 这个查询返回: 下一个示例显示当前月份的整数值: 这个查询返回: 解析 在这个技巧中,我们使用DATEPART函数从当前日期和时间中提取年、月和日整数值。你也可以使用不需要datepart参数的封装好的函数来显示这些值,下面的技巧会说。 8.5.6 使用YEAR、MONTH和DAY显示日期部分的整数值 你可以使用单个参数函数来显示日、月和年的整数值: 这个示例返回当前年: 这个查询返回: 这个示例返回当前月: 这个查询返回: 这个示例返回当前日: 这个查询返回: 解析 在这个技巧中,我演示了单个参数的日期函数。DAY返回日的整数值,MONTH返回表示月的整数,YEAR返回表示计算日期的年的整数。 8.6 使用Convert和Cast转换数据类型 CONVERT和CAST函数都是用来将多个数据类型从一种类型转换为另一种类型。CAST的语法是: 第一个参数是要转换的表达式(例如一个表列或文本值)。第二个参数是要转换成的数据类型。 CONVERT的语法是: 第一个参数是要转换成的数据类型。第二个参数是需要转换的表达式。第三个参数style允许我们配置某个日期呈现的格式。 8.6.1 转换数据类型 在这个技巧中,我会演示如何将整数数据类型转换为char(4)数据类型。在第一个示例中,把整数与字符串拼接: 这个查询返回下面的错误: 消息245,级别16,状态1,第1行 在将varchar值'Cannot be concatenated'转换成数据类型int时失败。 在下面的示例中,使用CONVERT来将整数值变为字符数据类型: 这个查询返回: 这个示例演示了使用CAST进行相同类型的转换: 这个查询返回下面的(部分)结果: 解析 第一个查询试图把整数和字符串值拼接在一起。这将会出现一个错误,因为两个数据类型必须是兼容的或者是一致的。第二次尝试使用CONVERT在把表达式和其他字符串拼接前修改表达式的数据类型为char(4)。CAST用来转换smallint列的数据类型,这样就能把它与字符串进行拼接。 8.6.2 进行数据转换 我前面提到过,CONVERT有一个可选的样式参数,它允许你转换datetime或smalldatetime为指定的字符格式。许多人很疑惑日期和时间是如何存储以和查询结果中实际的日期表示相符的。使用样式参数的时候,请记住你所做的只会影响日期字符形式的表现方式而不会影响它的存储方式(当然,除非你使用非datetime数据类型列存储要表现的日期)。 表8-7中列出了CONVERT函数可用样式格式的一些示例。 表8-7  CONVERT样式格式 样式代码 格    式 101 mm/dd/yyyy 102 yy.mm.dd 103 dd/mm/yy (续) 样式代码 格    式 108 hh:mm:ss 110 mm-dd-yy 112 yymmdd 例如 返回的当天日期格式为: 当诸如GETDATE()的函数执行并保存在datetime列的时候,日期和时间数据都会保存。然而,如果你仅仅希望保存日期级别的数据(没有具体时间),常用的技巧是使用CONVERT来消除样式为00:00:00.000的时间。 下面的示例转换日期值为字符值,然后重新把它转换为datetime数据类型: 这个查询返回: 解析 样式选项的101值告诉CONVERT来以mm/dd/yyyy格式返回日期。在把数据给呈现终端用户的时候,查询编写者通常会关心样式选项。当要把datetime或smalldatetime转换为字符数据类型的时候就要使用这种呈现方式。记住,如果你将日期类型转换回datetime并保存这种转换后的日期,根据你为字符数据选择的样式,可能会丢失原始小时、分钟、秒等的精度。 8.6.3 计算表达式是日期还是数字 当转换数据类型的时候,有时候知道SQL Server认为一个表达式是什么数据类型是很有用的。在这个技巧中,我会演示使用ISDATE和ISNUMERIC函数来检测表达式的数据类型: 返回1   返回0   返回1   返回0   解析 ISDATE检测表达式是否为有效的datetime值。ISNUMERIC检测表达式是否为有效的数字数据类型值。如果表达式计算为TRUE,ISNUMERIC和ISDATE都返回1,如果为FALSE,则都返回0。 8.7 排名函数 排名函数是SQL Server 2005增加的一个非常受欢迎的特性,它允许我们在结果集中返回和每行关联的值。表8-8描述了4个新的排名函数。 表8-8  排名函数 函    数 描    述 ROW_NUMBER 在结果集中为每行返回递增整数 RANK 和ROW_NUMBER相似,RANK为结果集中的每行递增值。主要的区别是如果行有重复值,它们会有相同的排名值 DENSE_RANK DENSE_RANK和RANK几乎一样,只是DENSE_RANK不在排名值中返回间隔 NTILE NTILE根据排序和可选的分区把结果集划分成指定数量的分组 下面4个技巧会演示这4个排名函数的使用。 8.7.1 使用递增行号 SQL Server 2005新的ROW_NUMBER在结果集中为每行返回递增的整数。ROW_NUMBER的语法如下: 第一个可选参数partition_by_clause允许你为分区列中的每个改变重置行号。第二个参数order_by_clause指定ROW_NUMBER应用到结果集的次序。 第一个示例返回结果集中间的6行,根据名字排序: 选择结果集中间从255到260的行   这个查询返回: 可选的partition_by_clause允许为分区列的每次修改重置行号。在这个示例中,结果根据Shelf来分区并且根据ProductID排序: 在返回的结果中,行号根据ProductID递增,但是每一次Shelf变化后,行号都会从1开始: 解析 在第一个示例中,我们使用ROW_NUMBER来根据产品名排列结果并且为每行增加递增值。在子查询的第三列中引用了ROW_NUMBER: 括号中的ORDER BY子句根据产品名对结果排序,它影响行返回的次序以及和每行关联的行号。结果集中的每一行都会有一个号码,每行都会加1。由于查询根据Name排列结果,第一个产品Adjustable Race的行号会是“1”。把这个查询作为子查询,这样ROW_NUMBER列就能在外部查询的WHERE子句中被引用,返回从255到260的行。 第二个查询演示了使用partition_by_clause参数。对于Shelf的每一次改变,行号都会从“1”开始。 有了SQL Server 2005的ROW_NUMBER函数,我们现在就可以不用像SQL Server 2000那样创建其他代码实现数据分页了(例如,显示25到50行)。 8.7.2 根据排名返回行 在这个技巧中,我会演示SQL Server 2005新的RANK函数,它和ROW_NUMBER相似,也为集合中的每一行增加值。RANK的语法如下: 主要的区别是如果行有相同的值存在,那么他们会获取相同的排名值,如本例演示的那样: 这个查询返回: 和ROW_NUMBER一样,OVER子句包含可选的partition_by_clause和必需的order_by_clause。order_ by_clause决定RANK值应用到每行的顺序,可选的partition_by_clause用于进一步划分排序分组,如下例: 这个查询根据销售人员的TerritoryID进行分区并返回SalesQuota的排名: 解析 RANK根据排序的列增加它的值,和ROWNUMBER的增加每行值不同,RANK会为排序值一致的行返回相同的值。 例如,在这个技巧中,查询指定了根据SalesQuota降序排序的RANK。由于两个SalesQuota值都等于280 000.00,它们都得到了排名7: 你也注意到了,下面的SalesQuota值排名是9(而不是8)。RANK函数没有使用第8个位置是因为两行是并列第七,那么下一个排名值是9。如果3行并列,那么下一个排名值是10,依此类推: 在第二个示例中,RANK根据TerritoryID分区,对于TerritoryID的每一次修改RANK值都会从“1”开始。 8.7.3 根据无间隔排名返回行 在这个技巧中,我会演示SQL Server 2005新的DENSE_RANK,它几乎和RANK一样,只是DENSE_RANK不返回有间隔的排名值: 这个查询返回: 解析 它的语法和使用与RANK相同,只是DENSE_RANK不在排名值中创建间隔。在这个技巧的示例中,由于两个SalesQuota是280 000.00,所以两个排名都是第7: 在7之后的DENSE_RANK值为8。 8.7.4 使用NTILE NTILE根据排序和可选的分区把结果集分成指定数量的分组。语法和其他排名函数相似,只是它包含一个integer_expression: integer_expression用于指定要将结果划分成的分组数。本例演示对Sales.SalePersonQuotaHis- tory表操作的NTILE排名函数: 这个查询返回: 解析 在这个示例中,结果集被划分成4个分组。结果根据SalesQuota排序(降序),并且指定了NTILE分组分配的次序。注意到,前面的两组1和2都有3行,而分组3和4都有2行。如果行数不能被分组数整除,那么前几个分组会比后面几个分组多一些行。否则,如果行数能被分组数整除,每一个分组会有相同数量的行 8.8 使用系统函数检测服务器、数据库以及连接级别的配置 SQL Server 2005包含了一些用于检测SQL Server实例系统设置的系统配置函数。有一些函数带有@@前缀,在之前版本的SQL Server中叫做变量。另外一些系统函数没有@@前缀,并且接受用于帮助收集有关SQL Server实例或者数据库的参数。 下面几个技巧会演示这些系统函数。 8.8.1 使用SQL Server的每周第一天设置 @@DATEFIRST函数返回SQL Server实例每周指定的第一天。因为这个值定义了诸如DATEPART和DATEADD等其他日期函数使用的每周datepart的计算,所以这是需要关注的。在本例中,我会演示返回SQL Server实例当前每周第一天: 这个查询返回: 解析 @@DATEFIRST函数显示了每周第一天的设置。要修改这个第一天的值你可以使用SET DATEFIRST命令。例如: 修改值的时候,“7”是星期日,“1”是星期一,依此类推。它会影响DATEPART和DATEADD函数dw(星期几)代码的返回值。 8.8.2 查看当前会话使用的语言 @@LANGID系统函数返回一个表示当前用户会话本地语言标识符的smallint数据类型的值,@@LANGUAGE系统函数返回语言名。 本例返回当前查询会话中使用的本地语言设置: 这个查询返回: 解析 这个技巧演示了返回SQL Server实例的语言。根据用于创建SQL Server实例的区域设置和排序规则各不相同。 8.8.3 查看和设置当前连接锁超时设置 SET LOCK_TIMEOUT命令配置当前会话中语句等待其他连接释放锁的毫秒数。@@LOCK_TIMEOUT函数用于以毫秒为单位显示当前连接锁超时设置。 本例演示设置和查看当前会话的锁超时值: 1000毫秒,1秒   没有限制   这个查询返回: 解析 本技巧中的示例一开始把锁超时时间设置为1 000毫秒。要使用@@LOCK_TIMEOUT查看修改。之后,又把锁超时时间改为-1来表示无限等待。锁超时时间值告诉我们语句会等待锁定资源多长时间,如果超过了阈值时间,语句会自动取消然后返回一个错误消息。 8.8.4 显示当前存储过程上下文的嵌套级别 @@NESTLEVEL返回存储过程上下文当前的嵌套级别。存储过程嵌套级别代表存储过程调用另外一个存储过程的次数。SQL Server 2005允许存储过程组成最大32次嵌套(非完整)调用。 这个技巧演示如何捕获存储过程上下文的当前嵌套级别(参阅第10章): 第二个过程   第一个过程   在创建两个存储过程之后,在调用usp_Call_QuickAndDirty存储过程之前使用@@NESTLEVEL函数: 返回1和2嵌套级别   返回0嵌套级别   这个查询返回3个结果集: 解析 在这个技巧中,我创建了两个存储过程,第一个存储过程usp_QuickAndDirty执行了@@NESTLEVEL。第二个存储过程也调用了@@NESTLEVEL然后执行第一个存储过程。在调用过程之前,@@NESTLEVEL返回了0。每次执行嵌套后,@@NESTLEVEL值都增加了。 8.8.5 返回当前SQL Server实例名和SQL Server版本 @@SERVERNAME显示本地服务器名并且@@VERSION返回SQL Server实例的版本、日期和处理器信息。 本例返回当前SQL Server实例名和版本信息: 解析 在这个技巧中,我演示了返回当前SQL Server实例名和版本信息。和之前的系统配置函数一样,不需要任何参数。 8.8.6 返回当前连接的会话ID(SPID) @@SPID返回当前连接的会话ID,你可以使用它来识别sp_who系统存储过程中的额外信息。 这个技巧返回当前SQL连接的服务器处理标识符: 这个查询返回: 解析 在这个技巧中,我演示了返回当前连接查询会话的SPID。注意之前版本中的SPID代表“服务器进程ID”而不是“会话ID”。 8.8.7 返回打开事务的数量 @@TRANCOUNT系统函数显示当前连接的活动事务。你可以使用这个函数来检测当前会话中打开事务的数量,并且根据这个信息来相应地COMMIT或ROLLBACK事务。这个技巧表明了如何返回当前连接中活动事务的数量: 返回2   返回1   返回3   ROLLBACK之后总是返回0   返回2   这个查询返回: (1行受影响)   (1行受影响)   (1行受影响)   (1行受影响)   (1行受影响)   解析 在这个技巧中,每次发出BEGIN TRAN,@@TRANCOUNT的值都会递增。每次COMMIT TRAN发生的时候,@@TRANCOUNT都会递减。ROLLBACK TRAN执行后,@@TRANCOUNT被设置为0。不管事务嵌套了几层,ROLLBACK TRAN都回滚会话所有打开的事务。 8.8.8 获取之前语句影响的行 @@ROWCOUNT返回当前域中上一个T-SQL影响的行数的整数值。@@ROWCOUNT_BIG返回bitint值。 在这个示例中,我会演示如何返回之前T-SQL语句影响的行: 这个查询返回两个结果集: 解析 在这个示例中,第一个语句从Production.ScrapReason表返回了3行——因此@@ROWCOUNT返回了被影响的3行。ROWCOUNT_BIG函数和@@ROWCOUNT差不多,只是它可以返回bigint数据类型的总数,而不是@@ROWCOUNT那样的整数数据类型。 @@ROWCOUNT和@@ROWCOUNT_BIG通常用于错误处理,例如检查以确保期望数量的行被之前的语句所影响(见第16章)。 8.8.9 使用系统统计函数 SQL Server 2005有几个内建的系统统计函数,如表8-9的描述。 表8-9 系统统计函数 函    数 描    述 @@CONNECTIONS 返回自SQL Server实例上次启动后连接的数量 @@CPU_BUSY 显示自SQL Server实例上次启动后CPU繁忙的毫秒数 @@IDLE 显示自SQL Server实例上次启动后总的空闲时间的毫秒数 @@IO_BUSY 显示自SQL Server实例上次启动后花费在I/O操作上的毫秒数 @@PACKET_ERRORS 显示自SQL Server实例上次启动后总的网络包错误数 @@PACK_RECEIVED 显示自SQL Server实例上次启动后从网络上读取的总的输入包数。你可以监视这个数值在增加还是保持不变来猜测是否有网络问题 @@PACK_SENT 显示自SQL Server实例上次启动后发送到网络上的总的输出包数 @@TIMETICKS 显示每一个tick的毫秒数。tick是由特定的毫秒数指定的度量单位(对于 Windows 2000是31.25毫秒) @@TOTAL_ERRORS 显示自SQL Server实例上次启动后遇到的读/写错误 @@TOTAL_READ 显示自SQL Server实例上次启动后非缓存的磁盘读取次数 @@TOTAL_WRITE 显示自SQL Server实例上次启动后的磁盘写入数量 本例演示在一个查询中使用系统统计函数: 这个查询返回: 解析 这个技巧演示了一个SELECT查询引用多个系统统计函数。你可以使用它们来跟踪SQL Server实例的各种统计值。 8.8.10 显示数据库和SQL Server设置 DATABASEPROPERTYEX系统函数允许你获取有关数据库选项的信息。DATABASEPROPERTYEX使用下面的语法: 第一个参数是你希望探测的数据库名。第二个参数是你希望查找的数据库属性。 本例演示了如何获取AdventureWorks数据库的排序规则、状态和恢复模式: 这个查询返回: SERVERPROPERTY系统函数允许我们获取有关SQL Server实例的信息。在它的语法中由于不需要指定数据库,所以只有一个属性名参数: 这个实例演示返回实例的版本和默认排序规则: 这个查询返回: 解析 DATABASEPROPERTYEX和SERVERPROPERTY都能用于获取重要的系统配置信息。在两个实例中,我们都在查询的SELECT子句中引用了函数。 注解     我在本书中演示了如何使用这些函数,但是没有列举可用属性的列表。对于完整列表,可以参阅SQL Server 2005联机丛书中的SERVERPROPERTY和DATABASEPROPERTYEX主题。 8.8.11 返回当前数据库ID和名称 DB_ID函数返回数据库整数ID并且DB_NAME返回当前数据库的名字(除非指定了参数)。 本例演示如何获取当前数据库系统ID和名称: 这个查询返回: 解析 在这个示例中,内部数据库ID(数据库创建的时候由SQL Server分配)和数据库名一起返回。函数会根据当前数据库上下文返回信息。 它们也都可以接受参数,例如: 使用它,无需切换数据库上下文到实际数据库就可以查找显式的数据库ID和名称值。 8.8.12 返回数据库对象名和ID OBJECT_ID返回数据库内部指定的数据库对象标识符。OBJECT_NAME根据对象标识符返回对象名。 在这个示例中,我会演示如何返回数据库对象的名称和ID: 这个查询返回: 解析 OBJECT_NAME和OBJECT_ID都通常和引用数据库对象标识符的系统目录视图或系统函数一起使用。OBJECT_ID函数用于找出某个对象的内部数据库标识符。它的单个参数就是对象名。OBJECT_NAME用于返回某个对象标识符的对象名。注意到两个函数都是针对数据库的——因为不同数据库的对象ID可以一样。对象ID只是针对某个数据库唯一。 8.8.13 返回当前用户会话的应用程序和主机 在这个技巧中,我会演示用于返回有关当前连接上下文信息的不同函数。APP_NAME返回当前SQL Server连接的应用程序名。HOST_ID返回当前连接的工作站标识号,HOST_NAME返回当前连接的工作站名。 本例演示了如何显示当前连接到SQL Server实例的应用程序名和主机: 这个查询返回: 解析 本例的这3个函数都结合SELECT子句使用,并且不需要任何参数。如果需要跟踪客户端和应用程序连接的信息,这个信息就很有用,它能帮助你建立标识。 8.8.14 获取当前用户和登录名上下文 SYSTEM_USER函数返回Windows或SQL登录名而USER函数返回当前用户的数据库用户名。 在第一个示例中,我会演示如何返回当前用户以及登录名上下文: 数据库用户   登录名   这个查询返回: 这2个函数还能用作表的DEFAULT值,如下面示例的演示: 这个查询返回下面的结果: 解析 在这个技巧中,我们在普通查询中使用SYSTEM_USER和USER函数,还把它们作为表的DEFAULT值。这些函数对数据库修改审核很有用——例如发生数据修改的时候能捕获当前用户。 8.8.15 查看用户连接选项 在这个技巧中,我会演示如何使用SESSIONPROPERTY函数查看当前用户连接的SET属性(更多有关SET选项的信息请参阅第22章): 解析 SESSIONPROPERTY允许我们查看当前用户的各种数据库连接设置。它接受一个参数,就是需要查看的属性名。在本例中,我们检查了每一个可用的SESSIONPROPERTY选项。如果选项是ON,这个函数返回1,如果是OFF则返回0 8.9 IDENTITY和uniqueidentifier函数 在本章最后的3个技巧中,我会介绍如何为表使用IDENTITY值以及如何生成新的uniqueidentifier值。 如果你回顾第4章的话就知道,IDENTITY列属性定义在表的某个列上,并且允许我们为表中的一个列定义自增的数字值。 和确保表中唯一的IDENTITY列不同,ROWGUIDCOL属性确保更高级别的唯一。这种唯一的ID保存在uniqueidentifier数据类型中,并且由NEWID系统函数生成。 8.9.1 返回最后一个标识值 在这个技巧中,我会演示3种方法来返回最后生成的标识值。在第一个示例中,我们使用IDENT_CURRENT函数来返回某个表最后生成的标识值。本命令接受一个参数:要运算的表名。 这个查询返回: 接着,向定义过IDENTITY列的表中新插入一行。在INSERT后立即使用SCOPE_IDENTITY和@@IDENTITY函数(两个函数的区别在示例后描述)获取最后生成的标识值: 在当前会话中,当前域中   任何表生成的最后标识值   示例插入,在表中生成IDENTITY值   该例返回当前会话当前域中表INSERT生成的最后标识值。域代表如果INSERT引起触发器触发并且插入另外一行到不同的基于IDENTITY的表中,我们只会看到当前会话中(不是域之外的触发器会话)最后的IDENTITY值: 执行@@IDENTITY生成当前会话任何域中的任何表生成的IDENTITY值: 在当前会话中,任何域中   任何表生成的最后标识值   这个查询返回: 尽管对于本示例的查询值一样,但是如果INSERT触发了触发器并引起另外一个基于IDENTITY的表的INSERT操作,那么我们会看到触发器域中另外一个表最后的标识值。 解析 本技巧演示了3种返回最后生成的标识值的方法。第一个查询使用了IDENT_CURRENT,它指定某个表最后生成的标识值。 下一个演示的函数SCOPE_IDENTITY针对当前用户的会话,并且返回当前域中最后生成的值。例如,当前域指的是当前SQL语句批处理、当前存储过程或者当前触发器。 而@@IDENTITY返回当前会话中任何表最后生成的值,它是跨所有域的。所以,如果当前域中的INSERT触发了一个触发器,它又插入一个记录到其他表,@@IDENTITY会返回受到触发器影响的插入行最新的值,不是你期望捕获的原来那个插入的值。 简而言之,如果你希望获取跨任何会话和域的某个表的最新的IDENTITY值,可以使用IDENT_ CURRENT。如果你希望获取当前域和当前会话中的任何表的最新IDENTITY值,可以使用SCOPE_IDENTITY。如果你希望得到当前会话任何域中任何表的最后IDENTITY值可以使用@@IDENTITY。 8.9.2 返回标识列的种子和增量值 IDENT_INCT函数显示某个表或者引用视图的IDENTITY列的原始增量值。IDENT_SEED返回显示某个表或引用视图的IDENTITY列上原始定义的种子值。这些函数对于检测插入行时IDENTITY列的增量和种子值很有用。 本例演示了返回某个表的标识增量和种子: 这个查询返回: 解析 在这个技巧中,我们使用IDENT_INCR和IDENT_SEED返回Production.Product表的增量和种子值。 8.9.3 创建新的uniqueidentifier值 NEWID函数用于创建uniqueidentifier数据类型的值。第一个示例在SELECT语句中返回一个新的uniqueidentifier值: 这个查询返回: 然后创建一个使用NEWID函数作为默认值的新临时表: 然后,插入一个新值到表中: 最后,从表中获取这个值: 这个查询返回: 解析 本技巧演示了NEWID可以用在SELECT语句中,也可以在CREATE或ALTER TABLE语句中用作DEFAULT列值。 第9章 条件处理、流控制和游标 在本章中,我会介绍一些技巧来演示用于下列操作的SQL Server 2005 T-SQL: l 条件处理(conditional processing)。你会学到如何使用CASE和IF...ELSE语句来计算条件并返回相应的值。我会介绍如何使用CASE函数来计算单个输入表达式并返回一个值,以及如何计算一个或多个布尔表达式。最后,我会演示当表达式为TRUE的时候返回一个值。 l 流控制功能(control-of-flow functionality)。这个技巧演示如何根据诸如RETURN、WHILE、WAITFOR和GOTO之类的命令控制T-SQL语句或批处理的执行。RETURN用来立即退出当前的T-SQL批处理,禁止之后的任何代码执行。WHILE命令用于在条件为TRUE的时候重复某个操作或者一批操作。WAITFOR命令用于为T-SQL代码的执行延迟一段时间或到某个时间点。GOTO用于跳转到T-SQL批处理中的某个标签,忽略GOTO之后的代码。 l 创建和使用游标(creating and using cursors)。在这里我会演示T-SQL游标,它允许你一次处理一行。如果内存消耗和代码量问题没有正确处理,游标就会引起巨大的性能问题。然而,还有一些少量的场合使用游标会比基于组的方案更好。 理解如何以及何时(何时不)使用这些技术能让你创建灵活且智能的T-SQL代码。 9.1  条件处理 条件处理允许我们根据一个或者一组表达式的值来返回结果。下面的几个技巧会演示SQL Server 2005的条件处理命令,包括CASE和IF...ELSE(其实IF...ELSE也具有内在的流控制功能)。 CASE函数用于根据一个表达式的值返回结果。它常用于将代码值转换为描述性的值或者计算多个条件来返回一个值(例如,“如果行是2005年的并且小于或等于当前的季度,则返回销售总数”)。 IF...ELSE结构计算一个布尔表达式,如果是TRUE则执行一个T-SQL语句或批处理。这个命令有很多用途,允许我们根据条件返回结果集、更新数据或根据一个或多个搜索条件执行存储过程。 下面3个技巧会实战演示条件处理。 9.1.1  使用CASE来计算单个输入表达式 CASE函数用于根据一个表达式的值返回结果。它同样能用于根据一个或多个布尔表达式的结果返回值。前一种CASE的语法如下: 表9-1描述了此命令的参数。 表9-1  CASE的参数 参    数 描    述 input_expression 要在CASE语句中计算的输入值 when_expression 要和input_expression比较的表达式。例如,如果input_expression是性别列,when_expression就可能是'F'或'M'或NULL。如果input_expression和when_expression之间存在匹配,就返回result_expression result_expression input_expression和when_expression相等时要返回的值 这个示例演示如何使用CASE来计算一个或多个条件,根据计算为TRUE的那些条件返回结果: 根据部门决定会议室   这个查询返回下面的(部分)结果: 解析 在这个技巧的示例中,CASE用来根据GroupName值分配一个会议室。CASE语句跟在SELECT子句中的Name列后面: 要计算的列GroupName跟在CASE关键字后面。接着,计算一组WHEN表达式。每一个部门根据GroupName值分配了不同的房间: 没有一个WHEN表达式计算为TRUE,则可选的ELSE子句用来指定一个默认的结果表达式: END关键字用于标记CASE语句的结束,在这个技巧中,它后面跟随列别名: 9.1.2  使用CASE来计算布尔表达式 CASE提供了另外一种语法,它不使用初始的输入表达式而是使用一个或多个布尔表达式,当表达式计算为TRUE的时候返回一个结果表达式。语法如下: 和前面的技巧相比,语法中另外的参数是要计算的表达式boolean_expression。每一个WHEN都计算一个布尔表达式而不是输入表达式,如果TRUE则返回结果表达式。这种形式的CASE允许额外的表达式,而不是仅仅计算一个输入表达式的值。 如果没有表达式计算为TRUE,则返回ELSE子句的result_expression,或者如果没有指定ELSE子句的话就返回NULL值。如果一行与多个布尔表达式都匹配,那么第一个计算为TRUE的布尔表达式决定了结果表达式。在这个示例中,除了计算部门名外,还要计算其他表达式,例如部门标识号和以字母T开头的房间名: 这个查询返回如下的(部分)结果: 解析 在这个示例中,我们使用了3个布尔表达式。如果部门名是Research and Development,则返回Room A: 第二个布尔表达式规定,如果部门名是Sales and Marketing或者DepartmentID等于10,则返回Room B: 第三个布尔表达式查找所有以字母T开头的部门名,如果存在匹配则返回Room C: 9.1.3  使用IF...ELSE IF...ELSE计算一个布尔表达式,如果是TRUE则执行一个T-SQL语句或批处理。 语法如下: 如果布尔表达式计算为FALSE则调用ELSE子句,执行ELSE后的一个T-SQL语句或批处理。 这个示例演示了根据局部变量的值有条件地执行查询: 这个查询返回: 解析 在这个技巧中,我们创建了一个叫@QuerySelector的整型局部变量,它被设置为值3: IF语句从计算@QuerySelector是否等于1开始: 如果计算得出@QuerySelector确实是1,则执行下面那段代码(从BEGIN语句开始): 如果IF后是单条语句,则BEGIN是可选的。但如果是必须成组执行的多个语句,则必须使用BEGIN和END。作为最佳实践,对于单个语句也最好使用BEGIN和END,这样如果以后代码有修改你就不会忘记。 如果前面的IF条件计算后为FALSE,则可选的ELSE子句搜索黑色的产品: 9.2  流控制 在接下来的几个技巧中,我会演示如何使用下列SQL Server 2005流控制函数和命令。 l RETURN。这个函数用于无条件退出既有域并将控制权返回给调用域。RETURN也能用于把整型值返回给调用者。这个技术通常用于把业务逻辑错误返回给调用的过程,或者确认批处理/查询/域中执行的任何东西都没有发生错误。 l WHILE。当布尔条件计算为TRUE时,你可以使用它来重复执行同样的批处理或T-SQL代码。WHILE通常用于游标(本章同样会介绍)的替代方式,因为你可以使用它遍历结果集,一次为一行产生行为,直到结果集变空为止。例如,你可以把一些碎片程度大于50%④的索引名填充到一个临时表。调用WHILE语句能保证只要表中有行就进行循环。对于每一次迭代,你可以得到第一个索引行并在从表中捕获的第一个索引名上进行索引重建。之后,你就可以从表中删除那行,并继续遍历索引,直到表变空才结束WHILE循环。 l GOTO。这个函数能用于在T-SQL批处理中跳转到一个标签。它通常用于在发生错误的时候跳转到某个错误处理器,或者跳过某个满足或不满足条件的代码。GOTO早就赢得了“通心粉”代码的坏名声。这是因为你为了完全理解这段代码或过程实际在做什么不得不在代码块间跳来跳去。尽管应该最小程度地使用GOTO,但仍还是被支持的,因此我们也会在技巧中介绍。 l WAITFOR。你可以使用这个函数来延迟它后面的后续T-SQL命令的处理——可以是一个固定的时间段或是到某个时间。在活动是同步的情况下这非常有用。例如,如果代码在外部任务结束前不能完成,而它还需要几秒/几分钟/几小时,或者如果你希望在某个时间进行某个活动(例如,非营业时间)。 9.2.1  使用RETURN RETURN用来立即退出当前的T-SQL批处理、查询或存储过程,并且不执行在它之后的批处理/查询/过程域中的任何代码。RETURN仅退出当前域中执行的代码,如果你在存储过程A中调用了存储过程B,并且存储过程B发起一个RETURN,那么存储过程B会立即终止,但是存储过程A还会继续,就好像B已经成功完成。 本例演示了如何使用RETURN来无条件终止一个查询: 不会再执行   这个查询返回: 命令已成功完成。   RETURN同样支持可选的整数表达式: 这个整数值能用于存储过程和调用应用程序的通信,例如: 创建一个产生错误的存储过程   接着,执行存储过程,从局部变量捕获RETURN代码: 这个查询返回: 消息8134,级别16,状态1,过程 #usp_TempProc________________________________________________________________________ ___________________________________00000180,第4行 遇到以零作除数错误。 8134 解析 在这个技巧中,IF条件检测是否存在粉红色的产品: 如果计算得到TRUE(没有粉红色的产品存在),则执行RETURN语句: 不会再执行   因为没有粉红色的产品,所以调用了RETURN,且跟在IF语句后的SELECT查询永远不会被执行。 第二个示例演示了创建一个包含了创建以0作除数的错误的T-SQL的临时存储过程。RETURN用来捕获@@ERRORCODE值8134,它返回给调用者并通过@ErrorCode局部变量打印出来。如果整数值没有显式放入RETURN的调用,那么会发送0作为默认值。 9.2.2  使用WHILE 在这个技巧中,我会演示WHILE命令,只要条件是TRUE,它能让我们重复某个操作或一批操作。 WHILE的语法如下: 只要布尔表达式还是TRUE,WHILE就会保证T-SQL语句或者批处理一直处理。BREAK关键字允许我们从最内部的WHILE循环中退出,CONTINUE关键字导致循环重启动。 在这个示例中,系统存储过程sp_spaceused用来返回@AWTables表变量中每个表的空间占用情况: 使用sp_spaceused汇报每个表的空间使用情况   把表名插入表变量   声明变量   这个查询返回多个结果集(一个结果集对应一个表)。在这里显示了3个结果集: 在前面的WHILE语法中我们看到,还可以在代码中使用关键字BREAK和CONTINUE。BREAK用来退出WHILE循环,而CONTINUE用来继续WHILE循环。例如: 这个查询返回: 解析 在这个技巧中,WHILE用来遍历AdventureWorks数据库中的每一个表,使用sp_spaceused系统存储过程来汇报信息。 这个技巧从声明两个变量开始: 表变量@AWTables用来保存所有的表名,@TableName变量用于保存单个表的名字。 表变量被填充AdventureWorks数据库中的所有表名(填充schema.table_name值): 然后开始了WHILE循环,只要@AWTables表变量中还有行就进行循环: 在WHILE中,使用@AWTables表变量中的第一个表名对@TableName局部变量进行赋值: 使用@TableName变量来执行EXEC sp_spaceused: 最后,已经汇报的表的那行从表变量中删除: WHILE会继续执行sp_spaceused,直到@AWTables表变量中所有行都被删除。 在技巧的第二个示例中,如果满足了某个条件(或者达到了阈值)我们使用BREAK来退出循环。使用BREAK可以防止无限循环。 9.2.3  使用GOTO 这个技巧演示了GOTO,它用来跳转到T-SQL批处理中的一个标签,而忽略它之后的代码。语法如下: 在这个示例中我们检查部门名是否已被既有的部门使用,如果是则使用GOTO跳过INSERT。如果不是则执行INSERT: 这个查询返回: 解析 在这个技巧中,声明了两个局部变量并对其设置了准备插入到HumanResources.Department表中的值。 接着,使用IF语句来检查是否存在和局部变量有相同部门名的行。如果存在这样的行就调用GOTO命令。GOTO引用了你希望跳转到的标签名,在这里是SkipInsert: IF语句后是INSERT,然而在我们的示例中它被跳过了,因为HumanResources.Department表中已经存在了‘Engineering’部门: 然后定义了要跳转到的标签,以一个冒号作为后缀。这个标签定义了一条要返回的一打印消息: 作为最佳实践,如果可以选择使用GOTO或其他流控制方法的话,你应该选择其他的而不是GOTO。GOTO会引起可读性问题和复杂的代码,因为你不得不在批处理或存储过程中转来转去以理解查询作者原来的意图。 9.2.4  使用WAITFOR 在这个技巧中,我演示了WAITFOR命令,它将T-SQL的执行延迟指定的一段时间: WAITFOR DELAY的time_to_pass参数是要在命令执行前等待的秒数、分钟数和小时数。WAITFOR TIME的time_to_execute参数用来指定执行批处理的实际时间(小时、分钟和秒)。receive_statement和TIMEOUT选项用来和Service Broker一起使用(参见第20章)。 在第一个示例中,在SELECT查询执行之前通过WAITFOR创建一个10秒的延迟: 在第二个示例中,查询直到指定时间才会执行,在这里是7:01PM: 解析 在这个技巧中,我们使用两个版本的WAITFOR来延迟T-SQL批处理的处理。 第一个查询在执行批处理前等待了10秒: 如果你知道另外一个操作必须同步执行而当前的批处理必须等待的话,那么等待一段时间是很有用的。例如,如果你使用sp_start_job系统存储过程发出一个同步SQL Server代理工作的话,控制权在工作开始执行后马上返回给批。如果你知道刚刚发出的工作至少需要运行5分钟,而之后的任务依赖这个工作的完成,那么就能使用WAITFOR来延迟处理,直到工作完成。 第二个查询一直等到指定时间的下个示例: 当某个操作必须在一天的某个时间段发生的话,则WAIRFOR TIME很有用。例如,假设你有一个存储过程为事务处理表进行数据仓库聚合。聚合可能需要几个小时来完成,但你不希望在营业时间之后才从生产表中加载完成的数据。在过程中使用WAITFOR TIME就可以停止表的最终加载,直到非营业时间。 9.3  游标 SQL Server的性能优势在于它基于组的处理能力。然而,有编程背景的新手查询编写者通常更习惯于使用游标而不是基于组的方案来获取或更新行。但是,游标有一些缺点。很多时候它们被认为会耗尽SQL Server实例的内存、锁定资源以及创建过多的代码。当开发者忽略基于组的低消耗方案时就会发生这样的问题。T-SQL是基于组的语言,也就是说它擅长操作和获取一组行,而不是一行一行地处理。 然而,你的应用程序或业务需求可能需要T-SQL游标提供的一行一行的处理方式。通常你应该在其他诸如WHILE循环、子查询、临时表或表变量等行级别的处理方法都不管用的时候才应该考虑使用游标。 T-SQL游标的大致生存周期如下: l 游标通过返回有效结果集的SQL语句来定义。 l 然后游标被填充(打开)。 l 一旦打开,行就能从游标中获取,一次一个或一组。行还能向前或向后移动,这取决于原始游标的定义。 l 根据游标类型,数据能随着行的滚动进行修改或读取以及使用其他操作。 l 最后,游标使用后应该显式关闭并从内存中释放。 DECLARE CURSOR命令用来创建游标,而且有很多会影响游标的灵活性和锁定行为的选项。语法如下: 有一些选项会影响游标数据是否能更新以及你是否可以向后向前移动游标中填充的行。表9-2简要描述了可用的一些选项: 表9-2  游标选项 参    数 描    述 LOCAL或GLOBAL 如果选择了LOCAL,游标只在SQL批处理、触发器或存储过程的范围中可用。如果选择了GLOBAL,游标对连接自身可用(例如,一个执行创建游标的存储过程的连接能使用通过执行存储过程创建的游标) FORWARD_ONLY或 SCROLL FORWARD_ONLY选项只允许我们从游标第一行开始向前移动。而SCROLL允许我们使用所有的FETCH选项(FIRST、LAST、NEXT、PRIOR、ABSOLUTE和RELATIVE)在结果集中前后移动。如果考虑性能,最好使用FORWARD_ONLY——因为这种游标类型的负载比SCROLL低 STATIC或KEYSET 或DYNAMIC或 FAST_FORWARD 如果指定了STATIC,则在tempdb数据库中会保存游标数据的快照,任何对原始数据源的修改不会影响游标数据。KEYSET允许我们看到游标外部对行的修改,但是看不到符合游标SELECT查询的插入。DYNAMIC允许我们在游标打开时看到基础数据源的更新、插入和删除。FAST_FORWARD定义两种行为:设置游标为只读和只向前状态(通常这是性能最好的游标选项,但是也最不灵活)。如果面临性能问题,并且你的功能不复杂,则使用这个选项 READ_ONLY或 SCROLL_LOCKS或 OPTIMISTIC READ_ONLY选项意味着不能通过游标进行更新。如果考虑性能和并发,则使用这个选项。SCROLL_LOCKS在行上加锁,这样就能保证在游标结束后进行更新和删除。OPTIMISTIC选项在已更新或已删除的行上不加锁,只有当上次数据读取之后游标外部没有进行数据更新时才会使修改有效 TYPE_WARNINGS 如果指定了TYPE_WARNINGS,如果游标显式地从一种类型转换成了另一种类型,那么会有警告被发送到客户端 select_statement参数是用来在游标中定义数据的查询。避免使用查询来返回用不到的列和行,因为游标打开的时候会放在内存中。UPDATE [OF column_name [,...n]]用来指定那些允许游标更新的列。 一旦游标使用DECLARE CURSOR来声明,下一步就是使用OPEN命令打开它并填充数据。语法如下: 游标能在局部(默认)或全局打开。一旦打开了,你就可以使用FETCH命令在游标中切换行。FETCH NEXT的语法如下: FETCH提供了几个选项用来在游标中切换行,通过把每个游标定义的列的结果填充到局部变量来实现(在下个技巧会演示)。 @@FETCH_STATUS函数用在FETCH操作后来检测FETCH状态,返回0表示成功,-1表示不成功, -2表示丢失。 一旦你用完了打开的游标,执行CLOSE命令从内存中释放结果集。语法如下: 现在,你仍然可以重新打开游标。然而,如果你已经用完了,应该通过使用DEALLOCATE命令移除内部系统对游标的引用。它释放游标使用的所有资源。例如,如果游标的卷锁被表引用,这些锁在DEALLOCATE后释放。语法如下: 下个技巧会演示每个命令。 创建和使用T-SQL游标 尽管本书推荐少使用游标,但是像下个示例演示的那样使用游标进行周期性的数据库管理信息收集是很恰当的。 这个技巧演示了使用游标来遍历当前SQL Server实例中活动的每个会话ID(SPID,正式应该称为“服务器进程ID”),并且执行DBCC INPUTBUFFER来查看每一个连接当前执行的SQL语句(如果当前在执行一些东西的话): 一次从游标获取一行   打开游标   声明游标   不在结果中显示行数   只要游标中还有行就继续获取   释放游标   关闭游标   提取下一行   看每一个spID在做什么   这个查询返回SQL Server实例的每个进程当前的T-SQL活动。 解析 这个技巧从设置SET NOCOUNT ON开始,它禁止SQL Server显示行数消息来提供更清楚的输出: 不在结果中显示行数   接着,定义局部变量来保存要从游标中获取的服务器进程ID的独立值: 游标通过使用DECLARE CURSOR来定义。游标包含sys.sysprocesses系统视图的SPID列: 声明游标   在游标定义后,我们把它打开(填充): 一旦打开,使用FETCH NEXT把第一行的值获取到@SPID局部变量中: FETCH NEXT用于获取第一行。在第一次获取之后,我们定义了WHILE条件来告诉SQL Server继续循环语句,一直到游标的获取状态不再成功为止(也就是说没有行能获取了)。 @@FETCH_STATUS用来返回最后一次游标FETCH语句最后发出的打开游标的状态,返回0表示最后一次FETCH成功,-1表示未成功,-2表示丢失。 在WHILE语句中,打印了@SPID变量,并使用EXEC来创建一个动态查询: 看每一个spID在做什么   动态查询为每一个独立SPID执行DBCC INPUTBUFFER,返回SPID当前缓冲中的所有SQL语句。 在这之后,运行另一个FETCH NEXT来填充下一个@SPID值: 提取下一行   在所有SPID都获取后,WHILE循环就退出了(因为@@FETCH_STATUS会返回-1)。然后使用CLOSE命令来关闭游标: 关闭游标   至此,游标仍然可以使用OPEN命令来打开,要彻底从内存中移除游标,需要使用DEALLOCATE: 释放游标   尽管有用,游标还是应该小心处理,因为它们会消耗大量资源,并且执行效果不如等价的基于组的方案。在考虑在T-SQL开发中使用游标之前,确保探索一下所有的基于组的方案。 第27章 链接服务器和分布式查询 链接服务器为SQL Server提供了从远程数据源访问数据的能力。使用链接服务器,你可以执行查询、实施数据修改以及执行远程过程调用。远程数据源可以是同类(意思是数据源是另一个SQL Server实例)或异类的(来自其他关系型数据库产品和数据源,比如DB2、Access、Oracle、Excel和文本文件)。跨越多个平台联结或检索数据的查询是跨平台查询。使用跨平台查询,可以访问遗留数据库系统,而不需要对既有的数据源进行合并和迁移。 通过OLE DB访问接口连接远程的数据源。OLE DB由微软开发,是用来提供到各种不同的数据源的一致性访问的一组COM(component object model,组件对象模型)接口。为了建立从SQL Server 2005实例到另一数据源的访问,需要选择适当的OLE DB访问接口。OLE DB访问接口的设计方式决定在分布式查询(SELECT、INSERT、UPDATE或DELETE存储过程执行)中实现何种分布式查询操作。 因此概括的说,链接服务器意思是建立到远程数据源的连接的途径。依赖用来设置链接服务器的OLE DB驱动,可以执行分布式查询来检索数据,或在远程数据源中执行操作。 分布式查询也可以不通过定义链接服务器来运行,例如通过使用T-SQL函数OPENROWSET。除了不通过链接服务器查询远程数据源,在SQL Server 2005增强了OPENROWSET,允许BULK读取ASCII、Unicode以及二进制文件。使用OPENROWSET和BULK,可以从文本文件中读取表状数据,或使用它将ASCII、Unicode或二进制类型文件导入到单个的大数据类型列及单个行中(比如varchar(max)、nvarchar(max)或varbinary(max))。 也是SQL Server 2005中的新特性,微软引入了SYNONYM对象,它允许你通过较短的名称引用长名称的对象。通常在使用长标识符时这是有用的,但在引用了4部分组成的链接服务器名称的分布式查询中,使用较短的名称代替数据源特别有用。 本章包括这些技巧:创建链接服务器、执行分布式查询、使用OPENROWSET和BULK从文本文件中读取以及使用新的SYNONYM对象。 27.1  链接服务器基础 下面一组技巧将展示如何使用链接服务器。特别地,我将展示如何完成下列操作: l 创建链接服务器连接到另一个SQL Server 2005实例。 l 配置链接服务器的属性。 l 查看SQL Server实例上的配置链接服务器的信息。 l 删除链接服务器。 我将先讨论如何使用系统存储过程sp_addlinkedserver来创建新链接服务器。 27.1.1  为另一SQL Server实例创建链接服务器 链接服务器允许从SQL Server实例中查询外部数据源。外部数据源可以是不同的SQL Server实例,也可以是非SQL Server数据源,比如说Oracle、MS Access、DB2或MS Excel。 使用系统存储过程sp_addlinkedserver来创建链接服务器。语法如下: 表27-1描述了这个系统存储过程的参数。 表27-1  sp_addlinkedserver参数 参    数 描    述 server 链接服务器的本地名称。也允许使用实例名称,例如MYSERVER\SQL1 product_name OLE DB数据源的产品名。对于SQL Server实例来说,product_name是'SQL Server' provider_name 这是OLE DB访问接口的唯一可编程标识。当没有指定它时,访问接口名称是SQL Server数据源。SQL Server显式的provider_name是SQLNCLI(Microsoft SQL Native Client OLE DB Provider)。Oracler的是MSDAORA,Oracle 8或更高版本的是OraOLEDB.Oracle。MS Access和MS Excel的是Microsoft.Jet.OLEDB.4.0。IBM DB2的是DB2OLEDB,以及ODBC数据源的是MSDASQL data_source 这是特定OLE DB访问接口解释的数据源。对于SQL Server,这是SQL Server(servername或servername\instancename)的网络名称。对于Oracle,这是SQL*Net别名。对于MS Access和MSExcel,这是文件的完整路径和名称。对于ODBC数据源,这是系统DSN名称 location 由特定OLE DB访问接口解释的位置 provider_string OLE DB 访问接口特定的连接字符串。对于ODBC连接,这是ODBC连接字符串。对于MS Excel,这是Excel 5.0 catalog catalog的定义变化基于OLE DB访问接口的实现。对于SQL Server,这是可选的数据库名称,对于DB2,这个目录是数据库的名称 在多SQL Server实例的网络环境中,链接服务器提供了方便的方式共享SQL Server数据,而不是必须要通过物理地推送或拉回数据和复制架构的方式来实现共享。 提示     在本章中,我讲解了在SQL Server实例之间通信的示例。对于异类的数据源,比如DB2、Access以及Oracle,参数会有很多变化。如果需要更全面的sp_addlinkedserver选项的讲解,请看SQL Server联机丛书中的主题“sp_addlinkedserver (Transact-SQL)”。 连接到异类数据源使用的配置会基于OLE DB访问接口而发生变化。如果你只是连接到不同的SQL Server实例,微软把它变得很简单。在这个示例中,我展示了创建连接到另一SQL Server实例的链接服务器: 也可以创建连接到SQL Server命名实例的链接服务器,例如: 解析 添加连接到外部数据源的链接服务器允许执行分布式查询(分布式查询将在本章稍后介绍)。当添加SQL Server链接服务器到一个SQL Server实例时,不管它是默认的还是命名的实例,微软都把它变得很简单,只需要server和product_name值。 连接到SQL Server实例的安全性方法是怎样的呢?当创建新的链接服务器时,使用当前用户的登录名安全凭证(SQL或Windows)来链接到链接服务器。也可以创建显示的链接服务器远程登录名映射,稍后会讨论该内容。 27.1.2  配置链接服务器属性 在创建链接服务器之后有许多不同的设置可以用来配置它。表27-2描述了这些设置。 表27-2  链接服务器属性 参    数 描    述 collation compatible 如果你确认SQL Server实例与远程SQL Server拥有相同的排序规则就启用这个设置。由于SQL Server不再需要对数据源之间的字符列执行比较操作,把它们假定为相同的排序规则,这样做可以提升性能 collation name 如果启用了use remote collation并且是非SQL Server的数据源,则collation name指定远程服务器排序规则的名称。这个排序规则名称必须是SQL Server所支持的 connect timeout 指定在超时发生之前到链接服务器的连接会尝试多少秒。如果数值为“0”,sp_ configure的remote query timeout的服务器值用来当作默认值 data access 如果启用,就允许分布式查询访问 lazy schema validation 如果设为true,则架构不会在查询开始时去检测远程表。尽管这样会减少远程查询的负载,但是如果架构发生了变化并且你没有进行架构检测,比如说查询中引用的对象不能与查询命令进行通信,就会生成错误 query timeout 指定查询等待的超时值(秒数)。如果这个值为0,则query wait选项使用sp_configure值 rpc 启用从服务器进行远程过程调用 rpc out 启用远程过程调用到服务器 use remote collation 指定是使用远程服务器排序规则(true)还是本地服务器排序规则(false) 使用系统存储过程sp_serveroption来修改链接服务器属性。语法如下: 表27-3描述了这个系统存储过程的参数。 表27-3  sp_serveroption参数 参    数 描    述 server 配置属性的链接服务器的名称 option_name 要配置的选项 option_value 选项的新值 在这个技巧中,链接服务器JOEPROD\NODE2的查询超时设置将修改为60秒: 解析 在这个技巧中,链接服务器JOEPROD\NODE2的查询超时限制被修改为60秒。称为server的第一个参数指定了链接服务器的名称。第二个参数option_name指定了要配置的选项,第三个参数option_value配置了新值。 27.1.3  查看链接服务器信息 可以使用系统目录视图sys.servers查看SQL Server实例中定义的链接服务器。例如: 它返回: 解析 系统目录视图sys.servers可以用来检索关于SQL Server实例中定义的链接服务器的信息。可以从sys.servers查看到的其他选项包括:product、provider、data_source、location、provider_string、catalog、is_linked、is_remote_login_enabled、is_rpc_out_enabled、is_data_access_enabled、is_collation_compatible、use_remote_collation和collation_name。is_linked列在查询中限定只返回链接服务器(不包括本地SQL Server实例设置)。 27.1.4  删除链接服务器 系统存储过程sp_dropserver用来删除链接服务器。sp_dropserver的语法如下: 表27-4描述了这个系统存储过程的参数。 表27-4  sp_dropserver的参数 参    数 描    述 server 从SQL Server实例中删除的链接服务器的名称 droplogins 如果指定droplogins,则在删除链接服务器之前要删除登录名映射(在本章稍后描述) 这个技巧展示了删除链接服务器: 解析 这个技巧展示了使用系统存储过程sp_dropserver从你的SQL Server实例中删除链接服务器。在第二个参数中指定的droplogins选项将在删除链接服务器之前删除既有的登录名映射(我将在下面几个技巧中介绍链接服务器登录)。如果在删除登录之前尝试删除链接服务器,你将得到如下消息: 仍有对服务器'JOEPROD'的远程登录或链接登录。 27.2  链接服务器登录名 在下面的3个技巧中,我将展示如何使用链接服务器登录名映射。具体而言,我将讲解如何完成下列操作: l 创建链接服务器登录名映射。 l 查看配置在SQL Server实例中的链接服务器登录名映射。 l 删除链接服务器登录名映射。 我将首先讨论如何使用系统存储过程sp_addlinkedsrvlogin来创建登录名映射。 27.2.1  添加链接服务器登录名映射 当在链接服务器上执行分布式查询时,SQL Server会将你的本地登录和凭据映射到链接服务器。基于远程数据源的安全性,凭据可能会被接受或是拒绝。当执行sp_addlinkedserver并且创建了链接服务器后,默认的行为是使用本地登录凭据(SQL或Windows)去访问链接服务器上的数据。即便没有足够的权限连接到链接服务器,在尝试执行分布式查询之前,SQL Server不会去检测链接服务器的安全性。由于SQL Server实例中安全性配置、登录和数据库用户的多样性,你可能需要从本地登录到不同的远程登录设置不同的映射。 登录名映射信息存储在定义链接服务器的SQL Server实例中,使用系统存储过程sp_addlinkedsrvlogin来创建登录名映射。 语法如下: 表27-5描述了这个系统存储过程的参数。 表27-5  sp_addlinkedsrvlogin的参数 参    数 描    述 rmtsrvname 要添加登录名映射的本地链接服务器 useself 当使用true值时,使用本地SQL或Windows登录名连接到远程服务器名。如果设为false,存储过程sp_addlinkedsrvlogin的locallogin、rmtuser和rmtpassword参数将应用到新的映射中 locallogin 这是映射到远程登录名的SQL Server登录或Windows用户的名称。如果这个参数置为NULL,映射将应用SQL Server实例中的所有本地登录名 rmtuser 用来连接到链接服务器的用户/登录名的名称 rmtpassword 用来连接到链接服务器的用户/登录名的密码 在这个技巧中,在链接服务器JOEPROD\NODE2中为所有本地用户创建了登录名映射——映射到登录名"test": 解析 应用到所有本地登录名   在这个技巧中,使用系统存储过程sp_addlinkedsrvlogin显式地创建登录名映射。第一个参数@rmtsrvname包含了要连接到的链接服务器的名称。第二个参数@useself置为false值,因此将使用在远程服务器的@rmtuser和@rmtpassword参数中定义的登录名和密码。@locallogin置为NULL,意思是所有本地SQL Server连接的登录名都会映射到test登录名上。现在,当在TESTSRV链接服务器上执行查询时,这些查询将以test远程用户身份运行。 27.2.2  查看链接登录名 可以查询系统目录视图sys.server_principals、sys.linked_logins和sys.servers来查看到远程登录的显式的本地登录名映射。正如这个查询所展示的: 它返回: 解析 这个技巧通过查询系统目录视图sys.linked_logins、sys.servers及sys.server_principals检索到远程登录的显式的登录名映射。查询返回链接服务器的名称、远程数据源的远程登录名以及映射到它的本地登录名。在这个例子中,结果返回远程登录名test及本地登录名NULL(意思是所有本地连接将映射到远程test登录)。 27.2.3  删除链接服务器登录名映射 使用系统存储过程sp_droplinkedsrvlogin删除链接服务器登录名映射。sp_droplinkedsrvlogin的语法如下: 表27-6描述了这个系统存储过程的参数。 表27-6  sp_droplinkedsrvlogin参数 参    数 描    述 rmtsrvname 登录名映射的链接服务器名称 locallogin 这是从链接服务器删除的SQL Server登录或Windows用户映射的名称 这个技巧展示了删除在前面技巧中创建的登录名映射: 解析 在这个技巧中,通过传送链接服务器名称到第一个参数,传送NULL值到第二个@locallogin参数,删除了所有本地用户的默认的登录名映射。 27.3  执行分布式查询 本章进行到现在为止,我展示了如何创建和配置链接服务器。在下面一组技巧中,你将学习到如何在连接到远程数据源的链接服务器上执行分布式查询。你被限定使用链接服务器来连接到远程数据源,并且在下面的一些技巧也会展示如何使用命令(比如OPENQUERY和OPENROWSET)访问外部数据。你也会学习到如何为链接服务器名称创建和使用别名。 27.3.1  在链接服务器上执行分布式查询 分布式查询引用一个或多个链接服务器进行查询,在远程表、视图或存储过程上执行读取和修改操作。链接服务器支持的查询类型(SELECT、INSERT、UPDATE、DELETE、EXEC)基于OLE DB访问接口提供的事务支持级别。可以通过在FROM子句中使用4部分组成的远程对象名称引用链接服务器,或使用T-SQL命令OPENQUERY(OPENQUERY将在本章稍后介绍)来运行分布式查询。 使用4部分组成的名称引用链接服务器的基本语法如下: 表27-7描述了四部分组成的名称的各个部分。 表27-7  链接服务器4部分组成的名称 部    分 描    述 linked_server_name 链接服务器的名称 catalog 目录(数据库)名称 schema 数据源对象的架构容器 object_name 数据库对象(例如视图、表、数据源或存储过程) 分布式查询从链接服务器上的动态管理视图sys.dm_os_performance_counters中选取了性能计数器值: 它返回: 下面的查询展示了在链接服务器(SQL Server命名实例)上执行系统存储过程。链接服务器是一个命名实例,因此完整的名称就放入方括号中: 它返回各种关于远程SQL Server实例的统计信息和结果集。 解析 正如你看到的,执行分布式查询只是使用4部分组成的名称简单地引用数据库对象。如果需要引用为SQL Server命名实例的链接服务器,就把链接服务器名称放入方括号内。 27.3.2  创建和使用引用4部分组成的链接服务器名的别名 SQL Server 2005引入了新特性,可以为数据库对象创建别名(包括存储过程、函数、表和视图)。可以在代码中引用这个别名,允许你将一个长名称缩短或将底层的对象源的修改隐藏起来(例如,开发环境与生产环境的链接服务器名称的切换)。 通过使用CREATE SYNONYM来实现这个功能,它使用如下的语法: 表27-8描述了这个命令的参数。 表27-8  CREATE SYNONYM 参    数 描    述 [ schema_name. ] synonym_name 可选的架构名称及必需的同义词名称 object 要赋予别名的对象。可以为以下格式:server_name.database_name. schema_name.object_name、database_name.schema_name.object_name或schema_name.object_name 使用DROP SYNONYM命令删除同义词。语法如下: 这个命令需要一个可选的同义词架构和必需的同义词名称。在这个技巧中,为链接服务器创建同义词: 下一步,在查询的FROM子句中使用新同义词名称来引用链接服务器的同义词: 在此之后,从数据库中删除同义词: 最后,用与前面相同的名称创建新的同义词,而这时指向不同的SQL Server实例了: 解析 在这个技巧中,创建了一个名称为PerfInfo的同义词,用来表示4部分组成的链接服务器上的表名称。同义词通过允许使用更短的名称表示链接服务器4部分组成的名称,可以减少输入。然后为了查询底层的链接服务器中的表,在FROM子句中使用同义词PerfInfo。在这之后,删除了同义词(尽管在实际情况下你可能会保留这个同义词以备后用)。最后,用之前的名称创建了新的同义词,它引用了新的数据源。这意味着原来的对dbo.PerfInfo的查询现在访问不同的SQL Server实例。同义词给了你不改变引用的同义词名称,而去修改底层数据源的能力。 27.3.3  使用OPENQUERY执行分布式查询 SQL Server为执行分布式查询提供除使用4部分组成的名称的方式以外的其他方式。 OPENQUERY是在查询的FROM子句中像表一样被引用,且对既有的链接服务器传递查询的函数。语法如下: 表27-9描述了这个命令的参数。 表27-9  OPENQUERY参数 参    数 描    述 linked_server_name 要查询的链接服务器的名称 query 对链接服务器连接发出的实际的查询 OPENQUERY命令通过发送以传递查询形式代替引用4部分组成的名称的链接服务器来查询链接服务器。传递查询在远程服务器上完整地执行,并且将结果返回到调用的查询。 为什么使用这个方式而不是另一个?用于创建链接服务器的一些 OLE DB访问接口可能拥有多种在FROM子句中使用4部分组成的名称被引用的能力。OPENQUERY是检索分布式数据,且4部分组成的名称的查询可能不正常工作时的另一种方式。 这个示例展示了使用与前面技巧中相同的查询来查询链接服务器,只是这次在OPENQUERY命令的第二个参数中,实际的查询在FROM子句使用了3部分的名称,而不是4部分: 解析 在这个技巧中,OPENQUERY命令中的第一个参数是链接服务器的名称。第二个参数是查询本身。注意在WHERE子句中包含了两个引号内的值,它的作用就是单引号。 27.3.4  使用OPENROWSET执行即席的查询 就像OPENQUERY,在FROM子句中引用OPENROWSET命令,并且像SELECT语句中的表一样操作它。不像OPENQUERY的地方是,OPENROWSET会创建一个到数据源的即席的连接。它没有使用既有的链接服务器连接查询远程数据源。如果不希望为SQL Server实例的远程数据源保留链接服务器,这是一个不错的功能。 OPENROWSET的语法如下: 表27-10描述了这个命令的参数。 表27-10  OPENROWSET参数 参    数 描    述 provider_name OLE DB访问接口的唯一可编程标识符 datasource ; user_id ; password | provider_string 这是OLE DB访问接口需要的连接字符串。可以指定datasource、user_id和password,或者指定访问接口字符串 catalog.schema.object | query 返回结果的对象名称或要执行的查询 注解     在SQL Server 2005中,使用OPENROWSET查询远程数据源的能力默认是关闭的。为了使用OPENROWSET,必须在SQL Server 2005外围应用配置器中启用它。在“即席远程查询”段中,点选“启用OPENROWSET和OPENDATASOURCE支持”复选框。 在这个技巧中,对SQL Server 2005命名实例执行查询: 它返回: 解析 在这个技巧中,我使用了OPENROWSET去查询远程数据源,而不需要定义链接服务器。命令的第一个参数指定为SQLNCLI,这是Microsoft SQL Native Client OLE DB Provider的访问接口名称。第二个参数包括3个分号分隔的值——SQL Server 2005实例名称、登录名及密码。命令的最后一个参数包含对远程SQL Server实例中的数据库AdventureWorks的查询。 OPENROWSET可以用在SELECT的FROM子句中,也可以用作INSERT、UPDATE或DELETE操作的目标表——依赖于OLE DB访问接口是否支持更新。 27.3.5  使用OPENROWSET BULK选项从文件中读取数据 这是SQL Server 2005引入的新特性,现在可以在OPENROWSET命令中使用新BULK选项从ASCII、Unicode或二进制文件查询数据。有了这个功能,可以查询文件以及使用数据修改语句的结果集——所有这些都不需要先从文件将数据物理地导入到SQL Server的表中。 在OPENROWSET中使用BULK选项的语法如下: 表27-11描述这个命令的参数。 表27-11  OPENROWSET...BULK的参数 参    数 描    述 data_file 要读取的文件名称和路径 format_file_path 格式化文件的名称和路径——它列出数据文件中的列定义。在SQL Server 2005中,可以选择两种格式化文件格式——XML或非XML bulk_options 这些选项定义如何读取数据和检索哪些行。细节内容请看下一表格 SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB 当指定这个选项时,将忽略格式化文件参数。作为替代,以单行单列值导入数据文件。例如,如果希望将文档或图像文件导入大数据类型列,则要指定其中一个标志。导入到varbinary(max)类型要指定SINGLE_BLOB对象,ASCII数据导入到varchar(max)数据类型指定SINGLE_CLOB,Unicode数据导入到nvarchar(max)类型要指定SINGLE_NCLOB BULK选项语法如下: 表27-12描述了这些选项。 表27-12  BULK选项 参    数 描    述 'ACP' | 'OEM' | 'RAW' | 'code_page' 这里指定选择何种字符数据的源数据代码页转换为目标SQL Server代码页。OEM是默认值。ACP是ISO 1252代码页,RAW表示没有转换,code_page是指定的代码页编号 (续) 参    数 描    述 file_name 用来保存BULK进程拒绝的行的错误文件名称 first_row 结果集中载入的第一行(默认为1)。如果第一行包括列名称,你可以指定first_row为“2”去跳过第一行 last_row 在结果集中载入的最后一行(结果集的最后一行,默认为0) maximum_errors 在载入失败之前载入操作中最大的错误数量(默认为10) rows_per_batch 这个值表示每个批处理导入的行数,虽然OPENROWSET总是以单个的批处理导入数据。指定这个值可以帮助查询处理器分配适当的资源。不过在大多数情况下,这个选项可以忽略或是设为0 在这个技巧中,我将展示两个从外部文本文件读取的示例。 第一个示例展示了使用SELECT语句从文本文件读取数据。文本文件中数据用逗号分隔,文件名为ContactType.txt: 这个文件中的列将使用名称为ContactTypeFormat.Fmt的格式化文件来定义,这个格式化文件包含如下的格式化文件定义(SQL Server 2005允许XML格式和常规文本格式文件): 这个查询从SELECT查询的ContactType.txt文件中进行读取: 它返回: 创建表保存导入文档   技巧中的第二个示例将把ContactType.txt文件导入到单个列且单个行中(而不是像前面的查询那样把它分到表状数据集中)。首先,创建表来保存导入的文档: 下一步,使用OPENROWSET...BULK将数据导入到新行中: 现在确认一下内容: 它返回: 解析 在这个技巧的第一个示例中,使用OPENROWSET BULK选项查询数据文件。SELECT子句包含了数据文件的列,它是通过格式化文件定义的: 然后将OPENROWSET命令包含在FROM子句中。BULK选项是这个命令中的第一个参数,随后是数据和数据格式化文件: 另外还包括了3个选项,指定要导入的数据文件的第一行: 也指定了在导入失败之前允许发生错误的数量,以及保存拒绝行的错误文件: 在右括号之后,为了在SELECT中使用,还需要一个表名称的别名: 第二个示例使用OPENROWSET将单个文件的整个内容插入到单列单行中。在创建保存结果的表之后,使用了INSERT SELECT: SELECT引用了系统列名称BulkColumn,当使用SINGLE_*选项时从OPENROWSET返回它: OPENROWSET放置在SELECT语句的FROM子句中,随后是文件的名称和SINGLE_CLOB选项(以ASCII文本的格式导入数据): 使用T-SQL导入文件(ASCII、Unicode或二进制)比在SQL Server以前的版本中要容易得多。对表执行查询,并且原始文件格式的结果显示到单列1行中(以逗号分隔的完整形式)。 第28章 性 能 调 优 在谈论数据库查询的时候,性能是一个主观的术语。可能一个最终用户对执行10秒的查询表示满意,而另一个用户只有当所有查询都在1秒内完成时才会满意。正是因为性能需求是如此多样化,所以要有多种方法去提升性能。有一些关键因素会影响SQL Server查询性能,它包括: l 数据库设计。这或许是影响查询性能和数据完整性的最重要的因素之一,设计决策影响读取和修改的性能。标准设计包括规范化OLTP数据库,它关注于数据完整性、去除冗余以及建立多个实体之间的关系。这是一个适合快速事务处理的设计。你通常会在规范化OLTP设计中看到较多的表,这意味着在你的查询中要联结较多的表。另一方面,数据仓库设计经常使用非规范化的星形结构或雪花状结构设计。这些设计使用了中心事实表,它联结了两个或更多的描述维度表。对于雪花状设计,维度表也可以存在于关联到它的关系表上。这种设计的焦点在于查询执行的速度而不是事务的快速更新。 l 物理硬件。我曾经花费了一整天的时间试图使执行3秒的查询降到1秒。不论我怎样加索引,或去查询我做的修改,都没办法让它的持续时间减少。这只不过是因为在结果集中需要太多的行。限制因素是I/O。过了几个月,我将该数据库迁移到更高配置的生产服务器上。执行查询的时间就一直低于1秒了。这强调了CPU、内存以及RAID子系统会严重影响(包括正面的和负面影响)查询性能的事实。实际上,对于SQL Server内存是最关键的资源之一,内存通过缓存数据和执行计划使SQL Server更快地进行数据检索和执行查询。如果你要确定购买服务器花费的优先级,不要吝啬花费在内存上的钱。 l 网络吞吐量。获得查询结果所花的时间会受到缓慢的或者不稳定的网络连接的影响。这不意味着只要查询执行的慢,你就去责怪可怜的网络工程师——但是你需要把这个潜在的原因记录在你要研究的范围的列表中。 l 索引碎片。由于长时间的数据修改的发生,索引将会产生碎片。由于碎片增多,数据将会扩散到更多的数据页。查询需要检索的数据页越多,查询就越慢。 l 适当的索引。在检测碎片的同时,也需要确认你的表中只存在有用的索引。表索引应当基于高优先级或频繁执行的查询。如果一个查询一天执行数千次且2秒内完成,则可以通过适当的索引使运行少于1秒,增加这个索引可以显著地减少SQL Server实例上的I/O压力。应该根据需要创建索引,并且要删除不用的索引(本章会告诉你如何操作它)。表上的每一个索引都会给数据修改操作增加开销,而且如果SQL Server判定使用低效的索引甚至还会减慢SELECT查询的速度。当你最初设计数据库的时候,使索引处于最少量的状态对你来说是更好的(至少要有聚集索引和外键的非聚集索引)。直到在有了关于数据库查询的更好的主意时,才增加额外的索引。 l 最新的统计信息。正如我在第22章讲到的,AUTO_CREATE_STATISTICS数据库选项激活了SQL Server自动产生关于列中值的分布的统计信息。如果关闭这个选项,统计信息就会过期。由于SQL Server依赖统计信息去决定如何执行查询,如果SQL Server基于过期的统计信息进行执行判定,它可能就会选择一个非最佳的计划。 在本章中,我将展示可以帮助你发现并解决故障和估计查询性能的命令及技术。你也将学习到如何定位存在碎片的索引和过期的统计信息,及估计数据库中索引的使用情况。我也将介绍一些图形化接口工作,比如SQL Server Profiler、图形化执行计划和数据库引擎优化顾问(Database Engine Tuning Advisor)。本章也将介绍一些混杂性能主题,包括如何使用sp_executesql作为执行动态SQL的替代方法,如何不修改查询本身来应用查询提示,以及如何强制一个查询使用特定的查询执行计划。 28.1  查询性能技巧 在开始讨论可以用来估计查询性能的命令和工具之前,我首先简要地介绍一些基本查询性能调优指导方针。查询性能是个广阔的主题,而且在许多章节中我努力在各种内容范围(比如存储过程、视图和触发器)包含小的技巧。既然本章讨论的查询性能独立于特定的对象,下面将详细描述构建SQL Server 2005查询的查询性能最佳实践(注意,索引技巧会在本章稍后介绍): l 在SELECT查询中,只返回需要的列。不要低估了窄的结果集的影响。查询中存在的列越少,则使用的I/O和网络带宽越少。 l 在减少使用列的同时,也应该考虑一下减少行。使用WHERE子句帮助减少查询返回的行。不要让应用程序在只需要显示前10行时却返回2000行。 l 保证FROM子句在你的控制之下。JOIN到单个查询中的每一张表都会增加额外的开销。我没办法给出明确数量的注意事项,因为它依赖于你数据库的设计、容量和用于联结查询的列。但是,经过多年的经验,我发现大量的查询功能虽是正确的,但是执行的时间太长。尽管使用单个的查询去执行复杂的操作显得很方便,但不要低估短小的查询的能力。如果存储过程中有一个执行很长时间的非常大的查询,我通常会尝试将这个查询分解为几个更小的中间结果集。这通常会显著地加快最后希望得到的结果集的生成。 l 只在你需要有序的结果时使用ORDER BY。大结果集的排序操作会导致额外的开销。如果它在你的查询中不是必需的,就删除它。 l 小心在真空环境下的测试。当在测试SQL Server实例中开发数据库时,将有代表性的数据集填充到表中是非常重要的。这个意思是你应该将与实际生产环境数量相当的行填充到表中,也包含有代表性的数据值集。不要在你的开发数据库内使用假数据然后指望查询的性能会与生产环境的相似。SQL Server性能非常依赖索引和统计信息,并且SQL Server将基于包含在表内实际的值做出决策。如果测试数据不是有代表性的“生活化”的数据,那么当生产环境中的查询效果与在测试数据库上看到的查询效果不同时,你会感到非常惊奇。 l 我将这点放入了第10章,我也觉得值得在这里重复它。存储过程经常可以产生比常规即席查询调用更好的性能。存储过程也提升了查询执行的稳定性(复用既有的查询执行计划)。如果你有一个执行时间不理想的查询,考虑一下将这个查询封装到存储过程里。 当阅读关于SQL Server性能调优方面的内容时(正如你现在做的),小心“从不”和“总是”这两个词。当阅读查询调优方面的内容时,对“它依赖……”这个回答不要觉得奇怪,结果可能变得多种多样。的确需要了解很多好的和坏的实践,但是性能调优既是艺术又是科学。做实验时(当然,是在测试环境中)保持思想开阔并放轻松。经常提出问题并且不要受定式思维影响。 28.2  捕捉和估计查询性能 在下面的一组技巧中,我将展示如何捕捉和估计查询性能。这本书基本上重点都在SQL Server 2005的T-SQL上,没有过多地涉及各种图形化接口工具。我将在这一章中打破这个惯例,简要地介绍一下如何使用SQL Profiler捕捉查询活动。并且,我将展示如何图形化地显示实际或估计的查询执行计划,这样就可以理解SQL Server为了生成查询结果集所做的决策。我也将展示其他一些T-SQL命令,它们可以用来返回查询执行计划的细节信息。 28.2.1  使用SQL Server Profiler捕捉高持续时间的查询 查询性能调优通常存在两个分支:主动和被动。主动的查询调优通常在开发期间发生。设计数据库,并填充数据,然后构建查询。要基于应用程序和最终用户需求构建查询。对于那些执行效果不好的查询,可以在部署存储过程完成之前或交给应用程序开发人员之前调整它们。 被动的性能调整是在已经开发完毕并部署到生产环境的代码上捕捉性能问题。随着时间的增长数据会变化,使用这些数据的索引和查询的效率也会随之变化。你可能会听到最终用户的抱怨,或是可能你自己就发现了执行效率很低的查询。 SQL Server 2005工具集中最有价值的图形化接口工具之一是SQL Server Profiler。在SQL Server Profiler中,可以监视发生在SQL Server实例中的查询活动。这个工具有很多用途。但是本技巧中,主要展示如何使用SQL Server Profiler去捕捉高持续时间的查询。 在这个技巧中,启动并配置SQL Server Profiler去捕捉高持续时间的查询。然后SQL Server Profiler会捕捉到在SQL Server Management Studio中执行的查询: (1) 首先进入“开始”菜单,选中“程序”→“Microsoft SQL Server 2005”→“性能工具”→“SQL Server Profiler”。 (2) 打开SQL Server Profiler,进入“文件”→“新建跟踪”。这将打开“连接到服务器”对话框(请看图28-1)。在这里选择你要连接的SQL Server实例的名称以及认证方式(Windows或SQL),当指定好这些值后,点击“连接”按钮。 图28-1 “连接到服务器”对话框 (3) 打开“跟踪属性”对话框(请看图28-2)。在“跟踪名称”输入框中,输入“Duration > 5秒的查询”。SQL Server Profiler提供一组各种各样的跟踪模块。这些模板包含预定义的事件选择,它允许你开始监视SQL Server实例活动的特定类型。在这个技巧中,从“使用模板”框中选择TSQL_Duration模板: 图28-2 “跟踪属性”对话框 (4) 下一步,在“跟踪属性”对话框中点击“事件选择”标签(请看图28-3)。这里载入预先存储的TSQL_DURATION模块的跟踪事件。现在将监视两个不同的事件,RPC:Completed和SQL:BatchCompleted。这个意思是SQL Server Profiler将返回在SQL Server实例中完成的远程过程调用或T-SQL语句的行。这个窗口也显示了跟踪数据要返回的列,在这个例子中包括了Duration(已完成的查询的持续时间)、TextData(捕捉实际的T-SQL)、SPID(调用者的服务器进程ID)以及BinaryData(根据事件返回不同的数据)。 图28-3 事件选择 (5) 为了查看可以添加到跟踪的其他事件和列,点击显示“所有事件”和“显示所有列”复选框(请看图28-4)。这增加了可以扩展或减少显示事件的事件种类。从而可以复选或非复选关联到独立事件的可用的列: 图28-4 扩展的事件和列 (6) 下一步,你希望去配置这个跟踪,只显示查询的持续时间超过5秒的事件。为了做到它,在事件选择窗口中点击“列筛选器”按钮。这会打开“编辑筛选器”对话框(请看图28-5)。为了筛选查询持续时间,在左边的列表框点击Duration列。在右边的列表框,扩展出“大于等于”筛选器并输入5001(它的时间单位是毫秒)。完成后,点击“确定”。 (7) 为了启动跟踪,在“跟踪属性”对话框中点击“运行”。 (8) 在SQL Server Management Studio的查询编辑器中,执行如下查询: (9) 切换到SQL Server Profiler,可以看到查询的确被跟踪捕捉到了(请看图28-6)。为了停止跟踪,打开“文件”菜单并选择“停止跟踪”。通过高亮SQL:BatchCompleted行,可以在下面的窗格中看到完整的SELECT语句。持续时间显示了查询花了10.3秒,并且是由服务器进程ID 53发生的。 解析 在此技巧中,我展示了使用SQL Server Profiler找出执行时间很长的查询。在这个示例中我筛选了基于查询持续时间的列表,但你也可以根据需要添加额外的筛选器。例如,你可以添加只包含AdventureWorks数据库的活动的筛选器。或者,你可以添加只包含引用指定数据库对象或列名的筛选器。一旦捕捉到了你要找的活动,可以将跟踪输出结果保存到文件或表。也可以启动数据库引擎优化顾问来估计潜在提高索引性能的跟踪数据。我会在本章稍后讲到数据库引擎优化顾问。 图28-6 SQL Server Profiler结果 28.2.2  使用sys.dm_exec_requests捕捉执行的查询 除了在SQL Server Profiler中捕捉查询,也可以通过查询动态管理视图sys.dm_exec_requests来捕捉当前正在执行的查询的SQL,正如这个技巧所展示的: 它会捕捉到所有当前正在执行的查询——即使这个查询是用来捕捉其他查询的: 解析 动态管理视图sys.dm_exec_requests返回关于SQL Server实例中正在执行的请求的信息。 查询的第一行选取了会话ID、查询的状态、开始时间、命令类型(例如SELECT、INSERT、UPDATE、DELETE)以及实际的SQL文本: 在FROM子句中,动态管理视图sys.dm_exec_requests与动态管理函数sys.dm_exec_sql_text交叉应用。该函数使用动态管理视图sys.dm_exec_requests中的sql_handle,并返回关联的SQL文本: 然后WHERE子句指定返回当前正在运行的进程: 28.2.3  查看查询的图形化执行计划 了解SQL Server如何执行查询可以帮助你决定如何才能修复一个执行性能差的查询。可以通过查看查询的执行计划(图形化或基于命令的)标识一些普通的操作,它包括: l 标识查询是否执行了扫描(查询堆中所有的页或索引)或查找(只访问选择的列)。 l 标识丢失的统计信息或其他警告。 l 执行高消耗排序或计算活动。 在这个技巧中,我将展示如何查看估计和实际的查询执行计划。我将在SQL Server Management Studio中使用前面技巧中的长持续时间的查询,使用数据库AdventureWorks数据库上下文(请看图28-7): 图28-7 SQL Server Management Studio 下一步,我将显示估计的查询执行计划。它返回SQL Server不通过实际执行查询而认为它将执行的操作。为了在SQL Server Management Studio中实现它,选择“查询和显示估计的执行计划”。它在查询下面的窗格中返回查询计划(请看图28-8)。执行计划通过包含一个或多个运算符的树表现出来。 图28-8 估计的执行计划图形化输出 图28-8中的图标表示每个SQL Server为了产生结果集而使用的运算符。运算符(operator)是SQL Server执行查询中的一个操作。有两类运算符:逻辑的和物理的。逻辑运算符(logical operator)描述了概念级的操作。而物理运算符(physical operator)用来实施逻辑运算符实际的操作。例如,INNER JOIN是一个逻辑运算符,但实施INNER JOIN的物理方式是Hash Match(联结两个结果集的物理方式之一)。 注解     为了得到关于所有可用运算符的列表和它们的说明的完整列表,请参看SQL Server 2005联机丛书“图形化执行计划图标(SQL Server Management Studio)”。 在这个示例中的执行计划返回的操作之一是聚集索引扫描(Clustered Index Scan)。它告诉我们为了返回结果集读取了整个聚集索引,因为查询没有使用WHERE子句去筛选结果集,所以这是有意义的。 执行计划包含了两个不同的“开销%”方面。“查询 1:(与该批有关的)查询开销”是第一个“开销%”,你可以在每个查询的首部找到它。它告诉你查询批处理中指定的查询的持续时间所占的百分比。你也将看到执行计划结果中每个图标下面的开销百分比。在这个示例中,聚集索引扫描占用了100%的查询开销。 除了估计的计划,还可以显示实际的执行计划。它的意思是查询自己被执行,返回结果,并且在另一个标签中返回执行计划。例如,为了展示它,选择“查询和包括实际的执行计划”。 这次你将改变查询,包含定义的客户范围,并通过ContactID排列结果: 然后像平时一样执行查询以返回结果,并执行实际的执行计划。在执行查询之后,产生了3个标签:一个是结果,一个是消息,一个是执行计划。点击“执行计划”标签会看到结果(请看图28-9)。 图28-9 实际的执行计划图形化输出 这次你将在执行计划输出中看到3个操作。在图形化执行计划输出中,从右向左,从上到下读取图标。你将看到在查询中增加的WHERE子句的CustomerID搜索条件,数据检索操作从聚集索引扫描变为聚集索引查找操作。扫描是从堆或索引中检索所有行。而查找操作更有选择性,是从索引中检索指定的行。也注意到这个操作的开销是32%。查询剩下的开销百分比由“排序”运算符(68%的开销)产生,因为查询中附加了ORDER BY子句,所以它出现在执行计划的结果中。 如果在一个批处理中存在多个查询,例如当执行一个存储过程时,可以使用图形化输出指明组中的哪一个批处理带来了最高的“开销%”。例如,在这种情况下将执行启用“包括实际的执行计划”的存储过程sp_help: 它返回两个结果集(部分的): 因为有两个查询,你也会看到两个实际的执行计划(请看图28-10)。 图28-10 sp_help实际的执行计划 这个存储过程的结果显示了存储过程的两个不同的执行计划窗格,每个窗格显示了生成结果集使用的各种操作。你可以在第一个查询的首部看到查询开销是95%(请看图28-11)且第二个查询的5%开销(请看图28-12)——是整个存储过程执行的百分比: 图28-11 批处理查询1的开销 图28-12 批处理查询2的开销 然后,如果你的工作是发现并解决存储过程的性能问题,则第一步应该标识出过程中最高开销的批处理。在标识出最高开销的批处理后,应该专注这个批处理中最高开销的操作。例如,在系统存储过程sp_help中,查询1消耗了总体开销的95%。再一个个地看查询1的运算符,寻找较高百分比的操作。例如在图28-13中,可以看到“散列匹配”运算符占用了28%的开销,两个聚集索引扫描操作各占用了10%和17%的开销。 图28-13 批处理查询1的开销 为了得到某个操作更多的信息,可以把鼠标指针停留在它的上面。它返回显示指定操作信息的“工具提示”窗口,包括了每个运算符实际工作的说明、影响的行数以及其他更多信息(请看图28-14)。 另外你可以查看包含粗箭头(thick arrow)的图像指示器。基于运算符之间传递行数的箭头变粗。鼠标指针停留在箭头上会给出传递到下一个操作的行数的工具提示信息。图28-15显示了“散列匹配”运算符的工具提示: 图28-14 工具提示 图28-15 箭头的工具提示 运算符图标的颜色也是有意义的。如果运算符是红色的,它表明某种类型的警告——例如,它告诉你表的统计信息丢失了。黄色图标用来代表游标操作。绿色图标代表语言元素,比如IF和WHILE。蓝色图标代表实际的执行计划剩余的物理和逻辑操作。 本技巧结束时,为了停止每次执行查询时返回实际的执行计划的查询分析器,点选“查询”菜单和反选“包括实际的执行计划”。 解析 在这个技巧中,我简单地讲解了如何以图形化的形式查看估计的和实际的查询执行计划。这是目前为止最简单的可视化理解查询中发生的操作的方法。如果查询执行了太长的时间,可以检验输出中较高开销的查询批处理以及查询批处理中较高开销的运算符。一旦你发现了高百分比的运算符,可以把鼠标指针停留在运算符上面去查看工具提示。 但是,不一定要使用图形化工具去查看查询的执行计划。SQL Server 2005还包含了在结果集表格中提供这些信息的T-SQL命令,下一个技巧中会看到这些内容。 28.2.4  使用T-SQL命令查看估计的查询执行计划 在SQL Server 2005中,有3个命令可以用来查看关于SQL语句或批处理的查询执行计划的详细信息:SET SHOWPLAN_ALL、SHOWPLAN_TEXT和SET SHOWPLAN_XML。这些命令的输出会帮助你了解SQL Server如何计划去处理和执行查询,标识使用的表联结类型及访问的索引等信息。例如,使用这些命令的输出,可以查看SQL Server在查询中是否使用了指定的索引,如果是这样,它是否通过索引查找(使用非聚集索引来检索操作中选择的行)或索引扫描(操作中检索的所有索引行)检索数据。 SET SHOWPLAN_ALL、SET SHOWPLAN_TEXT、SET SHOWPLAN_XML命令启用之后可以不通过执行查询提供计划信息,允许你在真正执行查询之前调整相关表的查询或索引。 这些命令通过不同的方式返回信息。SET SHOWPLAN_ALL以包括多列和多行的列表格式返回估计的查询计划。输出包含每个操作的估计的IO或CPU、操作中估计的行数、操作开销(与它自身及查询的变化相关的)以及使用的物理和逻辑运算符等信息。 注解     逻辑运算符描述了SQL Server必须在查询执行中实施的概念上的操作。物理运算符用来实施逻辑运算符实际的操作。例如查询中的逻辑操作INNER JOIN在实际的查询执行中会被转换成嵌套循环联结的物理操作。 SET SHOWPLAN_TEXT命令在单个列中返回数据,每个操作占多行。 SQL Server 2005引入的新特性,你也可以使用SET SHOWPLAN_XML命令以XML的格式返回查询执行计划。 这些命令的语法非常相似: 这些命令当设为ON时就启用,设为OFF就禁用。 这个技巧的示例展示在数据库AdventureWorks中通过使用SET SHOWPLAN_TEXT然后使用SET SHOWPLAN_XML(因为SET SHOWPLAN_ALL返回多个17列的行,不容易打印出来,所以该命令没有在本书中展示)返回估计的查询执行计划。 它返回下面的估计的查询执行计划输出: (3行受影响)   (1行受影响)   下一个示例以XML的格式返回估计的查询执行计划结果: 它返回如下的内容(实际的输出有一页多,这里只选择了部分内容): 解析 可以使用SHOWPLAN_ALL、SHOWPLAN_TEXT或SHOWPLAN_XML来调整T-SQL查询和批处理。这些命令不通过实际地执行查询显示估计的执行计划。可以使用这些命令返回的信息采取动作来改进查询性能(例如,为在搜索或联结条件中使用的列增加索引)。观察一下输出,可以发现SQL Server是否使用了希望的索引,以及如果是这样,SQL Server是否使用了索引查找、索引扫描或表扫描操作。 在这个技巧中,SET SHOWPLAN_TEXT和SET SHOWPLAN_XML设置为ON,然后跟随GO: 然后计算引用了Production.Product和Production.ProductReview的查询。这两张表通过在列ProductID上使用INNER联结进行了联结,只返回生产率大于等于2的产品: 在查询的结尾SHOWPLAN设置为OFF,这样这个连接上的后续查询就不会继续执行SHOWPLAN了。 观察输出的代码片段,你可以看到实施INNTER JOIN(逻辑操作)使用的嵌套循环联结(物理操作): 还可以从这个输出看到,通过使用PK_ProductReview_ProductReviewID主键聚集索引实施聚集索引扫描来从表ProductReview中返回数据: 然后通过使用聚集索引查找从表Product检索数据: SET SHOWPLAN_XML命令以XML文档格式返回估计的查询计划,显示与SHOWPLAN_TEXT相似的数据。通过使用属性和元素格式化XML数据。 例如:元素RelOp的属性显示Nested Loops的物理操作和Inner Join的逻辑操作——以及其他统计信息,如估计的操作影响的行: XML文档使用了一个指定的架构定义格式,它定义返回的XML元素、属性和数据类型。这个架构可以通过下面的URL来查看:http://schemas.microsoft.com/sqlserver/2004/07/showplan/showplanxml.xsd。 28.2.5  强制SQL Server 2005使用查询计划 SQL Server 2005引入新的USE PLAN命令,它允许你强制查询优化器为SELECT查询使用既有的、指定的查询计划。在一些非常环境下,当SQL Server选择了低效的查询计划而没有选择更好的时,可以使用这个功能覆盖SQL Server的选择。就像计划指南(稍后会讲到),由于SQL Server的查询优化器通常在判定是否重用或创建新的查询执行计划时都会产生好的决策,因此只有那些有经验的SQL Server专业人员才应该使用这个选项。 USE PLAN的语法如下: 参数xml_plan是已存储的查询执行计划的XML数据类型表示。通过几个方法可以派生出特定的XML查询计划,包括SET SHOWPLAN_XML、SET STATISTICS XML、动态管理视图sys.dm_exec_query_plan、SQL Server Profiler的Showplan XML事件。 在这个示例中,使用SET STATISTICS XML解析出在USE PLAN命令中使用的XML格式的查询计划: 然后把SET STATISTICS XML返回的XMLDocument结果复制到下一个查询。注意,那些在XML文档中出现的单引号(')必须通过增加一个额外的单引号来转义(除了USE PLAN中使用的引号): 解析 USE PLAN允许你去捕捉查询计划的XML格式然后强制查询在后面的执行中使用它。在这个技巧中,使用了SET STATISTICS XML ON捕捉查询的XML执行计划定义。这个定义被复制到OPTION子句中。USE PLAN提示需要Unicode格式,由此为XML文档的文本指定了前缀N'。 USE PLAN和计划指南只在迫不得已时才使用——在彻底地研究了查询设计、加索引、数据库设计、索引碎片整理以及过期的统计信息等其他可能性之后。USE PLAN可能具有短期的高效率,但是当数据改变时,查询执行计划的需求也会变。到最后,可能会发生这样的事情:在这段时间内, SQL Server将比你更能动态决定正确的SQL计划。但是,当SQL Server选择了不够好的查询执行计划时,微软提供这个选项用于高级调试。 28.2.6  查看执行运行时信息 SQL Server提供了4个用来返回查询和批处理执行的统计信息和其他信息的命令:SET STATISTICS IO、SET STATISTICS TIME、SET STATISTICS PROFILE和SET STATISTICS XML。 不像SHOWPLAN命令,STATISTICS命令返回在SQL Server中实际执行的查询信息。SET STATISTICS IO命令用来返回执行的语句产生的磁盘活动(也就是I/O)。SET STATISTICS TIME命令返回分析、编译和执行批处理中的所有语句所花费的时间,单位是毫秒。SET STATISTICS PROFILE和SET STATISTICS XML与SET SHOWPLAN_ALL和SET SHOWPLAN_XML功能相同,但是只返回实际的(不是估计的)执行计划信息和实际的查询结果。 这些命令的语法是相似的,ON启用统计,OFF关闭统计: 在第一个示例中,首先启用STATISTICS IO,然后执行从表Sales.SalesOrderHeader和表Sales. SalesTerritory中通过地域联结累加应付款的总额的查询: 它返回下面的(部分)结果: 对于相同的查询,用SET STATISTICS TIME取代SET STATISTICS IO将返回下面的(部分)结果: SQL Server分析和编译时间:    CPU时间 = 62毫秒,占用时间 = 117毫秒。 (10行受影响) SQL Server执行时间:    CPU时间 = 47毫秒,占用时间 = 87毫秒。 解析 SET STATISTICS命令返回实际的查询或批处理执行的信息。在这个技巧中,SET STATISTICS IO返回查询引用的表有关的逻辑的、物理的和大对象读取事件的信息。对于存在性能问题的查询(基于商业需求和“问题”的定义),可以使用SET STATISTICS IO查看哪里存在I/O热点。例如,在这个技巧的结果集中,可以看到SalesOrderHeader存在更高数量的逻辑读取: …… 表'SalesOrderHeader'。扫描计数1,逻辑读取703次,物理读取0次,预读0次,lob逻辑读取0次,lob物理读取0次,lob预读0次。 …… 注意高物理的(从磁盘中读取)或逻辑的读取值(从数据缓存中读取)——即使物理读取值是零而逻辑的是一个很高的值。也要寻找工作表(已经在这个技巧中看过): 表'Worktable'。扫描计数1,逻辑读取39次,物理读取0次,预读0次,lob逻辑读取0次,lob物理读取0次,lob预读0次。 通常会在使用了GROUP BY、ORDER BY、散列联结和UNION操作的查询中看到工作表。工作表在查询的执行期创建在tempdb中,并且在SQL Server完成操作时被自动删除。 在这个技巧的第二个示例中,使用SET STATISTICS TIME来显示查询的分析和编译时间(在实际的查询结果之前显示),以及实际的执行时间(在查询结果之后显示): SQL Server分析和编译时间:    CPU时间 = 62毫秒,占用时间 = 117毫秒。 (10行受影响) SQL Server执行时间:    CPU 时间 = 47毫秒,占用时间 = 87毫秒。 这个命令在计算查询从头到尾消耗的时间总和时是有用的,允许查看预编译是否花费比预计的更长的时间,或是在实际的查询执行时是否发生延迟。 其他两个STATISTICS命令,SET STATISTICS PROFILE和SET STATISTICS XML,返回与SET SHOWPLAN_ALL和SET SHOWPLAN_XML相似的信息,只不过结果基于实际的执行计划,而不是估计的执行计划。 28.2.7  查看性能统计信息和缓存的查询计划 在这个技巧中,展示了使用SQL Server 2005提供的动态管理视图和函数查看性能统计信息和缓存的查询计划。 在这个示例中,是一个简单的查询,它返回数据库AdventureWorks上表Sales.Individual中所有的行。在执行它之前,要清空过程缓存以便可以在这个示范中更容易地识别查询(请记住应该只在测试SQL Server实例上清空过程缓存): 现在,将查询动态管理视图sys.dm_exec_query_stats,它包含关于缓存在SQL Server实例中的查询的统计信息。这个视图包含了sql_handle,我将把它作为动态管理函数sys.dm_exec_sql_text的一个输入。这个函数用来返回T-SQL语句的文本: 它返回的信息是: 解析 这个技巧展示了清空过程缓存,然后执行查询,几秒后执行完成了。完成之后,查询动态管理视图sys.sm_exec_query_stats来返回缓存执行计划的统计信息。 SELECT子句检索查询中T-SQL文件上的信息:逻辑读取、物理读取、总的占用时间(秒)以及逻辑写入(等等): 总的占用时间列是以微秒为单位的,因此为了返回完整的秒数要把它除以1 000 000。 在FROM子句中,为了检索缓存查询的SQL文本,动态管理视图sys.dm_exec_query_stats与动态管理函数sys.dm_exec_sql_text进行了交叉应用: 这个信息对于识别高强读取或高强写入的查询是有用的,帮助确定哪个查询是需要调优的。要牢记这个技巧中的查询只能检索仍然在缓存中的查询信息。 这个查询返回总和,但是sys.dm_exec_query_stats也包含跟踪读取和写入最大、最小和最后的值的列。注意sys.dm_exec_query_stats还有其他有用的列,它们能表示CPU时间(total_worker_time、last_worker_time、min_worker_time以及max_worker_time)和.NET CLR对象执行时间(total_clr_time、last_clr_time、min_clr_time、max_clr_time)。 28.3  统计信息 正如我们在第22章中讨论的,数据库选项AUTO_CREATE_STATISTICS使SQL Server能自动地产生关于列中数据分布的统计信息。数据库选项AUTO_UPDATE_STATISTICS自动地更新既有的表或索引视图的统计信息。由于这个选项对查询性能会产生关键性的影响,除非有非常特殊的原因,否则一定不要在数据库中关闭这个选项。 统计信息对于有效的查询执行和查询性能来说是至关重要的,它允许SQL Server在产生执行计划时选择适当的物理操作。SQL Server可以手动或自动地产生表和索引视图的统计信息,收集产生有效查询执行计划的信息。 下面的几个技巧将展示如何直接使用统计信息工作。在阅读这些技巧时请记住,只要可能,要让SQL Server管理数据库中统计信息的自动创建和更新。这样会在特殊的故障诊断过程或做了重大数据改变时(例如,在大量数据载入后马上执行sp_updatestats)省去执行大部分这些命令。 28.3.1  手动创建统计信息 SQL Server通常基于查询活动产生所需要的统计信息。然而,如果你仍然希望去在列上显式地创建统计信息,可以使用CREATE STATISTICS命令。 语法如下: 这个命令的参数在表28-1中描述。 表28-1 CREATE STATISTICS参数 参  数 描  述 statistics_name 新统计信息的名称 table | view 要在其中创建统计信息的表或索引视图 column [,...n] 用于产生统计信息的一列或多列 FULLSCAN | SAMPLE number {PERCENT | ROWS} 当指定FULLSCAN时,在产生统计信息时读取所有行SAMPLE也可以读取指定数量的行或指定百分比的行 STATS_STREAM = stats_stream 此语法仅供微软内部使用 NORECOMPUTE 这个选项指定,一旦创建了统计信息,即使数据发生改变,统计信息也不会被更新。建议限制使用这个选项。及时的统计信息会帮助SQL Server产生理想的查询计划 在这个示例中,在列Sales.Customer CustomerType上创建新的统计信息: 解析 这个技巧展示了在表Sales.Customer上手动创建统计信息。代码的第一行指定了统计信息的名称: 代码的第二行指定了要在其中创建统计信息的表,随后是用于产生统计信息的列名: 代码的最后一行指定了为了生成统计信息要读取表中所有的行: 28.3.2  更新统计信息 在创建了统计信息之后,如果你希望手动更新统计信息,可以使用UPDATE STATISTICS命令。 语法如下: 表28-2是这个命令的参数描述 表28-2 UPDATE STATISTICS参数 参  数 描  述 table | view 要更新统计信息的表名或索引视图名 { index | statistics_name }| ({index |statistics_name } [ ,...n ] )} 要更新的索引名称或统计信息名称 FULLSCAN| SAMPLE number { PERCENT | ROWS } | RESAMPLE 当指定FULLSCAN时,在生成统计信息时读取所有行。SAMPLE读取指定数量的行或指定百分比的行。RESAMPLE基于原始抽样方法更新统计信息 [ALL | COLUMNS | INDEX] 当指定ALL时,更新所有既有的统计信息。当指定COLUMN时,只更新列的统计信息。当指定INDEX时,只更新索引统计信息 NORECOMPUTE 这个选项指定,一旦创建了统计信息,即便数据发生了改变,也不更新统计信息。在这里要重申一下,建议限制使用这个选项。及时的统计信息会帮助SQL Server产生理想的查询计划 这个示例更新了在前面技巧中创建的统计信息,基于最新的数据刷新统计信息: 解析 这个示例更新了在前面技巧中创建的统计信息,并用最新的数据刷新统计信息。代码的第一行指定了包含要更新的统计信息的表名: 第二行指定了要更新的统计信息的名称: 代码的最后一行指定了为了更新统计信息读取表中所有行: 28.3.3  生成及更新所有表的统计信息 通过系统存储过程sp_createstats,也可以自动地生成数据库中所有那些列上没有关联统计信息的表的统计信息。 语法如下: 表28-3描述了这个命令的参数。 表28-3 sp_createstats的参数 参  数 描  述 indexonly 当指定了indexonly时,只考虑在那些使用了索引的列上创建统计信息 fullscan 当指定了fullscan,估计所有行去生成统计信息。如果没有指定,默认的行为是通过采样提取统计信息 norecompute norecompute选项指定,一旦创建了统计信息,即便数据发生了改变,也不更新统计信息。像CREATE STATISTICS和UPDATE STATISTICS一样,建议限制使用这个选项。及时的统计信息会帮助SQL Server产生理想的查询计划 如果希望在当前数据库中更新所有的统计信息,可以使用系统系统存储过程sp_updatestats。该存储过程在SQL Server 2005中进行了改善,只更新必要的(当数据发生改变时)统计信息。不会更新未改变数据的统计信息。 这个示例展示了对数据库中那些没有关联统计信息的列创建新的统计信息: 它返回如下的(部分)结果集: 表   正为以下列创建统计信息:   表   正为以下列创建统计信息:   表   正为以下列创建统计信息:   下一个示例自动地更新在当前数据库中的所有统计信息: 它返回如下的(部分的)结果。注意“不需要更新……”这个信息。你看到的结果可能会因表统计信息的状态不同而不同: 正在更新 [Production].[ProductProductPhoto]     [PK_ProductProductPhoto_ProductID_ProductPhotoID],不需要更新...     [_WA_Sys_00000002_01142BA1],不需要更新...     [Primary],不需要更新...     [ModifiedDate],不需要更新...     已更新0条索引/统计信息,4不需要更新。 …… 28.3.4  查看统计信息 如果你怀疑特定表上的统计信息没有及时更新,或包含过期的信息,可以使用DBCC SHOW STATISTICS命令去查看要验证的详细的统计信息。 它的语法如下: 表28-4描述了这个命令的参数。 表28-4 DBCC SHOW_STATISTICS参数 参  数 描  述 'table_name' | 'view_name' 要验证的表或索引视图 target 要验证的索引或统计信息的名称 NO_INFOMSGS 当指定NO_INFOMSGS时,取消信息性消息 STAT_HEADER | DENSITY_VECTOR |  HISTOGRAM [,n] 如果指定其中一个或多个选项,可限制该命令返回的结果集。不指定任何选项表示将返回这3种结果集 这个示例展示了如何查看表Sales.Customer的统计信息Stats_Customer_CustomerType的统计信息内容: 它返回如下的结果集: 解析 在这个技巧示例的结果中,列All density表示列的选择性(selectivity): 选择性是指给定具体列的值返回的行的百分比。较低的All density值表示较高的选择性。经常用高选择性的列作为有用的索引(对查询优化过程是有用的)。 在SHOW_STATISTICS返回的第三个结果集中,CustomerType只有两个值,I和S(你可以在第三个结果集的RANGE_HI_KEY中看到): 相似的值具有如此高的密度,并且低选择性(返回的多个行中很可能为同一值),你可以做一个深思熟虑的假设,在生成查询执行计划时在这个特定列上的索引不太可能对SQL Server非常有用的。 28.3.5  删除统计信息 要删除统计信息,使用DROP STATISTICS命令。 语法如下: 这个命令允许你删除一个或多个统计信息,需要加上表或索引视图名的前缀。 在这个示例中,从数据库中删除统计信息Sales.Customer.Stats_Customer_CustomerType: 解析 这个技巧通过使用DROP STATISTICS删除用户创建的统计信息。统计信息使用3部分组成的名称schema.table.statistics_name。 28.4  索引调优 下一组技巧展示了管理索引的技术。我先讲一下如何去做下面的事情: l 标识出索引碎片,这样就可以指出应该对哪个索引进行重新生成或重新组织。 l 显示索引的使用情况,因此可以判断出哪一个索引没有被SQL Server使用。 l 使用数据库引擎优化顾问工具分析包含查询和索引建议的负载文件。 在你开始这个技巧之前,我想花一分钟时间讨论一些常规的索引最佳经验。在考虑这些最佳经验时,要记住像查询调优一样,几乎没有严格的“总是”或“从不”规则。SQL Server中索引的使用依赖于许多因素,包括(但不仅限于)查询的结构、查询中引用的表、引用的列、表中行的数量、索引列中数据的唯一性。当建立索引策略时要牢记几个基本的指导方针: l 基于高优先级和使用频繁的查询增加索引。基于你的商业需求,提前确定可接受的查询执行时间可能是多少。 l 不要同时增加多个索引。而应该增加一个索引同时测试查询,去看新的索引是否被使用。如果没有被使用,则删除它。如果被使用了,则对它进行测试,以确保它不会对其他查询产生负面影响。请记住,每个附加的索引都会在基础表的数据修改上增加额外的开销。 l 总是在每个表中都增加一个聚集索引,除非你有非常好的原因不这样做。没有聚集索引的表称为堆,意思是其中存储的数据没有特定的顺序。在索引重新生成或重新组织期间,聚集索引依照聚集键和它重排序的数据页进行排序。可是堆不会在索引重新生成或重新组织期间被重新生成,所以会脱离控制地生长,占用的数据页比必要的多很多。 l 随时监视查询性能。随着数据的改变,索引的性能和效能也会改变。 l 由于返回查询的结果需要更多的I/O操作,碎片化的索引会降低查询性能。基于时间表或需要,通过重新生成和/或重新组织你的索引保持索引碎片最小化。 l 选择很少改变、高度唯一、数据类型宽度狭窄的列作为聚集索引键。宽度非常重要,因为所有非聚集索引也包含了聚集索引键。聚集索引在应用到使用了范围查询的列中时是有用的。这包括使用了运算符BETWEEN、>、>=、<和<=的查询。对于返回大量结果集或高度依赖ORDER BY和GROUP BY子句的查询,聚集索引键也能帮助其减少执行时间。 l 非聚集索引对于小的或只有一行的结果集是理想的选择。再一次提醒,应该基于查询中的应用挑选列,特别在JOIN和WHERE子句中时。非聚集索引应该建立在包含高度唯一数据的列上。就像在第5章讨论到的,不要忘记为非键列考虑使用覆盖查询和SQL Server 2005引入的INCLUDE新功能。 l 为那些定位在只读文件组或数据库上的索引使用100%填充因子。因为完成查询的结果集需要较少的数据页,所以这减少了I/O并且可以提升查询性能。 l 基于你使用的查询设法预估需要哪些索引——但是也不要害怕频繁地使用数据库引擎优化顾问工具。使用数据库引擎优化顾问工具,SQL Server可以估计你的查询或查询的批处理,然后为了帮助查询运行得更快决定应该增加(或删除)哪一个索引。我将在稍后展示它。 下一技巧将展示如何显示索引碎片。 28.4.1  显示索引碎片 在SQL Server 2000中,使用DBCC SHOWCONTIG命令来显示索引碎片。碎片是表中数据修改自然产生的副产品。当在数据库中更新数据时,索引(基于索引键)的逻辑顺序不和实际数据页的物理顺序同步了。当数据页变得越来越无序,为了返回查询请求的结果,需要更多的I/O操作。重新生成或重新组织一个索引允许通过同步逻辑索引顺序、重排列物理数据页去匹配逻辑索引顺序,来整理索引的碎片。 注解  索引管理的介绍请看第5章,索引碎片整理和重新组织的介绍请看第23章。 现在在SQL Server 2005中,已经不建议使用DBCC SHOWCONTIG了,建议使用新的动态管理函数sys.dm_db_index_physical_stats。动态管理函数sys.dm_db_index_physical_stats返回能确定索引碎片级别的信息。 sys.dm_db_index_physical_stats的语法如下: 表28-5描述了这个命令的参数。 表28-5 sys.dm_db_index_physical_stats的参数 参  数 描  述 database_id | NULL 要检测索引的数据库ID。如果为NULL,返回SQL Server实例中的所有数据库的信息 object_id | NULL 要检测的表和视图(索引视图)的对象ID。如果为NULL,返回所有表的信息 index_id | NULL | 0 要检测的指定索引ID。如果为NULL,返回表中所有索引的信息 partition_number | NULL 要检测的分区的指定分区编号。如果为NULL,返回基于已定义数据库/表/选择的索引的所有分区的信息 LIMITED | SAMPLED | DETAILED | NULL | DEFAULT 这些模式影响了如何收集碎片数据。LIMITED模式扫描堆所有的页,但对于索引,则只扫描叶级上面的父级别页。SAMPLED收集在堆或索引中1%采样率的数据。DETAILED模式扫描所有页(堆或索引)。DETAILED是执行最慢的,但也是最精确的选项。指定NULL或DEFAULT的效果与LIMITED模式的相同 在这个示例中,查询动态管理视图sys.dm_db_index_physical_stats检索数据库AdventureWorks中平均碎片率大于30的所有对象: 它返回下面的(部分)结果: 第二个示例返回指定数据库、表和索引的碎片: 它返回 解析 第一个例子首先将数据库上下文改为数据库AdventureWorks: 由于OBJECT_NAME函数是数据库上下文敏感的,改变数据库上下文确保你查看到正确的对象。 下一步,SELECT子句显示对象名称、索引ID、描述和平均的碎片百分比: 列index_type_desc告诉你索引是堆、聚集索引、非聚集索引、主XML索引还是辅助XML索引。 下一步,FROM子句引用了sys.dm_db_index_physical_stats目录函数。括号中为参数,包括数据库名、扫描模式,其他参数为NULL: 由于sys.dm_db_index_physical_stats像表一样被引用(不像2000版本中的DBCC SHOWCONTIG),因此使用WHERE子句来限定只在结果中返回碎片百分比大于等于30%的行: 查询返回了数据库AdventureWorks中对象的碎片大于30%的几行。列avg_fragmentation_in_ percent显示聚集索引或非聚集索引的逻辑碎片,返回索引的叶级无序页的百分比。对于堆来说,avg_fragmentation_in_percent显示区级碎片。关于区,回忆一下SQL Server在页级读写数据。存储在块中的页被称为区,它由8个连续的8KB的页组成。使用avg_fragmentation_in_percent,你可以决定是否需要通过ALTER INDEX对索引重新生成或重新组织。 在第二个示例中,显示指定数据库、表和索引的碎片。SELECT子句包含了对索引名的引用(不是索引编号): FROM子句包含了指定的表名,它通过使用OBJECT_ID函数转换为ID。第三个参数包含了要检测碎片的索引的索引编号: 通过object_id和index_id,系统目录视图sys.indexes联结了函数sys.dm_db_index_physical_stats。 查询只返回了指定索引的碎片结果。 28.4.2  显示索引使用情况 与查询性能调优相似,在数据库中创建有用的索引经常是艺术和科学的结合。在加速SELECT查询的同时,索引减慢了数据的修改。必须平衡读取活动与数据修改活动带来的索引开销的代价/受益。每一个增加到表中的索引都会以数据修改速度为代价改善查询性能。在这之上,索引的效能会随着数据的改变而改变,由此几个星期前索引还是有用的,今天可能就没用了。在SQL Server 2000中最大的困难之一是找出哪一个索引没被使用——从来都不使用或很少使用。这是重要的,因为增加索引减慢更新速度。如果想在表中创建索引,则它们应该在高优先级的查询中很好地使用。如果SQL Server没有使用某个索引,它就是静负荷。 现在在SQL Server 2005中,可以通过查询动态管理视图sys.dm_db_index_usage_stats查看索引是否被使用。这个视图从SQL Server实例上次重启以后返回有关索引查找、扫描、更新或查找的次数的统计信息。它也返回引用索引的最后的时间。 在这个示例中,查询动态管理视图sys.dm_db_index_usage_stats去查看表Sales.Customer中的索引是否被使用过。在引用sys.dm_db_index_usage_stats之前,要在表Sales.Customer上执行两个查询,第一个返回所有的行和列,第二个返回具有指定的TerritoryID的列AccountNumber: 在执行完查询之后,查询动态管理视图sys.dm_db_index_usage_stats: 它返回: 解析 动态管理视图sys.dm_db_index_usage_stats允许查看SQL Server实例使用了哪个索引。在最后一次SQL Server重启之后统计信息才生效。 在这个技巧中,在表Sales.Customer上执行两个查询。在执行完查询后,查询动态管理视图sys.dm_db_index_usage_stats。 SELECT子句显示了索引的名称、用户查找和用户扫描的次数以及用户最后查找和最后扫描的时间: FROM子句通过object_id和index_id将动态管理视图sys.dm_db_index_usage_stats联结到系统目录视图sys.indexes上(所以索引名应该显示在结果中): WHERE子句限定只显示数据库AdventureWorks上的索引以及表Sales.Customer上的索引。DB_ID函数用来返回数据库系统ID,OBJECT_ID函数用来得到表的对象ID: 查询返回两行,显示表Sales.Customer的聚集索引PK_Customer_CustomerID最近被访问过(很可能是通过第一个SELECT *查询),并且第二个查询(限定了TerritoryID = 4)使用了非聚集索引IX_Customer_TerritoryID。 索引帮助提高了查询性能,但也增加了磁盘空间和数据修改的开销。使用动态管理视图sys.dm_db_index_usage_stats,可以监视索引是否真的被使用,如果没有,则把它们替换为更有效的索引。 28.4.3  使用数据库引擎优化顾问 如果性能问题难住了你,或是为了找到可能的性能提升,你愿意去检查一下当前的索引策略,那就使用数据库引擎优化顾问吧。 这个技巧将演示如何使用数据库引擎优化顾问为数据库AdventureWorks中的特定查询生成建议。 对于这个技巧,请把查询作为文件保存到C:\Apress\ProductsByLocation.sql,稍后我们会对它进行检测。 第一步,图28-16显示了这个查询的图形化执行计划。由于结果集较大,索引调优的基本目的是减少扫描的数量,多使用查找。正如在这个查询的执行计划中看到的,一些表使用了索引扫描操作,而只有一张表(表ProductCostHistory)使用了索引查找。 表28-16 通过地区查询的产品的执行计划 你将使用数据库引擎优化顾问来检测查询和给出提升查询性能的建议。数据库引擎优化顾问通过“开始”→“程序”→Microsoft SQL Server 2005→性能工具→数据库引擎优化顾问运行起来。 工具提示你在“连接到服务器”对话框(请看图28-17)中输入服务器名称和身份验证。在选好这些选项后,点击“连接”按钮。 图28-17 “连接到服务器”对话框 现在,主窗口打开了,左边的“会话监视器”窗口中显示了已连接的SQL Server实例,右边的窗口中是“常规”配置标签(请看图28-18)。在“常规”标签中,你可以在“工作负荷”区域通过选择“文件”选项选择包含你希望调优的SQL的文件,在这个示例中是C:\Apress\ProductsByLocation.sql。你也可以通过选择复选框然后选择数据库AdventureWorks,来选择你需要生成调优建议的数据库。 图28-18 数据库引擎优化顾问的“常规”选项 点击“优化选项”标签显示用于估计负荷的更多的选项(请看图28-19)。使用屏上的选项,可以设置工具估计工作负荷需要的最大时间。也可以指定需要给出调优建议的物理设计结构(在这个示例中,使用默认的索引就可以了)、是否给出分区策略的调优建议以及所有既有的对象(索引、索引视图)是否仍然保存在数据库中。 图28-19 “优化选项”标签 通过选取“操作”菜单并选取“开始分析”来开始分析操作。这将打开一个新的“进度”屏幕(请看图28-20)。 图28-20 “进度”标签 分析完成后,会增加两个标签:“建议”标签和“报告”标签。“建议”标签包含用来提升查询性能的的建议列表。注意在图28-21中屏幕的顶端估计的查询性能提升为22%。数据库引擎优化顾问推荐创建3个列在索引建议区域的新索引。在这里你可以看到索引要增加到哪一张表上,以及哪一列上,索引的大小单位是KB。 图28-21 “建议”标签 在窗口上方的“报告”标签(请看图28-22)显示了调优统计信息的摘要,并且在下面的屏中,可以查看其他调优报告(例如,图28-22显示了索引使用情况报告的结果)。 图28-22 “报告”标签 返回到“建议”标签,你可以选择是立即应用建议或不处理建议,再或者把它们保存到.sql文件中。在这个技巧中,可以通过选择“操作”菜单并选择“应用建议”立即应用这些调优建议。 这将打开“应用建议”对话框(请看图28-23)。在这里可以马上应用索引改变,或把它们安排入时间表中,以后处理。选择“立即应用”并点击“确定”按钮。 这将打开“应用建议”状态对话框(请看图28-24),当操作完成,点击“关闭”按钮。 图28-23 “应用建议”对话框 图28-24 “应用建议”状态对话框 现在,可以重新测试技巧中的源查询并查看对执行计划的所有影响。在这个示例中,正如在图28-25中看到的,两张表(原来只有一张表进行了索引查找)现在使用了索引查找操作。 图28-25 应用了调优建议后的执行计划 测试查询执行时间,你会发现通过创建索引节省的时间相对于新加入的建议索引增加的磁盘空间和数据库修改负载来说是值得的。 解析 这个技巧对关于如何使用数据库引擎优化顾问去做基于T-SQL查询的索引建议给出了简要介绍。可以使用这个工具通过查询的工作负荷来估计并给出调优建议。 作为最佳的经验,要在快速的查询时间的益处与磁盘空间和数据修改负载的问题之间做出抉择。也应该测试新加入的索引对既有查询的执行时间造成的影响。 而且要确定只对SQL Server实例中出现性能问题的时候调优数据库。这是因为数据库引擎优化顾问在分析工作负荷过程中会消耗大量的CPU和内存资源。为了减少给工具带来的不必要的工作负荷,确认要在“优化选项”标签中删除那些不希望进行调优的对象类型。 28.5  杂项技术 下面的两个技巧详细地讲述了一些在本章前面没有提到的技术。这两个技巧将展示如何使用另一种方法去通过系统存储过程sp_executesql执行动态SQL和存储过程。本章的最后一个技巧会展示如何在既有的查询中应用查询提示,而不去通过计划指南实际地修改应用程序的SQL代码。 28.5.1  使用执行动态SQL的另一种方法 使用EXECUTE命令,可以执行在批处理、存储过程或函数中由字符串组成的内容。也可以使用EXECUTE的缩写EXEC。 例如,随后的语句执行了从表Sales.Currency中SELECT的操作: 尽管这个技术允许动态格式化可以执行的字符串,它还是有很大的风险。 首先,最大的风险是SQL注入带来的危险。SQL注入可以将恶意代码插入到以后将传递给 SQL Server 供分析和执行的字符串中。允许用户输入连接到SQL字符串并执行的变量会对你的数据库引发各种各样的危险(更不用说潜在的保密性问题了)。这段恶意代码如果执行在具有充分权限的上下文中,它可以删除表、读取敏感数据甚至是关闭SQL Server进程。 字符串执行技术的第二个问题是它们的性能。尽管动态生成SQL有时可能性能还不错,但查询的性能也是不可靠的。不像存储过程,动态生成的SQL、常规即席SQL批处理及语句将会让SQL Server在它们每次执行时生成新的执行计划。 如果你的应用程序没办法使用存储过程,作为替代方法,系统存储过程sp_executesql通过创建和使用可重用的、只有查询参数发生更改的查询执行计划处理动态SQL的性能问题。参数是类型安全的,意思是不能以非指定的数据类型使用它们。当需要在即席语句和存储过程中做出选择时,这是有价值的解决方案。 警告     sp_executesql解决了一些性能问题,但是不能完美地解决SQL注入问题。要小心连接到SQL字符串中的用户传递的参数!下一步就会描述参数的功能。 sp_executesql的语句如下: 表28-6描述了这个命令的参数。 表28-6 sp_executesql参数 参  数 描  述 stmt 要执行的字符串 @parameter_name data_type  [ [ OUTPUT ][,...n] 包含在字符串语句中的一个或几个参数。OUTPUT与存储过程的参数OUTPUT相似 'value1' [ ,...n ] 传递到参数的实际值 在这个示例中,通过指定ProductID、TransactionType以及Quantity的最小值查询表Production.TransactionHistoryArchive: 查询返回如下的结果: 解析 sp_executesql允许执行一个动态生成的Unicode字符串。这个系统存储过程允许使用参数,这些参数又允许SQL Server重用它执行生成的查询执行计划。 注意在这个技巧中,由于sp_executesql需要Unicode语句字符串,第一个参数之前加上了N' Unicode前缀。第一个参数也包括了SELECT查询本身,包含了WHERE子句中的参数: 第二个参数进一步定义了包含在第一个参数中的SQL语句的每个参数的数据类型。用逗号分隔每个参数: 最后一个参数为所有包含的参数赋值,它在执行期间被动态地放入查询。 指定了3个参数的查询返回了8行数据。如果查询只改变不同的参数值再次执行,SQL Server很可能使用原始的查询执行计划(而不是创建新的执行计划)。 28.5.2  不修改应用程序的SQL去应用提示 正如我们在本章开始时提到的,发现并解决查询性能问题需要包括很多方面,例如数据库设计、索引的创建、查询结构。可以修改你的代码,但是如果代码你不能修改呢?如果你遇到不是你自己的代码而不能修改的数据库或查询问题(例如,压缩软件)——那样的话你的选择就非常受限制了。通常在这个第三方软件的情况下,你被限制不能去增加新索引或从大数据量的表中归档数据。通常修改厂商的实际数据库对象或查询也是被禁止的。 SQL Server 2005通过使用计划指南(plan guide)为这个普遍的问题提供了新的解决方案。计划指南允许你不去修改应用程序中实际的查询文本,而只是应用提示到查询中。计划指南可以应用到数据库对象(存储过程、函数、触发器)中指定的查询上,或是指定的独立SQL语句中。 通过使用系统存储过程sp_create_plan_guide创建计划指南: 表28-7描述了这个命令的参数。 表28-7 sp_create_plan_guide参数 参  数 描  述 plan_guide_name 新计划指南的名称 Statement_text 指定要进行调优的SQL文本 OBJECT | SQL | TEMPLATE 当选择了OBJECT,计划指南将应用到指定的存储过程、函数、或DML触发器中找到的语句文本。当选择了SQL,计划指南将应用到在独立的语句或批处理中找到的语句文本。TEMPLATE选项决定是否启用SQL语句的参数化。回顾一下22.2.8节的PARAMETERIZATION选项,当将它设置为FORCED,会增加查询被参数化的可能性,允许它形成可重用的查询执行计划。但是,当设为SIMPLE参数化时,仅影响较少数量的查询(依据SQL Server的判断)。如果数据库使用了SIMPLE参数化,你可以强制指定的查询语句为参数化,如果数据库使用了FORCED参数化,你可以强制指定的查询语句为非参数化 N'[schema_name.]object_ name' | N'batch_text' | NULL 当选择了TEMPLATE时,指定SQL文本所在的对象的名称,或批处理文本,再或者是NULL N'@parameter_name data_type [,...n ]' | NULL 在SQL或TEMPLATE类型的计划指南中使用的参数的名称 N'OPTION (query_hint [,...n ] )' | NULL 应用到语句的查询提示 使用系统存储过程sp_control_plan_guide删除或关闭计划指南: 表28-8描述了这个命令的参数。 表28-8 sp_control_plan_guide参数 参  数 描  述 DROP DROP操作从数据库中删除计划指南 DROP ALL DROP ALL从数据库中删除所有的计划指南 DISABLE DISABLE选项关闭计划指南,但是不将它从数据库中删除 DISABLE ALL DISABLE ALL关闭了数据库中所有的计划指南 ENABLE | ENABLE ALL ENABLE启用了一个关闭的计划指南,ENABLE ALL启用数据库中所有关闭的计划指南 plan_guide_name 对其执行操作的计划指南的名称 在这个技巧的示例中,计划指南被用来改变独立查询的表联结类型方法。在这个情况下,第三方软件包发出一个引发LOOP联结的查询。在这个情况下,你希望查询取代它而去使用MERGE联结。 警告     在通常情况下,应该让SQL Server自行做出关于如何处理查询的决策。只有在特定的情形下,并且由富有经验的SQL Server专业人员决定是否在你的SQL Server环境下创建计划指南。 在这个示例中,执行下面使用了sp_executesql的查询: 请看图28-26中显示的查询执行计划,它显示了使用嵌套循环运算符将表Vendor和VendorAddress联结到一起。 图28-26 应用计划指南前的查询执行计划 例如,如果你希望SQL Server使用一个不同的联结方式,但不修改应用程序中实际的查询则可以通过创建计划指南应用这个修改。 随后创建了在应用程序中的查询上应用联结提示的计划指南: 在创建了计划指南之后,使用sp_executesql执行查询: 请看图28-27中的图形化执行计划,现在看到嵌套循环联结已经变成了合并联结运算符——没有修改SQL Server上应用程序中的实际查询。 图28-27 应用计划指南后的查询执行计划 如果确定合并联结不再比嵌套循环联结有效率,可以通过使用系统存储过程sp_control_plan_ guide删除计划指南: 解析 计划指南允许不用修改应用程序本身,而增加查询提示到应用程序的查询。在这个示例中,一个特定的SQL语句实施了嵌套循环联结。不去修改实际的查询本身,SQL Server“看到”计划指南并将进入的查询匹配到计划指南中的查询。当匹配时,就将计划指南中的提示应用到进入的查询中。 sp_create_plan_guide允许为独立的SQL语句、对象(存储过程、函数、DML触发器)中的SQL语句以及不管是否被参数化(取决于数据库的PARAMETERIZATION设置)的SQL语句创建计划。 在这个技巧中,sp_create_plan_guide的第一个参数是新计划指南的名称: 第二个参数是应用计划指南的SQL语句(空白的字符、注释和分号将被忽略): 第三个参数是计划指南的类型,在这个示例中是独立的SQL: , 对于第四个参数,在这里不是存储过程、函数或触发器,@module_or_batch参数为NULL: @params参数也被设为NULL,因为这不是一个TEMPLATE计划指南: 最后的参数包含应用到传入查询上的实际指示——在这个例子中强制查询中的所有联结使用MERGE操作: 最后,使用系统存储过程sp_control_plan_guide从数据库中删除计划指南,为第一个参数指定DROP操作,然后为第二个参数指定计划指南名称。 第29章 备份与恢复 SQL Server最重要的职责之一就是保护数据。就像SQL Server 2005的很多特性一样,你可以不使用任何T-SQL代码来执行数据库备份和还原(在SQL Server Management Studio中实现)。但是,在紧急情况时,不能总是指望使用图形用户接口来还原数据。 这一章包含了各种备份数据库的技巧,它可以是一个完整、文件、文件组、事务日志或差异的备份(我们会详细地讲解所有这些备份)。你也将学习到使用这些备份类型来恢复(还原)数据库的方法。 注解     SQL Server 2000中的一些BACKUP和RESTORE的特性在SQL Server 2005中不再建议使用。它们是 BACKUP LOG WITH NO_LOG、BACKUP LOG WITH TRUNCATE_ONLY、BACKUP / RESTORE WITH MEDIAPASSWORD和BACKUP / RESTORE WITH PASSWORD。 29.1  创建备份和恢复计划 在详细讲解如何对你的SQL Server 数据库执行备份和还原之前,我首先会讨论如何生成一个数据库恢复计划,在通常情况下,你应该思考一下回答下面的问题: l 哪一个数据库是重要的?如果某个数据库是重要的,并且它不只是用来完成用完就丢掉的工作,那么应该备份它。 l 你可以忍受丢失多少数据?你能忍受丢失一天的数据吗?或是一小时的?或是一分钟的?你能承受丢失的数据越少,就越要经常进行数据库备份。 l 你是否有异地的存储设施?灾难发生时,设备会被淋湿或在火灾中被烧毁。如果对你来说数据是重要的,你需要通过磁带或网络把它转移到分隔的异地。 l 你的商业应用可以承受多少停机时间?在丢失数据库的所有数据后你需要花多少时间把所有数据恢复并运行起来?如果你的数据库很大,并且允许的停机时间非常少,你可能需要考虑对你现有的数据库做一个副本(数据库镜像、日志传送或复制)。 恢复计划基于你公司如何看待SQL Server实例及它的数据库的价值。对于专用数据库实例来说,其商业价值可以从“损坏则崩溃”到关键任务,或者说“不能丢失任何一位数据”。不用说大家也都知道关键的商业数据库是必须要备份的。如果你不能承受丢失数据或在数据库中重新生成数据,就应该做数据库备份。本章将讨论如何使用T-SQL执行备份操作,以及各种可以执行的备份类型。 备份需要考虑的另一个事项是备份频度。你可以承受丢失多少数据?你能忍受丢失一天工作的数据?或是几分钟?或一点也不行?如果你能忍受丢失24小时的数据,那么依据数据库的大小,一天一次的完整数据库备份计划是可以接受的。如果你不能忍受丢失30分钟以上的数据修改,你还是应该考虑每30分钟执行一次事务日志备份。如果你根本不能承受丢失任何数据,那么你应该研究一下像这样的解决方案:日志传送、数据库镜像、RAID镜像或提供存储区域网络(SAN)和分割镜像软件的第三方解决方案。这也暗示了你越要保证没有数据丢失,你就得花越多的钱。 随着备份的进行,也应该考虑将备份生成的文件归档到网络上的另一服务器或磁带上。如果SQL Server实例的主机毁坏了,就肯定需要从离线服务器或异地源上备份了。 最后一个需要考虑的重点是SQL Server实例和数据库最多允许多少停机时间。先不管丢失的数据,你能承受花多少时间把所有数据恢复并运行起来?每小时的停机时间会损失商业应用多少钱?如果这个数字很高,则需要考虑在冗余上投资来避免停机的影响。如果一个数据库的恢复操作需要8个小时,可能需要重新评价从备份中恢复是否合适,或者说是否划算。在这种情况下,可以选择使用复制、日志传送、数据库镜像或其他在两个或更多的SQL Server实例之间提供有效数据拷贝的第三方解决方案。故障转移集群也能通过排除单点故障(不包含共享磁盘)帮助提高SQL Server实例的可用性。如果你的硬件损坏了,你在现场有没有可替换的部分?或者是否需要去最近的商店买一个?对于高可用性的需求,需要考虑任何可能发生的单点故障,并且把它们指定为冗余的部分或进程。 作为一名DBA,为了创建SQL Server备份或恢复计划,应该考虑并按照所有在这部分中提到的问题进行操作。退一步讲,你也应该知道在灾难发生的情况下要用到的联系人的详细信息。随后是应该记录的条目列表和备份及恢复策略: l 需要知道每个连接到数据库的应用程序的主要联系人。谁和最终用户保持联络?如果一个数据库被破坏,谁来做从备份恢复数据(和潜在地丢失一些最近的数据更新)的决定,而不是和微软一起解决被破坏的数据? l 如果有备用服务器,你的IT部门中谁负责启用备用服务器并运行它?谁来安装操作系统、移动文件、切换DSN名称等?你是否有这些人的列表以及他们的名片/email/联系信息? l 你是否有硬件供应商及软件提供商的支持计划?你是否有列有注册码、服务代码及电话号码的重要文档? l 你是否有备用部件或可用的备用服务器? l 如果你的整个现场被破坏了,你是否有另一备选的现场?你是否有移动到这个备选现场的操作手册? 如果整个服务器都被破坏了,并且一定要从头开始重建它,则应该了解更多有用的信息。你的公司应该有如下的记录信息(及可用的参考): l 你的团队中谁负责重建服务器?他们能在凌晨两点进行工作吗?他们是否能在你需要他们的时候出现? l 你在哪里存放SQL Server的备份文件?你执行的是怎样的备份类型?它们执行的频度如何? l 是否有其他应用程序安装或配置在SQL Server服务器上?(请记住,除了性能提升,也要保证SQL Server主机为专用的服务器,以减少重新安装第三方或自主开发程序引起的复杂情况。) l 数据库运行在什么版本的操作系统之上?你是否有重新安装操作系统需要的光盘?以及重新安装SQL Server的光盘?你是否有所有必需的注册码? l 你是否记录了安装SQL Server 2005的步骤?要选择何种排序规则?是安装了所有有用的组件(例如Integration Service、Analysis Service)还是只装了数据库引擎? 要运行在SQL Server实例中的数据库和应用程序越多,就需要准备越多的资料去应对最坏的情况。重要的事情是把它们按重要性排列,首先为你公司最重要的数据库制定计划,然后寻求商业伙伴的帮助,保证你的备份及恢复的计划是更新过且有效的。 29.2  备份 在随后的技巧中,我会演示备份SQL Server 2005数据库的各种不同的方式。具体来说,我将演示如何执行完整、事务日志和差异备份。 完整备份(full backup)会生成一个数据库的拷贝。当数据库备份操作执行时,数据库对于数据库活动(当数据库处于在线活动状态)来说仍然处于可用状态。在所有的数据库备份选项当中,完整数据库备份是最耗时间的。完整备份包含备份操作完成时刻的所有数据改变和日志文件。一旦生成一个完整数据库备份,它允许你恢复整个数据库。完整备份是数据恢复计划的核心,而且它是利用事务日志或差异备份的先决条件(稍后你将会看到)。当创建备份时,你可以选择在磁盘上创建文件或直接写到磁带上。通常情况下,当SQL Server 备份直接写入磁盘时,备份执行并完成得更快。一旦创建了备份,可以把它复制到磁带上。 SQL Server 2005 数据库需要事务日志文件。事务日志逐条记录了已经提交的事务,及那些已经打开但仍没有提交的事务。这些文件包含正在进行事务的记录以及数据库内的改变。事务日志备份(transaction log backup)备份了最后的完整备份或事务日志备份完成后发生的事务日志的活动。当备份完成后,SQL Server截断日志不活动的部分(这部分不包含打开的事务活动)。事务日志备份具有低资源消耗的特性,并且可以频繁执行(例如,每15分钟执行一次)。 事务日志备份只可以在置为FULL或BULK_LOGGED恢复模式下的数据库上执行。回忆一下第22章提到的3个数据库恢复模式,它们是FULL、BULK_LOGGED和SIMPLE: l 当置为SIMPLE恢复模式时,SQL Server会自动地截断事务日志,取消了该模式执行事务日志备份的能力。在这个恢复模式下,丢失数据的风险依赖于完整或差异备份的时间安排,并且你将不能执行事务日志备份提供的时点恢复。 l BULK_LOGGED恢复模式允许执行完整备份、差异备份和事务日志备份——但是,在大容量操作时,最小限度的日志被记录到事务日志。这种恢复模式的优点是在执行大容量操作时减少了日志空间的使用,作为代价,事务日志备份只能用来恢复到最后事务日志备份完成的时间(不允许时点恢复及标记事务)。 l 在FULL恢复模式下,完整地记录所有事务活动,包括大容量操作。在这个最安全的模式下,所有的还原选项都是可用的,包括时点事务日志还原、差异备份和完整数据库备份。 除了允许从完成事务日志备份的完成点上执行还原操作,事务日志备份还允许执行时点恢复以及事务标记恢复。时点恢复对于将数据库还原到数据库被修改或出现故障之前的状态是有用的。事务标记允许你恢复到标记了事务的第一个实例(使用了BEGIN TRAN...WITH MARK)并且包括该事务内所做的更新。 事务日志备份文件的大小取决于数据库活动的级别以及你使用了FULL或BULK_LOGGED恢复模式中的何种。再重申一下,SIMPLE恢复模式是不允许事务日志备份的。 警告     尽管RESTORE会在本章中后面的技巧中提及,先理解事务日志备份的顺序和频度会影响数据库恢复计划也是很重要的。 从事务日志备份中恢复,必须先从完整备份中还原,然后再附加事务日志备份。事务日志是积累的,意思是每一个备份都是事务日志备份序列的一部分,而且必须以相同的顺序连续地还原。比如说,你不能在还原完整数据库备份之后就还原第三个事务日志备份,而跳过前两个事务日志备份。 只有在附加完所有你希望按备份的时间顺序附加的事务日志之后,数据库才会被恢复(意思是使其在线并可正常使用)。通过RESTORE命令的RECOVERY和NORECOVERY子句控制恢复,我们会在本章稍后介绍这些内容。 你必须了解已经做过的备份——备份中包含了什么,以及在你能还原它们之前何时执行过备份。在本章稍后我会演示可以用来查看这些信息的各种命令。随后地列表详细地列出了一个典型的备份序列:   时  间        备份类型   上午8点       完整数据库备份   上午10点      事务日志备份   下午1点       事务日志备份 如果希望将数据库恢复到下午1点,首先需要还原上午8点的完整备份,下一步还原上午10点的事务日志备份,最后是下午1点的事务日志备份。如果使用差异备份,必须先还原完整备份,下一步是还原差异备份,然后是在差异备份之后创建的事务日志备份。 差异备份(differential backup)复制最后一次完整备份之后的所有数据和日志页。由于当数据库备份时它是处于在线状态的,因此差异备份包含从备份开始到备份结束时间点发生的数据改变和日志文件。通过差异备份产生的文件通常要比完整数据库备份的小,并且创建得更快。 差异备份不像事务日志备份,它是自包含的并且只需要要还原的数据库最后的完整备份。但是事务日志备份是序列文件并且不包含前一个事务日志备份的数据。例如,如果你在上午8点运行完整备份,在上午10点进行差异备份,并且在下午1点进行一个补充的差异备份,下午1点的差异备份将仍然包含上午8点的完整备份之后发生的数据改变:   时  间        备份类型   上午8点       完整数据库备份   上午10点      差异备份(捕捉上午8点—上午10点的数据改变)   下午1点       差异备份(捕捉上午8点—下午1点的数据改变) 尽管在任何完整备份和差异备份还原之前,事务日志备份不能还原,但是差异备份还是可以与事务日志备份并行工作。 在这组备份技巧的第一个技巧中,我们会演示如何进行一个最简单的完整备份。 29.2.1  执行基本的完整备份 可以使用BACKUP DATABASE命令执行一个完整备份。执行一个完整备份到磁盘的简化语法如下: 此命令的参数说明见表29-1。 表29-1 BACKUP DATABASE 参数 参  数 描  述 database_name | @database_name_var 要备份的数据库名称(可以指定为字符串或局部变量) 'physical_backup_device_name' | @physical_backup_device_name_var 物理路径和文件名,或包含物理路径和文件名的局部变量 [ ,...n ] 你可以为一条BACKUP DATABASE命令指定最多64个备份设备名称 BACKUP命令也包含其他一些选项,大部分选项我们会在本章中演示(这个语法中省略掉的命令参数是SQL Server 2005不建议使用的): 表29-2中描述了这些选项。 表29-2 备份选项 参  数 描  述 BLOCKSIZE 用字节数指定块的大小(几乎没必要设置这个选项) CHECKSUM | NO_CHECKSUM 使用CHECKSUM增加页写入备份文件时的有效性检测。NO_CHECKSUM忽略检测操作(这是默认行为) STOP_ON_ERROR | CONTINUE_AFTER_ERROR STOP_ON_ERROR选项下,如果发现CHECKSUM错误,将停止备份CONTINUE_AFTER_ERROR下,即使发生验证错误,也会继续备份 DESCRIPTION 指定说明备份集的自由格式文本,帮助标识备份设备的内容 EXPIREDATE | RETAINDAYS EXPIREDATE指定备份集到期和允许被覆盖的日期。RETAINDAYS指定必须经过多少天才可以覆盖该备份媒体集 FORMAT | NOFORMAT FORMAT在用于此备份操作的所有卷上写入一个新的媒体标头。既有的标头会被覆盖。如果现有设备中存在条带(条带稍后会讲述),则整个媒体集都将变得不可用。NOFORMAT指定不将媒体标头写入用于此备份操作的所有卷 INIT | NOINIT INIT指定应覆盖所有既有的备份集,但是保留媒体标头。如果备份集没有过期,或BACKUP语句中的备份集名与备份媒体上的名称不匹配,备份集将不会被覆盖,NOINIT表示备份集将追加到磁盘或磁带设备上,NOINIT是默认选项 NOSKIP | SKIP NOSKIP检测日期与名称,它是确认备份不会被不适当地覆盖的额外的安全措施。SKIP禁用备份集的过期和名称检查 MEDIADESCRIPTION 媒体集的自由格式文本说明,用来识别媒体的内容 MEDIANAME 整个备份媒体集的名称,最多为128个字符 (续) 参  数 描  述 NAME 备份集的名称 NOREWIND | REWIND NOREWIND防止应用程序在SQL Server发出BACKUP或RESTORE命令之前使用磁带。有时候磁带设备不支持该选项。REWIND指定SQL Server重绕磁带并释放控制 NOUNLOAD | UNLOAD UNLOAD指定在备份完成后自动重绕并卸载磁带。NOUNLOAD意思是磁带不会在备份完成后从磁带设备中卸载,并且可以被其他备份操作使用 STATS 在备份过程中返回反馈到客户端。默认为每完成10%就返回信息 COPY_ONLY SQL Server 2005的新选项,允许你创建一个不破坏备份序列的数据库备份(例如,完整备份和事务日志备份的日志链) 在这个技巧中,我将执行一个简单的、TestDB数据库的完整数据库备份到一个磁盘设备(文件)上。为了演示BACKUP DATABASE,我先创建一个新的临时数据库,其中包含几个来自AdventureWorks数据库的对象: 下面,新的数据库将被备份: 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了2页。 BACKUP DATABASE成功处理了2474页,花费3.622秒(5.595MB/秒)。 解析 在这个简单的技巧中,我们对TestDB数据库进行了完整备份。代码的第一行指定了要备份的数据库名称: 代码的第二行指定了数据库备份的文件: 我们创建了一个a*.bak扩展名的备份文件。备份的名称显示了日期及军事时间。尽管文件名中包含的时间戳帮助识别备份创建的时间,但它不是必需的。在执行之后,会返回关于处理过的数据页的数量和备份操作花费的时间的信息。 29.2.2  命名和描述备份和媒体 在政府信息规章和保存法的时代,公司的规定可能需要你将数据库备份保留较长一段时间。由于较长的保留周期,备份集元数据变得更加重要。用数据库名和时间戳对数据库备份文件进行命名通常就足够了,可是SQL Server还包含了其他选项,可以利用它们来描述和命名备份。这些选项包括: 这些选项的描述请见表29-3。 表29-3 备份媒体选项 参  数 描  述 DESCRIPTION 说明备份集的自由格式文本,帮助识别备份设备的内容 MEDIADESCRIPTION 媒体集的自由格式文本说明,帮助识别媒体的内容 MEDIANAME 整个备份媒体集的名称,最多为128个字符 NAME 备份集的名称 在前面的表格中提到了两个关于SQL Server备份的术语:备份集和媒体集。简单地说,备份集(backup set)是数据库备份操作的结果。备份集可以跨越一个或多个备份设备(磁盘或磁带)。媒体集(media set)是备份集写入的备份设备的集合。 这个示例演示了为备份和媒体集指定描述和命名的操作: 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了1页。 BACKUP DATABASE成功处理了2473页,花费1.929秒(10.502MB/秒)。 解析 本技巧演示了如何在数据库备份中增加更多的描述信息。通过WITH子句为BACKUP DATABASE命令增加额外的选项。DESCRIPTION描述了备份集: NAME指定了备份集的名称: MEDIADESCRIPTION指定了媒体集的描述: MEDIANAME指定了整个备份媒体集的名称: 这个信息可以通过使用RESTORE命令检索(例如RESTORE HEADERONLY),我们会在本章稍后提及它。 29.2.3  配置备份保持期 在本章的第一个技巧中,如果在备份之前备份文件(设备)不存在,它会在BACKUP命令的执行期间被创建。如果文件已经存在,默认备份过程的行为是在既有的备份文件上附加备份数据(保留文件上的所有其他备份)。 一些BACKUP选项会影响备份集的保持期: 这些选项的描述请见表29-4。 表29-4 备份保持期选项 参  数 描  述 EXPIREDATE | RETAINDAYS EXPIREDATE指定备份集到期和允许被覆盖的日期。RETAINDAYS指定必须经过多少天才可以覆盖该备份媒体集 FORMAT | NOFORMAT FORMAT将在用于此备份操作的所有卷上写入一个新的媒体标头。既有的标头会被覆盖。如果现有设备中存在条带(条带稍后会讲述),则整个媒体集都将变得不可用。NOFORMAT指定不应将媒体标头写入用于此备份操作的所有卷 INIT | NOINIT INIT指定应覆盖既有备份集,但是保留媒体标头。如果备份集没有过期,或BACKUP语句中的备份集名与备份媒体上的名称不匹配,备份集将不会被覆盖,NOINIT表示备份集将追加到磁盘或磁带设备上,NOINIT是默认选项 NOSKIP | SKIP SKIP禁用备份集的过期和名称检查。NOSKIP检测日期与名称,而且它是确认备份不会被不适当地覆盖的额外的安全措施 这个技巧演示了执行一个备份集保持期设置为30天的完整数据库备份,在这个期限后备份集才可以被覆盖: 现在我们尝试覆盖一个既有的备份,这个备份在TestDB_Oct.bak文件上: 这个查询返回的信息是: 消息4030,级别16,状态1,第1行 设备'C:\Apress\Recipes\TestDB_Oct.bak'上的媒体于07 10 2007  4:10:55:000PM过期,无法覆盖。 消息3013,级别16,状态1,第1行 BACKUP DATABASE 正在异常终止。 解析 在这个技巧中,我们创建了一个新的、备份集保持期为30天的数据库备份。在备份创建之后,又执行了另一个备份,这次使用了INIT开关(这个操作将覆盖既有的备份集)。这个尝试失败了,返回了一个关于备份集尚未过期的错误警告,所以备份集不能被覆盖。 29.2.4  条带化备份集 条带化(stripping)备份使用一个以上的设备(磁盘或磁带)进行单个的备份集操作。事实上,当执行数据库备份时,可以在备份操作中使用最多64个设备(磁盘或磁带)。这个特性在备份非常大的数据库时是非常有用的,因为你能通过在分离的磁盘/阵列条带化备份文件增强备份性能。条带化备份文件意思是说同时以对称的方式写入每个文件。条带化备份使用并行写操作,可以显著提高备份操作的速度。 此技巧演示了在3个磁盘设备上条带化备份: 这个备份创建了3个文件,每个文件存储了三分之一还原数据库时需要的备份信息。 如果尝试在其中任何一个设备上进行备份,你将得到错误消息,正如下一个示例所演示的: 这个查询返回: 消息3132,级别16,状态1,第1行 媒体集有3个媒体簇,但只提供了1个。必须提供所有成员。 消息3013,级别16,状态1,第1行 BACKUP DATABASE正在异常终止。 解析 在这个技巧中,备份使用了3个设备,它也被称作媒体簇(media families)。这3个媒体簇被用作单个的媒体集,这个媒体集可以包含一个或多个备份集。在创建了由3个媒体簇组成的媒体集之后,技巧的第二部分尝试在一个既有的媒体簇上进行备份操作。这将引发一个错误,因为在文件被格式化(使用WITH FORMAT)之前,必须同时使用它们而不可以在备份操作中单独使用。 29.2.5  使用命名的备份设备 你可以在BACKUP或RESTORE命令中定义磁带或磁盘的逻辑名称。定义设备会把它添加到sys.backup_devices目录视图,不用再键入磁盘路径和文件或磁带名称。 为了增加一个新的备份设备定义,可以使用sp_addumpdevice系统存储过程: 这些命令的参数描述请见表29-5。 表29-5 sp_addumpdevice参数 参  数 描  述 device_type 用来指定设备类型:disk或tape logical_name 将在BACKUP和RESTORE中使用的备份设备的名称 physical_name 操作系统文件名(遵守通用命名约定(UNC)的名称)或磁带路径 controller_type 不再需要的参数:2代表磁盘,5代表磁带 device_status 这个选项确定ANSI磁盘标志是可读(noskip)还是被忽略(skip),在应用之前,noskip是磁带类型的默认值。该选项和controller_type只能指定其中一个,不能都指定 要查看备份设备的定义,可以使用sp_helpdevice系统存储过程,它只包含一个参数logical_ name: 删除备份设备使用sp_dropdevice: 第一个参数是备份设备的名称,当第二个参数指定了DELFILE时,将删除实际的备份设备文件。 在示例中的第一部分,创建了一个名为TestDBBackup的备份设备,它被映射到C:\Apress\Recipes\ TestDB_Device.bak文件上: 这个查询返回: 命令已成功完成。 下一步,使用sp_helpdevice查询关于设备的信息: 这个查询返回: 下一步,执行基于该设备的备份: 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了1页。 BACKUP DATABASE成功处理了2473页,花费1.887秒(10.735MB/秒)。 最后,我们使用sp_dropdevice删除该设备(如果没有指定第二个选项DELFILE,物理备份文件还将保留在操作系统内): 这个查询返回: 设备已除去。 解析 在这个技巧中,我演示了如何创建一个命名的备份设备,它允许你不去键入你需要在BACKUP或RESTORE命令中指定的完整磁盘或磁带名称。 在此技巧中的第一个示例使用sp_addumpdevice创建了一个设备。存储过程的第一个参数设置设备类型为disk。第二个参数是设备的逻辑名称,第三个参数是实际的物理文件路径和名称。此技巧的第二个查询演示了使用sp_helpdevice来返回设备的信息。信息包括与设备描述相关的status字段,以及指定设备类型(2代表磁盘设备,5代表磁带)的cntrltype列。此技巧中的第三个查询演示了在备份中使用设备,它只是指定了设备名而不是使用DISK或TAPE选项,在此技巧的最后一个查询中,我们使用sp_dropdevice删除了该设备。 29.2.6  镜像备份集 SQL Server 2005引入了新功能,现在你可以镜像一个数据库备份、日志备份、文件或文件组备份。镜像通过创建2个、3个或4个媒体集的副本创建备份冗余。因为你可以使用其他任何一个可用的镜像媒体集,如果某个媒体集被破坏或不可用,此冗余就可以派上用场了。 语法如下: MIRROR TO命令用来连接一个或多个备份设备列表,并且最多支持3个镜像。在这个示例中,一个备份被镜像为3个不同的副本。不像之前的条带化的示例,在数据库恢复操作中实际只需要一个生成的备份文件。但是如果该文件不可用了,可以尝试从其他3个副本中还原: 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了1页。 BACKUP DATABASE成功处理了2473页,花费4.966秒(4.079MB/秒)。 这第二个示例演示了镜像一个条带化备份: 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了1页。 BACKUP DATABASE成功处理了2473页,花费3.679秒(5.506MB/秒)。 解析 在此技巧的第一个示例中,我们将一个备份生成了3个镜像。它为TestDB数据库生成了4个备份文件。第一行代码指定了要备份的数据库: 第二行代码指定了主(非镜像)备份文件的位置: 后面的3行指定了备份的3个镜像副本: 注意在第一次创建镜像备份集时,需要使用WITH FORMAT。原始备份放置到了C盘下,然后每个镜像的副本分别放置到它们自己所在的盘符(D、E、F)下。在此示例中的任何一个 .bak文件都可用来还原TestDB数据库,从而为备份文件损坏的情况提供了冗余。 在此技巧中的第二个示例演示了镜像一个条带化的备份(在一个媒体集中有两个媒体簇)。这次 TO DISK包含了用来条带化原始备份的两个文件: MIRROR TO DISK也指定了两个文件,它们用来放置原始条带化备份的镜像副本: 注意MIRROR TO DISK只指定了一次,跟随在它后面的是镜像操作的两个设备。 29.2.7  执行事务日志备份 BACKUP LOG命令用来执行事务日志备份。随后是执行事务日志备份的基本语法: 正如你能从语法中看到的,BACKUP LOG用了许多与BACKUP DATABASE相同的选项和功能。本章还没有讲到的选项以及事务日志备份特有的选项在表29-6中描述: 表29-6 备份日志选项 参  数 描  述 NO_TRUNCATE 如果数据库损坏了,NO_TRUNCATE允许通过不截断不活动的部分(不活动的部分包含已提交的一批事务)来备份事务日志。我们经常在紧急的事务日志备份中用到它,它可以捕捉在RESTORE操作之前的活动事务。不要长期在这种方式下运行,因为日志文件容量将会一直增长 NORECOVERY | STANDBY = undo_file_name NORECOVERY备份事务日志的尾部,然后让数据库处于RESTORING状态(它是一个还会有附加RESTORE命令执行的状态)。STANDBY也备份事务日志的尾部,但不会将数据库处于RESTORING状态,而把它置为只读STANDBY状态(用于日志传送)。此选项需要指定一个文件,如果进行了日志还原操作,该文件将保存产生回滚所做的更改 在此技巧的第一个查询中,我们将执行TestDB数据库的事务日志备份: 这个查询返回: 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了21页。 BACKUP LOG成功处理了21页,花费0.186秒(0.924MB/秒)。 此技巧中的第二个例子演示了在事务日志尾部生成一个事务日志备份的操作。我们假定数据库发生了错误——执行“尾部”的备份意味着备份数据库最新的事务,而没去截断事务日志中不活动的部分: 解析 在此技巧中,我演示了两个关于事务日志备份的示例。注意,除非数据库以前进行过完整备份,否则BACKUP LOG不可以执行。而且在两个示例中,数据库必须使用FULL或BULK_LOGGED恢复模式: 第二行代码指定了备份操作的设备: 在备份完成之后,生成了一个文件,并且事务日志中不活动的部分被自动截断了。在第二个查询中,指定了WITH NO_TRUNCATE选项,它允许备份事务日志中活动的部分,而且并不截断事务日志中不活动的部分。 在本章中,稍后你会学习到如何从事务日志文件中还原数据,包括如何使用时点恢复。 29.2.8  使用COPY ONLY备份集 SQL Server 2005引入了新功能,现在数据库和事务日志备份可以使用COPY_ONLY选项来创建备份,这些备份不影响正常的备份序列。你将会在本章随后的技巧中看到,差异备份和事务日志备份都依赖于之前执行的完整备份。无论何时创建了其他的完整备份,备份序列都将重新开始。它的意思是之前生成的差异或日志备份不能使用后来生成的完整备份。只有那些在完整备份生成之后创建的差异或事务日志备份才可以使用。 但是,当使用COPY_ONLY选项时,完整备份不会破坏备份序列。这在有重大数据库改变之前需要创建特殊备份时是有用的,此时你不希望破坏标准的备份序列,但可能希望做可以有效RESTORE的“以防万一”的完整备份。这个示例演示如何对完整数据库备份使用COPY_ONLY: 当在事务日志备份中使用COPY_ONLY时,在备份创建之后事务日志不会被截断(保留未破坏的事务日志备份链)。这个示例演示了如何在事务日志备份中使用COPY_ONLY: 解析 此技巧演示了使用COPY_ONLY创建完整备份和事务日志备份。语法与前面的技巧中的相似,不同之处就是COPY_ONLY包含在WITH子句中。使用了该选项的完整数据库备份不会破坏恢复操作所需的、之前生成的事务日志或差异备份序列。使用了COPY_ONLY的事务日志备份也不会破坏其他事务日志备份的时间顺序。 29.2.9  执行差异备份 在这个随后的技巧中,我演示了如何创建差异备份。回顾一下本章中前面提到的差异备份,它用来备份在最后一次完整备份之后发生改变的所有数据和日志页。这点与事务日志备份不同,事务日志备份仅捕捉在最后事务日志和/或完整数据库备份之后发生的改变。 差异备份通过使用BACKUP DATABASE来执行,并且使用了与常规完整数据库备份相同的语法和功能——只包括DIFFERENTIAL关键字。此技巧演示了在TestDB数据库上创建一个差异备份: 这个查询返回: 已处理百分之58。 已处理百分之78。 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了40页。 已处理百分之100。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了1页。 BACKUP DATABASE WITH DIFFERENTIAL成功处理了41页,花费0.385秒(0.871MB/秒)。 解析 在此技巧中,我们在TestDB数据库上创建了一个差异备份。命令的用法与前面技巧中的相似,只是这次WITH子句中包含了DIFFERENTIAL关键字。还使用了其他两个选项(当使用其他备份类型时也适用):NOINIT,附加备份集到既有的磁盘或磁带设备上,以及STATS,在备份过程中返回反馈信息到客户端。 差异备份在完整数据库备份之后才可执行,所以对于新的数据库,差异备份并不是初始的备份方法。 29.2.10  备份单个文件或文件组 对于超大型数据库来说,如果完整备份的时间超过你的备份时间间隔,可以使用另一个备份指定文件组或文件的选项来减少备份时间。这个选项允许在丢失文件或文件组的情况下执行恢复操作。为了在激活读写功能的数据库上执行文件或文件组备份,数据库必须设置为完整或大容量日志恢复模式,事务日志备份必须在恢复文件或文件组的操作后执行。 除了需要使用FILEGROUP或FILE关键字及可以指定用逗号分隔的多个文件或文件组之外,备份文件或文件组的操作实际上使用了与完整数据库备份操作相同的语法。 为了演示备份文件组的操作,先创建一个新数据库,它使用一个称为FG2的辅助文件组: 第一个例子创建了一个文件组备份: 它返回如下结果: 已为数据库'VLTestDB',文件'VLTestDB2'(位于文件1上)处理了8页。 已为数据库'VLTestDB',文件'VLTestDB3'(位于文件1上)处理了8页。 已为数据库'VLTestDB',文件'VLTestDB_log'(位于文件1上)处理了2页。 BACKUP DATABASE...FILE=成功处理了18页,花费0.231秒(0.618MB/秒)。 第二个例子演示了备份此数据库的两个指定文件的操作。为了先得到文件名称列表,执行sp_helpfile: 它返回如下结果: 使用sp_helpfile结果中的逻辑文件名,这个示例演示了备份TestDB数据库中的TestDB3文件的操作: 这个查询返回: 已为数据库'VLTestDB',文件'VLTestDB2'(位于文件1上)处理了8页。 已为数据库'VLTestDB',文件'VLTestDB3'(位于文件1上)处理了8页。 已为数据库'VLTestDB',文件'VLTestDB_log'(位于文件1上)处理了2页。 BACKUP DATABASE...FILE=成功处理了18页,花费0.320秒(0.456MB/秒)。 解析 此技巧从演示备份指定文件组的操作开始。这个语法除了需要指定FILEGROUP,几乎都和常规的完整数据库备份相同: 第二个示例演示了使用FILE选项备份两个指定文件的操作,本例中备份了两个数据库文件: 从文件组或文件备份中还原的操作将在本章稍后进行演示。 29.2.11  执行部分备份 也是SQL Server 2005带来的新特性,部分备份(partial backup)自动创建数据库中主文件组和所有激活读写功能的文件组的备份。如果备份存在只读文件组的数据库,部分备份将只对主文件组进行备份。这个选项对于那些存在只读文件组的超大型数据库是理想的,它不需要像那些可写的文件组备份得那么频繁。 除了需要指定READ_WRITE_FILEGROUPS选项外,执行部分备份的语法几乎和完整备份的相同。如果想备份只读文件或文件组,也可以显式地指定它们。 在第一个示例中,只使用了READ_WRITE_FILEGROUPS选项(在这个示例中,文件组FG3是只读的): 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了1页。 BACKUP DATABASE...FILE=成功处理了2473页,花费2.408秒(8.413MB/秒)。 在第二个例子中,在部分数据库备份中显式地包含了只读文件组。为了准备进行这个示例,VLTestDB的FG2文件组将被设置为READONLY: 返回的信息是: 文件组属性'READONLY'已设置。 现在,只读文件组被显式地包含在部分数据库备份中: 返回的信息是: 已为数据库'VLTestDB',文件'VLTestDB'(位于文件1上)处理了176页。 已为数据库'VLTestDB',文件'VLTestDB2'(位于文件1上)处理了8页。 已为数据库'VLTestDB',文件'VLTestDB3'(位于文件1上)处理了8页。 已为数据库'VLTestDB',文件'VLTestDB_log'(位于文件1上)处理了2页。 BACKUP DATABASE...FILE=成功处理了194页,花费0.406秒(3.896MB/秒)。 解析 只读文件组包含了不能写入的文件。因为只读数据不能改变,只需要周期性地对它进行备份(为了接受更新将它改为可读写的)。对于超大型数据库,对只读文件组做不必要的备份会耗尽时间和磁盘空间。新的部分数据库备份选项允许只对主文件组和所有可写文件组和文件进行备份操作,并不需要显式地列出所有的文件组。如果希望在备份中包含只读文件组,你仍然可以做到。 在此技巧的第一个示例中,使用了READ_WRITE_FILEGROUPS选项,而没有指定任何文件,它备份了所有主数据文件和可写的文件,而省略了FG3文件组中的TestDB4只读文件。在此技巧的第二个例子中,通过指定FILEGROUP选项将文件组包含在备份中。 在这两个示例中,从部分备份进行数据库恢复的操作也假定你有对省略的文件进行的文件组或文件备份。本章稍后会演示从部分备份进行恢复操作。 29.2.12  查看备份元数据 一旦创建了备份,可以通过各种各样的RESTORE函数来查看媒体集的内容,包括RESTORE FILELISTONLY、RESTORE HEADERONLY、RESTORE VERIFYONLY和RESTORE LABELONLY: l RESTORE LABLEONLY用来返回关于在指定备份设备上的备份媒体的信息。 l RESTORE HEADERONLY为所有创建在指定设备上的备份集返回一行。 l RESTORE FILELISTONLY更深层次地显示数据库文件名(逻辑的、物理的)以及其他备份数据库的信息。 l RESTORE VERIFYONLY预先验证备份设备以汇报RESTORE操作是否可以无错误地成功执行。 这4个命令的语法非常相似。我把这4个命令组合到一个语法块中,但是注意并不是所有的参数都可以应用到任何命令中(例如MOVE参数只能使用在RESTORE VERIFYONLY命令中): 表29-7是此命令参数的描述。 表29-7 备份元数据的RESTORE参数 参  数 描  述 backup_device 要检验的备份设备(磁盘或磁带) CHECKSUM | NO_CHECKSUM 使用CHECKSUM将增加对页写入备份文件的有效性检验NO_CHECKSUM忽略此检验(它是默认操作) CONTINUE_AFTER_ERROR | STOP_ON_ERROR CONTINUE_AFTER_ERROR在遇到检验错误后将继续执行操作STOP_ON_ERROR如果发现CHECKSUM错误将停止执行操作 file_number 用在FILELISTONLY、VERIFYONLY和HEADERONLY(没有LABELONLY)上。这个数字标识要查看的备份集,“1”指示写入媒体中的第一个备份集,“2”指示第二个备份集,依此类推 LOADHISTORY 使用在VERIFYONLY命令中,当它被指定,关于备份集及恢复操作的信息将被保存到msdb系统数据库的历史数据表中 (续) 参  数 描  述 media_name | @media_name_varia- ble 要检验的媒体集名称 MOVE 'logical_file_name' TO 'operating_ system_file_name' 应用在VERIFYONLY上,MOVE允许你检验还原数据库到最初备份它的不同的文件名和/或路径的操作是否会成功 REWIND REWIND让SQL Server重绕磁带并释放控制 STATS 应用在VERIFYONLY上。在执行过程中返回反馈到客户端。默认为每完成10%就返回 UNLOAD | NOUNLOAD UNLOAD指定磁带在检验之后自动地重绕并卸载。NOUNLOAD意思是磁带不会在检验之后从磁盘设备上卸载,并且它可以使用在其他备份操作上 在大多数情况下,可以在编写实际的RESTORE DATABASE操作之前使用这些RESTORE命令去识别设备的内容。 在此技巧的第一个示例中,从TestDB.bak设备中返回媒体集信息: 它返回如下(部分)结果: 在第二个查询中,检验了相同的设备,查看在它上面存在什么备份集: 它返回如下(部分)结果: 在此技巧的第三个示例中,检验了在设备的备份集中的独立备份文件: 它返回如下(部分)结果: 在此技巧中最后的示例中,验证了备份设备RESTORE的有效性: 这个查询返回: 文件1上的备份集有效。 解析 这4个命令RESTORE FILELISTONLY、RESTORE HEADERONLY、RESTORE VERIFYONLY和RESTORE LABELONLY对于在执行RESTORE操作之前收集所需要的信息时,每一个都是有用的。 在此技巧的第一个示例中,使用RESTORE LABELONLY返回指定备份设备的媒体集信息。 第二个示例使用了RESTORE HEADERONLY去查看在设备上实际存在什么备份集,因此当还原时可以指定要还原的备份集文件编号(也让你确保从正确的时期和备份类型中还原)。 此技巧第三个示例中使用了RESTORE FILELISTONLY返回备份在设备中的备份集中的实际的数据库文件。如果你希望还原数据库到其他服务器,这是尤为有用的信息,因为新服务器和旧服务器对应的盘符和目录结构可能会不同。在本章稍后的技巧中,你将学习如何在执行还原操作时移动数据库文件的位置。 最后的示例检验备份设备,确认它在RESTORE DATABASE操作中是否有效。通过使用FILE = 1指定了备份集。同时,通过使用LOADHISTORY选项将关于备份集的历史数据保存在msdb系统数据库中 29.3  还原数据库 在本章的第一部分我们集中介绍了如何备份数据库,包括如何执行完整备份、事务日志备份、差异备份以及文件和文件组备份。本章的第二部分将讲述如何从备份文件中还原数据库。还原操作将备份媒体集中的数据、日志、索引页复制到目标数据库。目标数据库可以是既有的数据库(它将会被覆盖)或新数据库(将创建基于备份的新文件)。在还原操作后,跟随了“重做”阶段,它前滚在数据库备份结束时已提交的事务。在它之后,“撤销”阶段回滚未提交的事务(在SQL Server 2005中,数据库在“撤销”阶段开始后对用户是可用的)。 随后的一组技巧将演示数据库还原操作。 29.3.1  从完整备份还原数据库 在这个技巧中,我将演示如何通过使用RESTORE命令从完整数据库备份中还原数据库。不像BACKUP操作那样,RESTORE不总是可在线进行的操作——对于完整数据库还原来说,在还原数据库操作之前,用户连接必须要从数据库断开。其他还原类型(比如文件组、文件或新“页面”选项)可以允许其他未受还原操作影响的地方的数据库中存在在线活动。例如,如果文件组“FG2”正在进行还原操作,“FG3”仍然可以在操作中访问。 注解  在线还原是SQL Server 2005企业版的特性。 在常规的操作中,你可能需要在用户错误引发的数据丢失、文件损坏、需要一份数据库的副本或移动数据库到新的SQL Server实例中的情况下还原数据库。 随后是RESTORE命令的语法: 表29-8是对此命令参数的描述。 表29-8 RESTORE参数 参  数 描  述 database_name | @database_name_var 希望还原数据库到的数据库名称或字符变量(可以为新数据库名称或覆盖既有的数据库) backup_device 备份设备的名称(命名的备份设备,磁带或磁盘) CHECKSUM | NO_CHECKSUM 使用CHECKSUM增加页写入备份文件的有效性检验。NO_CHECKSUM忽略这个检验(它是默认行为) CONTINUE_AFTER_ERROR | STOP_ON_ERROR CONTINUE_AFTER_ERROR让还原操作在发现验证错误后继续执行。STOP_ON_ERROR如果CHECKSUM发现错误将停止还原操作 file_number | @file_number 希望还原的备份集的文件编号 KEEP_REPLICATION 防止在还原时删除复制设置(可在日志传送操作中使用) media_name | @media_name_variable 如果提供了媒体名称,该名称必须与备份卷上的媒体名称相匹配。否则检验不会通过 MOVE 'logical_file_name' TO ' operating_system_file_name' MOVE允许将数据库还原至不同的文件名和/或目录上 { RECOVERY | NORECOVERY | STANDBY = {standby_file_name | @standby_file_name_var } NORECOVERY让数据库保持还原状态,允许应用后续差异或事务日志备份。RECOVERY指定不需要再应用后续差异或事务日志。RECOVERY是默认选项,它启动了重做及撤销阶段。STANDBY状态与日志传送操作一起使用,指定数据库为主服务器的温备用服务器。数据库将接收并应用事务日志——直到由于主数据库出现故障,必须将数据库设为在线状态 REPLACE 用RESTORE DATABASE database_name中指定的数据库名称覆盖既有的数据库 RESTART 如果RESTORE过早地停止,RESTART允许你继续还原操作。建议在长时间的RESTORE操作中使用(比如几个小时的操作) RESTRICTED_USER 限制只有db_owner、dbcreator或sysadmin角色的成员才能访问新近还原的数据库 REWIND | NOREWIND NOREWIND指定在还原操作后,磁带不会被重绕。REWIND在还原结束时将重绕磁带 (续) 参  数 描  述 STATS [ = percentage ] 返回RESTORE操作进度百分数 STOPAT = { date_time | @date_time_var } STOPAT允许你从事务日志备份中恢复到指定的时间点 STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }[ AFTER datetime ] STOPATMARK恢复到第一个实例中标记的事务,并且包括这个事务产生的更新。指定STOPATMARK = ‘mark_name’ AFTER datetime恢复到第一个实例中标记的事务或恢复到指定的datetime之后 STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }[ AFTER datetime ] STOPBEFOREMARK还原数据库到第一个实例中标记的事务,不包括所有在这个事务中的活动。STOPBEFOREMARK AFTER datetime将还原数据库到第一个实例中标记的事务,或还原到指定的datetime之后,不包括这个事务 UNLOAD | NOUNLOAD NOUNLOAD不会在还原之后卸载磁带。UNLOAD在还原结束时卸载磁带 此技巧中第一个示例是一个从设备上最新的备份集中RESTORE的简单操作(在这个示例中,TestDB数据库在设备上存在两个备份集,并且你希望使用第二个备份集)。为了进行这个示范,你要先在一个设备上创建两个完整备份: 过了一段时间,我们在这个设备上创建另一个备份   现在使用设备上的第二个备份还原数据库(注意使用REPLACE参数告知SQL Server去覆盖存在的TestDB数据库): 它返回如下的输出: 已为数据库'TestDB',文件'TestDB'(位于文件2上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件2上)处理了1页。 RESTORE DATABASE成功处理了2473页,花费2.502秒(8.097MB/秒)。 在第二个示例中,通过从TestDB备份中还原来创建新数据库,创建的新数据库叫做TrainingDB1。注意使用MOVE参数去指定新数据库文件的位置: 这个查询返回: 已为数据库'TrainingDB1',文件'TestDB'(位于文件2上)处理了2472页。 已为数据库'TrainingDB1',文件'TestDB_log'(位于文件2上)处理了1页。 RESTORE DATABASE成功处理了2473页,花费2.446秒(8.282MB/秒)。 在此技巧最后的示例中,从条带化备份集中还原TestDB数据库(基于在本章前面创建的条带化备份集): 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了2472页。 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了2页。 RESTORE DATABASE成功处理了2474页,花费3.762秒(5.387MB/秒)。 解析 在第一个示例中,通过设置数据库到master数据库启动查询。这是因为完整RESTORE不是在线操作,而且为了运行还原操作,需要确认要还原的数据库上没有活动的连接。 RESTORE还原到TestDB数据库,并且将TestDB_Oct_15_2005.bak备份设备上的第二个备份集末尾的数据覆盖到当前的数据库。命令的第一行说明了要RESTORE的数据库: 这个示例中的第二行指定了备份设备的位置: 本示例中的最后一行指定RESTORE从哪个备份设备的备份集中进行(回顾本章前面讲到的,可以使用RESTORE HEADERONLY查看备份设备中有哪些备份集): 在最后一次备份后更新的所有数据都将丢失,由此这个示例假定数据丢失是可接受的,并且恢复最后一次备份的数据。 在第二个示例中,基于从另外的数据库的RESTORE操作创建了新数据库。这个示例与前面的查询相似,只是这次使用了MOVE命令去指定新数据库文件的位置(并且也使用了新的数据库名): 可以使用RESTORE FILELISTONLY(前面演示过)返回备份数据库的逻辑名称和物理路径。 提示     RESTORE...MOVE命令经常被用来通过使用不同的盘符和目录,将数据库迁移到另外的SQL Server实例。 在此技巧中最后的示例中,从条带化备份集中还原TestDB。为集合中的每个磁盘设备重复运用了FROM DISK: 在所有这些示例中,数据库被还原到了已恢复状态,意思是在重做阶段之后(并且在撤销阶段当中或之后),数据库对用户查询来说是在线的并且可用的。在随后的技巧中,你将看到在差异或事务日志备份被还原之前,数据库经常不是已恢复状态。 29.3.2  从事务日志备份还原数据库 事务日志还原需要在完整数据库还原基础上进行,并且如果你应用了多个事务日志,必须按时间顺序应用它们(基于事务日志备份何时生成)。不按顺序应用事务日志或是在备份时有间隔都是不允许的。还原事务日志的语法用RESTORE LOG而不是RESTORE DATABASE,不过它们的语法和选项都是一样的。 为了建立这个示范,创建了一个名为TrainingDB的新数据库: 新建一张表并且添加一些数据   数据库将执行一个完整备份和两个连续的事务日志备份: 经过两个小时后,运行了另外一个事务日志备份   第一个RESTORE示例假定要还原到的数据库没有存在连接。但是,在示例中我还是演示了在执行RESTORE之前如何踢掉所有在数据库上的连接: 踢掉所有其他连接   下一步,从备份中还原一个数据库备份和两个事务日志备份: 这个查询返回: 已为数据库'TrainingDB',文件'TrainingDB'(位于文件1上)处理了1672页。 已为数据库'TrainingDB',文件'TrainingDB_log'(位于文件1上)处理了1页。 RESTORE DATABASE成功处理了1673页,花费1.898秒(7.217MB/秒)。 已为数据库'TrainingDB',文件'TrainingDB'(位于文件1上)处理了0页。 已为数据库'TrainingDB',文件'TrainingDB_log'(位于文件1上)处理了1页。 RESTORE LOG成功处理了1页,花费0.031秒(0.115MB/秒)。 RESTORE LOG成功处理了0页,花费0.051秒(0.000MB/秒)。 在第二个示例中,我使用了STOPAT去还原数据库和事务日志到指定的时间点。为了演示它,首先要做一个TrainingDB数据库的完整备份: 下一步,从表中删除一些数据,然后查询在删除操作后的当前时间: 这个查询返回: (228行受影响) (1行受影响) 2005-10-14 20:18:57.583 下一步,执行事务日志备份: 这个查询返回: 已为数据库'TrainingDB',文件'TrainingDB_log'(位于文件1上)处理了11页。 BACKUP LOG成功处理了11页,花费0.249秒(0.359MB/秒)。 下一步,从备份中还原数据库,为了也可以还原事务日志备份,我们使用了NORECOVERY选项: 下一步,还原事务日志,还指定了在数据删除前一秒的时间(2005-10-14 20:18:57.583发生的删除操作): 下一步,随后的查询确认你还原的数据库恰好发生在2005-10-14 20:18:57.583的数据删除操作之前: 这个查询返回: 228 解析 在此技巧的第一个示例中,从完整数据库备份中还原TrainingDB数据库,并将其置为NORECOVERY模式。置为NORECOVERY模式允许附加其他事务日志或差异备份。在这个示例中,按照时间顺序附加了两个事务日志备份,在使用RECOVERY选项的那一瞬间,数据库置为在线状态。 此技巧的第二个示例演示了将数据库还原到一个指定时间点的操作。时点恢复在将数据库还原到数据库发生修改或是故障之前的时候是有用的。语法与前一个示例中的类似,只是在RESTORE DATABASE和RESTORE LOG中使用了STOPAT。每个RESTORE语句包含STOPAT确保了还原操作不会越过指定的时间恢复。 29.3.3  从差异备份还原数据库 差异数据库还原的语法与完整数据库还原的相同,只是完整数据库还原必须要执行在附加差异备份之前。当还原完整数据库备份时,数据库必须置为NORECOVERY模式。而且任何你希望还原的事务日志必须在附加了差异备份之后完成,正如示例所演示的。 首先,我通过对TrainingDB数据库执行完整备份、差异备份、事备日志备份来启动这个示例: 又过了一段时间   过了一段时间   现在,我将演示执行RESTORE,将数据库还原到最后事务日志备份完成时的状态: 事务日志备份   差异备份   完整数据库备份   这个查询返回: 已为数据库'TrainingDB',文件'TrainingDB'(位于文件1上)处理了1672页。 已为数据库'TrainingDB',文件'TrainingDB_log'(位于文件1上)处理了3页。 RESTORE DATABASE成功处理了1675页,花费1.801秒(7.615MB/秒)。 已为数据库'TrainingDB',文件'TrainingDB'(位于文件1上)处理了40页。 已为数据库'TrainingDB',文件'TrainingDB_log'(位于文件1上)处理了1页。 RESTORE DATABASE成功处理了41页,花费0.724秒(0.463MB/秒)。 RESTORE LOG成功处理了0页,花费0.184秒(0.000MB/秒)。 解析 差异备份捕捉数据库在最后一次完整数据库备份之后发生的变化。差异还原使用了与完整数据库还原相同的语法,只是它们必须总是在完整数据库还原(设置NORECOVERY)之后。在此技巧中,数据库最初从完整数据库还原,然后是差异备份的还原,最后是事务日志备份的还原。差异RESTORE命令的组成与前面RESTORE示例中的相似,只不过它是基于差异备份文件的。在最后一次还原操作时,指定RECOVERY选项使数据库处于可用状态。 29.3.4  还原文件或文件组 还原文件或文件组几乎使用了和完整数据库还原相同的语法,只是你还使用了FILEGROUP或FILE关键字。为了执行一个指定可读写文件或文件组的还原操作,你的数据库必须设置为完整或大容量恢复模式。这是因为附加事务日志备份的操作必须在还原文件或文件组备份之后运行。在SQL Server 2005中,如果你的数据库设置为简单恢复模式,那么只有只读文件或只读文件组才可进行文件/文件组备份和还原。 为了启动此技巧的示例,对VLTestDB数据库进行文件组备份: 过一段时间后,对数据库进行事务日志备份: 下一步,从备份中还原数据库的FG2文件组,然后还原事务日志备份: 这个查询返回: 已为数据库'VLTestDB',文件'VLTestDB2'(位于文件1上)处理了8页。 已为数据库'VLTestDB',文件'VLTestDB3'(位于文件1上)处理了8页。 RESTORE DATABASE ... FILE=成功处理了16页,花费0.057秒(2.299MB/秒)。 已为数据库'VLTestDB',文件'VLTestDB2'(位于文件1上)处理了0页。 已为数据库'VLTestDB',文件'VLTestDB3'(位于文件1上)处理了0页。 RESTORE LOG成功处理了0页,花费0.022秒(0.000MB/秒)。 解析 文件组或文件备份经常在非常大的数据库中使用,对它进行完整数据库备份可能会花费非常长的时间。使用文件组或文件备份会增大管理的复杂度,因为你必须在事故发生后使用多个备份集去进行恢复(比如说每个文件组一个备份集)。 在这个技巧中,从备份设备中还原名称为FG2的VLTestDB数据库文件组,并且为了可以附加事务日志还原操作设为NORECOVERY模式。为了将文件组恢复到在线状态,在事务日志还原操作使用了RECOVERY关键字。在SQL Server 2005企业版中,不同于主文件组的文件组可以在还原操作中被置为离线状态,保持其他活动文件组为可用状态(这被称为ONLINE还原)。 29.3.5  执行部分(PARTIAL)还原 在段落还原方式中,可以在RESTORE DATABASE命令中使用PARTIAL命令去还原辅助文件组。这个RESTORE的变种将主文件组置为在线状态,然后让你在有需要的情况下还原其他文件组。如果使用置为完整或大容量日志恢复模式的数据库,则可以在可读写文件组上使用这个命令。如果数据库置为简单恢复模式,则只能在只读辅助文件组上使用PARTIAL。 在这个示例中,通过使用PARTIAL关键字从完整数据库备份中还原VLTestDB,并且指定只有PRIMARY文件组置为在线状态(同时FG2和FG3文件组保持离线和未还原状态)。 首先,为了启动这个示例,备份VLTestDB中的主文件组和FG2文件组: 在此之后,执行事务日志备份: 下一步,执行部分RESTORE,只恢复PRIMARY文件组: 另一个文件组FG2现在包含不可用的文件。可以通过从VLTestDB数据库查询sys.database_files查看文件的状态: 这个查询返回: 解析 在这个技巧中,从完整备份中还原VLTestDB,只还原了PRIMARY文件组。WITH子句包含了PARTIAL关键字和NORECOVERY,由此可以还原事务日志备份。在事务日志还原之后,所有在PRIMARY文件组中的对象都将可用,并且在还原辅助文件组之前,在辅助文件组中的对象都是不可用的。 对于非常大的数据库,在RESTORE操作中使用PARTIAL关键字允许以优先顺序排列文件组并载入那些高优先级的文件组,使它们更快地变为可用。 29.3.6  还原页面 SQL Server 2005引入了对置为FULL或BULK_LOGGED恢复模式的数据库还原指定数据页的能力。在很少发生的、数据库中少许数据页被破坏的情况下,还原单个数据页可能比还原整个文件、文件组或整个数据库更为有效。 还原指定页面的语法与还原文件组或还原数据库相似,只是需要使用PAGE关键字和页ID。在msdb.dbo.suspect_pages系统表、SQL 错误日志或DBCC命令输出的返回中会标识出那些坏的页面。 为了启动这个示例,创建了TestDB数据库的完整数据库备份: 下一步,使用PAGE参数执行还原操作: 这个查询返回: 已为数据库'TestDB',文件'TestDB'(位于文件1上)处理了1页。 RESTORE DATABASE ... FILE=成功处理了1页,花费0.621秒(0.013MB/秒)。 在这一点上,可以对在最后完整备份之后产生的任何差异备份或事务日志备份进行还原操作。在这个示例中没有进行这些操作,没有继续还原其他备份。下一步,进行和前一示例相关的操作,必须创建新的事务日志备份来捕捉还原的页面: 这个查询返回: 已为数据库'TestDB',文件'TestDB_log'(位于文件1上)处理了4页。 BACKUP LOG成功处理了4页,花费0.126秒(0.243MB/秒)。 为了完成页面还原操作,在RESTORE...PAGE之后必须执行带RECOVERY的事务日志还原操作: 解析 在这个技巧中,通过在RESTORE DATABASE命令中使用PAGE选项从完整数据库备份中还原单个数据页。正如从FILE或FILEGROUP中还原,第一个RESTORE将数据库置为NORECOVERY状态,这将允许在完成还原之前附加额外的事务日志备份。可以使用这个技术还原最多1 000个单个页面。

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 20 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档