Return to Answer

changed the contrast (reasonable vs less reasonable instead of short vs long) and slightly rearranged for clarity; improved formatting; corrected typos

Source Link

edited Apr 30, 2018 at 19:19

Andriy M

23.3k
6
60
104

Short Version - No, youReasonable Answer: No.

You really should just let it create the unique index and let it do it's job. Space is cheap.

Edit - If you make the column you want unique the clustered index key, then youit won't cost you any additional disk space, but it may slow down access to the table if your queries don't go through the unique column.

Long Version -Less Reasonable Answer: Yes... but

But it burns CPU, increases disk I/O and slows down just about everything.

You put an AFTERAFTER trigger on the table and have it count the number of recordsrows in the base table for the column you want to make unique. If the count is greater than 0 then rollback the transaction.

Example: (Of how NOT to do it)Example (of how not to do it):

CREATE TABLE dbo.Test ( TestID INT NOT NULL PRIMARY KEY IDENTITY(1,1) , TestValue VARCHAR(100) NOT NULL ); GO CREATE TRIGGER trg_TestInsert ON dbo.Test AFTER INSERT, UPDATE AS BEGIN DECLARE @DuplicateCount INT; SET @DuplicateCount = 0; ;WITHWITH CTE_Count AS ( SELECT TestValue, COUNT(*) AS TestValueCount FROM dbo.Test GROUP BY TestValue ) SELECT @DuplicateCount = COUNT(TestValue) FROM CTE_Count WHERE TestValueCount >=2; IF @DuplicateCount >= 1 BEGIN ROLLBACK; END END; GO /** Test Insert Statement */ INSERT INTO dbo.Test (TestValue) VALUES ('TestMe'); INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); --HINT: This one will fail. INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); SELECT TestID, TestValue FROM dbo.Test; --Cleanup. DROP TABLE dbo.Test;

Short Version - No, you really should just let it create the unique index and let it do it's job. Space is cheap.

Edit - If you make the column you want unique the clustered index key, then you won't cost you any additional disk space, but it may slow down access to the table if your queries don't go through the unique column.

Long Version - Yes... but it burns CPU, increases disk I/O and slows down just about everything.

You put an AFTER trigger on the table and have it count the number of records in the base table for the column you want to make unique. If the count is greater than 0 then rollback the transaction.

Example: (Of how NOT to do it)

CREATE TABLE dbo.Test ( TestID INT NOT NULL PRIMARY KEY IDENTITY(1,1) , TestValue VARCHAR(100) NOT NULL ); GO CREATE TRIGGER trg_TestInsert ON dbo.Test AFTER INSERT, UPDATE AS BEGIN DECLARE @DuplicateCount INT; SET @DuplicateCount = 0; ;WITH CTE_Count AS ( SELECT TestValue, COUNT(*) AS TestValueCount FROM dbo.Test GROUP BY TestValue ) SELECT @DuplicateCount = COUNT(TestValue) FROM CTE_Count WHERE TestValueCount >=2; IF @DuplicateCount >= 1 BEGIN ROLLBACK; END END; GO /** Test Insert Statement */ INSERT INTO dbo.Test (TestValue) VALUES ('TestMe'); INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); --HINT: This one will fail. INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); SELECT TestID, TestValue FROM dbo.Test; --Cleanup. DROP TABLE dbo.Test;

Reasonable Answer: No.

You really should just let it create the unique index and let it do it's job. Space is cheap.

If you make the column you want unique the clustered index key, then it won't cost you any additional disk space, but it may slow down access to the table if your queries don't go through the unique column.

Less Reasonable Answer: Yes.

But it burns CPU, increases disk I/O and slows down just about everything.

You put an AFTER trigger on the table and have it count the number of rows in the base table for the column you want to make unique. If the count is greater than 0 then rollback the transaction.

Example (of how not to do it):

CREATE TABLE dbo.Test ( TestID INT NOT NULL PRIMARY KEY IDENTITY(1,1) , TestValue VARCHAR(100) NOT NULL ); GO CREATE TRIGGER trg_TestInsert ON dbo.Test AFTER INSERT, UPDATE AS BEGIN DECLARE @DuplicateCount INT; SET @DuplicateCount = 0; WITH CTE_Count AS ( SELECT TestValue, COUNT(*) AS TestValueCount FROM dbo.Test GROUP BY TestValue ) SELECT @DuplicateCount = COUNT(TestValue) FROM CTE_Count WHERE TestValueCount >=2; IF @DuplicateCount >= 1 BEGIN ROLLBACK; END END; GO /** Test Insert Statement */ INSERT INTO dbo.Test (TestValue) VALUES ('TestMe'); INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); --HINT: This one will fail. INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); SELECT TestID, TestValue FROM dbo.Test; --Cleanup. DROP TABLE dbo.Test;

Source Link

answered Apr 30, 2018 at 13:53

Jonathan Fite

9.4k
1
26
30

Short Version - No, you really should just let it create the unique index and let it do it's job. Space is cheap.

Long Version - Yes... but it burns CPU, increases disk I/O and slows down just about everything.

You put an AFTER trigger on the table and have it count the number of records in the base table for the column you want to make unique. If the count is greater than 0 then rollback the transaction.

Example: (Of how NOT to do it)

CREATE TABLE dbo.Test ( TestID INT NOT NULL PRIMARY KEY IDENTITY(1,1) , TestValue VARCHAR(100) NOT NULL ); GO CREATE TRIGGER trg_TestInsert ON dbo.Test AFTER INSERT, UPDATE AS BEGIN DECLARE @DuplicateCount INT; SET @DuplicateCount = 0; ;WITH CTE_Count AS ( SELECT TestValue, COUNT(*) AS TestValueCount FROM dbo.Test GROUP BY TestValue ) SELECT @DuplicateCount = COUNT(TestValue) FROM CTE_Count WHERE TestValueCount >=2; IF @DuplicateCount >= 1 BEGIN ROLLBACK; END END; GO /** Test Insert Statement */ INSERT INTO dbo.Test (TestValue) VALUES ('TestMe'); INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); --HINT: This one will fail. INSERT INTO dbo.Test (TestValue) VALUES ('TestYou'); SELECT TestID, TestValue FROM dbo.Test; --Cleanup. DROP TABLE dbo.Test;