Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael C on 25 Nov 2009 00:41 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:%23hvu% > I think there are some advantages that could be realized by adding > additional parameters to SQL CLR custom aggregates, and from what I > understand that is on the radar for future versions of SQL Server. In the > meantime you can work around the issue by using SQL CLR UDTs and passing > them to the aggregate. Interesting, I will give that a go. Thanks. > Adding the ability to pass multiple parameters to a SQL CLR UDA seems like > quite a distance from completely rewriting the entire DBMS engine to > support a new procedural front-end language. With VB6 you could do everything that was needed within reason. It could have been argued we could have had VB7 instead of a full rewrite. But by doing a full rewrite they came up with something a whole level above. We got features that most users never even dreamed about or thought of requested (how many people asked for linq? Or WPF?). The same could be done with sqlserver. And it's not just a matter of rewriting the whole thing to fix custom aggregates, it would be fixing so many issues and making so many great new features. BTW, Why do you say C# is procedural as opposed to what we have in sqlserver? When you take into account linq, don't we have something that is fairly similar? SQL has loops and if statement, variable declaration etc just like C# and the real sql statements are equivelant to linq. Just to go off on a tangent ..... the one thing I really like about C# is that it is such a departure from anything I've used before in that everything is designed from the ground up by industry experts using best practices. Every other product I have used you can tell it was designed in a more hap hazard way with at least some decisions made by possibly one person. For example, at some point in the history of sqlserver someone said that we need a way to list the tables in a database using sql. So someone created the sysobjects table. But that wasn't quite right so they reinvented it with INFORMATION_SCHEMA objects but that was missing a whole lot of information so they added other stored procs to get other details about a table. I'm sure there are other alternate ways to do the same thing which are all inconsistant with each other. It would be good to bring that same best practices into sqlserver (or an alternate product). > Again, if you really want a DBMS with procedural C# as it's native > language the relational model is probably not your best bet. If you want > an OODBMS with a procedural interface there are actually choices out > there. I believe IBM implements such a DBMS in their Notes line I really should get off my butt and investigate one of these solutions. I'd be interested to see how it is done and why OOP DBs are less popular. > Oh I'm not selling you on CLR. It certainly has areas in which it can be > used to enhance and extend SQL Server's functionality; however, I'm just > pointing out to you that you have the option to talk directly to SQL > Server in C# on the server. That seemed to be your main point, so I'm > simply pointing out that hey, there it is! Fair enough. :-) > Well, for one thing VB6 was comparatively cheap back in the day--I > remember picking up a copy of VB6 Pro at a trade show for about $50 way > back in the day. SQL Server is significantly more expensive. Throw in > licensing costs, etc., and you are now implementing an enterprise solution > to run the business on. That's true. Maybe big business never took VB6 too seriously and it was only small to medium size workshops that used it. > The main issue that VB6ers had was primarily with support, particularly > support for the COM-based technologies developed with it/for it back in > the day. But that's another discussion for another day. In the > meanwhile, what product do you want to sit alongside SQL Server? I doubt > MS is willing to split their development resources down the middle to > create an entirely new enterprise-class DBMS just so they can bypass SQL > and use C# as the native language. I mean, have you seen the economy > lately? From what I understand the SQL Server team is focusing on > improving SQL Server--I could be wrong, but I doubt there's a lot of > interest in cutting the budget in half to create a competing enterprise > DBMS. :) Who says the people need to come from the SQL Server team. Possibly it could be a new project, or maybe it could be considered a .net project and people could come from the .net team. > > -- > Thanks > > Michael Coles > SQL Server MVP > Author, "Expert SQL Server 2008 Encryption" > (http://www.apress.com/book/view/1430224649) > ---------------- >
From: Michael Coles on 25 Nov 2009 01:42 VB6 actually fell short in several areas, not the least of which was the reliance on the ever-sprawling Ruby engine. Talk about a ton of old features maintained for backwards-compatibility! And VB6 also didn't support true OO programming. The sys* tables were a vendor extension, like all other SQL DBMS platforms have to some degree or another. IIRC the sys* tables were actually implemented before the INFORMATION_SCHEMA tables were added to the SQL standard. When INFORMATION_SCHEMA was added to the standard, it was added to SQL DBMS implementations as well to maintain standards compliance. Of course INFORMATION_SCHEMA does not account for the vendor extensions to SQL, so vendors have to expose that information to admins and developers in other ways. In SQL 2005+ the method of choice is catalog views. We could discuss the SQL standard and "compliance" with the standard, but if you Google Celko you'll find plenty on that topic. > BTW, Why do you say C# is procedural as opposed to what we have in > sqlserver? When you take into account linq, don't we have something that > is fairly similar? SQL has loops and if statement, variable declaration > etc just like C# and the real sql statements are equivelant to linq. C# is a procedural language, SQL is declarative. C# does have a touch of LINQ, but C# is not LINQ. SQL has a few procedural extensions, but SQL is not procedural. They're basically at opposite ends of the spectrum. If you were writing your SQL code like you would write C# programs, I can almost guarantee that your SQL Server is working much harder than it needs to and your code is much less efficient than it could be. This is why I suggest that a SQL DBMS might not the best DBMS to use if you want to use a procedural language as its native language. LINQ provides several similar constructs to SQL (which are actually converted to SQL when you use LINQ to SQL). Microsoft has a website for enhancement requests like the ones you're suggesting - http://connect.microsoft.com. :) -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "Michael C" <mike(a)nospam.com> wrote in message news:eSJSvHZbKHA.5656(a)TK2MSFTNGP04.phx.gbl... > "Michael Coles" <admin(a)geocodenet.com> wrote in message news:%23hvu% >> I think there are some advantages that could be realized by adding >> additional parameters to SQL CLR custom aggregates, and from what I >> understand that is on the radar for future versions of SQL Server. In >> the meantime you can work around the issue by using SQL CLR UDTs and >> passing them to the aggregate. > > Interesting, I will give that a go. Thanks. > >> Adding the ability to pass multiple parameters to a SQL CLR UDA seems >> like quite a distance from completely rewriting the entire DBMS engine to >> support a new procedural front-end language. > > With VB6 you could do everything that was needed within reason. It could > have been argued we could have had VB7 instead of a full rewrite. But by > doing a full rewrite they came up with something a whole level above. We > got features that most users never even dreamed about or thought of > requested (how many people asked for linq? Or WPF?). The same could be > done with sqlserver. And it's not just a matter of rewriting the whole > thing to fix custom aggregates, it would be fixing so many issues and > making so many great new features. > > BTW, Why do you say C# is procedural as opposed to what we have in > sqlserver? When you take into account linq, don't we have something that > is fairly similar? SQL has loops and if statement, variable declaration > etc just like C# and the real sql statements are equivelant to linq. > > Just to go off on a tangent ..... the one thing I really like about C# is > that it is such a departure from anything I've used before in that > everything is designed from the ground up by industry experts using best > practices. Every other product I have used you can tell it was designed in > a more hap hazard way with at least some decisions made by possibly one > person. For example, at some point in the history of sqlserver someone > said that we need a way to list the tables in a database using sql. So > someone created the sysobjects table. But that wasn't quite right so they > reinvented it with INFORMATION_SCHEMA objects but that was missing a whole > lot of information so they added other stored procs to get other details > about a table. I'm sure there are other alternate ways to do the same > thing which are all inconsistant with each other. It would be good to > bring that same best practices into sqlserver (or an alternate product). > >> Again, if you really want a DBMS with procedural C# as it's native >> language the relational model is probably not your best bet. If you want >> an OODBMS with a procedural interface there are actually choices out >> there. I believe IBM implements such a DBMS in their Notes line > > I really should get off my butt and investigate one of these solutions. > I'd be interested to see how it is done and why OOP DBs are less popular. > >> Oh I'm not selling you on CLR. It certainly has areas in which it can be >> used to enhance and extend SQL Server's functionality; however, I'm just >> pointing out to you that you have the option to talk directly to SQL >> Server in C# on the server. That seemed to be your main point, so I'm >> simply pointing out that hey, there it is! > > Fair enough. :-) > >> Well, for one thing VB6 was comparatively cheap back in the day--I >> remember picking up a copy of VB6 Pro at a trade show for about $50 way >> back in the day. SQL Server is significantly more expensive. Throw in >> licensing costs, etc., and you are now implementing an enterprise >> solution to run the business on. > > That's true. Maybe big business never took VB6 too seriously and it was > only small to medium size workshops that used it. > >> The main issue that VB6ers had was primarily with support, particularly >> support for the COM-based technologies developed with it/for it back in >> the day. But that's another discussion for another day. In the >> meanwhile, what product do you want to sit alongside SQL Server? I doubt >> MS is willing to split their development resources down the middle to >> create an entirely new enterprise-class DBMS just so they can bypass SQL >> and use C# as the native language. I mean, have you seen the economy >> lately? From what I understand the SQL Server team is focusing on >> improving SQL Server--I could be wrong, but I doubt there's a lot of >> interest in cutting the budget in half to create a competing enterprise >> DBMS. :) > > Who says the people need to come from the SQL Server team. Possibly it > could be a new project, or maybe it could be considered a .net project and > people could come from the .net team. > >> >> -- >> Thanks >> >> Michael Coles >> SQL Server MVP >> Author, "Expert SQL Server 2008 Encryption" >> (http://www.apress.com/book/view/1430224649) >> ---------------- >> > >
From: Erland Sommarskog on 25 Nov 2009 03:46 Michael C (mike(a)nospam.com) writes: >> Again, you can of course write classes etc. But C# is just a language. >> SQL Server is far more than just a language. > > Aren't all the elements there is C# to be the front end for a database? > Triggers would be events, stored procs would be functions, views would be > linq statements, tables would be collections etc. Imagine writing your > application in C# and stepping through code and straight into a C# stored > proc (insert sound of Homer Simpson drooling here :-) Again: a relational database is just so much more than the language elements. You need memory management. You need process management. You need storage management. You need a cost-based optimizer. Keep in mind that C# is a procedural language. SQL is declarative. What you write in C# not only instructs the computer what to do, but also how. In SQL you only say what. This means that when you use LINQ, you essentially tell the compiler what SQL to generate. > I just said "There is absolutely no reason sqlserver would have an 8K > limit today, except for hangovers from the past." and you replied that > the reason it's 8K is because that's the way it is and changing it would > be a major change. Didn't you just say exactly what I said in different > words? :-) Whatever, there will be a limit in page size. You don't handle 8K in the same way you handle 8 MB or 8 GB. > Ok, fair point. But C# 1.0 was a brand new language and the restriction > wasn't really that silly. They could have held out to put it in but > decided to release without it. On the other hand sqlserver is 20+ years > old and shouldn't have 8k restrictions on anything. So what? I learnt Simula in 1983, and I learnt to use generics. And C# was not a brand-new language, it was constructed from existing languages. It's really silly not to put basic things in from day one. No, I don't really mean that, but as you say they had to ship, and there you have the answer why there are limitations in SQL Server as well. >> ADO .Net can do it. LINQ can't. > > If ADO.Net can do it then linq can do, you would just cast whatever > collection you have into whatever ADO.net requires. So prove it by giving a program that demonstrates how to do it. >> Well, since I'm SQL person and think TVPs are a great feature, I think >> that is quite a limitation. > > It's not a limitation. It can be done very easily with the most minor of > code. How? If you are so dead sure that it's possible, then prove it by doing it. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Michael C on 26 Nov 2009 18:12 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:Okx4EqZbKHA.5976(a)TK2MSFTNGP05.phx.gbl... > VB6 actually fell short in several areas, not the least of which was the > reliance on the ever-sprawling Ruby engine. Talk about a ton of old > features maintained for backwards-compatibility! And VB6 also didn't > support true OO programming. Sounds a lot like sqlserver :-))) > C# is a procedural language, SQL is declarative. C# does have a touch of > LINQ, but C# is not LINQ. SQL has a few procedural extensions, but SQL is > not procedural. They're basically at opposite ends of the spectrum. If > you were writing your SQL code like you would write C# programs, I can > almost guarantee that your SQL Server is working much harder than it needs > to and your code is much less efficient than it could be. Don't worry, although I'm no expert I know how to write fairly efficient sql. > This is why I suggest that a SQL DBMS might not the best DBMS to use if > you want to use a procedural language as its native language. LINQ > provides several similar constructs to SQL (which are actually converted > to SQL when you use LINQ to SQL). Microsoft has a website for enhancement > requests like the ones you're suggesting - http://connect.microsoft.com. > :) Makes sense but it is certainly a gray area now we have linq and sqlserver has procedural commands. Michael
From: Michael C on 26 Nov 2009 18:16
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CCE637FCD26FYazorman(a)127.0.0.1... > Again: a relational database is just so much more than the language > elements. You need memory management. You need process management. You > need storage management. You need a cost-based optimizer. Again, yes I know. That is why someone would need to write a linq database. > Keep in mind that C# is a procedural language. SQL is declarative. What > you > write in C# not only instructs the computer what to do, but also how. In > SQL you only say what. This means that when you use LINQ, you essentially > tell the compiler what SQL to generate. Sounds like a gray area to me. Certainly linq could be optimised by an optimiser in the same way sql is. > So prove it by giving a program that demonstrates how to do it. > >>> Well, since I'm SQL person and think TVPs are a great feature, I think >>> that is quite a limitation. >> >> It's not a limitation. It can be done very easily with the most minor of >> code. > > How? If you are so dead sure that it's possible, then prove it by doing > it. I have provided a code sample (check back through the posts, did you miss it?) but as I said I am not going to install sql2008 just to write some sample code. Michael |