TechEd India 2012: Online Indexing Enhancements March 12th, 2012

Vinod Kumar

Indexing is a core concept inside SQL Server and with the next version of SQL Server 2012, this only enhances to the next level. Let me take a moment here to talk about one of those enhancements inside SQL Server 2012 from an Online Indexing implementation.

Also worth mentioning is one of our latest video based course for your reference. There are tons of basic concepts that we made it happen into that course and drop me a note via comments if you want to watch the same. All feedbacks and mutual learning helps us get better.

Now with TechEd India 2012 count down started, we have a session lined up around “SQL Server Misconceptions and resolutions”. And this session has tons of interesting demo around Indexing concepts. So don’t miss to attend, if you are at TechEd India 2012.

SQL Server 2012 Online Indexing

Online re-indexing operation was introduced in SQL Server 2005 as a way to improve the re-indexing functionality for those environments which cannot afford downtimes on regular intervals for maintenance activities. But there were certain limitations of this feature, significant one being that it could not be used against indexes that included columns of LOB data types (including Varchar(max), NVarchar(max)). This restricted the usage of online re-indexing for some scenarios, such as clustered indexes when tables included LOB data types.

Msg 2725, Level 16, State 2, Line 1

Online index operation cannot be performed for index ‘<Index name>’ because the index contains column ‘<LOB column name>’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.

The Error message is quite self-explanatory and at this moment want to point you to this BOL reference – Guidelines for Performing Online Index Operations.

What changed?

With introduction of this feature with SQL Server 2012, you can now rebuild indexes online for tables, which uses varchar(max), nvarchar(max), XML or varbinary(max) LOB data types for its columns.

Restrictions continue to exist for tables, which include columns of TEXT, NTEXT, FILESTREAM and IMAGE data type. You will receive following modified error message to indicate the limitation:

Msg 2725, Level 16, State 2, Line 1

An online operation cannot be performed for index ‘<Index name>’ because the index contains column ‘<Column name>’ of data type text, ntext, image or FILESTREAM. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

This is an interesting improvement with SQL Server 2012 and feel free to use the same if you are an Administrator.

Share this article

Tags: , , , , , , , , , ,

This entry was posted on Monday, March 12th, 2012 at 19:24 and is filed under Personal, Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply



 

Email
Print