November 1, 2011 at 6:45 am
I'm trying to create a table - there will be others for a partitioned view. Column names only for test script.
--if object_id('TestForSSC', 'U') is not null
-- drop table TestForSSC
create table TestForSSC
(
Ref1 nvarchar(10) not null,
Ref2 nvarchar(10) not null,
Ref_Timestamp smalldatetime not null,
Ref_Value decimal(18, 4) not null
)
--primary key
alter table TestForSSC add constraint PK_TestForSSC
primary key nonclustered (Ref1, Ref2, Ref_Timestamp)
--clustered index (not unique) - used later for partitioned view
create clustered index IX_TestForSSC_Ref_Timestamp on
TestForSSC (Ref_Timestamp)
--re-create non-clustered index for primary key with include column
drop index PK_TestForSSC on TestForSSC
create unique nonclustered index PK_TestForSSC on
TestForSSC (Ref1, Ref2, Ref_Timestamp) include (Ref_Value)
--checks
alter table TestForSSC add constraint CK_TestForSSC_Ref_Timestamp
check (Ref_Timestamp < '01/01/2009')
I'm getting error:
Msg 3723, Level 16, State 4, Line 23
An explicit DROP INDEX is not allowed on index 'TestForSSC.PK_TestForSSC'. It is being used for PRIMARY KEY constraint enforcement.
I want to create a non-clustered index (with an include column) that is also the primary key.
If I don't first create the primary key, it will then create the non-clustered index ok, but I then haven't then got the primary key constraint. And if I try to create the primary key, won't this destroy the existing index on the columns?
November 1, 2011 at 8:16 am
A PK constraint is entirely enforced by it's index. They're intrinsically linked and cannot be seperated/changed without dropping the PK.
When the PK is Non-Clustered, this creates a Non-Clustered Unique index that cannot be altered in any way.
In order to have a non-clustered index with included columns as the one you've described, it would have to be in addition to the index that supports the PK, it can't replace it.
In this case (and I suspect your real world example is more complex), as Ref1 and Ref2 aren't particularly wide, the simple solution could be to just define your clustered PK as (Ref_Timestamp, Ref1, Ref2) in that order, therefore, your inserts would be in clustered index order and as the timestamp is the first column in the index, seeks can be performed against it.
November 1, 2011 at 8:34 am
Thanks.
I've just checked and my two Ref columns could actually be up to 50 wide which I think is the reason I had the clustered index only on the DateTime column (tried to read up on indexing and there was much on keeping the clustered index small).
The other reason, in the current version I'm using (without partitioned view), I had a big performance gain when I added the Include column - and that's why I wanted to retain a non-clustered index.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply