Unique clustered index on two columns for an indexed view

Question

I'm trying to setup an indexed view on a table that doesn't have a unique id. It has two unique identifiers that if combined would be unique for it's row. I'm having trouble actually creating the unique clustered index that the indexed view requires when I found an thread on MSDN that folks all agree it is possible to create a unique clustered index out of 2 columns for a indexed view @ http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f2c99845-3af1-46e8-9b52-363c24988744

But for the life of me, can't figure out how to create it. I'm rolling with this query, but it doesn't seem to cut it.

CREATE UNIQUE CLUSTERED INDEX  [PK] ON MyView
(
MyId1, MyId2
)

Error:

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.MyView' and the index name 'PK'. The duplicate key value is (71cd9b68-1a9e-47bc-bc6b-0008b230a6d8, 0e64aa3a-0631-4caf-82d9-73609ee79b19).

The two IDs listed as duplicates are IDs from MyId2.

So, how could I create a unique clustered index here?

It isn't a duplicated just on that column, is the combination of MyId1 and MyId2 that is duplicated — Lamak
– Lamak, Commented Jun 4, 2012 at 21:01
Run "select MyId1, MyId2 from MyView group by MyId1, MyId2 having count(*) > 1". That will show you your duplicates as you may have more than just the combination shown. SQL errored the moment it encountered an exception to the uniqueness it expected across MyId1, MyId2. — Zhenny
– Zhenny, Commented Jun 4, 2012 at 21:04
Why not create the index directly on the table? Why do you need a view? — Magnus
– Magnus, Commented Jun 4, 2012 at 21:08
Aaaaaaah yes, how'd those get there?!! Thanks folks, tidied up a bit and voila, got my indexed view!! — Levitikon
– Levitikon, Commented Jun 4, 2012 at 21:29

Abe Miessler · Accepted Answer · 2012-06-04 21:04:01Z

2

Well the error message seems to suggest that there is more than one record where MyId1 = 71cd9b68-1a9e-47bc-bc6b-0008b230a6d8 and MyId2 = 0e64aa3a-0631-4caf-82d9-73609ee79b19.

I would recommend running a query that selects based only on that criteria and confirming that this only returns one record. If it returns more, then you cannot recreate a UNIQUE constraint on these two columns unless you eliminate the duplicates.

answered Jun 4, 2012 at 21:04

Abe Miessler

85.7k104 gold badges323 silver badges496 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unique clustered index on two columns for an indexed view

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related