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

Article
09/08/2016

SQL Q&ANon-Clustered Indexes and Retaining Permissions

Saleem Hakani and Dan Carollo

Non-Clustered Indexes and Their Uses

Q What are non-clustered indexes, and what are the pros and cons of using them?

A A non-clustered index is similar to the type of index typically found at the back of a book. All the information contained within the book is represented in the index by topic, and there are pointers in the form of page numbers that direct you where to find the information perhaps in several areas of the book. Furthermore, the data in a book index is not listed in the same order as it appears in the text of the book. It is the same with non-clustered indexes. If you have a clustered index on a table, you can specify what the order of items will be. Otherwise, there is no way to be certain what the order will be.

In addition, non-clustered indexes have two limitations: only 16 columns can be included in the index and the maximum size of the index key cannot exceed 900 bytes. So what does that mean? Let's see what happens if you want to index the following columns in the Movie table from the sample MovieList database: MovieTitle NVarchar(50), DirectorName NVarchar(50), ShortStory NVarchar(400).

Assume you issue the following statement to create the table:

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

This will spawn the following error message: "Warning! The maximum key length is 900 bytes. The index 'Movie_IDX' has maximum length of 1000 bytes." For some combination of large values, the insert/update operation will fail. You will receive the above message because "nvarchar" datatype consumes 2 bytes for each character; an index that contains the previous three columns would exceed the 900 byte size limit.

With the release of SQL Server® 2005, you now have the opportunity to overcome this problem by adding the columns to the INCLUDE clause. This is a very useful feature if you want to overcome both size and column limitations. You can do this by executing the following statement:

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

It's important to note that when you use the INCLUDE clause during index creation, the database engine does not consider non-key columns when calculating the number of index key columns or index key size.

To find more helpful information on non-clustered indexes, visit "Non-clustered Indexes" at msdn2.microsoft.com/aa174537 and "Using Non-clustered Indexes" at msdn2.microsoft.com/aa933130. For tips on optimizing indexes, see sql-server-performance.com/optimizing_ indexes.asp.

Preserving Permissions Data

Q How can I avoid losing permissions when a subscription is reinitialized? I have experienced the problem several times that when a snapshot is reinitialized, it loses all the granted permissions.

A By default, all the objects in the subscription database are dropped and recreated whenever a subscription is reinitialized. But there are two ways you can handle this scenario.

First, you can reapply all the permissions after the reinitialization. When you set up the permissions manually, you should script out all the object/statement-level permissions and store them separately so you can use them immediately after you reinitialize the subscription.

Second, you can configure your subscription so that it does not drop any objects whenever you reinitialize the subscription. To do so you can use SP_CHANGEARTICLE system stored procedure to configure the value of PRE_CREATION_CMD for the parameter @PROPERTY and a value of NONE, DELETE or TRUNCATE for the parameter @Value.

Also, in the Article Properties dialog box in the destination object section, select a value of "Keep existing object unchanged, delete data. If article has a row filter, delete only what matches the filter. Truncate all data in the existing object." Make sure to try this in your test environment, and if you need further help, check the latest version of SQL Server Books Online for up-to-date information.

Saleem Hakani is a Senior Database Engineer, Senior Problem Engineer, and Worldwide Microsoft SQL Server Community Lead with 14 years of experience in database systems. He leads the external SQL Server Community Web site sqlcommunity.com and can be reached at Saleem@sqlcommunity.com.

Dan Carollo is an Operations Engineer and SQL Server DBA who works with the Windows Anti-Malware Research and Response team at Microsoft. He has an MCT in SQL Server.

Additional resources