By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent.

By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.

You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

A single row matched to a single value provided

Multiple rows based on a single value provided

Multiple rows based on multiple values provided

Ranges of rows, such as for a specific period of time

What are commonly indexed columns?

Primary Keys

Foreign Keys

Columns that are used in JOINs, WHERE, ORDER BY, GROUP BY, HAVING and other clauses

Which index should be selected?

Clustered index -- This is reserved for a single index when data is physically ordered in the table. It is not possible to sort the table physically in more than one way. A good candidate for a clustered index is the Primary Key, a column that uniquely identifies each row, or a column that supports a range, such as a date.

Non-clustered index -- This is used with ordered data based on a clustered index or without a clustered index, just based on the data. Good candidates for a non-clustered index could be foreign keys or columns used in JOINs, WHERE, ORDER BY, GROUP BY, HAVING and other clauses.

None -- Use this for tables with a small number of rows that are consistent, such as lookup tables where SQL Server can scan the table faster than it can using using an index.

Do I always need to have a clustered index?

No, but "always" is the key word. Think of the 80/20 rule: Clustered indexes should be used in most, if not all, circumstances to physically order the data. Typically, when they are not used, it is because a table has a large number of transactions and the perceived overhead for SQL Server to maintain the clustered index is too costly. I should note, though, that I have personally seen large tables benefit from clustered indexes where conventional wisdom frowned on using them. The benefit was substantial in terms of improved data-access time for queries and minimizing I/O resources. It was a big win!

Do I always need to have a Primary Key?

No, but "always" is the key word once again. Based on my experience, you need a Primary Key 90% of the time or more to maintain referential integrity or to support third-party tools that compare data.

How many columns should be in the index?

One column per index is probably the best approach to start with if you are unsure of your exact indexing needs.

Multiple columns per index are valuable if the columns in the index match the column order used in many queries or key queries that are issued frequently. The only caveat is that the statistics for the index are only based on the first column, not the group of indexes.

How else can I configure the indexes?

Index order -- You can create indexes either in ascending or descending order.

Fill factor -- Determine the fill factor for each index to establish how much free space remains on the page when the index is created or rebuilt.

Statistics -- Ensure statistics are either manually created for the indexes or else permit SQL Server to create and update them automatically depending on the database size.

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

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