I've added a clustered index to some large but necessary temp tables, which has vastly improved the performance of some sluggish slow-running stored procedures. Some of the temp tables have a unique identifier data type, which I understand is good practice because it narrows scope. However, having an IDENTITY column in the temp table also causes an error "Msg 2601" when I attempt to add a clustered index because it causes a unique key violation.
My clustered index:
CREATE UNIQUE CLUSTERED INDEX IDX_TEMPANDetails_BuildNumber
ON #TEMPANDetails (BuildNumber)
Error message:
Msg 2601 ...
Cannot insert duplicate key row in object 'dbo.#TEMPANDetails' with unique index 'IDX_TEMPANDetails_BuildNumber'. The duplicate key value is (150362).
Is there a workaround that avoids "Msg 2601" error while retaining a narrow scope? A lot of solutions suggest creating a brand new table. Because these temp tables are so large, I'm not keen to recreate them and insert values into every row.
The following potential two work-arounds appeal, because I only INSERT into the columns with duplicates, i.e., the ones that cause "Msg 2601":
CREATE TABLE #TEMPANDetails <lots of code> ;
<Option A>
IF NOT EXISTS (SELECT 'X'
FROM [dbo].[#TEMPANDetails] AS Target
WHERE [BuildNumber] = '150362')
INSERT INTO [dbo].[#TEMPANDetails] ([BuildNumber], [BuildName])
VALUES ( 'Null', 'Null' )
GO
<Option B>
INSERT INTO [dbo].[#TEMPANDetails] ([BuildNumber], [BuildName])
SELECT '150362', 'Null'
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[#TEMPANDetails]
WHERE [BuildNumber] = 'Null')
GO
CREATE UNIQUE CLUSTERED INDEX IDX_TEMPANDetails_BuildNumber
ON #TEMPANDetails (BuildNumber)
INSERT INTO #TEMPANDetails
SELECT DISTINCT <lots of code> ;
Note: (BuildName) is a placeholder.
From this post (https://dba.stackexchange.com/questions/6736/what-does-x-stand-for-in-this-query), I understand that 'X' is the alias for the inline derived table. Elsewhere I read that the query (above) should work if the temp table hasn't been loaded yet (it hasn't). This StackOverflow post is informative, but not directly related to my problem: How can I catch UniqueKey Violation exceptions with EF6 and SQL Server? ...
Could there be a way to handle these violations nicely in SQL Server using a TRY/CATCH block?
