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,
Dee
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
|||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:
>
|||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
sql