Billy Pang
tuxedo_man at hotmail.com
Thu Sep 23 14:58:43 CDT 2004
If we are only talking about primary keys, "Not in" should have a similiar execution plan when compared to the "outer join" method except that the "outer join" method uses an extra filter in the execution plan to get the same results. So "Not in" is theoretically supposed to a molecule faster than "outer join". Billy >From: "John W. Colby" <jwcolby at colbyconsulting.com> >Reply-To: dba-sqlserver at databaseadvisors.com >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer] Violation of primary key constraint >Date: Thu, 23 Sep 2004 10:05:23 -0400 > >I created a NOT IN (SELECT...) and that does work. An outer join per >Arthur's suggestion is marginally faster. > >In fact I have now succeeded in the operation using the outer join method. > >John W. Colby >www.ColbyConsulting.com > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike & >Doris Manning >Sent: Wednesday, September 22, 2004 9:52 AM >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer] Violation of primary key constraint > > >Take a look at the NOT IN topic in BOL. > >Doris Manning >Database Administrator >Hargrove Inc. >www.hargroveinc.com > > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John W. >Colby >Sent: Wednesday, September 22, 2004 8:56 AM >To: dba-sqlserver at databaseadvisors.com >Subject: RE: [dba-SQLServer] Violation of primary key constraint > > >Well I just discovered the <> operator in the Join in SQL Server. I think >this will solve my problem since I just back up and pull only the records >with a ZIP in field B where the PK <> PK in the destination table. NO idea >what implication this has for execution time though. > >John W. Colby >www.ColbyConsulting.com > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of John W. >Colby >Sent: Wednesday, September 22, 2004 8:48 AM >To: dba-sqlserver at databaseadvisors.com >Subject: [dba-SQLServer] Violation of primary key constraint > > >I built a table that I want to dump records in to. The source data is the >PK of my big table plus one of two different zip code fields. Two queries, >all the records - PKs plus ZIP - from any records with a Zip in field A. >The >second all the records - PK plus Zip - with any Zip in field B. Append >query A, append query B. In access, Query B would append all the records >where there was not a collision with the PKs already in the table. In SQL >Server the entire second query just fails because of a collision, giving me >a "Violation of primary key constraint, statement terminated". > >As Bill Cosby says in one of his wonderful acts, "brain damaged children". > >'Scuse me, I WANT the primary key constraint to prevent records from going >in but I also want those records without a violation to go in. So how do I >override this brain damaged child and tell it to accept those records that >do not violate the PK constraint? > >John W. Colby >www.ColbyConsulting.com > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > _________________________________________________________________ Designer Mail isn't just fun to send, it's fun to receive. Use special stationery, fonts and colors. http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN® Premium right now and get the first two months FREE*.