SQL Indexing Question

What is the difference between these two commands?

create index myindex on mytable(C)
include (A,B)

as opposed to
create index myindex on mytable(C,A,B)

I read something the include versions make these non-columns and they do not count in the number of fields in the index, but from a performance standpoint does it make any difference?
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
its basically the key structure that is used to structure the index..

wjen you include the additional columns then thse columns do not form part of the key and therefore should lead to fewer significant index updates if the data in the included columns undergoes a change.
0
batchakamalCommented:
Hi,

Included columns are in the leaf level of an Index. They do not appear in the upper levels, as the key column of the index will take care.
The included columns play no part in the ordering of the leaf level rows thats why you may not find the options in GUI to Move-up or Move-down button and Sort Order options.

There are many other advantages & limitations while using INCLUDE options in Indexes, you may find them in books online itself.

When we come to your specific query,

create index myindex on mytable(C)
include (A,B)

1. This will create index on C with leaf level index only for A&B.
2. This index is more useful in terms of index size, if A & B are of type string  based data types.
3. This is helpful to avoid bookmark lookups that can be very bad on performance when querying large tables.
4. If any change in A & B will not affect index re-order as they are in INCLUDE options.
5. This index will best fit for a statement like 'SELECT A, B FROM mytable WHERE C = 100


Now when it comes to

create index myindex on mytable(C,A,B)

1. This will create index on C, A and B as Index Pages.
2. This index is more useful when you want to search the table using all the three columns.
3. This index occupies more space than the previous one.
4. If any change in A,B & C will affect index re-order.
5. This index will best fit for a statement like 'SELECT * FROM mytable WHERE C = 100 AND A = 200 AND B = 5






 
0
rwheeler23Author Commented:
What is required if sometimes the users will use all three keys but sometimes just A, or just B or just C or maybe C and A or A and B? In other words, the where clause can contain any combination of A, B and C. It sounds like the key here is the number of records. For a large number of records, the include seems to be the way to go.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

batchakamalCommented:
Whenever you go for Indexing..
independent of huge records or less records,

how often you insert & update records?
what is your performance priority? faster insert/update or faster retrieval?

If you prefer faster retrieval then create indexes on all fields..
If you prefer faster insert / update then create most frequently used columns in indexes..with INCLUDE option if required.

so your decision should have a balance between R & CUD of CRUD operation..

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
Very good tip. I have always been curious about this. I work with an accounting application and I have never seen any of the indexes built with an include statement. This program is used from small companies with 1-5GB to large companies with databases in excess of 500GB. If they have users entering orders all day long this would definitely affect peformance.  I think this falls into the category of one size does not fit all.
0
batchakamalCommented:

Yes..Indexes are based on requirements, and there is no concrete rule for indexes.

Moreover, make sure that your accounting application is not migrated from older SQL server database versions, because include option was introduced since SQL Server 2005.
0
rwheeler23Author Commented:
Well too late for that. It is a Microsoft database so you would think they would know how to handle it.
0
batchakamalCommented:
Yeah..just I was given a thought.

I hope my answers your questions...please accept if you are satisfied.

Otherwise let me know how can I help you more in this regard..
0
rwheeler23Author Commented:
Both answers provided useful insight.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.