We help IT Professionals succeed at work.

When to use a Unique Index? A Clustered Index?

245 Views
Last Modified: 2017-05-02
When I have a slow performing query that uses a single field in the where clause, is that a good field on which to create a Unique Index?

Otherwise, what are a few rules of thumb when deciding which field could boost performance by having a Unique Index?

Now, please explain how a Clustered Index fits in? What big problems do they solve?

Thanks.
Comment
Watch Question

Senior Database Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
When you have a slow performing query that uses a single field in the where clause then it is a good field on which to create the index in some cases...

No need to be unique as the data are not unique obviously.
Index does not help when the WHERE condition does not reduce the number of retrieved records, e.g.

WHERE SomeIntegerColumn > -2147483648  -- all integers fulfill this condition

The best index performance is achieved when you retrieve just a few rows out of many rows in the entire table, e.g.

You may read more e.g. here: http://sqlmag.com/database-performance-tuning/indexing-dos-and-don-ts

Clustered indexes are about the physical records order. They are slowing down the table creation (new records insertion) but the positive effect is they keep related records together and reduce the number of physical reads if your queries are retrieving all these related records at once. Example is e.g. Order lines having the clustered index on Order ID (+ Order Line ID to have it unique as this is better than the non-unique clustered index).
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
larryhSr. Software Engineer
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
curiouswebsterSoftware Engineer

Author

Commented:
thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.