covering Primary Keys.. What's too much

I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
ChrisWhatever has to be unique must be constrained to be unique. IOW, if your
business rules require that this table have a 6-col combination that is
unique, then you should make that unique - either via a PRIMARY KEY or
UNIQUE constraint. That said, if you will have other tables referencing
this table, then it would make sense to create a surrogate key and then use
that key in the FOREIGN KEY constraints.
That said, consider the surrogate key anyway and cluster on it. Make it,
say, your PK. Then, create a UNIQUE constraint on the 6 col combo. Inserts
into the table - if you use an identity for the PK, will perform well and
the table (i.e. clustered index) will not need to be defragged. The
nonclustered index on the UNIQUE constraint would likely need a fill factor
< 100 to mitigate fragmentation.
HTH
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
I have a table which I'd like to create a Primary Key that would have to
cover 6 fields to enforce uniqueness.
My question is, is that too much. I know it's common to have tables with
PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields, 2
varchar(5) fields, 1 tinyint]
I know I can use a surrogate "ID" identity field as the PK, but I prefer to
use PK's that are more natural if possible.
Any thoughts on this?
Thanks,
Chris|||This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||If that is the case, then you're probably fine without the surrogate key.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Chris" <rooster575@.hotmail.com> wrote in message
news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
This table holds only FK's and could be considered a "detail" table.
It's also almost completely a "read" table. Writing is completed and then
done again very seldomly.
If I add an "ID" column as a PK, it will never be referenced by another
table. [No FK's will exist], which makes me lean more towards the 6-key PK
option. Does this sway your answer at all?
I appreciate your help. After re-reading your response, the solution seems
clear. What do you think?
-Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
> Whatever has to be unique must be constrained to be unique. IOW, if your
> business rules require that this table have a 6-col combination that is
> unique, then you should make that unique - either via a PRIMARY KEY or
> UNIQUE constraint. That said, if you will have other tables referencing
> this table, then it would make sense to create a surrogate key and then
> use
> that key in the FOREIGN KEY constraints.
> That said, consider the surrogate key anyway and cluster on it. Make it,
> say, your PK. Then, create a UNIQUE constraint on the 6 col combo.
> Inserts
> into the table - if you use an identity for the PK, will perform well and
> the table (i.e. clustered index) will not need to be defragged. The
> nonclustered index on the UNIQUE constraint would likely need a fill
> factor
> < 100 to mitigate fragmentation.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23VyZlhmhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> I have a table which I'd like to create a Primary Key that would have to
> cover 6 fields to enforce uniqueness.
> My question is, is that too much. I know it's common to have tables with
> PK's that cover 2-3 fields, but 6 seems like a little much. [3 int fields,
> 2
> varchar(5) fields, 1 tinyint]
> I know I can use a surrogate "ID" identity field as the PK, but I prefer
> to
> use PK's that are more natural if possible.
> Any thoughts on this?
> Thanks,
> Chris
>|||Thanks again.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:eebldFnhGHA.1612@.TK2MSFTNGP04.phx.gbl...
> If that is the case, then you're probably fine without the surrogate key.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> "Chris" <rooster575@.hotmail.com> wrote in message
> news:%23P2RCumhGHA.3996@.TK2MSFTNGP03.phx.gbl...
> This table holds only FK's and could be considered a "detail" table.
> It's also almost completely a "read" table. Writing is completed and then
> done again very seldomly.
> If I add an "ID" column as a PK, it will never be referenced by another
> table. [No FK's will exist], which makes me lean more towards the 6-key PK
> option. Does this sway your answer at all?
> I appreciate your help. After re-reading your response, the solution seems
> clear. What do you think?
> -Chris
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ORD4WpmhGHA.3896@.TK2MSFTNGP02.phx.gbl...
>|||>> I have a table which I'd like to create a Primary Key that would have to cove
r 6 fields [sic] to enforce uniqueness. My question is, is that too much. <<
This is rare but possible. Since we do not have any specs or knwo
anything about the problem, all we can do is generalize. I have seen
some people create super-keys (sertial number + manufacturer, not
knowning that serial number has manufacturer in it).
1) it is not a surrogate key because it is exposed to the user; that is
the definition from Dr. Codd.
2) you will need to use a UNIQUE (c1,.. ,c6) constraint anyway, so you
now have two indexes
3) You will need code to assure that the IDENTITY is always validate
and points to the right target.

Covering indexes in SQL Server 2005

Hi,
To look up information about indexes for a given table in SQL Server
2005, I can use:
sp_helpindex TableName
or
select * from sys.indexes where object_id = object_id('TableName')
But this just gives me information about the column or columns on which
the index has been defined. How do I find out what columns may have
been included in the index?
For example, if I created the following index:
CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
Where will I find complete information about IX_1 - which also
indicates whether this is a covering index and if yes - what columns
were included?
Thanks much,
SmithaHi Smitha
You'll need to join sys.indexes with sys.index_columns, and sys.columns.
Here is a view I created to do that:
CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name, 'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
So after creating this view, you can use it as follows:
SELECT * FROM get_index_columns
WHERE object_name = 'TableName'
--
HTH
Kalen Delaney, SQL Server MVP
<smithabreddy@.gmail.com> wrote in message
news:1155047736.933287.185730@.i42g2000cwa.googlegroups.com...
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>|||Hi
Check out sys.index_columns and the is_included_column bit/
John
"smithabreddy@.gmail.com" wrote:
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>

Covering indexes in SQL Server 2005

Hi,
To look up information about indexes for a given table in SQL Server
2005, I can use:
sp_helpindex TableName
or
select * from sys.indexes where object_id = object_id('TableName')
But this just gives me information about the column or columns on which
the index has been defined. How do I find out what columns may have
been included in the index?
For example, if I created the following index:
CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
Where will I find complete information about IX_1 - which also
indicates whether this is a covering index and if yes - what columns
were included?
Thanks much,
SmithaHi Smitha
You'll need to join sys.indexes with sys.index_columns, and sys.columns.
Here is a view I created to do that:
CREATE VIEW get_index_columns
AS
SELECT object_name(ic.object_id) as object_name , index_name = i.name,
'column' = c.name, 'column usage' = CASE ic.is_included_column
WHEN 0 then 'KEY'
ELSE 'INCLUDED'
END
FROM sys.index_columns ic JOIN sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
So after creating this view, you can use it as follows:
SELECT * FROM get_index_columns
WHERE object_name = 'TableName'
HTH
Kalen Delaney, SQL Server MVP
<smithabreddy@.gmail.com> wrote in message
news:1155047736.933287.185730@.i42g2000cwa.googlegroups.com...
> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>|||Hi
Check out sys.index_columns and the is_included_column bit/
John
"smithabreddy@.gmail.com" wrote:

> Hi,
> To look up information about indexes for a given table in SQL Server
> 2005, I can use:
> sp_helpindex TableName
> or
> select * from sys.indexes where object_id = object_id('TableName')
> But this just gives me information about the column or columns on which
> the index has been defined. How do I find out what columns may have
> been included in the index?
> For example, if I created the following index:
> CREATE INDEX IX_1 ON TableName(ColA) INCLUDE(ColB)
> Where will I find complete information about IX_1 - which also
> indicates whether this is a covering index and if yes - what columns
> were included?
> Thanks much,
> Smitha
>

covering indexes

Hi,
I asked the similar question before but I have again some doubts about covering indexes.
Besides, tomorrow I have a Microsoft MCAD 70-229 exam so please help me.
In here,
SELECT * FROM Order WHERE OrderID > 12 ORDER BY OrderDate

we create composite nonclustered index for both OrderID and OrderDate column.
Leftmost is OrderID.
So

when our first research is happening(Where clause), the only

nonclustered index which is used is OrderID index. And then, when we

pass through the second search(ORDER BY clause), OrderDate index become activated.
So

we can say that the seleection of indexes in composite indexes is

determined according to the situation of the query at that time.

Hence, is this all correct ?

Best wishes,
Mert

Hmm,
Now thanks to the strict studies on indexes, I conceived that my approach to this is completely false.
So again in my opinion, the best solution for the above query is that we must use two separate indexes and for Ordeing we should use nonclustered index since it brings much performance benefit, for instance; we do not have to look up the data table since we will have a available ordered set.
So would you please explain your own opinions ?

Thanks

covering indexes

In lot of atricles regarding sql server performacne i read about "consider
using covering indexes" .
my question is How do u create a covering index, can we create it explicitly
or
sql server creates it automatically?
Thanks
Sri
Hi Sri
I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.
A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.
So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
> explicitly
> or
> sql server creates it automatically?
>
> Thanks
> Sri
>
>
|||"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
explicitly
> or
> sql server creates it automatically?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

covering indexes

In lot of atricles regarding sql server performacne i read about "consider
using covering indexes" .
my question is How do u create a covering index, can we create it explicitly
or
sql server creates it automatically?
Thanks
SriHi Sri
I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.
A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.
So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
> explicitly
> or
> sql server creates it automatically?
>
> Thanks
> Sri
>
>|||"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
explicitly
> or
> sql server creates it automatically?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

covering indexes

In lot of atricles regarding sql server performacne i read about "consider
using covering indexes" .
my question is How do u create a covering index, can we create it explicitly
or
sql server creates it automatically?
Thanks
SriHi Sri
I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.
A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.
So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
> explicitly
> or
> sql server creates it automatically?
>
> Thanks
> Sri
>
>|||"Sri" <Sri@.discussions.microsoft.com> wrote in message
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@.microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
> my question is How do u create a covering index, can we create it
explicitly
> or
> sql server creates it automatically?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004

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/
---
"paraa" 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/
"paraa" <paraa@.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 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
>|||Hi
As well , read this article
http://www.sql-server-performance.c...ing_indexes.asp
"paraa" <paraa@.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/
>
> "paraa" <paraa@.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/
"paraa" <paraa@.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/
>
> "paraa" <paraa@.discussions.microsoft.com> wrote in message
> news:7380D5D0-1345-4949-9D1E-318C9C85AADB@.microsoft.com...
>

covering index query

hi all,
can anybody help me out with a covering index query and
say how exactly it helps out in data retreival?
thanks,
james bondFrom the SQL 2000 Books Online:
<Excerpt href="http://links.10026.com/?link=createdb.chm::/cm_8_des_05_2ri0.htm">
Covered queries can improve performance. Covered queries are queries where
all the columns specified in the query are contained within the same index.
For example, a query retrieving columns a and b from a table that has a
composite index created on columns a, b, and c is considered covered.
Creating indexes that cover a query can improve performance because all the
data for the query is contained within the index itself; only the index
pages, not the data pages, of the table must be referenced to retrieve the
data, thereby reducing overall I/O. Although adding columns to an index to
cover queries can improve performance, maintaining the extra columns in the
index incurs update and storage costs.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"jamesbond" <anonymous@.discussions.microsoft.com> wrote in message
news:063201c3b57d$0b2e0290$a301280a@.phx.gbl...
> hi all,
> can anybody help me out with a covering index query and
> say how exactly it helps out in data retreival?
> thanks,
> james bond

Covering Index

