Looking into the Microsoft documentation, it says when a unique constraint is created, a unique index is created automatically.
Is there a trick/workaround to have a unique constraint and NOT have a unique index, so it does not take space?
- 4Why would you want this? A unique constraint can't be efficiently enforced without an index.Charles Duffy– Charles Duffy2018-04-30 17:04:29 +00:00Commented Apr 30, 2018 at 17:04
- 2Bad idea, this is almost the same of asking "Hey how I force all inserts (and possible update/delete) and almost all queries to do a full table scan in my table?"jean– jean2018-04-30 18:15:06 +00:00Commented Apr 30, 2018 at 18:15
- The legacy RDBMS I still manage allows that, since it doesn't automatically create an index when you create a unique constraint. (You've got to manually create the index yourself, which lets you decide what kind of index to use.) This can be useful when you use "mixed" pages (multiple table records on the same page) and hashed indexes for OLTP systems. In that one instance, a non-unique hashed index along with a PK constraint is quite efficient.RonJohn– RonJohn2018-04-30 18:33:21 +00:00Commented Apr 30, 2018 at 18:33
- @RonJohn I've emulated this in SQL Server by using a computed column of the hash of a large text column (always accepting the risk of collisions) and putting a unique index on the computed column (persisted, of course).Jonathan Fite– Jonathan Fite2018-04-30 19:33:22 +00:00Commented Apr 30, 2018 at 19:33
Add a comment |
1 Answer
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; 
- Side note: the viability of even the hacky answer depends entirely on the proper
ISOLATION_LEVELbeing used. To prevent duplicate rows, you'll need to useSERIALIZABLE(becauseREPEATABLE_READallows phantom reads) which, if you don't have an index on the column (and use the query shown here), is going to lock the entire table.Clockwork-Muse– Clockwork-Muse2018-04-30 18:44:02 +00:00Commented Apr 30, 2018 at 18:44 - It's a terrible hack all around. I didn't really want to make it any more bulletproof because it shouldn't even be considered as a solution. Putting an index on the TestValue column will make this go lots faster... but then why not just turn the dial, make it a unique index and walk away. I should have also joined onto the INSERTED table (on TestValue) to help limit what was scanned, but it's still terrible.Jonathan Fite– Jonathan Fite2018-04-30 19:00:59 +00:00Commented Apr 30, 2018 at 19:00
- 3Hey Jonathan, I've taken the liberty of giving your two suggestions a slightly different spin: reasonable vs less reasonable, instead of short vs long which didn't really make sense to me, as they are completely different suggestions rather than different wordings of the same suggestion. I also accompanied that with minor formatting/arrangement changes – all of them purely to reflect my view of how this answer could look better. Naturally you don't have to agree with any of my changes and I'm perfectly happy if you decide to roll them back, cheers.Andriy M– Andriy M2018-04-30 19:19:48 +00:00Commented Apr 30, 2018 at 19:19