Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IGNORE_DUP_KEY Expand / Collapse
Author
Message
Posted Tuesday, November 4, 2008 9:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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
Post #597085
Posted Wednesday, November 5, 2008 5:16 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, June 28, 2014 8:50 AM
Points: 2,649, Visits: 766
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
Post #597236
Posted Wednesday, November 5, 2008 6:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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 ;)

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. :D



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #597278
Posted Wednesday, November 5, 2008 12:10 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #597614
Posted Wednesday, November 5, 2008 12:12 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
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.
Post #597617
Posted Wednesday, November 5, 2008 1:29 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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. :)

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
Post #597659
Posted Thursday, November 6, 2008 1:06 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:37 AM
Points: 5,353, Visits: 1,389
Good Questions Hugo...


Post #597932
Posted Thursday, November 6, 2008 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
Anirban Paul (11/6/2008)
Good Questions Hugo...


Thanks, Anirban ;)



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #597941
Posted Thursday, November 6, 2008 8:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:22 PM
Points: 2,555, Visits: 3,809
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.

:)
Post #598232
Posted Thursday, November 6, 2008 2:34 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 5,984, Visits: 8,242
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.

:)


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



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #598561
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse