Code in the database or middle tier (the CLR controversy)  
Author Message
MaioMan





PostPosted: Wed Jun 01 05:49:23 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

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:


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.hide-link.com/

"SOA, Multi-Tier Architectures and Logic in the Database"
http://www.hide-link.com/

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://www.hide-link.com/

"The bad news is that SQL Server 2005 will you define your own
aggregate
functions in a CLR language."


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

SQL Server231  
 
 
Serge





PostPosted: Wed Jun 01 05:49:23 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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:

>
> 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/showArticle.jhtml?articleID=50500830
>
> "SOA, Multi-Tier Architectures and Logic in the Database"
> http://www.sqlsummit.com/Articles/LogicInTheDatabase.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.com/archives/002419.html
>
> "The bad news is that SQL Server 2005 will you define your own
> aggregate
> functions in a CLR language."

>
> 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





PostPosted: Wed Jun 01 07:27:50 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)


> 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:


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/showArticle.jhtml?articleID=50500830
>
> "SOA, Multi-Tier Architectures and Logic in the Database"
> http://www.sqlsummit.com/Articles/LogicInTheDatabase.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.com/archives/002419.html
>
> "The bad news is that SQL Server 2005 will you define your own
> aggregate
> functions in a CLR language."

>
> 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





PostPosted: Wed Jun 01 08:16:02 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)


> > 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:

>
> 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





PostPosted: Wed Jun 01 08:51:08 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

> 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

(replace x with u to respond)
 
 
JT





PostPosted: Wed Jun 01 09:13:10 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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!



> 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:

>
> 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/showArticle.jhtml?articleID=50500830
>
> "SOA, Multi-Tier Architectures and Logic in the Database"
> http://www.sqlsummit.com/Articles/LogicInTheDatabase.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.com/archives/002419.html
>
> "The bad news is that SQL Server 2005 will you define your own
> aggregate
> functions in a CLR language."

>
> 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--





PostPosted: Wed Jun 01 11:01:57 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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.

 
 
Thomas





PostPosted: Wed Jun 01 11:53:52 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) > 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.

That's a curious point of view. PL/SQL and Java did not kill Oracle. Adding the
CLR to DB2 has not killed IBM.


Thomas


 
 
Thomas





PostPosted: Wed Jun 01 12:02:48 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) > 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?

There are features in the SQL for handling Nulls. There are features in 1.1 of
the framework for handling null (although they have a couple of downsides)

Regarding booleans, do you note that in all cases 0 = False and !0 = True? Is
that really that difficult?

> 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?

Actually, there is a COBOL.NET and numerous case studies of companies that have
migrated to it. Second, provide proof of your 70% estimate of *in use* COBOL
code.

Further, there is a J#.NET (however you wish to pronounce it) for Java folks.

You can still code middle-tier code against SQL Server. You don't *have* to use
the built in CLR. You argument is entirely without merit. If you want code
features for Oracle you need to learn PL/SQL or Java. For SQL Server, you need
to learn T-SQL and/or one of the .NET languages.

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

I'm sure you can make a SQL/PSM.NET variant that does just that if you like.


Thomas


 
 
William





PostPosted: Wed Jun 01 13:25:03 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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.aspx?PostID=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]



> 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:

>
> 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/showArticle.jhtml?articleID=50500830
>
> "SOA, Multi-Tier Architectures and Logic in the Database"
> http://www.sqlsummit.com/Articles/LogicInTheDatabase.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.com/archives/002419.html
>
> "The bad news is that SQL Server 2005 will you define your own
> aggregate
> functions in a CLR language."

>
> 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





PostPosted: Wed Jun 01 14:05:23 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)



>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--





PostPosted: Wed Jun 01 14:32:00 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) >> Regarding booleans, do you note that in all cases 0 = False and !0 = True? Is that really that difficult? <<

Only in the X3J langauges that use 1 for FALSE :)

>> Second, provide proof of your 70% estimate of *in use* COBOL code. <<

Let me pull some quotes whcih are a little dated but make the point
quickly:

Robert Glass (1997) estimates that two-thirds of all the world's
programmers use COBOL. Estimates of the number of COBOL programmers
range from 2.5 million (Keuffel 2000) to 3 million (Fussichen, 1990).
A voluntary response survey by Carr and Kizior (2000) on the future of
COBOL education indicates that more than 87% of the business
respondents' current develop and maintain COBOL applications. They
also said that more than 45% of the respondents would continue to use
COBOL through the next decade, although 30% thought their use of COBOL
would decline.

Estimates of the amount of COBOL code in productive use vary widely,
but they do not contradict each other. Arranga (2000) estimates
between 18 billion and 200 billion lines of COBOL code are running
production applications worldwide. Estimates of 80 billion by Fusichen
(1990), 150 to 175 billion lines by Wheatley in (Arranga, 1999) and 180
billion by Hardgrave and Doke (2000) fall within that range. The last
authors also estimate that 5 billion lines of new COBOL code are
written each year. Addressed from the perspective of software
applications written, Arranga and Price (2000) say that 35% of all new
business application development is written in COBOL.

The survey already cited by Hardgrave and Doke (2000) says that COBOL
is the primary language of the respondents. Glass reported in 1996 that
even looking at only client-server development, COBOL is the second
most popular language after Visual Basic. He also reported that for
the US Defense Department, 59% of information systems applications used
COBOL. In a Roundtable discussion printed in IEEE Software, Ian
Archbell (2000) stated:

"75% of all production transactions on mainframes is done using COBOL."

"Over 60% of all Web-access data resides on a mainframe."
"COBOL mainframes process more than 83% of all transactions worldwide."

"Over 95% of finance-insurance data is processed with COBOL."

Bibliography
Arranga, E, Archbell, I, Bradley, J, Coker, P, Townsend, C, Wheatley,
M. (2000 March/April). "Roundtable in COBOL's defense". IEEE
Software, 17(2), 70-72.

Arranga, E. & Price, W. (2000 March/April). "Fresh from Y2K, what's
next for COBOL?". IEEE Software, 17(2), 16-20.

Arranga, E. (2000 March 6). "An open letter to Ed Yourdon". The
COBOL Report [on-line]. Available: http://www.objectz.com/cobolreport/
[2000 March 13].

Carr, D. & Kizior, R. (2000 March/April). "The case for continued
COBOL education". IEEE Software, 17(2) 33-36.

Cezzar, R. (1995). A Guide to Programming Languages. Boston: Artech
House.

Coyle, F. (2000 March/April). "Does COBOL exist?". IEEE Software,
17(2), 22,24.

Fussichen, K. (1990). "Why COBOL programmers refuse Ada". ACM
Digital Library [on-line]. Available:
http://www.acm.org/dl/newsearch.html [2000 February 16].

Glass, R. (1996 September-October). "Business applications: what
should a programming language offer?". The Software Practitioner,
6(5) 5-10.

Glass, R. (1997 September). "COBOL - a contradiction and an
enigma". Communications of the ACM, 4(9), 11-13.

Hardgrave, B. & Doke, E. (2000 March/April). "COBOL in and
object-oriented world: a learning perspective". IEEE Software, 17(2),
26-29.

Joiner, J. & Tsai, W. (1994). "Re-engineering legacy COBOL
programs". ACM Digital Library [on-line]. Available:
http://www.acm.org/dl/newsearch.html [2000 February 16].

Keuffel, W. (2000 March). "COBOL: the next Internet language?".
Software Development, 8(3), 67-68.

 
 
--CELKO--





PostPosted: Wed Jun 01 14:35:59 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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





PostPosted: Wed Jun 01 14:43:18 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) This is "dead" technology.
http://www.psm.qc.ca/



>

>
>
>>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.


 
 
JT





PostPosted: Wed Jun 01 14:42:14 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 200 billion lines of source code? This can be easily explained by the fac
that most of these legacy COBOL applications were written back in the day
when productivity was measured in lines of source written per day.



> >> Regarding booleans, do you note that in all cases 0 = False and !0 =
True? Is that really that difficult? <<
>
> Only in the X3J langauges that use 1 for FALSE :)
>
> >> Second, provide proof of your 70% estimate of *in use* COBOL code. <<
>
> Let me pull some quotes whcih are a little dated but make the point
> quickly:
>
> Robert Glass (1997) estimates that two-thirds of all the world's
> programmers use COBOL. Estimates of the number of COBOL programmers
> range from 2.5 million (Keuffel 2000) to 3 million (Fussichen, 1990).
> A voluntary response survey by Carr and Kizior (2000) on the future of
> COBOL education indicates that more than 87% of the business
> respondents' current develop and maintain COBOL applications. They
> also said that more than 45% of the respondents would continue to use
> COBOL through the next decade, although 30% thought their use of COBOL
> would decline.
>
> Estimates of the amount of COBOL code in productive use vary widely,
> but they do not contradict each other. Arranga (2000) estimates
> between 18 billion and 200 billion lines of COBOL code are running
> production applications worldwide. Estimates of 80 billion by Fusichen
> (1990), 150 to 175 billion lines by Wheatley in (Arranga, 1999) and 180
> billion by Hardgrave and Doke (2000) fall within that range. The last
> authors also estimate that 5 billion lines of new COBOL code are
> written each year. Addressed from the perspective of software
> applications written, Arranga and Price (2000) say that 35% of all new
> business application development is written in COBOL.
>
> The survey already cited by Hardgrave and Doke (2000) says that COBOL
> is the primary language of the respondents. Glass reported in 1996 that
> even looking at only client-server development, COBOL is the second
> most popular language after Visual Basic. He also reported that for
> the US Defense Department, 59% of information systems applications used
> COBOL. In a Roundtable discussion printed in IEEE Software, Ian
> Archbell (2000) stated:
>
> "75% of all production transactions on mainframes is done using COBOL."
>
> "Over 60% of all Web-access data resides on a mainframe."
> "COBOL mainframes process more than 83% of all transactions worldwide."
>
> "Over 95% of finance-insurance data is processed with COBOL."
>
> Bibliography
> Arranga, E, Archbell, I, Bradley, J, Coker, P, Townsend, C, Wheatley,
> M. (2000 March/April). "Roundtable in COBOL's defense". IEEE
> Software, 17(2), 70-72.
>
> Arranga, E. & Price, W. (2000 March/April). "Fresh from Y2K, what's
> next for COBOL?". IEEE Software, 17(2), 16-20.
>
> Arranga, E. (2000 March 6). "An open letter to Ed Yourdon". The
> COBOL Report [on-line]. Available: http://www.objectz.com/cobolreport/
> [2000 March 13].
>
> Carr, D. & Kizior, R. (2000 March/April). "The case for continued
> COBOL education". IEEE Software, 17(2) 33-36.
>
> Cezzar, R. (1995). A Guide to Programming Languages. Boston: Artech
> House.
>
> Coyle, F. (2000 March/April). "Does COBOL exist?". IEEE Software,
> 17(2), 22,24.
>
> Fussichen, K. (1990). "Why COBOL programmers refuse Ada". ACM
> Digital Library [on-line]. Available:
> http://www.acm.org/dl/newsearch.html [2000 February 16].
>
> Glass, R. (1996 September-October). "Business applications: what
> should a programming language offer?". The Software Practitioner,
> 6(5) 5-10.
>
> Glass, R. (1997 September). "COBOL - a contradiction and an
> enigma". Communications of the ACM, 4(9), 11-13.
>
> Hardgrave, B. & Doke, E. (2000 March/April). "COBOL in and
> object-oriented world: a learning perspective". IEEE Software, 17(2),
> 26-29.
>
> Joiner, J. & Tsai, W. (1994). "Re-engineering legacy COBOL
> programs". ACM Digital Library [on-line]. Available:
> http://www.acm.org/dl/newsearch.html [2000 February 16].
>
> Keuffel, W. (2000 March). "COBOL: the next Internet language?".
> Software Development, 8(3), 67-68.
>


 
 
