SanjayAttray (11/5/2008)
CREATE INDEX NotAllowedON Dest (Value)
WITH(IGNORE_DUP_KEY = ON);
go
Msg 1916, Level 16, State 4, Line 1
CREATE INDEX options nonunique and ignore_dup_key are mutually exclusive.
ignore_dup_key should come with Unique key.
CREATE UNIQUE INDEX NotAllowed
Hi Sanjay,
Maybe I am misunderstanding you - isn't this exactly as I say in the answer explanation?
Good question though. I used this option mostly while merging data from different server.tables into my reporting server, where it won't insert duplicate data.
Thanks for the kind words. 🙂
Situations such as what you describe, also known as staging tables, are indeed about the only place where I would consider using this option (though even in that case, I would probably prefer a DISTINCT and/or NOT EXISTS to prevent duplicates - unless that would cause too many performance problems).