Solved

SQL 2005 Composite Index vs Non Composite Index

Posted on 2010-09-08
4
368 Views
Last Modified: 2012-05-10
I have been told not to create composite index's over tables.
If I have a table and create a composite index over Cust#, Invoice# and Date,
I was told that it is better to create 3 index's one over Cust#, one over Invoice# and one over Date.

Any truth to this ?

What is best way ?
0
Comment
Question by:thayduck
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 33627070
The honest truth is...it depends!

Depends on the queries and the usage.  THE query optimizer can intersect indexes, so seperate queries will often show benefit.  However, in terms of bookmark lookups...you need the columns in the same NC index.  

Clear as mud?  :)
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 25 total points
ID: 33627091
the answer is: it depends on the queries you run against the table.composite indexes can be helpful, if most queries have more than 1 condition in the where clause for this table.usually, you search in the invoices either by invoice# , or by cust#, but normally not by both at the same time.however, cust# and date might be specified together, so a composite index on cust + date is not "bad".
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 33627095
As mentioned above, it depends..
Its recommended to keep the index size smaller and hence you need to be more careful when adding more and more columns to your indexes.
0
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 25 total points
ID: 33627125
>>Its recommended to keep the index size smaller and hence you need to be more careful when adding more and more columns to your indexes.

From a disk space point of view, having 3 indexes is going to be larger than 1 3-column index.  In the case w/ the 3 indexes, each index has the clustered key contained in the NC.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question