--CELKO--





PostPosted: Wed Jun 01 14:53:24 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) >> .. most of these legacy COBOL applications were written back in the day when productivity was measured in lines of source written per day. <<

Actually, we measured things by "wall clock time" because the computer
were so much more expensive than the programmers. You ran the shop on
three or four shifts per day, so the programmers, the cops and the
****s all ate at the same all-night diners.

I think the reason COBOL is so verbose is that it took so long to get
test time, you had to give the programmers busy work to do.

 
 
Paul





PostPosted: Wed Jun 01 14:51:56 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)


>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.
 
 
Payson





PostPosted: Wed Jun 01 15:08:55 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) oh, geez... talk about a blast from the past.

I wrote COBOL for a whole group of years. I still get occasional
requests from placement agencies, but not nearly like it was pre y2k.

As an aside, I do remember getting penalized 5 points per run (compile,
link and go) when I took my first COBOL course. Talk about desk
checking...

Payson



> >> .. most of these legacy COBOL applications were written back in the day when productivity was measured in lines of source written per day. <<
>
> Actually, we measured things by "wall clock time" because the computer
> were so much more expensive than the programmers. You ran the shop on
> three or four shifts per day, so the programmers, the cops and the
> ****s all ate at the same all-night diners.
>
> I think the reason COBOL is so verbose is that it took so long to get
> test time, you had to give the programmers busy work to do.

 
 
Raymond





PostPosted: Wed Jun 01 15:13:56 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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.



>

>
>>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.


 
 
Payson





PostPosted: Wed Jun 01 15:17:54 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) oh, geez... talk about a blast from the past.

I wrote COBOL for a whole group of years. I still get occasional
requests from placement agencies, but not nearly like it was pre y2k.

As an aside, I do remember getting penalized 5 points per run (compile,
link and go) when I took my first COBOL course. Talk about desk
checking...

Payson



> >> .. most of these legacy COBOL applications were written back in the day when productivity was measured in lines of source written per day. <<
>
> Actually, we measured things by "wall clock time" because the computer
> were so much more expensive than the programmers. You ran the shop on
> three or four shifts per day, so the programmers, the cops and the
> ****s all ate at the same all-night diners.
>
> I think the reason COBOL is so verbose is that it took so long to get
> test time, you had to give the programmers busy work to do.

 
 
DA





PostPosted: Wed Jun 01 15:34:50 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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

(replace x with u to respond)
 
 
Thomas





PostPosted: Wed Jun 01 16:48:10 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) > Robert Glass (1997) estimates that two-thirds of all the world's
> programmers use COBOL. Estimates of the number of COBOL programmers
> range from 2.5 million (Keuffel 2000) to 3 million (Fussichen, 1990).
> A voluntary response survey by Carr and Kizior (2000) on the future of
> COBOL education indicates that more than 87% of the business
> respondents' current develop and maintain COBOL applications. They
> also said that more than 45% of the respondents would continue to use
> COBOL through the next decade, although 30% thought their use of COBOL
> would decline.

Your statistics are severly skewed. Microsoft claimed, around the same time
(1997), that over 80% of the world's code was written in VB. That's just as
believeable (more believeable) as your sources. I live in a city with over 8
million people in the greater metropolitian area and in the 25 years since I
started writing code I can count the number of people that I have encountered
that have ever written anything in COBOL on one hand with fingers left over. Who
were they asking? Were they only interviewing people at insurance companies? Did
they define a COBOL programmer as ones that were *currently* writing code in
COBOL?

InfoWorld study - COBOL is not even listed.
http://www.infoworld.com/article/04/09/24/39FErrdev_1.html

On the popularity of languages based on Google hits...COBOL is last
http://www.dedasys.com/articles/language_popularity.html

ComputerWorld survey on the popularity of languages - COBOL is not even listed.
http://www.computerworld.com/developmenttopics/development/story/0,10801,100542,00.html

Programming Language Popularity: The TCP Index for December, 2004
COBOL is 13th
http://www.developer.com/net/net/article.php/3448451


> Estimates of the amount of COBOL code in productive use vary widely,
> but they do not contradict each other. Arranga (2000) estimates
> between 18 billion and 200 billion lines of COBOL code are running
> production applications worldwide. Estimates of 80 billion by Fusichen
> (1990), 150 to 175 billion lines by Wheatley in (Arranga, 1999) and 180
> billion by Hardgrave and Doke (2000) fall within that range. The last
> authors also estimate that 5 billion lines of new COBOL code are
> written each year. Addressed from the perspective of software
> applications written,

First, COBOL is the poster child for verbosity. So, there is the issue that code
in COBOL will have more lines than the equivalent functionality in a modern
language.

Second, if those numbers are to be believed, then non-COBOL languages would blow
that out of the water. First there is almost every variant of Unix which runs on
C. Then there's all versions of Windows, all versions of Macs, every commecial
program written in the past 15 years, all defense department code not writtin in
COBOL (e.g. ADA, C etc). The list goes on. Excel 5 alone contained at least 7
million lines of code. Window 2000 contains over 40 million lines of code. Even
if you treat those as one program, you have every version of every Microsoft
program ever written and that's only Microsoft. That doesn't count Sun, Oracle,
IBM (non-COBOL stuff) and so on. Then there's open source. Beyond all that, is
pretty much every website ever written. After all of that is all the custom
commecial code written in the past 10 or 15 years which was not in COBOL.


> Arranga and Price (2000) say that 35% of all new
> business application development is written in COBOL.

Sorry, don't accept that number. I have worked with more than a few city
governments and large corporations and no where did I see COBOL. I saw all kinds
of obscure languages or something like VMS. I'm not at all suggesting it doesn't
exist. Rather, I find this claim of 35% of new development **highly**
questionable given the rarity with which I have encounterd it, the rareity with
which it is mentioned on the net, and would want to see the specifics of how
they came up with that number. (Which of course they do not provide)


> The survey already cited by Hardgrave and Doke (2000) says that COBOL
> is the primary language of the respondents. Glass reported in 1996 that
> even looking at only client-server development, COBOL is the second
> most popular language after Visual Basic.

Who and where exactly is this survey? What exactly were the questions asked and
to whom?

> He also reported that for
> the US Defense Department, 59% of information systems applications used
> COBOL. In a Roundtable discussion printed in IEEE Software, Ian
> Archbell (2000) stated:
>
> "75% of all production transactions on mainframes is done using COBOL."

Ok. *That* I'll buy. However, what would be interesting is know the number of
companies still using mainframes out all companies worldwide (meaning IBM counts
as 1)


> "Over 60% of all Web-access data resides on a mainframe."
> "COBOL mainframes process more than 83% of all transactions worldwide."

Based on what? What exactly do they mean by "transaction". Again, highly
questionable. I would want to see how they came up with this number. Neither
Google, Microsoft, Yahoo nor eBay run off mainframes. (To claim the contrary
means we'd have to define what we mean by a "mainframe.") That right there
represents a significant portion of the worldwide daily web traffic. How did
they determine how much "web-access" data actually exists?

> "Over 95% of finance-insurance data is processed with COBOL."

This might be a plausible but overstated fact given than finance and insurance
companies are generally older companies with older computer systems and
resistent to change. That said, I'll bet that number is significantly lower
today only five years after that claim was made.

In doing a cursory Google search, I was unable to find any site that would
substantiate your above claims. Every survey I found indicated what I expected:
C, C++, Java, and VB are **by far** the most popular languages and that doesn't
include scripting languages like PHP, VBScript, Javascript etc.

The only evidence you provide that would even be close to substantiating your
claim is the Carr and Kizior survey. Not wanting to purchase $20?! article, I
notice in the abstract that it states:

In accessing the IS manager's view and the academic's view of Cobol's future,
this survey found that 95% of its academic respondents and 90% of the IS
managers polled want the IS curricula to continue offering Cobol instruction.
Also, nearly 90% of IS managers indicate that Cobol instruction in colleges
should cover both Cobol's OO and Web-based features.

First of all, notice the wording: "95% of its academic respondents and 90% of
the IS managers polled". Who were these people? How many did they poll? What
cross-section of IS managers did they poll? I went to a size-able four year
university and AFAIK, COBOL was only taught as class; not used anywhere in
production. I worked at the then only micro computer lab on campus where all
development was cycled and we had no COBOL programmers. This sounds like an
article meant to persuade rather than an objective analysis.

Second COBOL's OO features?! Are you kidding? Why would anyone ever consider
doing OO or web design in COBOL?

Barring this survey, I find no evidence to back your claim that 70% or 78% of
the world's code is in COBOL. For us to consider that claim seriously, we would
have to know how your are determining that someone is a "COBOL programmer"
and/or we would need to know how you are measuring the "world's code". Measuring
it purely in lines of code is misleading because of the extraordinarly verbose
nature of COBOL. COBOL has no hope of winning in terms of sheer numbers of
programs, although we would have to define how we are differentiating programs
(e.g. does spell check count as a separate program?). COBOL has no hope of
winning in terms of active programmers over the past five perhaps even ten
years.

I have no doubt that there are many industries that still have thousands of
lines of COBOL code. I simply do not accept your claim that 70%+ of the world's
code is in COBOL.



Thomas



 
 
Erland





PostPosted: Wed Jun 01 17:08:53 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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.

--


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





PostPosted: Wed Jun 01 19:20:54 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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"...



> 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!
>


> > 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:

> >
> > 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/showArticle.jhtml?articleID=50500830
> >
> > "SOA, Multi-Tier Architectures and Logic in the Database"
> > http://www.sqlsummit.com/Articles/LogicInTheDatabase.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.com/archives/002419.html
> >
> > "The bad news is that SQL Server 2005 will you define your own
> > aggregate
> > functions in a CLR language."

> >
> > 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





PostPosted: Wed Jun 01 20:57:08 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
>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:


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





PostPosted: Wed Jun 01 22:17:03 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>
>>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:

>
>
> 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

(replace x with u to respond)
 
 
William





PostPosted: Wed Jun 01 23:34:09 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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]





>>
>>>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:

>>
>>
>> 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

> (replace x with u to respond)


 
 
Mark





PostPosted: Thu Jun 02 01:06:47 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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





PostPosted: Thu Jun 02 01:50:14 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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





PostPosted: Thu Jun 02 08:51:14 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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

(replace x with u to respond)
 
 
Serge





PostPosted: Thu Jun 02 09:12:22 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>
>> 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





PostPosted: Thu Jun 02 09:33:01 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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]




>> 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

> (replace x with u to respond)


 
 
DA





PostPosted: Thu Jun 02 10:22:34 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>> 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

(replace x with u to respond)
 
 
DA





PostPosted: Thu Jun 02 10:26:20 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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

(replace x with u to respond)
 
 
Erland





PostPosted: Thu Jun 02 16:01:59 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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.

--


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





PostPosted: Thu Jun 02 16:10:26 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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.)


--


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





PostPosted: Thu Jun 02 16:45:54 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>>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

(replace x with u to respond)
 
 
DA





PostPosted: Thu Jun 02 16:51:07 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>
>>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

(replace x with u to respond)
 
 
Serge





PostPosted: Thu Jun 02 21:32:49 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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





PostPosted: Thu Jun 02 23:24:53 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>
>> 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

(replace x with u to respond)
 
 
William





PostPosted: Fri Jun 03 08:17:17 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) > 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





PostPosted: Fri Jun 03 08:58:03 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
>>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

(replace x with u to respond)
 
 
William





PostPosted: Fri Jun 03 09:25:31 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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]




>>>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

> (replace x with u to respond)


 
 
DA





PostPosted: Fri Jun 03 09:51:45 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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

(replace x with u to respond)
 
 
Serge





PostPosted: Fri Jun 03 10:19:43 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>
>> 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





PostPosted: Fri Jun 03 12:05:08 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) > 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

> (replace x with u to respond)


 
 
DA





PostPosted: Fri Jun 03 14:08:03 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

> 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

(replace x with u to respond)
 
 
DA





PostPosted: Fri Jun 03 14:10:07 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
>>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

(replace x with u to respond)
 
 
William





PostPosted: Fri Jun 03 16:29:28 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) > 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





PostPosted: Fri Jun 03 16:36:02 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) > 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





PostPosted: Fri Jun 03 16:53:01 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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.


--


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





PostPosted: Fri Jun 03 17:24:25 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

> 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

(replace x with u to respond)
 
 
Erland





PostPosted: Fri Jun 03 17:26:06 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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.)

--


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





PostPosted: Fri Jun 03 19:00:45 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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






>>
>>>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





PostPosted: Sat Jun 04 02:01:52 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) 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





PostPosted: Sat Jun 04 07:04:50 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>> 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?".

--


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





PostPosted: Sat Jun 04 08:29:18 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

> 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

(replace x with u to respond)
 
 
Erland





PostPosted: Sat Jun 04 14:21:58 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> 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 *=.


--


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





PostPosted: Sat Jun 04 15:20:29 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>>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 *=.

Consider these two INNER JOINS formatted to match.

-- ISO syntax
SELECT p.last_name, t.title_name
FROM person p , title t
WHERE p.title_1 = t.title_abbrev;

-- ANSI syntax
SELECT p.last_name, t.title_name
FROM person p INNER JOIN title t
ON p.title_1 = t.title_abbrev;

Line 2:
What advantage in replacing a single
comma with "INNER JOIN"?

Line 3:
What value in replace WHERE with ON

If performance is identical? The issue is not much
different from asking which is better, a car with
the steering on the right or a car with the steering
on the left.

Now I know those used to the ANSI syntax will likely
be thinking compatibility with other systems. But
those with 10+ years in Oracle will be thinking: So
what!
--
Daniel A. Morgan
http://www.psoug.org

(replace x with u to respond)
 
 
Erland





PostPosted: Sat Jun 04 17:35:02 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> Consider these two INNER JOINS formatted to match.
>
> -- ISO syntax
> SELECT p.last_name, t.title_name
> FROM person p , title t
> WHERE p.title_1 = t.title_abbrev;
>
> -- ANSI syntax
> SELECT p.last_name, t.title_name
> FROM person p INNER JOIN title t
> ON p.title_1 = t.title_abbrev;
>
> Line 2:
> What advantage in replacing a single
> comma with "INNER JOIN"?
>
> Line 3:
> What value in replace WHERE with ON

In this example: not very much. But digest this:

FROM notes nte, insloannotes iln, contnotes con, instruments ins,
trades trd, accounts acc, customers cst, addresses adr,
customeraddresses cad, insloanstartnotes ils, countries cou,
notprintedcontnotes npc
WHERE nte.nteid = npc.conid
AND npc.printdate IS NULL
AND nte.nteid = iln.conid
AND iln.loanconid = con.conid
AND nte.trdid = trd.trdid
AND con.insid = ins.insid
AND iln.loanevent = 'I'
AND iln.loanconid = ils.conid
AND nte.accno = acc.accno
AND acc.cstno = cad.cstno
AND acc.cstno = cst.cstno
AND cad.isdefaultsendout= 1
AND cad.adrid = adr.adrid
AND adr.coucode = cou.coucode
AND trd.butcode = 'IIL'




versus:

FROM notprintedcontnotes npc
JOIN notes nte ON nte.nteid = npc.conid
JOIN trades trd ON trd.trdid = nte.trdid
JOIN insloannotes iln ON nte.nteid = iln.conid
JOIN contnotes con ON con.conid = iln.loanconid
JOIN insloanstartnotes ils ON con.conid = ils.conid
AND iln.loanconid = ils.conid
JOIN instruments ins ON con.insid = ins.insid
AND trd.insid = ins.insid
JOIN accounts acc ON nte.accno = acc.accno
AND con.accno = acc.accno
JOIN customers cst ON acc.cstno = cst.cstno
JOIN customeraddresses cad ON acc.cstno = cad.cstno
AND cad.isdefaultsendout = 1
JOIN addresses adr ON adr.adrid = cad.adrid
LEFT JOIN countries cou ON adr.coucode = cou.coucode
WHERE npc.printdate IS NULL
AND iln.loanevent = 'I'
AND trd.butcode = 'IIL'




I know which one I rather have for breakfast.

> If performance is identical? The issue is not much
> different from asking which is better, a car with
> the steering on the right or a car with the steering
> on the left.

On SQL Server, the performance is indeed identical. The story is the
query processor rewrites the query internally. Obviously, I cannot
tell what Oracle does.

> Now I know those used to the ANSI syntax will likely
> be thinking compatibility with other systems. But
> those with 10+ years in Oracle will be thinking: So
> what!

Actually, as long as we are into inner joins, both syntaxes are
ANSI-compatible. It is when it comes to outer joins it matter.
Here both SQL Server and Oracle have their own propritary operators.
I don't know about the Oracle operator, but the *= in SQL Server
is very problematic. For this reason, there is a strong recommendation
to use ANSI JOIN for outer joins, and once you are there, it's logical
to use it for inner joins as well. But this is for SQL Server. If
the Oracle outer-join operator (+= is it?) is sounder, you certainly
have one incentive less to change.

--


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





PostPosted: Sat Jun 04 20:40:26 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

> In this example: not very much. But digest this:
>
> FROM notes nte, insloannotes iln, contnotes con, instruments ins,
> trades trd, accounts acc, customers cst, addresses adr,
> customeraddresses cad, insloanstartnotes ils, countries cou,
> notprintedcontnotes npc
> WHERE nte.nteid = npc.conid
> AND npc.printdate IS NULL
> AND nte.nteid = iln.conid
> AND iln.loanconid = con.conid
> AND nte.trdid = trd.trdid
> AND con.insid = ins.insid
> AND iln.loanevent = 'I'
> AND iln.loanconid = ils.conid
> AND nte.accno = acc.accno
> AND acc.cstno = cad.cstno
> AND acc.cstno = cst.cstno
> AND cad.isdefaultsendout= 1
> AND cad.adrid = adr.adrid
> AND adr.coucode = cou.coucode
> AND trd.butcode = 'IIL'



>
> versus:
>
> FROM notprintedcontnotes npc
> JOIN notes nte ON nte.nteid = npc.conid
> JOIN trades trd ON trd.trdid = nte.trdid
> JOIN insloannotes iln ON nte.nteid = iln.conid
> JOIN contnotes con ON con.conid = iln.loanconid
> JOIN insloanstartnotes ils ON con.conid = ils.conid
> AND iln.loanconid = ils.conid
> JOIN instruments ins ON con.insid = ins.insid
> AND trd.insid = ins.insid
> JOIN accounts acc ON nte.accno = acc.accno
> AND con.accno = acc.accno
> JOIN customers cst ON acc.cstno = cst.cstno
> JOIN customeraddresses cad ON acc.cstno = cad.cstno
> AND cad.isdefaultsendout = 1
> JOIN addresses adr ON adr.adrid = cad.adrid
> LEFT JOIN countries cou ON adr.coucode = cou.coucode
> WHERE npc.printdate IS NULL
> AND iln.loanevent = 'I'
> AND trd.butcode = 'IIL'



>
> I know which one I rather have for breakfast.

And I'd likely agree. But then I wouldn't use this query as
a demo of EXPLAIN PLAN for what should be equally obvious reasons.

> Actually, as long as we are into inner joins, both syntaxes are
> ANSI-compatible. It is when it comes to outer joins it matter.
> Here both SQL Server and Oracle have their own propritary operators.

Not true. Oracle has for multiple versions now allowed either ANSI or
ISO syntax.
> --

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

--
Daniel A. Morgan
http://www.psoug.org

(replace x with u to respond)
 
 
Serge





PostPosted: Sat Jun 04 23:14:15 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>
>> In this example: not very much. But digest this:
>>
<snip>
>> Actually, as long as we are into inner joins, both syntaxes are
>> ANSI-compatible. It is when it comes to outer joins it matter.
>> Here both SQL Server and Oracle have their own propritary operators.
>
>
> Not true. Oracle has for multiple versions now allowed either ANSI or
> ISO syntax.
Not his point. The point is that the prorietary synatx also has
proprietary semantics. So why teach what Oracle discourages?
Interesting to note that Oracle bothered. Apparantly they saw a need
for compliance for core function...
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/queries006.htm
Quote:
Oracle recommends that you use the FROM clause OUTER JOIN syntax rather
than the Oracle join operator. Outer join queries that use the Oracle
join operator (+) are subject to the following rules and restrictions,
which do not apply to the FROM clause OUTER JOIN syntax:

*

You cannot specify the (+) operator in a query block that also
contains FROM clause join syntax.
*

The (+) operator can appear only in the WHERE clause or, in the
context of left-correlation (that is, when specifying the TABLE clause)
in the FROM clause, and can be applied only to a column of a table or view.
*

If A and B are joined by multiple join conditions, then you must
use the (+) operator in all of these conditions. If you do not, then
Oracle Database will return only the rows resulting from a simple join,
but without a warning or error to advise you that you do not have the
results of an outer join.
*

The (+) operator does not produce an outer join if you specify
one table in the outer query and the other table in an inner query.
*

You cannot use the (+) operator to outer-join a table to itself,
although self joins are valid. For example, the following statement is
not valid:

-- The following statement is not valid:
SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;


However, the following self join is valid:

SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id;

*

The (+) operator can be applied only to a column, not to an
arbitrary expression. However, an arbitrary expression can contain one
or more columns marked with the (+) operator.
*

A WHERE condition containing the (+) operator cannot be combined
with another condition using the OR logical operator.
*

