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

[SAN3141]

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

Two authors have written articles that fall on different sides of the
debate.

"Keys to the Database"
http://www.intelligententerprise.com...cleID=50500830

"SOA, Multi-Tier Architectures and Logic in the Database"
http://www.sqlsummit.com/Articles/Lo...heDatabase.HTM

Joe Celko wrote the first article, but his objections point to
Microsoft SQL Server 2005:

"I have an article at WWSUG.com on how much I hate the CLR stuff that
Microsoft is putting out."
http://blog.intelligententerprise.co...es/002419.html

"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."
Message id: 41*************************@posting.google.com

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

[Serge Rielau]

SA*****@netscape.net wrote:

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

Two authors have written articles that fall on different sides of the
debate.

"Keys to the Database"
http://www.intelligententerprise.com...cleID=50500830

"SOA, Multi-Tier Architectures and Logic in the Database"
http://www.sqlsummit.com/Articles/Lo...heDatabase.HTM

Joe Celko wrote the first article, but his objections point to
Microsoft SQL Server 2005:

"I have an article at WWSUG.com on how much I hate the CLR stuff that
Microsoft is putting out."
http://blog.intelligententerprise.co...es/002419.html

"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."
Message id: 41*************************@posting.google.com

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

I don't see what one has to do with the other.
First: CLR is a piece of technology. It's support for procedures and
functions allows for it's placement on the server. It doesn't force it
anymore than PL/SQL, Java, C, Cobol, ....
Second: User defined aggregates are an extensibility option for DBMS.
_When_ the function is needed then it is better placed in the engine
because the DBMS can parallelize the aggregation in both SMP and MPP.
TEh App cann not this and not placing teh aggregation in the engine
will swamp the network.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Greg D. Moore \(Strider\)]

<SA*****@netscape.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).
If I have time I'll review all these links... but my take away is this...

Used intelligently, CLR will be a godsend.

But, like procedural programmers coming to SQL and writing loops, cursors,
etc, with the wrong mindset, it is a potential disaster for performance,
data integrity, etc.

It'll be interesting to see how it's used.


Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

Two authors have written articles that fall on different sides of the
debate.

"Keys to the Database"
http://www.intelligententerprise.com...cleID=50500830

"SOA, Multi-Tier Architectures and Logic in the Database"
http://www.sqlsummit.com/Articles/Lo...heDatabase.HTM

Joe Celko wrote the first article, but his objections point to
Microsoft SQL Server 2005:

"I have an article at WWSUG.com on how much I hate the CLR stuff that
Microsoft is putting out."
http://blog.intelligententerprise.co...es/002419.html

"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."
Message id: 41*************************@posting.google.com

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

[Damien]

Greg D. Moore (Strider) wrote:

<SA*****@netscape.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

If I have time I'll review all these links... but my take away is this...

Used intelligently, CLR will be a godsend.

[snip]
We're all doomed, aren't we :-|

My own take is actually pretty similar. I think it has potential. It
doesn't *have* to be used, but the danger is that the "less
experienced" will encounter a problem they cannot solve easily in SQL,
and decide to go for BFI instead of either going to a good book, or
asking an intelligent question on usenet (my two main sources of
learning)

Personally, I'm looking forward to it, although I personally don't feel
they've gone far enough :-) Hey, do you suppose if they opened up the
interoperability between SQL server internals and the CLR, such that it
was possible to augment T-SQL up to full SQL-99 (or whatever standard
we chose) compliance, certain people would still object?

[DA Morgan]

SA*****@netscape.net wrote:

There doesn't seem to be consensus about when to put code in the
database or in the middle tier.
Actually there is ... among professionals that have an understanding
of data integrity and security. And of course among those who work
in countries such as the US (think SarbOx and HIPAA) where you can
not comply with federal law if you don't.
Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

I really really really want Microsoft to add C# and VB to SQL Server
2005. It will do more to kill the product than anything Oracle or IBM
could add to theirs. And given the products lack of security, stability,
and scalability ... it deserves to be gutted by Microsoft's own hand.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[JT]

Generally speaking; for performance, security and maintenance reasons,
any programming which updates, inserts or deletes from a datbase table
should be tied as close to the database as possible and managed by the DBA
or a designated SQL Developer. If someone needs to update transactions in an
account table, then provide them with an SP, grant them exec only rights,
and document the parameters.
Every time a developer asks for direct access to the database, I can't
help recalling that Capital One commercial where the barbarians come
crashing through the gates of the castle with axes and swords!

<SA*****@netscape.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

Two authors have written articles that fall on different sides of the
debate.

"Keys to the Database"
http://www.intelligententerprise.com...cleID=50500830

"SOA, Multi-Tier Architectures and Logic in the Database"
http://www.sqlsummit.com/Articles/Lo...heDatabase.HTM

Joe Celko wrote the first article, but his objections point to
Microsoft SQL Server 2005:

"I have an article at WWSUG.com on how much I hate the CLR stuff that
Microsoft is putting out."
http://blog.intelligententerprise.co...es/002419.html

"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."
Message id: 41*************************@posting.google.com

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

[--CELKO--]

LOL! Actually, it will be all of the barbarians, snow monsters, etc.
in those "What's in your Wallet?" ads ALL hitting you at once.

What do the CLR languages do with bits? Did you know that +1, +0, -0
and -1 are all used for BOOLEANs, but not consistently? This varies in
MS proprietary language like C# and VB. How do they define their
functions? In the first edition SQL FOR SMARTIES, I had a list of the
various vendor MOD() functions differences in the SQL products of the
day. What about NULLs in the CLR languages?

How do you maintain a mixed system that uses languages you probably do
not know? COBOL is the most popular language on Earth and 70% of the
commercial code is in COBOL. I would guess this newsgroup has less
than a dozen COBOL programmers in it. How many DBAs know C#, C++, C,
Java, VB, Pascal (Delphi), and whatever else a developer can sneak into
the schema before they leave?

What they should have done is implement the SQL/PSM and Standard
trigger syntax as a replacement for T-SQL.

[William Stacey [MVP]]

Jim Gray of MS DB fame talks about 2-3 tier and other stuff on a Channel9
two part video at http://channel9.msdn.com/ShowPost.as...ID=50438#50438
Interestingly, he sees a switch back to two tiers. As for CLR support in
the DB, I think is natural and a good thing. I mean why pay the comm tax if
you don't have too. This will only get better. Even with the CLR, we
still have the gap between managed language and TSQL. So you have to know
both and talk to both. They have made this workable, but it is still not
very natural. Folks are working on data access stuff for C# 3.0 so maybe
much of this gap could go away and make for a pure managed experience. Will
be interesting to see.

--
William Stacey [MVP]

<SA*****@netscape.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

Two authors have written articles that fall on different sides of the
debate.

"Keys to the Database"
http://www.intelligententerprise.com...cleID=50500830

"SOA, Multi-Tier Architectures and Logic in the Database"
http://www.sqlsummit.com/Articles/Lo...heDatabase.HTM

Joe Celko wrote the first article, but his objections point to
Microsoft SQL Server 2005:

"I have an article at WWSUG.com on how much I hate the CLR stuff that
Microsoft is putting out."
http://blog.intelligententerprise.co...es/002419.html

"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."
Message id: 41*************************@posting.google.com

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

[Paul]

"--CELKO--" <jc*******@earthlink.net> wrote:

What they should have done is implement the SQL/PSM and Standard
trigger syntax as a replacement for T-SQL.

PSM?

Pacific Sintered Metals (PSM)?
SQL stored procedures/ PSM for Daffodil DB?
Persistent Security Model?
I did Google and got lots of stuff, but what do *_you_* mean by PSM
(or is it a female complaint 8-) )
Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[--CELKO--]

Persistent Stored Modules. This is the ANSI/ISO Standard 4GL language
designed to work with SQL. IBM has it and so does Mimer. There are
probably a few others out there, too. Look at Jim Melton's book for
some details. Oracle's PL/SQL is something like it in design and
power.

I did Google and got lots of stuff, but what do *_you_* mean by PSM (or is it a female complaint 8-) ) <<

Nah! The female problem is called "PMS" because "Mad Cow Disease" was
taken ... I am going to get shot for that one:)

[Raymond D'Anjou]

This is "dead" technology.
http://www.psm.qc.ca/

"Paul" <pa*************@hotmail.com> wrote in message
news:qi********************************@4ax.com...

"--CELKO--" <jc*******@earthlink.net> wrote:

What they should have done is implement the SQL/PSM and Standard
trigger syntax as a replacement for T-SQL.

PSM?

Pacific Sintered Metals (PSM)?
SQL stored procedures/ PSM for Daffodil DB?
Persistent Security Model?
I did Google and got lots of stuff, but what do *_you_* mean by PSM
(or is it a female complaint 8-) )
Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't
work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[Paul]

"Raymond D'Anjou" <rd*****@savantsoftNOSPAM.net> wrote:

This is "dead" technology.
http://www.psm.qc.ca/

Tres drole Raymond.... Tu as peut-etre pris un peu trop de rose
d'Anjou ce soir?
Paul...
--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[Raymond D'Anjou]

Salut Paul,
Ce soir... it's 4:12 in the afternoon here.

I served that at my son's baptism and I think that's the last time I drank
some.
....a long time ago. I'm a grandfather now.

"Paul" <pa*************@hotmail.com> wrote in message
news:tb********************************@4ax.com...

"Raymond D'Anjou" <rd*****@savantsoftNOSPAM.net> wrote:

This is "dead" technology.
http://www.psm.qc.ca/

Tres drole Raymond.... Tu as peut-etre pris un peu trop de rose
d'Anjou ce soir?
Paul...
--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't
work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[DA Morgan]

--CELKO-- wrote:

LOL! Actually, it will be all of the barbarians, snow monsters, etc.
in those "What's in your Wallet?" ads ALL hitting you at once.

What do the CLR languages do with bits? Did you know that +1, +0, -0
and -1 are all used for BOOLEANs, but not consistently? This varies in
MS proprietary language like C# and VB. How do they define their
functions? In the first edition SQL FOR SMARTIES, I had a list of the
various vendor MOD() functions differences in the SQL products of the
day. What about NULLs in the CLR languages?

How do you maintain a mixed system that uses languages you probably do
not know? COBOL is the most popular language on Earth and 70% of the
commercial code is in COBOL. I would guess this newsgroup has less
than a dozen COBOL programmers in it. How many DBAs know C#, C++, C,
Java, VB, Pascal (Delphi), and whatever else a developer can sneak into
the schema before they leave?

What they should have done is implement the SQL/PSM and Standard
trigger syntax as a replacement for T-SQL.

Good points but IMO missing the most essential.

When database programming is restricted to SQL, T-SQL, PL/SQL, etc. then
database programming is "unofficially" restricted to people who at least
have some familiarity with database concepts (yes we might agree on how
much or little but at least some).

Open up the database to VB, C#, etc. and you are encouraging every
incompetent, untrained, self-annointed programmer, to think they know
something about relational algebra, set theory, constraints, and more:
And we all know they don't.

My prediction: 2 years after VB is available in SQL Server ... the
majority of SQL Server code will be VB. And it will be as solid as an
overripe tomato.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Erland Sommarskog]

(SA*****@netscape.net) writes:

