Solved

SQL 2005 Composite Index vs Non Composite Index

Posted on 2010-09-08
4
367 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why is the output of this function is like this? 4 47
Caste datetime 2 73
Help Required 2 56
why sql server only update some statistics in the database ? 3 47
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…

734 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