Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael Coles on 26 Nov 2009 20:30 "Michael C" <mike(a)nospam.com> wrote in message news:OLQSu3ubKHA.1596(a)TK2MSFTNGP06.phx.gbl... > "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 :-))) I give SQL Server a lot of credit in that regard -- various older features are deprecated with every release, and newer more powerful features are built in to replace them. The deprecation process in SQL Server gives customers the ability to phase out old code and learn the newer features, as opposed to the wholesale replacement you mentioned in regards to VB6 and ..NET. >> 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. SQL Server has always had procedural statements--many think of them as tools of "last resort". They do have a place, but are best avoided in most cases because they tend to override SQL Server's automatic optimization processes. LINQ is not currently in a position to replace SQL, and from the ADO.NET team's postings it sounds as if EF is preferred over LINQ to SQL for relational database access scenarios (http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx). So I don't know whether huge investments in bringing LINQ to SQL up to the same level as EF features (or future EF features) for OR/M solutions is in the cards or not. I doubt that designing an entirely new enterprise database engine just for LINQ to SQL (to avoid the SQL part) is probably not in the cards, but you can definitely suggest it over at Connect :) You might have a slightly better chance of convincing MS to create an Enterprise OODBMS optimized for EF as opposed to LINQ, although convincing them of the need to split their enterprise DBMS resources in half to build two competing enterprise DBMS's might be a tough sell... -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ----------------
From: Michael Coles on 26 Nov 2009 21:00 "Michael C" <mike(a)nospam.com> wrote in message news:%23$umv5ubKHA.5656(a)TK2MSFTNGP04.phx.gbl... >> 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. The SQL Server optimizer is the result of 2 decades of R&D, production implementations and response to customer feedback. To completely start creating a new transactional database engine from scratch based on the OO model (as opposed to relational), complete with an "industrial-strength" optimizer would require huge investments of time, money and resources. It would also require huge investments in marketing, trying to convince people they need this new DBMS while not annihilating the client base for your SQL DBMS. I'm sure it could be done, but is it worth it to anyone? Who knows... >> So prove it by giving a program that demonstrates how to do 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. You spoke previously of your dislike of backwards-compatibility features in SQL Server, etc. But it is precisely because people don't immediately upgrade to the newest versions and convert all their existing code to use all of the latest and greatest features that these backwards-compatibility features even exist... -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ----------------
From: Michael C on 26 Nov 2009 21:26 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:FE606D14-28E2-47D9-AF11- > I give SQL Server a lot of credit in that regard -- various older features > are deprecated with every release, and newer more powerful features are > built in to replace them. The deprecation process in SQL Server gives > customers the ability to phase out old code and learn the newer features, > as opposed to the wholesale replacement you mentioned in regards to VB6 > and .NET. I'm not saying that the approach they use with sqlserver is wrong, just that either approach (compatibility vs whole new product) has it's advantages and disadvantages. I think the vb6 to .net switch has a lot of similarity to what I'm suggesting with a move from sql to a linq database. Certainly they upset a lot of people abandoning vb6 but then again they made a product that was far far ahead of vb6. Obviously they can't abandon sqlserver. > SQL Server has always had procedural statements--many think of them as > tools of "last resort". They do have a place, but are best avoided in > most cases because they tend to override SQL Server's automatic > optimization processes. That depends how you use them. If you look at any stored proc written by MS they all have a bunch of If statements at the start verifying the parameters passed in. This hardly causes an slow down and could actually be considered a performance improvement because the query does not need to be executed if the parameters and invalid. > LINQ is not currently in a position to replace SQL, and from the ADO.NET > team's postings it sounds as if EF is preferred over LINQ to SQL for > relational database access scenarios > (http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx). > So I don't know whether huge investments in bringing LINQ to SQL up to the > same level as EF features (or future EF features) for OR/M solutions is in > the cards or not. I doubt that designing an entirely new enterprise > database engine just for LINQ to SQL (to avoid the SQL part) is probably > not in the cards, but you can definitely suggest it over at Connect :) > You might have a slightly better chance of convincing MS to create an > Enterprise OODBMS optimized for EF as opposed to LINQ, although convincing > them of the need to split their enterprise DBMS resources in half to build > two competing enterprise DBMS's might be a tough sell... Quite possibly, doesn't mean it's not a good idea though. The thinking in the sqlserver camp seems to be more towards compatibility than advancement. This is probably good if you're a bank but not so good for me. In 2009 I still can't inherit from a person table to create staff and customer tables. Michael
From: Michael C on 26 Nov 2009 21:33 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:FCC6E2B4-2B6A-45C8-BDF5- > The SQL Server optimizer is the result of 2 decades of R&D, production > implementations and response to customer feedback. Do you really think this declarative approach works that well? I still find that I tell that database what I want to do and a lot of the time it does it the wrong way and I have to go back in and tell it how to do it anyway. To completely start > creating a new transactional database engine from scratch based on the OO > model (as opposed to relational), complete with an "industrial-strength" > optimizer would require huge investments of time, money and resources. It > would also require huge investments in marketing, trying to convince > people they need this new DBMS while not annihilating the client base for > your SQL DBMS. I'm sure it could be done, but is it worth it to anyone? > Who knows... Nothing is impossible. No one was screaming for .net but it's been a big success. > You spoke previously of your dislike of backwards-compatibility features > in SQL Server, etc. But it is precisely because people don't immediately > upgrade to the newest versions and convert all their existing code to use > all of the latest and greatest features that these backwards-compatibility > features even exist... That's true but I am only 1 version behind. Besides, I'm not against backwards compatibility, I just think that after a certain point in time it becomes necessary to make a complete break from the past sins. We're still in the very early stages of computing in 2009, do we really want sql2050 to still only be able to return a square block of data from a stored proc? Michael
From: Michael Coles on 26 Nov 2009 22:09
"Michael C" <mike(a)nospam.com> wrote in message news:ew$i7jwbKHA.2572(a)TK2MSFTNGP05.phx.gbl... >> SQL Server has always had procedural statements--many think of them as >> tools of "last resort". They do have a place, but are best avoided in >> most cases because they tend to override SQL Server's automatic >> optimization processes. > > That depends how you use them. If you look at any stored proc written by > MS they all have a bunch of If statements at the start verifying the > parameters passed in. This hardly causes an slow down and could actually > be considered a performance improvement because the query does not need to > be executed if the parameters and invalid. One-off administrative tasks are the bulk of the procedures you're talking about (or possibly you're talking about the IF EXISTS ... code that SSMS adds to build scripts). The recommended use for procedural statements is in fact cases where you have code that deals in very small data sets and performs administrative tasks, and one-offs like checking for existence of objects before executing DDL statements. Using IF statements in production stored procs that perform DML will affect performance; the extent of the effect is dependent on several factors. >> LINQ is not currently in a position to replace SQL, and from the ADO.NET >> team's postings it sounds as if EF is preferred over LINQ to SQL for >> relational database access scenarios >> (http://blogs.msdn.com/adonet/archive/2008/10/29/update-on-linq-to-sql-and-linq-to-entities-roadmap.aspx). >> So I don't know whether huge investments in bringing LINQ to SQL up to >> the same level as EF features (or future EF features) for OR/M solutions >> is in the cards or not. I doubt that designing an entirely new >> enterprise database engine just for LINQ to SQL (to avoid the SQL part) >> is probably not in the cards, but you can definitely suggest it over at >> Connect :) You might have a slightly better chance of convincing MS to >> create an Enterprise OODBMS optimized for EF as opposed to LINQ, although >> convincing them of the need to split their enterprise DBMS resources in >> half to build two competing enterprise DBMS's might be a tough sell... > > Quite possibly, doesn't mean it's not a good idea though. The thinking in > the sqlserver camp seems to be more towards compatibility than > advancement. This is probably good if you're a bank but not so good for > me. In 2009 I still can't inherit from a person table to create staff and > customer tables. You can easily design your tables to simulate OO inheritance if you so desire, so I don't really see this as being an issue. What you just stated is actually really, really easy to do if you so desire. If you look at the SQL Server 2005/2008 Catalog views you will see that they expose data using this same inheritance design pattern. I might be missing your point on this as well... Getting a chance to refactor your code to take advantage of new features while not instantaneously breaking all of your existing code is not necessarily a bad thing. Not sure who you think in the "SQL Server camp" doesn't want advancement--there were lots of advances made in SQL 2008 covering a wide range of functionality; from B.I. to DB Admin and Security to SQL Development. 2008 R2 adds more advances, and I suspect there are plenty of additional advances in the works for the next version or two as well, so I suppose I'm missing your point on this... Of course as I understand from your previous posts you haven't actually installed SQL 2008 yet, so you may not have even used any of the existing advancements at this point. Is that an accurate assessment? -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- |