Ask the Expert

Index types in SQL Server 2005

Does SQL Server 2005 have any new index types?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: