Index Question

Hi,
I have the following table:
create table ProjectResource
(
ProjectCode nvarchar(20) not null,
RevisionNum int not null,
ResourceID nvarchar(15) not null,
ResourceSiteURN nvarchar(128) not null,
ActiveFlag int not null
default 1
constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
PrimaryFlag int not null
default 0
constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
(1,0)),
EPMProjectUID uniqueidentifier null ,
EPMResourceUID uniqueidentifier null ,
constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
ResourceID, ResourceSiteURN)
)
You will see that the PK constraint contains the ProjectCode, RevisionNum,
ResourceID, and ResourceSiteURN columns. I've been given a request to add a
new index on the table where it contains the following columns in the order
they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
PrimaryFlag.
What are the pros and cons of creating this new index?
Thanks in advance,
DeeThe pro is that an index that includes no more than those five columns
will be covered by the non-clustered index, and will perform a bit
faster.
The con is that the index takes up considerable space, and adds a
certain amount of overhead when rows are inserted or deleted.
Roy Harvey
Beacon Falls, CT
On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Hi,
>I have the following table:
>create table ProjectResource
>(
> ProjectCode nvarchar(20) not null,
> RevisionNum int not null,
> ResourceID nvarchar(15) not null,
> ResourceSiteURN nvarchar(128) not null,
> ActiveFlag int not null
> default 1
> constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> PrimaryFlag int not null
> default 0
> constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
>(1,0)),
> EPMProjectUID uniqueidentifier null ,
> EPMResourceUID uniqueidentifier null ,
> constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
>ResourceID, ResourceSiteURN)
>)
>You will see that the PK constraint contains the ProjectCode, RevisionNum,
>ResourceID, and ResourceSiteURN columns. I've been given a request to add a
>new index on the table where it contains the following columns in the order
>they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
>PrimaryFlag.
>What are the pros and cons of creating this new index?
>Thanks in advance,
>Dee|||Thanks, Roy, for your quick response! I was a bit concerned at first since
we already have a clustered index on four of the five columns that are in the
non-clustered index. I thought that maybe this non-clustered index is
considered as a "duplicate" index.
"Roy Harvey (SQL Server MVP)" wrote:
> The pro is that an index that includes no more than those five columns
> will be covered by the non-clustered index, and will perform a bit
> faster.
> The con is that the index takes up considerable space, and adds a
> certain amount of overhead when rows are inserted or deleted.
> Roy Harvey
> Beacon Falls, CT
>
> On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> <bpdee@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have the following table:
> >
> >create table ProjectResource
> >(
> > ProjectCode nvarchar(20) not null,
> > RevisionNum int not null,
> > ResourceID nvarchar(15) not null,
> > ResourceSiteURN nvarchar(128) not null,
> > ActiveFlag int not null
> > default 1
> > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > PrimaryFlag int not null
> > default 0
> > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> >(1,0)),
> > EPMProjectUID uniqueidentifier null ,
> > EPMResourceUID uniqueidentifier null ,
> > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> >ResourceID, ResourceSiteURN)
> >)
> >
> >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> >new index on the table where it contains the following columns in the order
> >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> >PrimaryFlag.
> >
> >What are the pros and cons of creating this new index?
> >
> >Thanks in advance,
> >Dee
>|||On Tue, 13 Nov 2007 11:31:02 -0800, bpdee
<bpdee@.discussions.microsoft.com> wrote:
>Thanks, Roy, for your quick response! I was a bit concerned at first since
>we already have a clustered index on four of the five columns that are in the
>non-clustered index. I thought that maybe this non-clustered index is
>considered as a "duplicate" index.
I would not normally set up an index like that unless there was a
specific need for a covered index that it satisfied. Other than that
it really serves no purpose at all.
Roy Harvey
Beacon Falls, CT|||I agree with Roy. When the index key is narrow, and the row size
relatively wide, then such a "duplicate" index could be useful for
covering queries. However, in this case the row is only 36 bytes wider
than the index key of the "duplicate" index, which is probably not even
50% larger than the key size.
Had the primary key just been one int, then IMO it would have been a
different matter. As it is, I think adding this extra index is a waste
of space and will cause unnecessary contention for
inserts/updates/deletes.
BTW: I wonder why the two "Flag" columns are defined as int, and not as
something like tinyint.
--
Gert-Jan
bpdee wrote:
> Thanks, Roy, for your quick response! I was a bit concerned at first since
> we already have a clustered index on four of the five columns that are in the
> non-clustered index. I thought that maybe this non-clustered index is
> considered as a "duplicate" index.
> "Roy Harvey (SQL Server MVP)" wrote:
> > The pro is that an index that includes no more than those five columns
> > will be covered by the non-clustered index, and will perform a bit
> > faster.
> >
> > The con is that the index takes up considerable space, and adds a
> > certain amount of overhead when rows are inserted or deleted.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >
> >
> > On Tue, 13 Nov 2007 11:02:02 -0800, bpdee
> > <bpdee@.discussions.microsoft.com> wrote:
> >
> > >Hi,
> > >
> > >I have the following table:
> > >
> > >create table ProjectResource
> > >(
> > > ProjectCode nvarchar(20) not null,
> > > RevisionNum int not null,
> > > ResourceID nvarchar(15) not null,
> > > ResourceSiteURN nvarchar(128) not null,
> > > ActiveFlag int not null
> > > default 1
> > > constraint CK_ProjectResource_ActiveFlag check (ActiveFlag in (1,0)),
> > > PrimaryFlag int not null
> > > default 0
> > > constraint CK_ProjectResource_PrimaryFlag check (PrimaryFlag in
> > >(1,0)),
> > > EPMProjectUID uniqueidentifier null ,
> > > EPMResourceUID uniqueidentifier null ,
> > > constraint PK_ProjectResource primary key (ProjectCode, RevisionNum,
> > >ResourceID, ResourceSiteURN)
> > >)
> > >
> > >You will see that the PK constraint contains the ProjectCode, RevisionNum,
> > >ResourceID, and ResourceSiteURN columns. I've been given a request to add a
> > >new index on the table where it contains the following columns in the order
> > >they are given: ProjectCode, RevisionNum, ResourceID, ResourceSiteURN,
> > >PrimaryFlag.
> > >
> > >What are the pros and cons of creating this new index?
> > >
> > >Thanks in advance,
> > >Dee
> >