CREATE DATABASE failed: 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
TIAHi
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/
---
"para

a" wrote:

> 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 ma
ke
> 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
>|||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 s

for that condition. It can scan the index, but
not s

. 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 IT
W 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/
"para

a" <para

a@.discussions.microsoft.com> wrote in message
news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.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 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)?
"Tibor Karaszi" wrote:

> 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 s

for that condition. It can scan the index, b
ut not s

. That might be
> why it suggested an index in the ID, PartNum and Product, Creator. In shor
t, 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/
>
> "para

a" <para

a@.discussions.microsoft.com> wrote in message
> news:F4D4DB24-A406-4C0E-9168-89886C909369@.microsoft.com...
>|||The ordering of the columns is one big issue. If you have a query with a whe
re 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 toge
ther will cover the
query. Now you can search for whichever column you have ion the indexes and
you can always have a
s

. 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 f
or 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/
"para

a" <para

a@.discussions.microsoft.com> wrote in message
news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
> 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)?
> "Tibor Karaszi" wrote:
>|||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
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u1CSKoZzFHA.2212@.TK2MSFTNGP15.phx.gbl...
> 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 s

. 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/
>
> "para

a" <para

a@.discussions.microsoft.com> wrote in message
> news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
>

Friday, February 24, 2012

Covering Index Question

No comments:

Post a Comment

CREATE DATABASE failed

Blog Archive

About Me