create index idx_tab1_col1col2
on dbo.tab1([col1])
include([col2])
on tab1_index
My question is this. I require this index to be created. col1 doesn't have a lot of unique values but col2 is our pk and adding col2 to the index makes it unique. Can I make this a unique index then? Also, can someone explaing me a bit as to why it's possible or y not? Thank you very much for your help.
I dare say that you could have created the index as unique in less time than it took to navigate to here and write the question. But you didn't so let's try it together.
Create Table dbo.tab1 (Col1 varchar(5) null, Col2 int not null primary key)
Insert Into dbo.tab1
Select 'aaaaa', 1
Union
Select 'bbbbb', 2
Union
Select 'ccccc', 3
Union
Select 'ccccc', 4
Union
Select 'ddddd', 5
create unique index idx_tab1_col1col2
on dbo.tab1([col1])
include([col2])
Msg 1505, Level 16, State 1, Line 2
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.tab1' and index name 'idx_tab1_col1col2'. The duplicate key value is (ccccc).
The statement has been terminated.
So, no, you can't. Uniqueness must exist within the indexed fields without regard to any included columns.
|||Include column can not make index unique. Because include column is not part of index key.|||Hey Robert. Yes, you are correct but at that time, I was in middle of implememnting a checkdb process for my company, which was never in place. Well, thank you very much for your time and effort and the answer.