November 18, 2011 at 3:45 am
Hi All,
I think filtered indexes must be my favourite feature of SQL server, am trying to a use a covering filtered index to speed up my querying when I am always putting a where clause on a hard coded value. Problem is, I'm getting a 'Cannot insert duplicate key row in object' error when I don't think I should be. When updating and changing two rows to swap the row with the filtered index column I get the duplicate key error, when actually after the update is completed there is no violation of the unique index.
Here is an example
CREATE TABLE UniqueTest (Col1 INT NOT NULL, Col2 INT NOT NULL)
CREATE UNIQUE NONCLUSTERED INDEX IX_Col1 ON UniqueTest (Col1)
WHERE Col2 = -1
INSERT INTO UniqueTest
SELECT 1, 1
UNION
SELECT 1, -1
UPDATE UniqueTest SET Col2 = -Col2
If you run it you will get the error, but as you can see only in one row is Col2 equal to -1.
Am I going crazy or is this a bug? Is there a workaround? I don't really want to lose my unique index 🙁
November 18, 2011 at 3:50 am
waxingsatirical (11/18/2011)
Hi All,I think filtered indexes must be my favourite feature of SQL server, am trying to a use a covering filtered index to speed up my querying when I am always putting a where clause on a hard coded value. Problem is, I'm getting a 'Cannot insert duplicate key row in object' error when I don't think I should be. When updating and changing two rows to swap the row with the filtered index column I get the duplicate key error, when actually after the update is completed there is no violation of the unique index.
Here is an example
CREATE TABLE UniqueTest (Col1 INT NOT NULL, Col2 INT NOT NULL)
CREATE UNIQUE NONCLUSTERED INDEX IX_Col1 ON UniqueTest (Col1)
WHERE Col2 = -1
INSERT INTO UniqueTest
SELECT 1, 1
UNION
SELECT 1, -1
UPDATE UniqueTest SET Col2 = -Col2
If you run it you will get the error, but as you can see only in one row is Col2 equal to -1.
Am I going crazy or is this a bug? Is there a workaround? I don't really want to lose my unique index 🙁
What version are you using? This works for me: -
BEGIN TRAN
CREATE TABLE UniqueTest (Col1 INT NOT NULL, Col2 INT NOT NULL)
CREATE UNIQUE NONCLUSTERED INDEX IX_Col1 ON UniqueTest (Col1)
WHERE Col2 = -1
INSERT INTO UniqueTest
SELECT 1, 1
UNION
SELECT 1, -1
SELECT * FROM UniqueTest
UPDATE UniqueTest SET Col2 = -Col2
SELECT * FROM UniqueTest
ROLLBACK
Returns: -
(2 row(s) affected)
Col1 Col2
----------- -----------
1 1
1 -1
(2 row(s) affected)
(2 row(s) affected)
Col1 Col2
----------- -----------
1 -1
1 1
(2 row(s) affected)
So works as expected on -
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
November 18, 2011 at 3:51 am
waxingsatirical (11/18/2011)
Hi All,I think filtered indexes must be my favourite feature of SQL server, am trying to a use a covering filtered index to speed up my querying when I am always putting a where clause on a hard coded value. Problem is, I'm getting a 'Cannot insert duplicate key row in object' error when I don't think I should be. When updating and changing two rows to swap the row with the filtered index column I get the duplicate key error, when actually after the update is completed there is no violation of the unique index.
Here is an example
CREATE TABLE UniqueTest (Col1 INT NOT NULL, Col2 INT NOT NULL)
CREATE UNIQUE NONCLUSTERED INDEX IX_Col1 ON UniqueTest (Col1)
WHERE Col2 = -1
INSERT INTO UniqueTest
SELECT 1, 1
UNION
SELECT 1, -1
UPDATE UniqueTest SET Col2 = -Col2
If you run it you will get the error, but as you can see only in one row is Col2 equal to -1.
Am I going crazy or is this a bug? Is there a workaround? I don't really want to lose my unique index 🙁
Your unique index is on Col1 and that will be checked for UNIQUEness. Col2 is just filter (not part of unique check) so it's not a BUG. 🙂
November 18, 2011 at 4:05 am
I am definitely going crazy then, I still get the error when I copy/paste your sql (all I added was the SELECT @@VERSION)
BEGIN TRAN
SELECT @@VERSION
CREATE TABLE UniqueTest (Col1 INT NOT NULL, Col2 INT NOT NULL)
CREATE UNIQUE NONCLUSTERED INDEX IX_Col1 ON UniqueTest (Col1)
WHERE Col2 = -1
INSERT INTO UniqueTest
SELECT 1, 1
UNION
SELECT 1, -1
SELECT * FROM UniqueTest
UPDATE UniqueTest SET Col2 = -Col2
SELECT * FROM UniqueTest
ROLLBACK
Results to text:
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
(1 row(s) affected)
(2 row(s) affected)
Col1 Col2
----------- -----------
1 1
1 -1
(2 row(s) affected)
Msg 2601, Level 14, State 1, Line 17
Cannot insert duplicate key row in object 'dbo.UniqueTest' with unique index 'IX_Col1'.
The statement has been terminated.
Col1 Col2
----------- -----------
1 1
1 -1
(2 row(s) affected)
November 18, 2011 at 4:11 am
Cadavre (11/18/2011)
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)Jun 17 2011 00:54:03
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
waxingsatirical (11/18/2011)
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
You're using a different version to me. I'm guessing it's a bug that was fixed in SP1.
November 18, 2011 at 4:12 am
Just found this
looks like it is a bug, although according to microsoft it was fixed in SP2 not SP1, so I don't know why you are not getting it Cadavre (SP1)
November 18, 2011 at 4:17 am
waxingsatirical (11/18/2011)
Just found thislooks like it is a bug, although according to microsoft it was fixed in SP2 not SP1, so I don't know why you are not getting it Cadavre (SP1)
Actually, it says it was fixed in SP2 for SQL Server 2008, which is not the same as SQL Server 2008 R2.
November 18, 2011 at 4:24 am
Oh ok, thanks for your responses.
Another day of sanity ahead.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply