SQL 2005 Composite Index vs Non Composite Index

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 ?
thayduckAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
chapmandewCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
 
chapmandewConnect With a Mentor Commented:
>>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
All Courses

From novice to tech pro — start learning today.