There doesn't seem to be consensus about when to put code in the
database or in the middle tier.
Indeed, there are different opinions on that one. Then again, it
depends a little on your requirements. If portability is an issue,
you need to abstract out the DB engine in some way, so you have
more code in the middle tier. Then again, bouncing data forth and
back, could be expensive, so it's natural to have the logic where
the data is.

Not that this any much to do with CLR.
Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

The CLR will be a very valuable addition to SQL Server, as it removes
the need to use extended stored proceedures and OLE objects which in
SQL 2000 can cause stability problems. The CLR is also good since
complicated logic without data access in Transact-SQL can be implemented
in a more effecient language.

But there is no doubt that the CLR will also be widely abused and a
lot of code will be written in VB or C# which should have been written
in T-SQL.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Will Honea]

As one of the barbarians, I find myself wholly in agreement with you.
As a developer, I found that I never had the specialized knowlege of
(or even access to) the convoluted business rules governing detailed
requirements sufficient to stick my fingers into the works. As a
manager, I found that the QA effort required a separate, dedicated
process apart from the general application oriented flow. Any
modification/extension of the database engine (as seen from an end
user perspective) that will affect ALL operations of the system most
definitely needs to be handled by someone who understands the side
effects and system implications of such implementations.

Of course, I speak as one who can "break an anvil"...

On Wed, 1 Jun 2005 14:13:10 UTC "JT" <so*****@microsoft.com> wrote:

Generally speaking; for performance, security and maintenance reasons,
any programming which updates, inserts or deletes from a datbase table
should be tied as close to the database as possible and managed by the DBA
or a designated SQL Developer. If someone needs to update transactions in an
account table, then provide them with an SP, grant them exec only rights,
and document the parameters.
Every time a developer asks for direct access to the database, I can't
help recalling that Capital One commercial where the barbarians come
crashing through the gates of the castle with axes and swords!

<SA*****@netscape.net> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

Elsewhere there's been discussion about Microsoft SQL Server 2005
adding the CLR to support stored procedures in languages such as C#. A
scan of the Web and discussion forums finds differing opinions about
this.

Two authors have written articles that fall on different sides of the
debate.

"Keys to the Database"
http://www.intelligententerprise.com...cleID=50500830

"SOA, Multi-Tier Architectures and Logic in the Database"
http://www.sqlsummit.com/Articles/Lo...heDatabase.HTM

Joe Celko wrote the first article, but his objections point to
Microsoft SQL Server 2005:

"I have an article at WWSUG.com on how much I hate the CLR stuff that
Microsoft is putting out."
http://blog.intelligententerprise.co...es/002419.html

"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."
Message id: 41*************************@posting.google.com

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

--
Will Honea

[JRStern]

On 1 Jun 2005 03:28:41 -0700, SA*****@netscape.net wrote:

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).
Quite.

Generally, the bulk of such discussion is between people who know only
one tier and are trying to use it for everything.
IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

It's nice to have the option, but like with any power tool, if you
don't know what it's for or how to use it, you may hurt yourself and
others.

One can indeed have long discussions about the architectural issues in
theory and practice, but overall, I'd say it's a two steps forward,
one step back. That nets (sic) out to a good thing.

Josh

[DA Morgan]

JRStern wrote:

On 1 Jun 2005 03:28:41 -0700, SA*****@netscape.net wrote:

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

Quite.

Generally, the bulk of such discussion is between people who know only
one tier and are trying to use it for everything.

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

It's nice to have the option, but like with any power tool, if you
don't know what it's for or how to use it, you may hurt yourself and
others.

One can indeed have long discussions about the architectural issues in
theory and practice, but overall, I'd say it's a two steps forward,
one step back. That nets (sic) out to a good thing.

Josh

I think the main difference is that in the DB2 and Oracle worlds
databases are generally under the control of DBAs that are formally
trained and know how to say No! In the SQL Server world the vast
majority of practitioners are home-schooled and have little if any
format training on data bases and/or didn't come up from mainframe
methodologies. They are far more likely to take the tool they know,
VB, and use it to solve all problems with little understanding of
the consequences.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[William Stacey [MVP]]

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.

--
William Stacey [MVP]

"DA Morgan" <da******@psoug.org> wrote in message
news:1117682093.775003@yasure...

JRStern wrote:

On 1 Jun 2005 03:28:41 -0700, SA*****@netscape.net wrote:

There doesn't seem to be consensus about when to put code in the
database or in the middle tier. There was a long discussion about this
in an Oracle newsgroup (message ID:
UL**************@nwrddc02.gnilink.net).

Quite. Generally, the bulk of such discussion is between people who know
only
one tier and are trying to use it for everything.

IBM DB2 and Oracle are doing the same thing with the .NET CLR. Is this
a non-issue or are all three companies misguided?

It's nice to have the option, but like with any power tool, if you
don't know what it's for or how to use it, you may hurt yourself and
others.

One can indeed have long discussions about the architectural issues in
theory and practice, but overall, I'd say it's a two steps forward,
one step back. That nets (sic) out to a good thing.

Josh

I think the main difference is that in the DB2 and Oracle worlds
databases are generally under the control of DBAs that are formally
trained and know how to say No! In the SQL Server world the vast
majority of practitioners are home-schooled and have little if any
format training on data bases and/or didn't come up from mainframe
methodologies. They are far more likely to take the tool they know,
VB, and use it to solve all problems with little understanding of
the consequences.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Mark Townsend]

William Stacey [MVP] wrote:

Databases exist to service applications (and
hence a business need), not the other way around.

Actually, I think you live with your data for ever. You can drive your
business from your data (and hence identify a business need), and
whatever apps your are running at the moment tend to be just the latest
and greatest incantation of how to get at it and make sense of it. Data
(Information) is king, everything else in IT is pretty much secondary.

But then I would think that.

[Damien]

--CELKO-- wrote:

