Covering Index Question  
Author Message
natei6





PostPosted: Mon Oct 10 06:10:03 CDT 2005 Top

SQL Server Developer >> Covering Index Question

we have a table with the following structure (not full but should give an
idea how it looks like)

CREATE TABLE [dbo].[JK_Product] (
[Creator] [varchar] (50) NOT NULL ,
[ID] [int] NOT NULL ,
[Product] [int] NOT NULL ,
[PartNum] [char] (32) NOT NULL ,
...
...
...
[CheckShipping] [char] (1) NULL
) ON [PRIMARY]
GO

We have these 3 indexes already on this table

CLUSTERED INDEX1
([Creator], [Product])

PRIMARY KEY NONCLUSTERED INDEX1
([Creator],[ID])

NONCLUSTERED INDEX2
([Creator], [Product], [PartNum])

ITW suggested me these 3 new indexes based on some workload i gave

NONCLUSTERED INDEX3
([ID])

NONCLUSTERED INDEX4
([PartNum])

NONCLUSTERED INDEX5
([Product], [Creator])

is it necessary to create INDEX3 and INDEX4 since we already have those
covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
use of the existing indexes effectively? what if we create a covering NCI
consisting of Creator, ID, Product, PartNum columns and drop other NCIs

TIA

SQL Server7  
 
 
chandra





PostPosted: Mon Oct 10 06:10:03 CDT 2005 Top

SQL Server Developer >> Covering Index Question Hi
You need not create Index 3 and 4 as Index 1 and 2 takes care of it.

Creating an Index depends on the query that you frequently use.


--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---------------------------------------





>
> we have a table with the following structure (not full but should give an
> idea how it looks like)
>
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
>
> We have these 3 indexes already on this table
>
> CLUSTERED INDEX1
> ([Creator], [Product])
>
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
>
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
>
> ITW suggested me these 3 new indexes based on some workload i gave
>
> NONCLUSTERED INDEX3
> ([ID])
>
> NONCLUSTERED INDEX4
> ([PartNum])
>
> NONCLUSTERED INDEX5
> ([Product], [Creator])
>
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
>
> TIA
>
 
 
Tibor





PostPosted: Mon Oct 10 06:14:30 CDT 2005 Top

SQL Server Developer >> Covering Index Question First, it is meaningless to talk about a clustered index covering a query. A clustered indexes
covers all queries against the table.

So lets focus on the other indexes. If you don't have what you search for in the first column of the
index, SQL Server cannot seek for that condition. It can scan the index, but not seek. That might be
why it suggested an index in the ID, PartNum and Product, Creator. In short, without knowing your
workload, selectivity etc, we cannot say whether the recommendations from ITW are good
recommendations...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/




>
> we have a table with the following structure (not full but should give an
> idea how it looks like)
>
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
>
> We have these 3 indexes already on this table
>
> CLUSTERED INDEX1
> ([Creator], [Product])
>
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
>
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
>
> ITW suggested me these 3 new indexes based on some workload i gave
>
> NONCLUSTERED INDEX3
> ([ID])
>
> NONCLUSTERED INDEX4
> ([PartNum])
>
> NONCLUSTERED INDEX5
> ([Product], [Creator])
>
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
>
> TIA
>

 
 
Uri





PostPosted: Mon Oct 10 06:20:45 CDT 2005 Top

SQL Server Developer >> Covering Index Question Hi
As well , read this article
http://www.sql-server-performance.com/covering_indexes.asp







>
> we have a table with the following structure (not full but should give an
> idea how it looks like)
>
> CREATE TABLE [dbo].[JK_Product] (
> [Creator] [varchar] (50) NOT NULL ,
> [ID] [int] NOT NULL ,
> [Product] [int] NOT NULL ,
> [PartNum] [char] (32) NOT NULL ,
> ...
> ...
> ...
> [CheckShipping] [char] (1) NULL
> ) ON [PRIMARY]
> GO
>
> We have these 3 indexes already on this table
>
> CLUSTERED INDEX1
> ([Creator], [Product])
>
> PRIMARY KEY NONCLUSTERED INDEX1
> ([Creator],[ID])
>
> NONCLUSTERED INDEX2
> ([Creator], [Product], [PartNum])
>
> ITW suggested me these 3 new indexes based on some workload i gave
>
> NONCLUSTERED INDEX3
> ([ID])
>
> NONCLUSTERED INDEX4
> ([PartNum])
>
> NONCLUSTERED INDEX5
> ([Product], [Creator])
>
> is it necessary to create INDEX3 and INDEX4 since we already have those
> covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer
> make
> use of the existing indexes effectively? what if we create a covering NCI
> consisting of Creator, ID, Product, PartNum columns and drop other NCIs
>
> TIA
>


 
 
parasada





PostPosted: Mon Oct 10 06:51:04 CDT 2005 Top

SQL Server Developer >> Covering Index Question tibor thx for the reply. yeah, i know a clustered index covers all queries
against the table. i was more interested to find out if instead of having 3-4
small NCIs to cover some X queries, wouldn't it be better to have one long
NCI in general (I haven't fixed the order of columns in it yet...and maybe
the order can be fixed on further analysis of %age of its use in those X
queries)?



> First, it is meaningless to talk about a clustered index covering a query. A clustered indexes
> covers all queries against the table.
>
> So lets focus on the other indexes. If you don't have what you search for in the first column of the
> index, SQL Server cannot seek for that condition. It can scan the index, but not seek. That might be
> why it suggested an index in the ID, PartNum and Product, Creator. In short, without knowing your
> workload, selectivity etc, we cannot say whether the recommendations from ITW are good
> recommendations...
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>


> >
> > we have a table with the following structure (not full but should give an
> > idea how it looks like)
> >
> > CREATE TABLE [dbo].[JK_Product] (
> > [Creator] [varchar] (50) NOT NULL ,
> > [ID] [int] NOT NULL ,
> > [Product] [int] NOT NULL ,
> > [PartNum] [char] (32) NOT NULL ,
> > ...
> > ...
> > ...
> > [CheckShipping] [char] (1) NULL
> > ) ON [PRIMARY]
> > GO
> >
> > We have these 3 indexes already on this table
> >
> > CLUSTERED INDEX1
> > ([Creator], [Product])
> >
> > PRIMARY KEY NONCLUSTERED INDEX1
> > ([Creator],[ID])
> >
> > NONCLUSTERED INDEX2
> > ([Creator], [Product], [PartNum])
> >
> > ITW suggested me these 3 new indexes based on some workload i gave
> >
> > NONCLUSTERED INDEX3
> > ([ID])
> >
> > NONCLUSTERED INDEX4
> > ([PartNum])
> >
> > NONCLUSTERED INDEX5
> > ([Product], [Creator])
> >
> > is it necessary to create INDEX3 and INDEX4 since we already have those
> > covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
> > use of the existing indexes effectively? what if we create a covering NCI
> > consisting of Creator, ID, Product, PartNum columns and drop other NCIs
> >
> > TIA
> >
>
>
 
 
Tibor





PostPosted: Mon Oct 10 07:56:30 CDT 2005 Top

SQL Server Developer >> Covering Index Question The ordering of the columns is one big issue. If you have a query with a where clause in which you
only search for colA, then an index on (colB, colA) will not be searchable. SQL Server can still
scan the index (assuming it covers the query), but a search is much better (especially if the index
covers the query as no bookmark lookups are needed).

So, you could say that it is better to create a bunch of one-column indexes and let SQL Server join
them as the query is processed (index intersection) so that the indexes together will cover the
query. Now you can search for whichever column you have ion the indexes and you can always have a
seek. OTOH, you will pay the price for the index intersections as the query is processed (in the
cases then SQL Server joins the indexes in run-time).

So, we cannot say which is better. You can, but either analyzing the queries etc. Or by running a
load test for your particular load and see which index alternative is pest for your load test.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/




> tibor thx for the reply. yeah, i know a clustered index covers all queries
> against the table. i was more interested to find out if instead of having 3-4
> small NCIs to cover some X queries, wouldn't it be better to have one long
> NCI in general (I haven't fixed the order of columns in it yet...and maybe
> the order can be fixed on further analysis of %age of its use in those X
> queries)?
>

>
>> First, it is meaningless to talk about a clustered index covering a query. A clustered indexes
>> covers all queries against the table.
>>
>> So lets focus on the other indexes. If you don't have what you search for in the first column of
>> the
>> index, SQL Server cannot seek for that condition. It can scan the index, but not seek. That might
>> be
>> why it suggested an index in the ID, PartNum and Product, Creator. In short, without knowing your
>> workload, selectivity etc, we cannot say whether the recommendations from ITW are good
>> recommendations...
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>>