Hi,
How can one create covering index and what are the benefits of having covering index?
Thanks much in advance
Thank
GYKCheck out: http://www.sql-server-performance.com/covering_indexes.asp
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
> How can one create covering index and what are the benefits of having
covering index?
> Thanks much in advance.
> Thanks
> GYK|||Hi,
There is no seperate syntax for Covering Index. Covering index is nothing
but, if your query (Select statement) reads the data from Index page
rather than scanning data page.
For result, Since the Query reads the Index page the execution will be much
faster.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
> How can one create covering index and what are the benefits of having
covering index?
> Thanks much in advance.
> Thanks
> GYK|||Hi Hari,
Thanks for your reponse. But the way you have explained, it looks more like a non-clustered index. Even in non-clustered index, the data is first scanned through the index pages(pointers) and then the data is fetched. Am not able to understand the exact difference between them.
Thanks
GYK
-- Hari wrote: --
Hi,
There is no seperate syntax for Covering Index. Covering index is nothing
but, if your query (Select statement) reads the data from Index page
rather than scanning data page.
For result, Since the Query reads the Index page the execution will be much
faster.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> Hi,
>> How can one create covering index and what are the benefits of having
covering index?
>> Thanks much in advance.
>> Thanks
> GYK|||It is a bit similar to a clustered index, actually. Imagine you have an NC
index which contains all the columns you are referring to in your query.
Then there is no need for SQL Server to go an visit the data pages. The
index pages has all the information that the query refers to!
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:A3193BD2-EA46-4299-9ED1-6A2DA35C8E86@.microsoft.com...
> Hi Hari,
> Thanks for your reponse. But the way you have explained, it looks more
like a non-clustered index. Even in non-clustered index, the data is first
scanned through the index pages(pointers) and then the data is fetched. Am
not able to understand the exact difference between them.
> Thanks
> GYK
> -- Hari wrote: --
> Hi,
> There is no seperate syntax for Covering Index. Covering index is
nothing
> but, if your query (Select statement) reads the data from Index page
> rather than scanning data page.
> For result, Since the Query reads the Index page the execution will
be much
> faster.
>
> Thanks
> Hari
> MCDBA
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:3F6E4037-F2B1-4A05-8310-B463ED14E0CA@.microsoft.com...
> > Hi,
> >> How can one create covering index and what are the benefits of
having
> covering index?
> >> Thanks much in advance.
> >> Thanks
> > GYK
>
>|||Hi,
Thanks guys, I think i got the concept :-)
Basically I guess it is just a concept, where in all the information provided by the users (in the where clause) is covered by the non-clustered index, and it refers to the index pages rather than the data pages. Hope am right here
Thanks very muc
Yogish|||Hi Yogesh,
Like Tibor mentioned , not only the where clause even the contents in select
clause will be picked from Index page.
eg:
If You have an index on empno, empname,empsex field in empdetails table,
you query is:
select empno,empname,empsex from empdetails where empno betweeen 100 and 110
and empsex='m'
In this case empno , empname and empsex will be picked from Index page, this
select statement never reads the data page to extract emp info.
Tibor,
Correct me if I am wrong.
Thanks
Hari
MCDBA
"GYK" <anonymous@.discussions.microsoft.com> wrote in message
news:43255A14-BA5A-4003-805F-57B2E9190AD6@.microsoft.com...
> Hi,
> Thanks guys, I think i got the concept :-)
> Basically I guess it is just a concept, where in all the information
provided by the users (in the where clause) is covered by the non-clustered
index, and it refers to the index pages rather than the data pages. Hope am
right here?
> Thanks very much
> Yogish|||> In this case empno , empname and empsex will be picked from Index page,
this
> select statement never reads the data page to extract emp info.
> Tibor,
> Correct me if I am wrong.
Correctomundo! :-)
Also, SQL Server can even combine several NC indexes so that the combination
of those covers a query. So we might want to differentiate between a covered
index (the index covers the whole query) and a query which is covered by
indexes (where (possibly) several indexes in combination covers a query).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23spIxkQqDHA.4004@.TK2MSFTNGP11.phx.gbl...
> Hi Yogesh,
> Like Tibor mentioned , not only the where clause even the contents in
select
> clause will be picked from Index page.
> eg:
> If You have an index on empno, empname,empsex field in empdetails table,
> you query is:
> select empno,empname,empsex from empdetails where empno betweeen 100 and
110
> and empsex='m'
> In this case empno , empname and empsex will be picked from Index page,
this
> select statement never reads the data page to extract emp info.
> Tibor,
> Correct me if I am wrong.
>
> Thanks
> Hari
> MCDBA
>
>
> "GYK" <anonymous@.discussions.microsoft.com> wrote in message
> news:43255A14-BA5A-4003-805F-57B2E9190AD6@.microsoft.com...
> > Hi,
> >
> > Thanks guys, I think i got the concept :-)
> >
> > Basically I guess it is just a concept, where in all the information
> provided by the users (in the where clause) is covered by the
non-clustered
> index, and it refers to the index pages rather than the data pages. Hope
am
> right here?
> >
> > Thanks very much
> > Yogish
>|||Hi Hari, Tibor
Thanks a lot. I get the picture now...
GYK|||I have a question to what Hari has sai
Hari said, the following
Table - Empdetail
Query - Select empno,empname,empsex from empdetails where empno betweeen 100 and 11
and empsex='m
Index - empno, empname,empse
My questio
=======Index is on 3 columns, but "Where" clause is on 2 columns, will still the index be used|||Yes.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Prasanna" <anonymous@.discussions.microsoft.com> wrote in message
news:A200A69F-42AF-4233-AAA1-7A44C300E59C@.microsoft.com...
> I have a question to what Hari has said
> Hari said, the following
> Table - Empdetails
> Query - Select empno,empname,empsex from empdetails where
empno betweeen 100 and 110
> and empsex='m'
> Index - empno, empname,empsex
> My question
> ========> Index is on 3 columns, but "Where" clause is on 2 columns, will still the
index be used?
>