LOL! Actually, it will be all of the barbarians, snow monsters, etc.
in those "What's in your Wallet?" ads ALL hitting you at once.

What do the CLR languages do with bits? Did you know that +1, +0, -0
and -1 are all used for BOOLEANs, but not consistently? This varies in
MS proprietary language like C# and VB. How do they define their
functions? In the first edition SQL FOR SMARTIES, I had a list of the
various vendor MOD() functions differences in the SQL products of the
day. What about NULLs in the CLR languages?

Um. You are aware that in the COMMON language runtime, VB.NET, C#, and
any other CLR language all have EXACTLY the same view of booleans,
aren't you? And that in the 2.0 edition of the framework (the one
integrated into Server 2005), they even support the concept of
nullability.

Damien

[DA Morgan]

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.

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.

The other place where I take issue with you is what I read as an
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
da******@x.washington.edu
(replace x with u to respond)

[Serge Rielau]

DA Morgan wrote:

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.

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.

The other place where I take issue with you is what I read as an
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.

1. You are not even using anything but SQL here. So all you prove is
that there may be some bad SQL there (surpise).

2. Don't draw conclusions from Oracle's optimizer to other DBMS...
The idea of SQL is that you say WHAT you want and the optimzier decides
how to best get it. DBMS may well give you surpisingly good plans.

I do not see the difference between a CLR/C/Java function/procedure and
a PL/SQL, SQL/PSM, T-SQL function/procedure.
All of which invite procedural logic.
Given that PL/SQL is also supported on the client IIRC it infact is in
the exact same position as CLR. Just because I know PL/SQL does not make
be an SQL expert.
I doubt the threshhold to learn PL/SQL is any higher than C# or VB.
I can bulk collect my result into an array and of I go...
The moment CURSORS and IF THEN ELSE enter the stage it's all shades of
grey...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[William Stacey [MVP]]

I still don't know what you point is. Bad code is bad code. Some people
may write bad or inefficient code, some may not. All I was saying is that
you can not just flat out assume that because you can now write stored
procs/functions in c# or VB that that will change everything. Naturally,
there is a broad range of uses for a DB. Many are single use or turn key
where the DB is only needed in the first place for the application. Maybe
they are upgrading from a simple XML file or Access or something else. On
the other end is your Enterprise DBs running on big AIX machines or
something with a staff of DBAs. I think that is more the scenario you are
talking about. In this case, then yes you need to be really careful as
other production stuff is also involved. But the DBAs need to do that
anyway and is just part of doing business. They have to assume people will
try stupid things and protect the resource. That is what ref integrity,
triggers, and so on are for. As for your Selects, people have been able to
do this from the beginning, so what is changing? If a DBA wants to tie
everything down, then allow only SPs (that the DBAs write) and no external
selects. No new issues here that I can see.

--
William Stacey [MVP]

"DA Morgan" <da******@psoug.org> wrote in message
news:1117720147.568210@yasure...

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.

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.

The other place where I take issue with you is what I read as an
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
da******@x.washington.edu
(replace x with u to respond)

[DA Morgan]

Serge Rielau wrote:

The other place where I take issue with you is what I read as an
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.

1. You are not even using anything but SQL here. So all you prove is
that there may be some bad SQL there (surpise).

2. Don't draw conclusions from Oracle's optimizer to other DBMS...
The idea of SQL is that you say WHAT you want and the optimzier decides
how to best get it. DBMS may well give you surpisingly good plans.

I do not see the difference between a CLR/C/Java function/procedure and
a PL/SQL, SQL/PSM, T-SQL function/procedure.
All of which invite procedural logic.
Given that PL/SQL is also supported on the client IIRC it infact is in
the exact same position as CLR. Just because I know PL/SQL does not make
be an SQL expert.
I doubt the threshhold to learn PL/SQL is any higher than C# or VB.
I can bulk collect my result into an array and of I go...
The moment CURSORS and IF THEN ELSE enter the stage it's all shades of
grey...

Cheers
Serge

My point was exactly that there can be bad SQL. Demo 5 took me 15
minutes to write because it is sometimes hard to write intentionally
bad SQL (far easier to do it by accident).

This thread is going to all major c.d. groups so the Oracle example was
as good as any ... but the point is that even after running through the
optimizer ... the result set is the same but the query plan and the cost
are vastly different.

The difference is not the language. Bad T-SQL is bad T-SQL just as bad
PL/SQL is bad PL/SQL. The difference is that if one is encouraged to use
the database language one is likely to have the code written by those
with an actual understanding of databases. Let any self-taught home-
schooled self-annointed programmer write in the database and the chances
for bad SQL go up by more than a magnitude.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[DA Morgan]

William Stacey [MVP] wrote:

I still don't know what you point is. Bad code is bad code. Some people
may write bad or inefficient code, some may not.

What I am saying is that you are more likely to get good database code
from people that know databases than you are from people that know VB.
And to think that those coding in VB know databases at more than a
superficial level is just not realistic.

What we are seeing, in practical terms, is the death of T-SQL rather
than an effort, by Microsoft, to fix it. Inviting more cooks into the
kitchen may sell more frying pans. But it does not improve the quality
of the food being prepared.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

What I am saying is that you are more likely to get good database code
from people that know databases than you are from people that know VB.
And to think that those coding in VB know databases at more than a
superficial level is just not realistic.

What we are seeing, in practical terms, is the death of T-SQL rather
than an effort, by Microsoft, to fix it. Inviting more cooks into the
kitchen may sell more frying pans. But it does not improve the quality
of the food being prepared.

Huh? This simply does not make any sense at all. What is Microsoft
supposed to fix? T-SQL is by no means dead. There are significant
improvments to T-SQL in SQL 2005.

OK, some people who speak Visual Basic as their first language, also write
some SQL statements, and these may be of inferior quality. I don't know
about systems that uses Oracle as DBMS, but I cannot see why the same
thing could happen there. Maybe traditions are different, but that is not
inherit in the product.

Another things to consider is that many today do not write stored
procedure, but send their SQL statements from the client. If they
move that code into the server, there is at least some improvement
in terms fewer network roundtrips, although it might still be a poor
design.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

The other place where I take issue with you is what I read as an
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.

My experience from reading posts on the SQL Server newsgroups, is
that most inexperienced users go for #2. And in the days of 6.5
it was a good idea to rewrite that this into #6 for improved
performance. And while I may still give people this advice, it may
only be for esthetic reasons only, because I believe that the
optimizer now is smart enough to essentially rewrite #2 into #6
internally.

Which goes to show that what is the best is not always obvious. Of
course, #4 is likely to perform less effecient because of the distinct.
#3 and #5 are too convluted to make sense (and #5 won't rnn on
SQL Server.) #1 is potentially bad, because it could return a different
result. (You didn't include any DDL, so I don't know how the tables
are related.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[DA Morgan]

Erland Sommarskog wrote:

What we are seeing, in practical terms, is the death of T-SQL rather
than an effort, by Microsoft, to fix it. Inviting more cooks into the
kitchen may sell more frying pans. But it does not improve the quality
of the food being prepared.

Huh? This simply does not make any sense at all. What is Microsoft
supposed to fix? T-SQL is by no means dead. There are significant
improvments to T-SQL in SQL 2005.

Let me emphasize that this is just personal opinion. But I believe that
when VB and C# are available in SQL Server ... Transact will die ... a
slow death but a death non-the-less. I expect a very different
experience from what we saw in the Oracle world with Java in the
database where the majority of developers didn't change.
OK, some people who speak Visual Basic as their first language, also write
some SQL statements, and these may be of inferior quality. I don't know
about systems that uses Oracle as DBMS, but I cannot see why the same
thing could happen there. Maybe traditions are different, but that is not
inherit in the product.
I partially agree. The difference is that VB can not be written inside
other database products and there is a culture that would toss a VB
programmer on their head if they tried. I know plenty of DBAs that
wouldn't even consider letting Java inside if it was internally written.
Another things to consider is that many today do not write stored
procedure, but send their SQL statements from the client. If they
move that code into the server, there is at least some improvement
in terms fewer network roundtrips, although it might still be a poor
design.

I agree with what you've said. But it opens the door for them to do
what they have generally been precluded from doing before: Design
tables, views, etc. It blurs the line. And having seen schemas
designed by Java developers I tremble in fear at what the VB crowd
might be capable of doing.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[DA Morgan]

Erland Sommarskog wrote:

DA Morgan (da******@psoug.org) writes:

The other place where I take issue with you is what I read as an
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.

My experience from reading posts on the SQL Server newsgroups, is
that most inexperienced users go for #2. And in the days of 6.5
it was a good idea to rewrite that this into #6 for improved
performance. And while I may still give people this advice, it may
only be for esthetic reasons only, because I believe that the
optimizer now is smart enough to essentially rewrite #2 into #6
internally.

Which goes to show that what is the best is not always obvious. Of
course, #4 is likely to perform less effecient because of the distinct.
#3 and #5 are too convluted to make sense (and #5 won't rnn on
SQL Server.) #1 is potentially bad, because it could return a different
result. (You didn't include any DDL, so I don't know how the tables
are related.)

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.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Serge Rielau]

DA Morgan wrote:

I partially agree. The difference is that VB can not be written inside
other database products.

That is incorrect. DB2 supports VB.NET (CLR) procs and functions today.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[DA Morgan]

Serge Rielau wrote:

DA Morgan wrote:

I partially agree. The difference is that VB can not be written inside
other database products.

That is incorrect. DB2 supports VB.NET (CLR) procs and functions today.

Thanks for the correction.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[William Stacey [MVP]]

> I agree with what you've said. But it opens the door for them to do

what they have generally been precluded from doing before: Design
tables, views, etc. It blurs the line. And having seen schemas
designed by Java developers I tremble in fear at what the VB crowd
might be capable of doing.

- Not a VB programmer, but if I was, would be offended by that statement.
You seem to have some issue with VB.
- VB/C#/Managed C++/J# Hello World app reduces to ~same IL and binary. The
language does not matter.
- If you take VB and C#/C++ crowd out of the picture, who is writing the
applications?
- You still can't design tables/views in VB or C#, you need DDL or a Tool
(SSMS or VS). CLR does not change this.
- CLR integration does not magically allow them to do crazy things. They
still need to use SQLClient inside the DB to submit SQL statements, which
they have been able to do for a long time anyway - so this does not change.
- Your client base (i.e. programmers) does not change just because of CLR
integration. If you have 100 programmers yesterday, having SqlClr does not
mean your going to have 200 VB programmers tomorrow. Besides you still need
to have controls in-place as always. Your not going to let anyone code
against your DB anyway.
- TSQL has been improved and is not going away any time soon.
- What they do get is another tool in the box to improve productivity for
*certain procs/functions. Most procs/functions will still be in TSQL.
- When working in both, the gap is very noticeable when working in TSQL
without strong typing, intellisense, etc. Anything they can do to add
typing and compile time checks can only improve things IMO. Typing and
overloaded procs/functions would be a good start.

--
William Stacey [MVP]

[DA Morgan]

William Stacey [MVP] wrote:

I agree with what you've said. But it opens the door for them to do
what they have generally been precluded from doing before: Design
tables, views, etc. It blurs the line. And having seen schemas
designed by Java developers I tremble in fear at what the VB crowd
might be capable of doing.

- Not a VB programmer, but if I was, would be offended by that statement.
You seem to have some issue with VB.

Then let me extend the perceived insult. Over the years I have seen
RDBMS designs from C, C++, Java, Pascal, and many many other developers.
And the number that were acceptable, not event good, can be counted on
the fingers of one hand.

It is unrealistic and unreasonable to expect that someone with no formal
training in a subject can do well in it. And that isn't just about
front-end developers ... it is about any subject. I wouldn't want my
pharmacist removing my gall bladder either.

The difference is that most people know their limitations or are
precluded by laws from doing things they have not been trained to do.
(and yes I am speaking of the professions here). My issue with those
who write VB is that the number of them that connect to databases and
thus think they have some level of expertise is high. The percentage
of them that could make a decision with respect to denormalizing from
4NF to 3NF for performance quite small.

And this is just my experience so your mileage may vary.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[William Stacey [MVP]]

But your talking design now. That has nothing to do with SQLCLR
integration. I don't see the DB design process changing just because of
SQLCLR. You will still have DBAs/ DB architects doing that work (or
should). The app guys are still going to be the app guys. You need both.
So nothing has changed in that regard.

--
William Stacey [MVP]

"DA Morgan" <da******@psoug.org> wrote in message
news:1117806959.589791@yasure...

William Stacey [MVP] wrote:

I agree with what you've said. But it opens the door for them to do
what they have generally been precluded from doing before: Design
tables, views, etc. It blurs the line. And having seen schemas
designed by Java developers I tremble in fear at what the VB crowd
might be capable of doing.

- Not a VB programmer, but if I was, would be offended by that statement.
You seem to have some issue with VB.

Then let me extend the perceived insult. Over the years I have seen
RDBMS designs from C, C++, Java, Pascal, and many many other developers.
And the number that were acceptable, not event good, can be counted on
the fingers of one hand.

It is unrealistic and unreasonable to expect that someone with no formal
training in a subject can do well in it. And that isn't just about
front-end developers ... it is about any subject. I wouldn't want my
pharmacist removing my gall bladder either.

The difference is that most people know their limitations or are
precluded by laws from doing things they have not been trained to do.
(and yes I am speaking of the professions here). My issue with those
who write VB is that the number of them that connect to databases and
thus think they have some level of expertise is high. The percentage
of them that could make a decision with respect to denormalizing from
4NF to 3NF for performance quite small.

And this is just my experience so your mileage may vary.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[DA Morgan]

William Stacey [MVP] wrote:

But your talking design now. That has nothing to do with SQLCLR
integration. I don't see the DB design process changing just because of
SQLCLR. You will still have DBAs/ DB architects doing that work (or
should). The app guys are still going to be the app guys. You need both.
So nothing has changed in that regard.

Sorry if I wasn't clear before but I thought I brought this up.

In a DB2 or Oracle environment development will be primarily in SQL even
if other languages are allowed inside the database. In Oracle JAVA will
not replace PL/SQL. In DB2 allowing VB in will have a negligible effect.

But my read of the SQL Server market is that VB, C#, etc. is intended to
open the door for non-database developers to begin doing
inside-the-database development. In other words ... to expand the
market. And that the proximate result will be those unqualified doing
design work. I know I previously used the phrase "blurring the lines"
in this regard.

So no I am not worried about them writing bad code so much as them
creating tables and making design decisions such as whether to write
triggers, or natural vs surrogate key decisions.

Once again ... and this is just my read on it ... in Oracle, DB2,
Informix, and Sybase there is a cadre of IT professionals called DBAs,
that have a culture to defend the integrity of the database. In the
SQL Server world I do not see those with the same job having the
culture or training required to "just say no."
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Serge Rielau]

DA Morgan wrote:

William Stacey [MVP] wrote:

But your talking design now. That has nothing to do with SQLCLR
integration. I don't see the DB design process changing just because
of SQLCLR. You will still have DBAs/ DB architects doing that work
(or should). The app guys are still going to be the app guys. You
need both. So nothing has changed in that regard.

Sorry if I wasn't clear before but I thought I brought this up.

In a DB2 or Oracle environment development will be primarily in SQL even
if other languages are allowed inside the database. In Oracle JAVA will
not replace PL/SQL. In DB2 allowing VB in will have a negligible effect.

I think this is where we disagree. You appear to claim that TSQL,
PL/SQL, SQL/PSM are SQL and hence RDBMS friendly.
If you take a look at the procedural constructs of each of these SQL
"extensions" you can mess things up equally fine as with VB, Java or C#.
A VB programmer will have little difficulty using the procedural
constructs of any of these SQL extensions today because they are procedural.
In fact, doesn't Oracle support PL/SQL as an application language?
Are programmers using PL/SQL on the app side any smarter w.r.t.
relational SQL than those using VB? Do they write better SQL because
they use PL/SQL?

IMHO, there is nothing inherintly worse in VB/SQL compared to PL/SQL...
While VB/SQL locks you into Windows PL/SQL locks you into Oracle.
Rather obvious considering the respective owners....

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[William Stacey [MVP]]

> So no I am not worried about them writing bad code so much as them

creating tables and making design decisions such as whether to write
triggers, or natural vs surrogate key decisions.
This is what I don't understand. None of that changes. You still have
security (even more) to limit what people can and can not do. SqlClr does
not change that. If I can create a proc today, I have the option to create
a clr proc. I still need to use TSQL to get access to the data. I just
have a framework available to me to do other logic if I need it. It is
actually a wrapper. A ~normal sp is created that calls my clr proc. Anyone
can still say no. So I am still lost on what your issue is.

Once again ... and this is just my read on it ... in Oracle, DB2,
Informix, and Sybase there is a cadre of IT professionals called DBAs,
that have a culture to defend the integrity of the database. In the
SQL Server world I do not see those with the same job having the
culture or training required to "just say no."
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[DA Morgan]

