November 17, 2021 at 4:16 pm
Adding PK to existing table like in the below example with existing Clustered index automatically creates an index on PK column. But I already have a CI index there, we don't want another one.
is Turning Clustered index into PK without creating additional index possible via T-SQL?
create table Z1 (id int not null, name varchar(10))
create clustered index ci1 on z1(id)
--Alter table z1 add primary key(ID)
alter table Z1 Add constraint PK_Z1 primary key (id)
-- alter table Z1 Drop constraint PK_Z1
drop table Z1
Likes to play Chess
November 17, 2021 at 4:29 pm
No. If you really need/want to convert the unique index to a primary key, you will have to drop the index and create the primary key - I don't believe you can use with drop_existing for a primary key.
It's probably not necessary. It offends our sense of consistency and aesthetics, but the clustered index serves the same purpose. Most SQL Server limitations involving primary key will work with a unique index or a primary key.
November 17, 2021 at 4:40 pm
No. If you really need/want to convert the unique index to a primary key, you will have to drop the index and create the primary key - I don't believe you can use with drop_existing for a primary key. It's probably not necessary. It offends our sense of consistency and aesthetics, but the clustered index serves the same purpose. Most SQL Server limitations involving primary key will work with a unique index or a primary key.
Agreed, while noting that the index in the example given is not defined as unique.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 17, 2021 at 5:52 pm
I am doing it this way as it seems the only way:
Drop index ci1 on Z1
alter table Z1 Add constraint PK_Z1 primary key clustered (id) on [primary]
Likes to play Chess
November 17, 2021 at 6:53 pm
It's probably not necessary. It offends our sense of consistency and aesthetics, but the clustered index serves the same purpose. Most SQL Server limitations involving primary key will work with a unique index or a primary key.
What does the clustered index serve as the same purpose? A primary key? A unique index? Can you explain this?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 17, 2021 at 7:21 pm
create clustered index ci1 on z1(id) --> creates a non-unique clustered index ( that holds the data at leaf level ! )
alter table Z1 Add constraint PK_Z1 primary key (id) --> creates a unique index which - in this case - points to a clustered index key
Modifying this all to a single index ( keep IO in mind !! )
Begin tran
ALTER TABLE Z1 DROP CONSTRAINT PK_Z1 ;
drop index ci1 on Z1;
alter table Z1 Add constraint PK_Z1 primary key CLUSTERED (id)
-- commit tran
-- rollback tran
Keep in mind to perform this in a TRANSACTION !! so your action actually prevents others from trying to modify any data!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 17, 2021 at 7:37 pm
A primary key is really just a unique index (clustered or not, depending on how it was created). Microsoft has wrapped it w/ some logic that syntactically implements it as a constraint instead of an index. But a primary is treated as an index in query execution plans.
So, a unique clustered index is equivalent to a primary key. As Phil pointed out, I had missed the fact that the definition didn't define it as unique. The identity column on which the index was defined assured uniqueness, but features like foreign keys, replication, or indexed views, would require a unique index.
Your old clustered index was not equivalent to a primary key because it was not specified as unique. (When you know a clustered index is inherently unique, it's best to define it as a unique index so that SQL Server doesn't create a uniquifier column to ensure it's uniqueness.
November 17, 2021 at 8:06 pm
A primary key is really just a unique index (clustered or not, depending on how it was created). Microsoft has wrapped it w/ some logic that syntactically implements it as a constraint instead of an index. But a primary is treated as an index in query execution plans.
So, a unique clustered index is equivalent to a primary key. As Phil pointed out, I had missed the fact that the definition didn't define it as unique. The identity column on which the index was defined assured uniqueness, but features like foreign keys, replication, or indexed views, would require a unique index.
Your old clustered index was not equivalent to a primary key because it was not specified as unique. (When you know a clustered index is inherently unique, it's best to define it as a unique index so that SQL Server doesn't create a uniquifier column to ensure it's uniqueness.
Clustered has nothing to do with a unique index or a primary key. They are completely separate constructs.
So, a unique clustered index is equivalent to a primary key.
That is not true. A primary key cannot contain a null, a unique index can contain one null. Since a null is undefined, treating a PK and a unique index as equal may cause issues. And, again, clustered has no bearing on this
The identity column on which the index was defined assured uniqueness.
That is not true. There is nothing that guarantees uniqueness on an identity column unless a unique index or primary key is created on that column.
but features like foreign keys, replication, or indexed views, would require a unique index.
The first index on an indexed view must be a clustered unique index. However, no such restriction exists on the underlying table(s).
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 22, 2021 at 3:04 am
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply