Re: Code in the database or middle tier (the CLR controversy)

DA Morgan (damorgan_at_psoug.org) writes:
> It is all available, including the data, at:
> http://www.psoug.org
> click on Morgan's Library
> click on Explain Plan
>
> #5 is a piece of work. It is the realization, in SQL, of a double
> negative. In Oracle #6 is definitely the superior solution with the
> specific data set I created. But I've never met a front-end programmer
> that knew enough SQL to write it.

Thanks for the scripts. After some tweak I got the script running on SQL Server. On SQL 2005, #1, #2, #4 and #6 produced the same plan. I tried adding the missing foreign key, and also a non-clustered index on serv_inst.srvid. All plans were still the same.

On SQL 2000, #2, #4 and #6 produced the same plan. #1 and #5 did not produce any plan at all, as INSERSECT and EXCEPT are not supported on SQL 2000.

Thus, while your script for Oracle is a good demonstration of Explain Plain, and that different constructs may affect the query plan, for SQL Server it may rather demonstrate that SQL Server is quite good at rewriting queries internally. (But don't worry. Funny tweaks with queries to good performance are commonplace with SQL Server as well.)

-- 
Erland Sommarskog, SQL Server MVP, esquel_at_sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp