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

[Quoted] William Stacey [MVP] wrote:
> Not sure I see the issue here. Databases exist to service applications (and
> hence a business need), not the other way around. People could do selects
> from any .Net language/ADO.Net for years now. Bringing the CLR into the db
> does not open up more wild selects/updates/deletes as that goes. On the
> contrary, at least you can store and manage that code in the DB and not have
> it spread out in various client apps. Not sure it matters what current or
> future language you use; perf issues will always have to be addressed from
> all tiers - that is just part of the game. IMO, bringing the "bar down"
> does not directly equate to soft code; I would, however, think the reverse
> is true.

[Quoted] You may not see an issue but my disagreement is 100%.

The application front-end is irrelevant. I could use one front-end today and another tomorrow. No issue related to security, scalability, performance, integrity, auditability, etc. is tied to my front-end. If I change my front-end tomorrow ... no big deal ... but the data stored in the database is the value. If that gets changed I might as well lock the doors and go home.

[Quoted] [Quoted] The other place where I take issue with you is what I read as an [Quoted] implicit assumption that a SQL statement is a SQL statement is a SQL statement: Which is clearly not true. Look at it from the standpoint of someone whose background is VB or C#. Which of the following SQL statement is the one to use? And yes they are all syntactically correct and all produce the exact same result set (in Oracle).

1.
SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

2.
SELECT srvr_id
FROM servers
WHERE srvr_id IN (

    SELECT srvr_id
    FROM serv_inst);

3.
SELECT srvr_id
FROM servers
WHERE srvr_id IN (

    SELECT i.srvr_id
    FROM serv_inst i, servers s
    WHERE i.srvr_id = s.srvr_id);

4.
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

5.
SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (

    SELECT srvr_id
    FROM servers
    MINUS
    SELECT srvr_id
    FROM serv_inst);

6.
SELECT srvr_id
FROM servers s
WHERE EXISTS (
    SELECT srvr_id
    FROM serv_inst i
    WHERE s.srvr_id = i.srvr_id);

My bet is you went straight for #4. And it is not the best by a very substantial margin.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)