Reputation: 2570
I need to drop and rebuild a primary key in a temp table in SQL Server. The most common solution requires you to enter the name of the PK index. However, since this is inside a store procedure, doing so limits any parallel use of this sp.
CREATE TABLE #mytable (Date_ DATETIME NOT NULL, Name_ CHAR(50) NOT NULL, VALUE_ INT )
ALTER TABLE #mytable ADD PRIMARY KEY CLUSTERED (Date_, Name_)
Now I need to drop this primary key and create a new one on just the Name_ column.
For e.g.,
ALTER TABLE #mytable DROP OLD PK
ALTER TABLE #mytable ADD PRIMARY KEY CLUSTERED (Name_)
Upvotes: 2
Views: 6385
Reputation: 7227
Dynamic SQL and NEWID()
will give you a unique identifier that you can append to the PK's name.
DECLARE @GUID VARCHAR(50)
SELECT @GUID = REPLACE(NEWID(), '-', '')
PRINT @GUID
CREATE TABLE #mytable (Date_ DATETIME NOT NULL, Name_ CHAR(50) NOT NULL, VALUE_ INT )
EXEC('ALTER TABLE #mytable ADD CONSTRAINT PK_'+@GUID+' PRIMARY KEY CLUSTERED (Date_, Name_)')
EXEC('ALTER TABLE #mytable DROP CONSTRAINT PK_'+@GUID)
ALTER TABLE #mytable ADD PRIMARY KEY CLUSTERED (Name_)
DROP TABLE #MyTable
Upvotes: 2