A WHERE condition cannot use the IN comparison condition to
compare a column marked with the (+) operator with an expression.
*

A WHERE condition cannot compare any column marked with the (+)
operator with a subquery.

If the WHERE clause contains a condition that compares a column from
table B with a constant, then the (+) operator must be applied to the
column so that Oracle returns the rows from table A for which it has
generated nulls for this column. Otherwise Oracle returns only the
results of a simple join.

In a query that performs outer joins of more than two pairs of tables, a
single table can be the null-generated table for only one other table.
For this reason, you cannot apply the (+) operator to columns of B in
the join condition for A and B and the join condition for B and C.
Please refer to SELECT for the syntax for an outer join.

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





PostPosted: Sun Jun 05 09:14:42 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>> Actually, as long as we are into inner joins, both syntaxes are
>> ANSI-compatible. It is when it comes to outer joins it matter.
>> Here both SQL Server and Oracle have their own propritary operators.
>
> Not true. Oracle has for multiple versions now allowed either ANSI or
> ISO syntax.

I think you misunderstood. I don't question that Oracle has the LEFT
JOIN operator and that. What I was trying to say is that it could be the
case that Oracle's own outer-join operator is not as tainted as the
one of SQL Server, and thus the incentive to use the ANSI syntax would
not be as strong.

But judging from Serge Rideau's post, there are problems with (+)= as well.
Although the *= of SQL Server could still be worse. As a matter of fact,
in SQL 2000, *= will only be available in compatibility mode only.

--


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





PostPosted: Sun Jun 05 13:05:38 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

> Not his point. The point is that the prorietary synatx also has
> proprietary semantics. So why teach what Oracle discourages?

Normally I encourage that students, and professionals, follow Oracle's
advice. For example when Oracle said stop using LONG and LONG RAW I
dropped every example that used them.

But there is a significant difference with the ISO syntax and here is
why.

1. 99% of all code in Oracle anyone sees will be ISO compliant and the
Oracle professional must understand it and be able to maintain it.

2. In the Oracle community those that code with ANSI are generally
viewed as newbies who have recently come over from some other
product. Use ANSI in an interview and likely you won't get the job
if the other person that applied is your technical equal but uses
the syntax that looks more "normal" to the interview team.

And while I expect you won't like #2, your liking it is not relevant to
the fact that I have seen this played out in multiple interviews.

There are things the Oracle supported ANSI syntax will do, such as full
outer joins that are far more complex to do with ISO. And there are
things the ANSI syntax allows that are just plain moronic ... such as
natural joins. I encourage all of my students to learn all of it and
then use the appropriate tool for the environment in which they are
working.

> Interesting to note that Oracle bothered. Apparantly they saw a need for
> compliance for core function...
> http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/queries006.htm

They seem to always embrace that which is common. But also to extend
where they see a 'need' (you may read that as 'market opportunity').
The critical factor is that I can accomplish the goal in the manner that
is most pleasing to me as a developer. The amount of ANSI code I have
seen in Oracle applications is a very small percentage: Not more than a
few percent. That may change over decades but it isn't changing any time
soon.
--
Daniel A. Morgan
http://www.psoug.org

(replace x with u to respond)
 
 
DA





PostPosted: Sun Jun 05 13:16:50 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>

>>
>>>Actually, as long as we are into inner joins, both syntaxes are
>>>ANSI-compatible. It is when it comes to outer joins it matter.
>>>Here both SQL Server and Oracle have their own propritary operators.
>>
>>Not true. Oracle has for multiple versions now allowed either ANSI or
>>ISO syntax.
>
>
> I think you misunderstood. I don't question that Oracle has the LEFT
> JOIN operator and that. What I was trying to say is that it could be the
> case that Oracle's own outer-join operator is not as tainted as the
> one of SQL Server, and thus the incentive to use the ANSI syntax would
> not be as strong.

I misunderstood. You are correct in your assumption.

SQL> SELECT DISTINCT i.srvr_id
2 FROM serv_inst i, servers s
3 WHERE i.srvr_id = s.srvr_id(+);

11 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=11 Bytes=88)
1 0 SORT (UNIQUE) (Cost=6 Card=11 Bytes=88)
2 1 NESTED LOOPS (OUTER) (Cost=5 Card=999 Bytes=7992)
3 2 TABLE ACCESS (FULL) OF 'SERV_INST' (TABLE) (Cost=4
Card=999 Bytes=3996)
4 2 INDEX (UNIQUE SCAN) OF 'PK_SERVERS' (INDEX (UNIQUE))
(Cost=0 Card=1 Bytes=4)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
532 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed

SQL> SELECT DISTINCT i.srvr_id
2 FROM serv_inst i LEFT OUTER JOIN servers s
3 ON i.srvr_id = s.srvr_id
4 /

11 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=11 Bytes=88)
1 0 SORT (UNIQUE) (Cost=6 Card=11 Bytes=88)
2 1 NESTED LOOPS (OUTER) (Cost=5 Card=999 Bytes=7992)
3 2 TABLE ACCESS (FULL) OF 'SERV_INST' (TABLE) (Cost=4
Card=999 Bytes=3996)
4 2 INDEX (UNIQUE SCAN) OF 'PK_SERVERS' (INDEX (UNIQUE))
(Cost=0 Card=1 Bytes=4)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
532 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed

As you can see: Identical.

> But judging from Serge Rideau's post, there are problems with (+)= as well.
> Although the *= of SQL Server could still be worse. As a matter of fact,
> in SQL 2000, *= will only be available in compatibility mode only.

Serge is incorrect in that these limitations have always been there and
Oracle developers have been using in-line views to work around them for
so long that they don't even pay any attention: It just comes naturally.

I can't speak for Oracle but I expect the ISO operators will be there
for the rest of my professional life. Oracle has always been far more
committed to backward compatibility than some other companies. Often to
my dismay in that some of the antiquities encourage bad practices.
--
Daniel A. Morgan
http://www.psoug.org

(replace x with u to respond)
 
 
Erland





PostPosted: Sun Jun 05 17:16:42 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> As you can see: Identical.

For such a simple query, I would expect anything else. I did not try
the same on SQL Server, but I would expect identical plans there as well.

The problems with *= are not really with performance, but funny
restrictions, and unexpected results that are difficult to explain. And
there is no *=* for a full outer join.

> I can't speak for Oracle but I expect the ISO operators will be there
> for the rest of my professional life. Oracle has always been far more
> committed to backward compatibility than some other companies. Often to
> my dismay in that some of the antiquities encourage bad practices.

Microsoft has in my opinion a quite elegant take on this: you can run a
database in compatibility mode for an earlier version. This permits
MS to drop support or to make incompatible changes in order to improve
things, but still makes it possible for people to migrate.

I have seen few people lament that *= are going away from the mainstream.
In our system we had over 500 stored procedures with this syntax, and I
really welcome the change. (And thanks to that I outlawed the syntax in
our load tool, our numbers have now started to decrease.)

--


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





PostPosted: Sun Jun 05 18:08:33 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

> Microsoft has in my opinion a quite elegant take on this: you can run a
> database in compatibility mode for an earlier version. This permits
> MS to drop support or to make incompatible changes in order to improve
> things, but still makes it possible for people to migrate.

I'm not sure I'd use the phrase "quite elegant" as it is just a copy of
functionality available in all commercial RDBMS products.

For example Oracle, in its init.ora (now compilable as the spfile), has
a parameter called compatible that can be set to a wide range of
previous versions. That parameter has existed longer than SQL Server.
--
Daniel A. Morgan
http://www.psoug.org

(replace x with u to respond)
 
 
Jurgen





PostPosted: Mon Jun 06 02:49:23 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)


>> 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.
>

LOL

I'm using SQL in PERL, am I a bad person now?
I've used PL/SQL client side, and I found it to be tough, I was much
more busy with the language elements than creating fast, optimized SQL.
Perl is a really sloppy (not inefficient though) language, you don't
have to think (about the language) while coding, put some nice SQL in it
though.

-R-
 
 
Erland





PostPosted: Mon Jun 06 03:28:15 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)
> I'm not sure I'd use the phrase "quite elegant" as it is just a copy of
> functionality available in all commercial RDBMS products.
>
> For example Oracle, in its init.ora (now compilable as the spfile), has
> a parameter called compatible that can be set to a wide range of
> previous versions. That parameter has existed longer than SQL Server.

OK, I didn't know that. Thanks for the information.

--


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





PostPosted: Tue Jun 07 01:13:11 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)

>

>
>
>>> 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.
>>
>
> LOL
>
> I'm using SQL in PERL, am I a bad person now?
> I've used PL/SQL client side, and I found it to be tough, I was much
> more busy with the language elements than creating fast, optimized SQL.
> Perl is a really sloppy (not inefficient though) language, you don't
> have to think (about the language) while coding, put some nice SQL in it
> though.
>
> -R-

Post some of your complex SQL statements if you'd like to find out
what others think.

But if you read my comments closely you would have noted that much of
my concern was that these people would then blur the line into designing
schemas, creating tables, indexes, and likely few if any constraints.

And yes I am painting with an extremely broad brush. A few will write
good code: I think the vast majority will trash the environment in which
they are working.
--
Daniel A. Morgan
http://www.psoug.org

(replace x with u to respond)
 
 
ken_north





PostPosted: Wed Jun 08 16:08:42 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy)


> >
> >> 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.

This isn't a new concept or untested technology. Oracle and DB2 have
supported Java database plug-ins (e.g., UDFs, stored procedures) since
the late '90s (DB2 UDB ver 5.1 and Oracle 8.1.4).

> 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#.
> .. Do they write better SQL because they use PL/SQL?

If there was a pattern of systemic failures, database corruption or
security problems due to database extensions written in a programming
language, Oracle and IBM would know by now. There have been several
major releases since Java in the database emerged in 1997.

If allowing non-SQL database extensions were a serious product design
flaw, we'd see IBM, Oracle, Sybase and Microsoft taking a different
tack. They've embraced the Java VM and .NET CLR because they provide
better server stability than older technology, such as writing extended
stored procedures in C.

The potential for harm isn't so much a question of which tool to use as
who should be doing the work. It's obvious you should not use someone
to develop VB, C# or Java database extensions if he/she isn't normally
trusted with production database work, such as creating stored
procedures with PL/SQL or T-SQL.

The guideline I've expressed for eight years about Java in the database
applies also to the CLR issue:

"When it comes to developing SQL database extensions, it's better to
take a database person and teach him/her Java then to to assign the
task to someone who knows Java but lacks database expertise."

The same applies to working with the CLR for extending databases,
whether they're DB2, Oracle or Microsoft SQL Server. Don't use Java, VB
or C# skills as the primary basis for deciding who's writing database
extensions.



Ken North
======================
http://www.SQLSummit.com
http://www.GridSummit.com
http://www.WebServicesSummit.com

 
 
Tim





PostPosted: Fri Jun 10 15:59:05 CDT 2005 Top

SQL Server Developer >> Code in the database or middle tier (the CLR controversy) Not to mention that most developers do not seem to care in the slightest bit
about formatting their code, which is always unforunate for the poor soul who
has to come in behind them and make changes to it.



> 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.
>
>