SQL Q&A: Non-Clustered Indexes, Retain Permissions Data

项目
08/17/2016

SQL 问题与解答非群集索引和保留权限

Saleem Hakani and Dan Carollo

非群集索引及使用方法

问：什么是非群集索引？使用它们都有什么优缺点？

答：非群集索引与通常位于书籍最后部分的索引非常相似。书籍中包含的所有信息都在索引中以主题的形式表示，并以页码作为指针来指示该信息在书籍中的位置，可能多个地方都包含此信息。此外，书籍索引中的数据与书籍正文内容出现的顺序并不相同。这一点与非群集索引相同。如果为某个表建立了群集索引，则可以指定表中项目的顺序。否则，将无法确定其排列顺序。

此外，非群集索引有两方面的局限性：索引中只能包括 16 列且索引键最大字节数不得超过 900。这意味着什么？让我们以 MovieList 示例数据库中的 Movie 表为例，看看如果希望对以下各列创建索引会出现什么情况：MovieTitle NVarchar(50)、DirectorName NVarchar(50)、ShortStory NVarchar(400)。

假设提交以下语句创建该表：

Use MovieList;
CREATE INDEX Movie_IDX ON Movie(MovieTitle, DirectorName, ShortStory);

这将会提示以下错误消息：“警告！最大键长度为 900 个字节。索引 'Movie_IDX' 的最大长度为 1000 个字节。对于某些较大值的组合，插入/更新操作将失败”。您之所以收到上面的错误消息，是因为“nvarchar”数据类型中每个字符占 2 个字节，包含前面所述三列的索引将超过 900 字节大小的限制。

使用 SQL Server® 2005 版，您现在可以通过将这些列添加到 INCLUDE 语句来解决这一问题。如果希望同时解决大小和列数限制，这将是一项非常有用的功能。可以通过执行以下语句来实现此目的：

CREATE INDEX Movie_IDX ON Movie(MovieTitle, DirectorName) INCLUDE (ShortStory);

必须注意，在创建索引过程中使用 INCLUDE 语句时，数据库引擎在计算索引键列数或索引键字符大小时不会考虑非键列。

要了解有关非群集索引的更多有用信息，请参阅 msdn2.microsoft.com/aa174537 中的“非群集索引”和 msdn2.microsoft.com/aa933130 中的“使用非群集索引”。要获得有关优化索引的提示，请参阅 sql-server-performance.com/optimizing_ indexes.asp。

保留权限数据

问：在重新初始化订阅时，如何才能避免丢失权限？我已多次遇到这一问题：在重新初始化快照时，所有已授予的权限都会丢失。

答：默认情况下，只要重新初始化订阅，订阅数据库中的所有对象都将被删除然后再重新创建。不过，有两种方法可解决此问题。

第一种方法是在重新初始化后重新应用所有权限。在手动设置好权限后，应使用脚本导出所有对象/语句级权限并将其单独保存，这样在重新初始化订阅后，您即可立即使用它们。

第二种方法是对订阅进行配置，使其在重新初始化订阅时不删除任何对象。为此您可以使用 SP_CHANGEARTICLE 系统存储过程来配置参数 @PROPERTY 的 PRE_CREATION_CMD 值，以及参数 @Value 的 NONE、DELETE 或 TRUNCATE 值。

同时，在目标对象部分的“项目属性”对话框中，选择“保持现有对象不变，删除数据”。“如果文章有行筛选器，则仅删除与筛选器匹配的内容”。“清空现有对象中的所有数据”。请务必先在测试环境下对此方法进行测试，如果需要更多帮助，请查看最新版的 SQL Server 联机丛书来了解最新信息。

Saleem Hakani 是一名高级数据库工程师、高级问题解决工程师和全球 Microsoft SQL Server 社区负责人，拥有 14 年的数据库系统经验。他负责外部 SQL Server 社区网站 sqlcommunity.com，您可以通过 Saleem@sqlcommunity.com 与他联系。

Dan Carollo 是一名运营工程师和 SQL Server DBA，他在 Microsoft 与 Windows 反恶意软件研究和响应团队合作。他拥有 SQL Server 的 MCT 证书。

其他资源