Prev: SQL Server Output Buffer
Next: cannot open backup device
From: TheSQLGuru on 10 Dec 2009 16:47
Not sure anyone touched on the fact that you can get more precise estimates
for the optimizer with a compound index, which can help get optimal queries
for scenarios where you use both columns in join/where clauses.

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"mark" <user(a)msgroups.net/> wrote in message
news:O93vDjWeKHA.4880(a)TK2MSFTNGP05.phx.gbl...
>I got a question with indexing. If I create an index and select 2 or more
>columns, what is the difference with that and creating 2 (or more
>separate ) indexes for them?
>
> Thanks
>
> ---
> http://msgroups.net/microsoft.public.sqlserver.server/


From: Erland Sommarskog on 10 Dec 2009 17:39
mark (user(a)msgroups.net/) writes:
> So I take it that if both columns are specified in the where, the
> composite index is faster(maybe not by much) than the separate indexes?

"Maybe not by much", well that depends. For a large table, that could
indeed be much. Say that you need to search a table with 100 million
rows for people name John Smith in Seattle. With one index each on first
name, last name and City, I would expect the optimizer to give up and
scan the table. With a compositie index, the optimizer would urely use it.

> And if only the first column is specified in the where, the separate
> indexes is faster than the composite?

Yes, but I don't think difference can be as dramatic as in the case above.
--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Tom Cooper on 10 Dec 2009 22:42
Another consideration is that if you have two indexes, inserting and
deleting rows will be more expensive than if you only have one composite
index.

Also, if these are nonclustered indexes, two indexes will take more disk
space than one composite index (because the clustered index key columns are
stored in every nonclustered index).

The only valid short answer is "it depends".

Tom

"mark" <user(a)msgroups.net/> wrote in message
news:ukRgE6ZeKHA.5996(a)TK2MSFTNGP05.phx.gbl...
> Thanks for your replies,
>
> So I take it that if both columns are specified in the where, the
> composite index is faster(maybe not by much) than the separate indexes?
>
> And if only the first column is specified in the where, the separate
> indexes is faster than the composite?
>
>
> ---
> frmsrcurl:
> http://msgroups.net/microsoft.public.sqlserver.server/Sql-Server-Indexing-With-Two-or-More-Columns

From: Ron on 12 Dec 2009 06:54
If I may, just as for a little clarification on this point, as it is of
particular interest to me...

If the table were to have say 4 possible columns that were used in a search,
(lets call them index1 thru index4), and each of them were searchable (ANDed)
in any order...for example

WHERE INDEX1='foo' AND INDEX2='bar'
or
WHERE INDEX2='foo' AND INDEX1='bar'
or
WHERE INDEX1='foo' AND INDEX3='bar'
etc etc

In this situation,I expect creating indexes with included columns for each
combination and order would be going too far. So in this situation, is the
only way to cover the necessary columns is to add an index on each ?

"Erland Sommarskog" wrote:

> mark (user(a)msgroups.net/) writes:
> > So I take it that if both columns are specified in the where, the
> > composite index is faster(maybe not by much) than the separate indexes?
>
> "Maybe not by much", well that depends. For a large table, that could
> indeed be much. Say that you need to search a table with 100 million
> rows for people name John Smith in Seattle. With one index each on first
> name, last name and City, I would expect the optimizer to give up and
> scan the table. With a compositie index, the optimizer would urely use it.
>
> > And if only the first column is specified in the where, the separate
> > indexes is faster than the composite?
>
> Yes, but I don't think difference can be as dramatic as in the case above.
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
> .
>
From: Jay on 12 Dec 2009 07:27
I was of the distinct impression that the Query Optimizer chose zero to one
index, but never more.

> WHERE INDEX1='foo' AND INDEX2='bar'
> or
> WHERE INDEX2='foo' AND INDEX1='bar'

These two would be the same and would prefer a single compound index of
(index1, index2). As long as both columns are specified in the where clause,
the order they are supplied doesn't matter.

> WHERE INDEX1='foo' AND INDEX3='bar'

This could use the *(index1, index2)' index as index1 heads the composite
index, however, the index2 portion would be ignored. In fact, depend on the
statistics, the QO may just choose a sequential scan. If you had a composite
index of (index1, index2, index3) and ran the query, it would be the same as
index2 wasn't specified in the where clause, so it can't really get to
index2.

Jay

> If I may, just as for a little clarification on this point, as it is of
> particular interest to me...
>
> If the table were to have say 4 possible columns that were used in a
> search,
> (lets call them index1 thru index4), and each of them were searchable
> (ANDed)
> in any order...for example
>
> WHERE INDEX1='foo' AND INDEX2='bar'
> or
> WHERE INDEX2='foo' AND INDEX1='bar'
> or
> WHERE INDEX1='foo' AND INDEX3='bar'
> etc etc
>
> In this situation,I expect creating indexes with included columns for each
> combination and order would be going too far. So in this situation, is the
> only way to cover the necessary columns is to add an index on each ?
>
> "Erland Sommarskog" wrote:
>
>> mark (user(a)msgroups.net/) writes:
>> > So I take it that if both columns are specified in the where, the
>> > composite index is faster(maybe not by much) than the separate indexes?
>>
>> "Maybe not by much", well that depends. For a large table, that could
>> indeed be much. Say that you need to search a table with 100 million
>> rows for people name John Smith in Seattle. With one index each on first
>> name, last name and City, I would expect the optimizer to give up and
>> scan the table. With a compositie index, the optimizer would urely use
>> it.
>>
>> > And if only the first column is specified in the where, the separate
>> > indexes is faster than the composite?
>>
>> Yes, but I don't think difference can be as dramatic as in the case
>> above.
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>> .
>>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: SQL Server Output Buffer
Next: cannot open backup device