Francisco H Tapia
my.lists at verizon.net
Wed Feb 26 11:00:22 CST 2003
the purpose was to show how one has to be extremely careful with the IN statement as the first one deletes all the records because it doesn't explicitly evaluate that the au_id does not exists in the Publisher's table. Would you ever do this in real life? not on purpose, but you could make a typo only to find that the results of your SELECT/UPDATE/DELETE were all wrong. If you modify the first Select (from the examples I gave) to Publishers.au_id then it actually yields an error, likewise if you were to only run the statement SELECT au_id From Publishers... the point is this is a "side effect" of using the IN clause in which errors can crop up and the dba not even realize it, unlike a performance hit, where the results are visually apparent, this kind of side effect can go on undetected until results are questioned. -Francisco http://rcm.netfirms.com ----- Original Message ----- From: "Djabarov, Robert" <Robert.Djabarov at usaa.com> To: <dba-sqlserver at databaseadvisors.com> Sent: Wednesday, February 26, 2003 6:54 AM Subject: RE: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent. : The "side effects" that I thought we are talking about here are performance and contingency...and you're talking about syntax errors? First of all, your first example in a sense deletes all records from TitleAuthor, because your IN clause actually selects AU_ID from TitleAuthor, not from Publishers, as it might seem (unfortunately, I don't see the purpose of this IN clause in this particular case at all). In fact, your IN clause will select AU_ID as many times as there are rows in Publishers... Would you ever consider doing it in real life? : : What I was talking about was the impact of IN/NOT IN/NOT EXISTS vs. EXISTS/LEFT OUTER JOIN. Since you started withy IN vs. EXISTS, here's the sample that you can run in QA and see the difference in performance for yourself: : : select * from authors where au_id in ( : select au_id from titleauthor) : go : select * from authors a where exists ( : select * from titleauthor t where a.au_id = t.au_id) : go : : Just turn Statistics Time on in your Connection Options. In mine I see 19ms delay in the first query vs. the second. : : ______________________________________________________ : Robert Djabarov : Certified MS SQL Server DBA : Certified MS SQL Server Programmer : Certified MS VB Programmer : ? (210) 913-3148 - phone : ( (210) 753-3148 - pager : : -----Original Message----- : From: Francisco H Tapia [mailto:my.lists at verizon.net] : Sent: Tuesday, February 25, 2003 6:11 PM : To: dba-sqlserver at databaseadvisors.com : Subject: Re: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent. : : NOT EXIST isn't exposed to the same side effects... Take the following for : an example... : : : BEGIN TRANSACTION : SELECT COUNT(*) FROM Titleauthor : DELETE Titleauthor : WHERE au_id IN ( SELECT au_id FROM Publishers) : SELECT COUNT(*) FROM Titleauthor : ROLLBACK : : however this statement dosent error out, true it's the EXISTS not the NOT : EXISTS but the side-effects are the same... : : BEGIN TRANSACTION : SELECT COUNT(*) FROM Titleauthor : DELETE Titleauthor : WHERE EXISTS(SELECT au_id FROM Publishers : WHERE Publishers.au_id = Titleauthor.au_id) : SELECT COUNT(*) FROM Titleauthor : ROLLBACK : : note that you now get the invalid column au_id with the EXISTS : statement...which is at it should be... the first IN statement doesn't give : this feedback, a workaround is to declare the au_id in the IN statement as : Publisher.au_id in order to cause it to explicilty resolve in which case : you'd get the error.. : : -Francisco : http://rcm.netfirms.com : ----- Original Message ----- : From: "Djabarov, Robert" <Robert.Djabarov at usaa.com> : To: <dba-sqlserver at databaseadvisors.com> : Sent: Tuesday, February 25, 2003 12:19 PM : Subject: RE: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite : Urgent. : : : : NOT IN has the same side effects as NOT EXISTS. It can be easily : re-written as follows: : : : : insert into tableA (Payroll, JobDate, JobType) : : select B.Payroll, B.JobDate, B.JobType : : from tableB B left outer join ( : : select A.Payroll, A.JobDate, A.JobType : : from tableA A : : inner join tableB tb : : on A.Payroll = tb.Payroll : : AND A.Jobdate = tb.JobDate : : AND A.JobType = tb.JobType : : ) t on B.Payroll = t.Payroll : : and B.JobDate = t.JobDate : : and B.JobType = t.JobType : : where t.Payroll is null : : and t.JobDate is null : : and t.JobType is null : : : : : : ______________________________________________________ : : Robert Djabarov : : Certified MS SQL Server DBA : : Certified MS SQL Server Programmer : : Certified MS VB Programmer : : ? (210) 913-3148 - phone : : ( (210) 753-3148 - pager : : : : -----Original Message----- : : From: Francisco H Tapia [mailto:my.lists at verizon.net] : : Sent: Tuesday, February 25, 2003 10:36 AM : : To: dba-sqlserver at databaseadvisors.com; AccessD; : dba-VB at databaseadvisors.com : : Subject: Re: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite : Urgent. : : : : How about .... : : : : Insert into tableA (Payroll, jobdate, jobtype) : : Select Payroll, jobdate, jobtype : : From tableB AS B : : WHERE NOT EXISTS( Select Payroll, jobdate, jobtype : : From tableA AS A : : Where B.Payroll = A.Payroll : AND : : B.Jobdate = A.JobDate AND B.JobType = A.JobType) : : : : I could have used a NOT IN clause but then NOT IN can have some serious : side : : effects...In General the above statement will be evaluated by the engine : : where as a NOT IN could miss evaluating the B alias in the where clause : and : : cause it to report bad data, using Exists (or NOT EXISTS) usually yields : : faster performance and the assurance that it evaluates correctly. : : -Francisco : : http://rcm.netfirms.com : : ----- Original Message ----- : : From: <paul.hartland at fsmail.net> : : To: <dba-SQLServer at databaseadvisors.com>; <accessd at databaseadvisors.com>; : : <dba-vb at databaseadvisors.com> : : Sent: Tuesday, February 25, 2003 1:42 AM : : Subject: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite : Urgent. : : : : : : : To all, : : : : : : Watch for Word Wrap............... : : : : : : Being quite a novice with Stored Procedures on SQL Server 7.0, I'm : looking : : for some pretty urgent help at the moment. I have two tables, lets say : : Table A & Table B. Table A is my master table, and Table B is a temporary : : table. Before Table be gets populated with any data, every existing : record : : is deleted so I start with a fresh table so to speak. Table B consists of : 3 : : fields PayrollNo, JobDate, JobType (these fields are also in the master : : table, but the master table is used to contain other consting information, : : which is entered at a later date). : : : : : : What I would like is a Stored Procedure which would Insert Table B : : contents into Table A, but making sure that I don't insert any duplicates : : into Table A (something like an unmatched query in access but with an : Insert : : also). i.e. : : : : : : Table A Table B : : : payroll jobdate jobtype id : payroll : : jobdate jobtype : : : 999001 01/01/01 0 1 999001 : : 01/01/01 0 : : : 999001 01/01/01 2 2 999001 : : 01/01/01 1 : : : 999002 01/01/01 2 3 999001 : : 01/01/01 2 : : : 4 999002 : : 01/01/01 0 : : : 5 999002 : : 01/01/01 1 : : : 6 999002 : : 01/01/01 2 : : : : : : So when I invoke the stored procedure, it would insert records 2, 4 and : 5 : : from Table B into Table A........Anyone any idea what I'm on about, if so : : can anybody please help....... : : : : : : Thanks in advance : : : : : : Paul Hartland : : : : : : : __________________________________________________________________________ : : : Freeserve AnyTime - Go online whenever you want for just £6.99 a month : for : : : your first 3 months, that's HALF PRICE! And then it's just £13.99 a : month : : : after that. : : : : : : For more information visit http://www.freeserve.com/time/ or call free : on : : : 0800 970 8890 : : : : : : : : : _______________________________________________ : : : 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 : : : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : http://www.databaseadvisors.com :