?
Solved

SQL 2005 Composite Index vs Non Composite Index

Posted on 2010-09-08
4
Medium Priority
?
369 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 100 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 100 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

777 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