April 21, 2009 at 6:00 am
Hi,
I have a Non cluster Index on a PK column. and I need to Include one more column to the existing non cluster index.
Table Script:
-----------------
CREATE TABLE [dbo].[Request](
[RequestID] [bigint] IDENTITY(1,100) NOT FOR REPLICATION NOT NULL,
[Requestor] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[RequestorTypeID] [smallint] NOT NULL,
[RequestTypeID] [tinyint] NOT NULL,
[RequestStatusID] [tinyint] NOT NULL,
[ServerID] [smallint] NOT NULL,
[ApplicationID] [int] NOT NULL,
[LogGUID] [uniqueidentifier] NULL,
[ChangeControl] [timestamp] NOT NULL,
[CreatedBy] [varchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_Request_CreatedBy] DEFAULT (suser_sname()),
[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_Request_CreatedDate] DEFAULT (getdate()),
[PlatformID] [tinyint] NOT NULL CONSTRAINT [DF_Request_PlatformID] DEFAULT (1),
CONSTRAINT [RequestPK] PRIMARY KEY NONCLUSTERED
(
[RequestID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Need to Include CreatedDate clumn to already existing Index RequestPK. Can some one help me on this.
April 21, 2009 at 6:50 am
Indexes created by constraints (like primary key) do not allow include columns.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2009 at 5:34 am
Do we have any alternate for this. Actually Including Createddate column to the existing Non cluster Index as recommended by DTA.
April 22, 2009 at 9:55 am
Other than dropping the primary key (bad idea), you could create a second index.
Where's the clustered index on this table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 4:52 am
There is no cluster Index on this table
sp_helpindex request
output:
---------
index_name index_description index_keys
--------------------------------------------------------------------------------------------------------------------------------
Requestor nonclustered located on PRIMARY Requestor, RequestorTypeID, ApplicationID
RequestPK nonclustered, unique, primary key located on PRIMARY RequestID
RequestStatus nonclustered located on PRIMARY RequestStatusID, ServerID, CreatedDate(-)
As per the DTA recommendations I need to include CreatedDate column to Non Cluster Index RequestPK.
Can you please suggest what to do??
Thanks
Naveen.
April 23, 2009 at 7:31 am
Change the primary key to clustered. Is there any good reason it was created as a nonclustered index in the first place?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 23, 2009 at 12:03 pm
Thanks a lot for your response.
Actually I am not sure why they have created a non clustered index on this column. I just got a work to implement the recommendatins of DTA and to check whether the performance is improved or not.
Do you think we may have any possible good reason for this.
I also think instead of non cluster we should have a cluster index on this column as its a identity column.
If I am going for a Cluster Index can I include a extra column to that as suggested?
April 23, 2009 at 1:07 pm
Naveen Kumar Tiwary (4/23/2009)
If I am going for a Cluster Index can I include a extra column to that as suggested?
It won't be necessary. Take a look at the architecture of clustered indexes (in books online), specifically at what the leaf level of the clustered index is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply