SQL Server Performance Forum – Threads Archive
primary key index problem
I am a newly hired person in a company. Their server is having a big performance issue and I tried to re-index the table at the beginning.The problem is, someone choose a primary key only as an index with out other column, I would like to remove it , but it always give me an error message that "it is used for primary key constraint reforcement".
IS there anyway to remove it? It is wasting the cluster index…. Thanks
You can’t use the DROP INDEX statement, which I assume you tried. Try something like this:
CREATE TABLE #t
(
col1 INT IDENTITY CONSTRAINT pk_id PRIMARY KEY
, col2 INT
)
GO
ALTER TABLE #t DROP CONSTRAINT pk_id
GO
CREATE CLUSTERED INDEX pk_CI ON #t(col2)
DROP TABLE #t ———————–
–Frank
http://www.insidesql.de
———————–
Ooh, I forgot, depending on the size of your table(s) expect this to take a long time when there are other nonclustered indexes.
Here’s the rationale from BOL:
quote:
If a clustered index is created on a table with several secondary indexes, all of the secondary indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Likewise, if a clustered index is deleted on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables. The preferred way to build indexes on large tables is to start with the clustered index and then build the nonclustered indexes. When dropping all indexes, drop the nonclustered indexes first and the clustered index last. That way, no indexes need to be rebuilt.
If a clustered index is created on a table with several secondary indexes, all of the secondary indexes must be rebuilt so that they contain the clustering key value instead of the row identifier (RID). Likewise, if a clustered index is deleted on a table that has several nonclustered indexes, the nonclustered indexes are all rebuilt as part of the DROP operation. This may take significant time on large tables. The preferred way to build indexes on large tables is to start with the clustered index and then build the nonclustered indexes. When dropping all indexes, drop the nonclustered indexes first and the clustered index last. That way, no indexes need to be rebuilt.
———————–
–Frank
http://www.insidesql.de
———————–
Damn, one man show. Copy and paste isn’t that easy.
Forgot this CREATE TABLE #t
(
col1 INT IDENTITY CONSTRAINT pk_id PRIMARY KEY
, col2 INT
)
GO
ALTER TABLE #t DROP CONSTRAINT pk_id
GO
ALTER TABLE #t ADD CONSTRAINT pk_id PRIMARY KEY NONCLUSTERED (col1)
CREATE CLUSTERED INDEX pk_CI ON #t(col2)
DROP TABLE #t
in case you want to keep the PRIMARY KEY but only make it NONCLUSTERED. ———————–
–Frank
http://www.insidesql.de
———————–
ok. What I need to do is to first drop the constraint pk_id
and then recreate the index I want, right?
Is their any problem by dropping the constraint?
Define problem, please! ———————–
–Frank
http://www.insidesql.de
———————–
]]>