Serge Rielau wrote:

I think this is where we disagree. You appear to claim that TSQL,
PL/SQL, SQL/PSM are SQL and hence RDBMS friendly.
Not at all: That is not what I am saying. What I am saying is that
those who are using them are far more likely to have training in
RDBMSs and thus be more knowledgeable and more competent.
If you take a look at the procedural constructs of each of these SQL
"extensions" you can mess things up equally fine as with VB, Java or C#.
A VB programmer will have little difficulty using the procedural
constructs of any of these SQL extensions today because they are
procedural.
My point has nothing to do with what the language, with extensions,
can or can not do. This is not about the language: It is about the
practitioners that know the language.
In fact, doesn't Oracle support PL/SQL as an application language?
Au contraire. But that is not relevant to the point I am trying to make
so ignore this please.
Are programmers using PL/SQL on the app side any smarter w.r.t.
relational SQL than those using VB? Do they write better SQL because
they use PL/SQL?
Yes. Yes. Yes.
IMHO, there is nothing inherintly worse in VB/SQL compared to PL/SQL...
Other than speed and scalability and security? But that is irrelevant
to what I have been trying to communicate. This isn't about the language
or its capabilities.
While VB/SQL locks you into Windows PL/SQL locks you into Oracle.
Rather obvious considering the respective owners....
Also irrelevant to my point.
Cheers
Serge

Once again ... those with formal schooling in relational databases, thus
those writing T-SQL, PL/SQL, SQL/PSM, etc. are also going to have a
stronger skill set with respect to normalization, proper use of
constraints, issues related to security and scalability, etc.

This is not to say that you could not use VB effectively. But it is to
say that your competence would to a great degree be predicated on the
fact that you understand what you are doing: The language is irrelevant.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[DA Morgan]

William Stacey [MVP] wrote:

So no I am not worried about them writing bad code so much as them
creating tables and making design decisions such as whether to write
triggers, or natural vs surrogate key decisions.

This is what I don't understand. None of that changes. You still have
security (even more) to limit what people can and can not do. SqlClr does
not change that. If I can create a proc today, I have the option to create
a clr proc. I still need to use TSQL to get access to the data. I just
have a framework available to me to do other logic if I need it. It is
actually a wrapper. A ~normal sp is created that calls my clr proc. Anyone
can still say no. So I am still lost on what your issue is.

We are beating a dead horse here so seriously consider letting this
thread die. But please read the following paragraph. It is my point.

Once again ... and this is just my read on it ... in Oracle, DB2,
Informix, and Sybase there is a cadre of IT professionals called DBAs,
that have a culture to defend the integrity of the database. In the
SQL Server world I do not see those with the same job having the
culture or training required to "just say no."

My point is not one language versus another which is what you and Serge
seem to assume. It is about the professionals wielding the tools and
their competence to use the tools properly.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[William Stacey [MVP]]

> My point is not one language versus another which is what you and Serge

seem to assume. It is about the professionals wielding the tools and
their competence to use the tools properly.

But this is too obvious. That is only saying that if your a SQL expert then
your good at SQL. This still has nothing to do with VB/C# in the SQLCLR as
this tread started out with. You will probably have to restate your
thoughts with an example or two if you want to go on. Cheers.

--
William Stacey [MVP]

[William Stacey [MVP]]

> Not at all: That is not what I am saying. What I am saying is that

those who are using them are far more likely to have training in
RDBMSs and thus be more knowledgeable and more competent.
That is stating the obvious.

Are programmers using PL/SQL on the app side any smarter w.r.t.
relational SQL than those using VB? Do they write better SQL because they
use PL/SQL?

Yes. Yes. Yes.

Be serious. Red is also better then blue if you prefer red.

IMHO, there is nothing inherintly worse in VB/SQL compared to PL/SQL...

Other than speed and scalability and security?

What facts do you have to back this up? Or is this just your opinion?
But that is irrelevant
This isn't about the language or its capabilities.

You brought up the VB thing. There are many talented SQL people out there
that also use VB and/or c#.

--
William Stacey [MVP]

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

Let me emphasize that this is just personal opinion. But I believe that
when VB and C# are available in SQL Server ... Transact will die ... a
slow death but a death non-the-less. I expect a very different
experience from what we saw in the Oracle world with Java in the
database where the majority of developers didn't change.

You are certainly not the first to raise that concern. I would even
go as far as saying that is is typical of people who have just seen
the headlines of what is new in SQL 2005.

First of all, there is no away SQL as such can die because of the CLR -
because data access is through SQL statements. What theoretically
could wither away is the control-of-flow stuff in Transact-SQL. But if
Microsoft had believed this to happen soon, they would not have
implemented what in my opinion is the most important improvement in
SQL 2005: TRY-CATCH handling in T-SQL.

Your idea appears to be that the crowd that works with SQL Server is
somehow different than the crowd that works with other engines. I am
not in a position to make such a comparison, since I don't hang around
with Oracle and DB2 people. But my experience from the SQL Server
newsgroups is that indeed not all people who work with SQL Server
work in an environment where there is a DBA with some sort of formal
training. This may be particularly true for people who set up some
database some smaller web site, or who develops smaller apps based
on MSDE. I don't know if Oracle or DB2 have any edition which is suitable
for that segment. If not, this could explain why Oracle and DB2 does
not have this group of developers. If you want to find another
engine that does have this sort of developers, look at MySQL.

Anyway, it should also be clear now where your leap of logic is. Even
it if is true that there is a segment of SQL Server developers that
will write their stored procedures in VB .Net only, there will still
be plenty of sites, that are just like your typical Oracle or DB2
shops, and that have a strong DBA, or other people with strong SQL
experience. They will not switch to VB. Net at whim.