>> >
>> > we have a table with the following structure (not full but should give an
>> > idea how it looks like)
>> >
>> > CREATE TABLE [dbo].[JK_Product] (
>> > [Creator] [varchar] (50) NOT NULL ,
>> > [ID] [int] NOT NULL ,
>> > [Product] [int] NOT NULL ,
>> > [PartNum] [char] (32) NOT NULL ,
>> > ...
>> > ...
>> > ...
>> > [CheckShipping] [char] (1) NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > We have these 3 indexes already on this table
>> >
>> > CLUSTERED INDEX1
>> > ([Creator], [Product])
>> >
>> > PRIMARY KEY NONCLUSTERED INDEX1
>> > ([Creator],[ID])
>> >
>> > NONCLUSTERED INDEX2
>> > ([Creator], [Product], [PartNum])
>> >
>> > ITW suggested me these 3 new indexes based on some workload i gave
>> >
>> > NONCLUSTERED INDEX3
>> > ([ID])
>> >
>> > NONCLUSTERED INDEX4
>> > ([PartNum])
>> >
>> > NONCLUSTERED INDEX5
>> > ([Product], [Creator])
>> >
>> > is it necessary to create INDEX3 and INDEX4 since we already have those
>> > covered by the PRIMARY KEY and INDEX2 respectively? can't the optimizer make
>> > use of the existing indexes effectively? what if we create a covering NCI
>> > consisting of Creator, ID, Product, PartNum columns and drop other NCIs
>> >
>> > TIA
>> >
>>
>>

 
 
Jerry





PostPosted: Mon Oct 10 11:41:27 CDT 2005 Top

SQL Server Developer >> Covering Index Question In addition you may consider the space requirements for the nonclustered
indexes. This can vary but can be high for a table with a large number of
rows and although not recommended - a large clustered index key (will be
dupped in each nc index).

HTH

Jerry


> The ordering of the columns is one big issue. If you have a query with a
> where clause in which you only search for colA, then an index on (colB,
> colA) will not be searchable. SQL Server can still scan the index
> (assuming it covers the query), but a search is much better (especially if
> the index covers the query as no bookmark lookups are needed).
>
> So, you could say that it is better to create a bunch of one-column
> indexes and let SQL Server join them as the query is processed (index
> intersection) so that the indexes together will cover the query. Now you
> can search for whichever column you have ion the indexes and you can
> always have a seek. OTOH, you will pay the price for the index
> intersections as the query is processed (in the cases then SQL Server
> joins the indexes in run-time).
>
> So, we cannot say which is better. You can, but either analyzing the
> queries etc. Or by running a load test for your particular load and see
> which index alternative is pest for your load test.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
>


>> tibor thx for the reply. yeah, i know a clustered index covers all
>> queries
>> against the table. i was more interested to find out if instead of having
>> 3-4
>> small NCIs to cover some X queries, wouldn't it be better to have one
>> long
>> NCI in general (I haven't fixed the order of columns in it yet...and
>> maybe
>> the order can be fixed on further analysis of %age of its use in those X
>> queries)?
>>

>>
>>> First, it is meaningless to talk about a clustered index covering a
>>> query. A clustered indexes
>>> covers all queries against the table.
>>>
>>> So lets focus on the other indexes. If you don't have what you search
>>> for in the first column of the
>>> index, SQL Server cannot seek for that condition. It can scan the index,
>>> but not seek. That might be
>>> why it suggested an index in the ID, PartNum and Product, Creator. In
>>> short, without knowing your
>>> workload, selectivity etc, we cannot say whether the recommendations
>>> from ITW are good
>>> recommendations...
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>


>>> >
>>> > we have a table with the following structure (not full but should give
>>> > an
>>> > idea how it looks like)
>>> >
>>> > CREATE TABLE [dbo].[JK_Product] (
>>> > [Creator] [varchar] (50) NOT NULL ,
>>> > [ID] [int] NOT NULL ,
>>> > [Product] [int] NOT NULL ,
>>> > [PartNum] [char] (32) NOT NULL ,
>>> > ...
>>> > ...
>>> > ...
>>> > [CheckShipping] [char] (1) NULL
>>> > ) ON [PRIMARY]
>>> > GO
>>> >
>>> > We have these 3 indexes already on this table
>>> >
>>> > CLUSTERED INDEX1
>>> > ([Creator], [Product])
>>> >
>>> > PRIMARY KEY NONCLUSTERED INDEX1
>>> > ([Creator],[ID])
>>> >
>>> > NONCLUSTERED INDEX2
>>> > ([Creator], [Product], [PartNum])
>>> >
>>> > ITW suggested me these 3 new indexes based on some workload i gave
>>> >
>>> > NONCLUSTERED INDEX3
>>> > ([ID])
>>> >
>>> > NONCLUSTERED INDEX4
>>> > ([PartNum])
>>> >
>>> > NONCLUSTERED INDEX5
>>> > ([Product], [Creator])
>>> >
>>> > is it necessary to create INDEX3 and INDEX4 since we already have
>>> > those
>>> > covered by the PRIMARY KEY and INDEX2 respectively? can't the
>>> > optimizer make
>>> > use of the existing indexes effectively? what if we create a covering
>>> > NCI
>>> > consisting of Creator, ID, Product, PartNum columns and drop other
>>> > NCIs
>>> >
>>> > TIA
>>> >
>>>
>>>
>