Indexing in Microsoft SQL

975 pts.
Tags:
Database design
SQL
SQL Database
SQL Server 2005
What is an index in Microsoft SQL? What types of indexes are available in SQL Server 2005? Do I need to know about this to create and deploy a database?

Answer Wiki

Thanks. We'll let you know when a new response is added.

An index is a way to mark data in such a way to make it faster to find than a full table scan. Indexes are designed to minimize the number of read operations required to find a given row of data. A structure known as a “balanced tree” divides the data in such a way that no more than a few reads should ever be required to find the data.

However, the index should be built on the column or columns primarily used for searching so it is important to know how the data will be accessed in order to maximize the benefit of an index.

Clustered indexes are most typical and arguably every table should have one based on the primary key. Multiple columns can be used as the “cluster key” and this will return data based on the order of the columns defined in the cluster key. This imposes a predefined sort order of the data in the table. If searches generally correspond to the priority and order of the data defined by the cluster key, you have designed a good clustered index. Again, it depends on how the data is typically accessed as to the best design of your index. You can only have ONE clustered index on a table.

Non-clustered indexes do not impose a sort order on the table columns, so you can create multiple non-clustered indexes on a table. This is good if you have many searches on the data that are significant but fall outside the typical usage that the clustered index doesn’t accommodate well or at all.

Covering indexes allow you to tack on additional columns of data other than the column defined in the index key. This allows users to search on a column and return additional data from the table with it without having to go to the actual table to pull the additional data. For example, if you know people search phone numbers, you might build an index on the phone number but also include name and address as part of the covering index. This way, additional reads won’t be required to go to the table to grab the name and address after the phone number is found.

A filtered index is simply an index with a WHERE clause. Only the index keys matching the WHERE clause are added to the index, allowing you to build indexes that focus on the highly selective portions of the table while allowing SQL Server to choose another method for the less-selective range.

Indexes are not a cure or counter-balance to bad code. You must know how the data is used to design good indexes. Indexes can be helpful but too many indexes can increase overhead dramatically and actually make things slower than they would be without them.

Design well and test performance after each index.

Best!

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

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
  • Scottwnelson
    Also, you don't *need* to know anything about indexes to deploy a database. But if you experience slowness on searches against your data, you should learn how to use them effectively.
    265 pointsBadges:
    report
  • Kccrosser
    This sounds suspiciously like a homework question... However... You can logically think of an index as another "table" in the database. If the index is on a single column, then the index "table" would contain two fields - one field containing a pointer to the actual source data table record, and one field containing the data value that is contained in the data row in that record. Since the index doesn't contain ALL the data from the source record, it is much smaller, allowing the database engine to navigate through the values quicker. Further, the index "table" is usually implemented in a structure similar to a B+ tree (google "B+ tree" and go to the Wikipedia entry for a good discussion). This tree has a dense collection of values and pointers so that it can be traversed quickly. Given a value to look up, starting at the root node, the system can quickly check if the value is less than, greater than, or equal to the value stored in a node and then move down the tree to the next node as necessary. With a "pure" balanced B-tree, you can find a value in the tree with no more than log2(N) comparisons (so with no more than 20 comparisons, you can find a value in 1,000,000 records). A B+ tree "clumps" more data together at each node for even more efficient searching. As Scottwnelson notes - you can deploy a database without knowing anything about indexes, but you will probably have poor performance if you have lots of data and no (or improper) indexes on the data.
    3,830 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

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

Thanks! We'll email you when relevant content is added and updated.

Following