Clustered and Non-clustered Index

40 pts.
Tags:
Clustered Index
Non-Clustered Index
Query optimization
Hi, I have one table having more than 3 millions rows with following columns: [SITE_ID] [USER_NAME] (http://xs434.xs.to/xs434/08513/queries-columns997.jpg or http://xs.to/xs.php?h=xs434&d=08513&f=queries-columns997.jpg) Please help.. Thanks Sharma
ASKED: December 17, 2008  1:51 PM
UPDATED: December 17, 2008  8:47 PM

Answer Wiki

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

You need to create a single index which contains all the columns which you are in your WHERE clause as well as your ORDER BY clause. The columns in your SELECT statement should be put into the index as included columns (if using SQL 2005 or higher).

You will probably end up with a few different indexes to handle all your queries. Each query with a different ORDER BY Clause will need it’s own index, with the Order by columns included in the index in the order you are sorting by them. The queries which don’t have ORDER BY statements can all be handled by one index just make sure that you have all the columns that all the queries return listed in the INCLUDE section.

Look at the execution plan to see what index is currently being used.

Discuss This Question:  

 
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

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