Q

Index types in SQL Server 2005

While SQL Server 2005 does not introduce any new index types, it does include indexing enhancements. SQL Server 2005 expert Adam Machanic discusses these enhancements in this tip.

Does SQL Server 2005 have any new index types?

SQL Server 2005 does not introduce new index types for relational tables. The basic -- clustered and non-clustered

indexes implemented as B-trees -- still apply. However, SQL Server 2005 does include indexing enhancements both for Full Text Indexing and for XML data, as well as enhancements that ease some problems associated with relational indexing.

SQL Server 2005's Full Text Indexing feature is completely new and rewritten. For information on this feature, watch Nimish Khanolkar's archived MSDN webcast, Introducing Full-Text Search in SQL Server 2005.

XML has also undergone a drastic transformation in the way it is treated in SQL Server 2005. There is now a first-class XML data type available to developers. The type supports the XQuery query language, and columns using the type can be indexed using a special form of XML indexes. More information on the XML type can be found in this MSDN article.

A variety of enhancements have been made to the T-SQL relational indexing commands. Perhaps the most interesting of these is the new "online" indexing mode, which allows database administrators to perform index maintenance tasks without locking users out of tables. This hopefully marks the beginning of the end of database administrators having to wait for the 3:00 a.m. maintenance window to fix database issues! More information on this feature can be found in this SQL Server Worldwide Users Group article.


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in September 2005

Dig deeper on Microsoft SQL Server 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close