By the way, the unexperienced developer who want to use the CLR in
SQL Server for this SQL Express app, actually has to pass a test:
he will have to find out how to enable CLR, because SQL Server 2005
ships with the CLR disabled by default.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[DA Morgan]

Erland Sommarskog wrote:

By the way, the unexperienced developer who want to use the CLR in
SQL Server for this SQL Express app, actually has to pass a test:
he will have to find out how to enable CLR, because SQL Server 2005
ships with the CLR disabled by default.

Thanks for the clarification.

Now lets hope that in the time it takes the product to go from Beta
to reality that doesn't change.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Erland Sommarskog]

DA Morgan (da******@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, es****@sommarskog.se

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

[JRStern]

Don't know what this has to do with CLR controversy, but ...

If indeed those six alternatives are equivalent, then #4 is certainly
the best one to give any competent RDBMS. Except that you might try
learning ANSI join syntax.

You've got to be joking that #5 would produce a better execution plan
on any real database, unless you have so few rows that the whole thing
is moot, and you forgot to define any appropriate keys or indexes.

Are you telling us that you saw better *peformance* between one plan
and another, or are you just giving us your (mis-)interpretation of
what the plans look like? Certainly on SQLServer it's easy to misread
the plans in regards to what kind of execution you will actually get.

And of course, the issue is moot for the additional reason that Erland
observed, that one would expect, and he showed for SQLServer, that the
optimizer will mostly ignore these syntactic variations and come up
with the same plan in most or all cases, anyway.

Josh
On Thu, 02 Jun 2005 14:51:07 -0700, DA Morgan <da******@psoug.org>
wrote:

Erland Sommarskog wrote:

DA Morgan (da******@psoug.org) writes:

The other place where I take issue with you is what I read as an
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.

My experience from reading posts on the SQL Server newsgroups, is
that most inexperienced users go for #2. And in the days of 6.5
it was a good idea to rewrite that this into #6 for improved
performance. And while I may still give people this advice, it may
only be for esthetic reasons only, because I believe that the
optimizer now is smart enough to essentially rewrite #2 into #6
internally.

Which goes to show that what is the best is not always obvious. Of
course, #4 is likely to perform less effecient because of the distinct.
#3 and #5 are too convluted to make sense (and #5 won't rnn on
SQL Server.) #1 is potentially bad, because it could return a different
result. (You didn't include any DDL, so I don't know how the tables
are related.)

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.

[louis]

I don't see any significant consequences with the ability to write
stored procedures in C#. As for Dan's comment that C# will lead to bad
SQL code -- I see extremely bad SQL code in MSSQL 2000 already. I've
seen developers who lovingly comment every method and function of their
C++ code but have zero comments in the stored procedures. I've seen
stored procedures that exceed 2000 lines without a single comment, not
explaining purpose, reason, or functionality. Their attitude is that
SQL is a second language to them, while their primary language is OOP.
Their development tools, Rational Rose etc., are very sketchy about
databases. The way Visual Studio interacts with MSSQL is also
something of a Rube Goldberg contraption. I don't see the situation
improving or getting any worse with the addition of the CLR into MSSQL
2000.

[Erland Sommarskog]

DA Morgan (da******@psoug.org) writes:

Erland Sommarskog wrote:

By the way, the unexperienced developer who want to use the CLR in
SQL Server for this SQL Express app, actually has to pass a test:
he will have to find out how to enable CLR, because SQL Server 2005
ships with the CLR disabled by default.

Thanks for the clarification.

Now lets hope that in the time it takes the product to go from Beta
to reality that doesn't change.

I would not really expect that. In the first betas, the switch was on
by default.

The practical impact of the current state remains to see. If nothing
else, it's going to be a very common question in the SQL Server newsgroups
"why can't I create a stored procedure in Visual Basic?".

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[DA Morgan]

JRStern wrote:

Don't know what this has to do with CLR controversy, but ...

If indeed those six alternatives are equivalent, then #4 is certainly
the best one to give any competent RDBMS. Except that you might try
learning ANSI join syntax.
In an Oracle class? Why? They can be done ... but zero value in the
more verbose syntax.
You've got to be joking that #5 would produce a better execution plan
on any real database,
You've got to be joking if you think that is what he demo proves.
It does exactly the opposite. #5 is an intentional disaster area.
Are you telling us that you saw better *peformance* between one plan
and another

Exactly. Otherwise what would be the point in doing this demo in front
of a university class?

Do keep in mind the audience for this thread is at least four separate
RDBMS products. Your mileage may vary. And do keep in mind also that,
as both Erland and I have both observed, different versions of the
same product do different things. Adding indexes, and the type of
index, etc.
--
Daniel A. Morgan
http://www.psoug.org
da******@x.washington.edu
(replace x with u to respond)

[Erland Sommarskog]

JRStern (jx*****@bogus.com) writes:

Don't know what this has to do with CLR controversy, but ...

If indeed those six alternatives are equivalent, then #4 is certainly
the best one to give any competent RDBMS.
I will have to agree with Daniel that #6 is my favourite. I will have
to say that I did not at all expect #4 to have the same plan as #2
and #6 (which I suspected would have the same plan).

And the reason that #6 is my favourite, is not because of performance,
but because it expresses the problem better: "show me all servers
that have a server instance"). (#2 does this as well, but is less
extensible, as it breaks down for two-column keys, unless you have
set constructors, which SQL Server has not.)
Except that you might try learning ANSI join syntax.

Here I don't agree with Daniel, and I will have to say that I was
surprised to see his dismay for this syntax. Then again, it took me
some time to start appreciate it. But it might be that Oracle's
proprietary operator for outer join (+= or whatever it is) is less
horrible than SQL Server's old *=.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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