[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL 2005 Composite Index vs Non Composite Index

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

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

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

656 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