SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IGNORE_DUP_KEY


IGNORE_DUP_KEY

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18955 Visits: 12426
Comments posted to this topic are about the item IGNORE_DUP_KEY


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jamie-2229
Jamie-2229
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3872 Visits: 857
The negative ("This option may NOT be specified...") - urrgh... semantics. Does it mean that it can be but that you shouldn't because it doesn't make sense but it is allowed anyway?

To my surprise, the syntax for CREATE TABLE and ALTER TABLE does allow you to specify the IGNORE_DUP_KEY option for PRIMARY KEY and UNIQUE constraints.


Just the same Hugo - good question. It looks closely at an issue that is odd at best. Thanks

Jamie
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18955 Visits: 12426
Jamie Longstreet (11/5/2008)
The negative ("This option may NOT be specified...") - urrgh... semantics. Does it mean that it can be but that you shouldn't because it doesn't make sense but it is allowed anyway?[/hr]


Hi Jamie,

You're right. Looking back at the question, I think it would have been better to phrase it as "... may be specified ..." (and invert the correctness mark of this answer, of course), or possibly as "... can't be specified ...".
To clarify, my intention was solely to test for allowed syntax, not for good or bad practice - though I personally consider this option to be bad practice in its entirety Wink

Just the same Hugo - good question. It looks closely at an issue that is odd at best. Thanks


You're welcome, and thanks for the kind words. BigGrin


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5749 Visits: 1619
CREATE INDEX NotAllowed
ON 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

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.

SQL DBA.
SanjayAttray
SanjayAttray
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5749 Visits: 1619
Got answer right by fluke. Wasn't sure if I should select his option or not. Though I thought this should be in.

-- This option may not be specified for the index that supports the PRIMARY KEY.

SQL DBA.
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18955 Visits: 12426
SanjayAttray (11/5/2008)
CREATE INDEX NotAllowed
ON 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. Smile

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).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)SSCrazy Eights (9.8K reputation)

Group: General Forum Members
Points: 9839 Visits: 1407
Good Questions Hugo...



Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18955 Visits: 12426
Anirban Paul (11/6/2008)
Good Questions Hugo...


Thanks, Anirban Wink


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
OCTom
OCTom
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4151 Visits: 4152
Good question. Not because of the question itself but because of one of the answers. I went on a tangent to relearn the goals of ACID. That reminded me of a design issue I'm currently working on. It's amazing how a question like this will have relevance when you least expect it.

Very timely.

Thanks.

Smile
Hugo Kornelis
Hugo Kornelis
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18955 Visits: 12426
skjoldtc (11/6/2008)
Good question. Not because of the question itself but because of one of the answers. I went on a tangent to relearn the goals of ACID. That reminded me of a design issue I'm currently working on. It's amazing how a question like this will have relevance when you least expect it.

Very timely.

Thanks.

Smile


You're most welcome. Wink (Though in all honesty, I can't take any credit for the timing BigGrin)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search