Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael C on 23 Nov 2009 21:16 "Michael Coles" <michaelcoAToptonlineDOTnet> wrote in message news:% > I didn't realize there was such an animal as a "linq database". Currently there is not but the main point I am trying to make is that MS should create a native linq database. > LINQ generally provides a common interface to query a variety of different > datasources--SQL databases, Objects, websites, or anything else that you > care to write a provider for. LINQ's strength is its ability to expose > disparate data sources via a common query interface, so I'm not sure about > a couple of things: 1) what would a "linq database" even look like, and A linq database would be similar to sqlserver except you would write in C# code instead of stored procs and linq instead of sql. There would be a collection of tables and each table would have a collection of rows. 2) how > it would be "better", given LINQ's primary purpose. It would be more extendable, giving you the ability to write your own sql keywords. Linq would make it more flexible and it would be more consistant. There would be less of the restrictions that are common in sql. It would be more modern as it wouldn't need to be compatible with sql server from 1956. > As Erland pointed out, different languages have different strengths and > weaknesses. SQL's strength is it's ability to efficiently store and > retrieve data in sets and to abstract away a lot of the details so you can > focus on your end results instead of focusing on the steps needed to get > there. C#'s strengths lie in other areas that are more procedural, such as > bit manipulation, looping, string manipulation, etc. But now we have linq C# potentially has all the strengths of sql, with not so many of it's weaknesses. > While it is entirely possible to write your own data providers for C# and > create your own databases using C++ and generate your own query engine > using VB, you could potentially run into a couple of issues. You've missed my point there. I'm not suggesting writing my own database. I'm just giving examples of how C# is extensible. I might not want to write my own database but I might want to add an extra function to the cursor object. In C# I could inherit from the cursor object and add my own functions. This would be a few minutes work potentially and add some very useful functions to the cursor object. My point is that these sort of things are possible in C# while in sqlserver there is much less facility for these sort of things. When they do give extensibility it's usually very difficult to use and restrictive. Here's another example. In SQL Server if we want to include some C++ code we have created we can create an extended stored proc. This works but is pretty painful and very restricted. In C# we have a whole array of methods available for adding C++ code.
From: Michael Coles on 24 Nov 2009 10:02 You do realize that extended stored procs (unmanaged C++ code) are deprecated and will be going away at some point? They have been replaced by SQL CLR, which supports .NET code written in C#, with which you can roll your own custom "cursor-like" objects all day long. I don't quite understand your infatuation with inheriting from and extending cursors to create your own custom cursors -- cursors tend to be one of the worst possible way to get things done in SQL. What functionality would you like to add to cursors anyway? BTW, your dates seem to be a teeny weeny little bit off. The direct descendants of SQL weren't implemented until the 1970s, and SQL did not become a standard until 1986. Dr. Codd didn't even publish his seminal paper on the relational model until 1970. Perhaps you're confusing SQL (circa 1986) with COBOL (circa 1959)? "Michael C" <mike(a)nospam.com> wrote in message news:efZ$jwKbKHA.2188(a)TK2MSFTNGP04.phx.gbl... > "Michael Coles" <michaelcoAToptonlineDOTnet> wrote in message news:% >> I didn't realize there was such an animal as a "linq database". > > Currently there is not but the main point I am trying to make is that MS > should create a native linq database. > >> LINQ generally provides a common interface to query a variety of >> different datasources--SQL databases, Objects, websites, or anything else >> that you care to write a provider for. LINQ's strength is its ability to >> expose disparate data sources via a common query interface, so I'm not >> sure about a couple of things: 1) what would a "linq database" even look >> like, and > > A linq database would be similar to sqlserver except you would write in C# > code instead of stored procs and linq instead of sql. There would be a > collection of tables and each table would have a collection of rows. > > 2) how >> it would be "better", given LINQ's primary purpose. > > It would be more extendable, giving you the ability to write your own sql > keywords. Linq would make it more flexible and it would be more > consistant. There would be less of the restrictions that are common in > sql. It would be more modern as it wouldn't need to be compatible with sql > server from 1956. > >> As Erland pointed out, different languages have different strengths and >> weaknesses. SQL's strength is it's ability to efficiently store and >> retrieve data in sets and to abstract away a lot of the details so you >> can focus on your end results instead of focusing on the steps needed to >> get there. C#'s strengths lie in other areas that are more procedural, >> such as bit manipulation, looping, string manipulation, etc. > > But now we have linq C# potentially has all the strengths of sql, with not > so many of it's weaknesses. > >> While it is entirely possible to write your own data providers for C# and >> create your own databases using C++ and generate your own query engine >> using VB, you could potentially run into a couple of issues. > > You've missed my point there. I'm not suggesting writing my own database. > I'm just giving examples of how C# is extensible. I might not want to > write my own database but I might want to add an extra function to the > cursor object. In C# I could inherit from the cursor object and add my own > functions. This would be a few minutes work potentially and add some very > useful functions to the cursor object. My point is that these sort of > things are possible in C# while in sqlserver there is much less facility > for these sort of things. When they do give extensibility it's usually > very difficult to use and restrictive. > > Here's another example. In SQL Server if we want to include some C++ code > we have created we can create an extended stored proc. This works but is > pretty painful and very restricted. In C# we have a whole array of methods > available for adding C++ code. >
From: Erland Sommarskog on 24 Nov 2009 16:27 Michael C (mike(a)nospam.com) writes: > C# with linq would be more than capable of being the language for a > database (not sure of the technical term here). It is certainly not a > boat out of water. Today it is. Of course, you could implement a number of classes, and also add language constructs for the querying part. Well, it is already there. But you would also need the memory management, the storage management, the process management etc that are core parts of a database engine. SQL Server is *lot* more than just T-SQL. > That's just not true. C# can easily handle more data than there is > memory available. I have a C# program that handles 80GB files. SQLServer > is simply C code, there is no reason C# can't replicate anything > sqlserver does. Besides, I'm not suggesting this "Linq database" be > actually written in C#, just that C# is the front end for it. Again, you can of course write classes etc. But C# is just a language. SQL Server is far more than just a language. Can you put all those 80 GB in one single string variable? Or even 2GB, even if machine only has a virtual address space of 1GB? > There is absolutely no reason sqlserver would have an 8K limit today, > except for hangovers from the past. Maybe if we go past 8K we get a > performance hit but that would be fine. And how do you know? The page size in SQL Server is 8192. It could be made larger or variable, but that would be a major change, particularly the latter. And the page size sets the limit between small objects and big objects. >> If you really think the limitation of 8000 chars gets in your way, >> you can always declare you string variables and string columns as >> (n)varchar(MAX), but I advice against it. I've found that there is a >> performance penalty, even if the actual length is below 8000. > > It's not varchar that's the problem for me, it's calling CLR code for a > custom aggregate that is the issue. Eh, that limit has been lifted in SQL 2008. If you want to play that game I can add complaints on that C# 1.0 did not have generics. After all, that is a quite essential feature in an object-oriented language. Quite a silly restriction. I mean, if you want to play that game. > But my point was this is not a limitation of linq. If it is not possible > then this is because ado.net does not allow it. If ado.net allows it then > linq can do it. ADO .Net can do it. LINQ can't. > I don't have sql2008 here and it would take me several hours to get it > installed and write a sample so I cannot do this. Even if this is a > limitation of linq then it's really just a limitation interacting with > sql. Well, since I'm SQL person and think TVPs are a great feature, I think that is quite a limitation. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Michael C on 24 Nov 2009 18:32 "Michael Coles" <michaelcoAToptonlineDOTnet> wrote in message news:erbBpcRbKHA.5156(a)TK2MSFTNGP05.phx.gbl... > You do realize that extended stored procs (unmanaged C++ code) are > deprecated and will be going away at some point? They have been replaced > by SQL CLR, which supports .NET code written in C#, with which you can > roll your own custom "cursor-like" objects all day long. I don't quite > understand your infatuation with inheriting from and extending cursors to > create your own custom cursors -- cursors tend to be one of the worst > possible way to get things done in SQL. What functionality would you like > to add to cursors anyway? Mike, it's just AN EXAMPLE :-))) > BTW, your dates seem to be a teeny weeny little bit off. The direct > descendants of SQL weren't implemented until the 1970s, and SQL did not > become a standard until 1986. Dr. Codd didn't even publish his seminal > paper on the relational model until 1970. Perhaps you're confusing SQL > (circa 1986) with COBOL (circa 1959)? I was just joking of course :-) My point was sqlserver is a very backwards compatible product. This has advantages if you need backwards compatbility but if you don't it's just one HUGE disadvantage, holding large amounts of the product back in the past. Michael
From: Michael Coles on 24 Nov 2009 18:41
"Michael C" <mike(a)nospam.com> wrote in message news:uwDf25VbKHA.808(a)TK2MSFTNGP02.phx.gbl... >> What functionality would you like to add to cursors anyway? > > Mike, it's just AN EXAMPLE :-))) That's OK; do you have a more useful example? Like I said, you have SQL CLR where you can code with C# to interact with the database to your heart's content. > I was just joking of course :-) My point was sqlserver is a very backwards > compatible product. This has advantages if you need backwards compatbility > but if you don't it's just one HUGE disadvantage, holding large amounts of > the product back in the past. Hard to tell on here sometimes. Backwards compatibility is seen by some as a positive, especially companies that have invested hundreds of thousands, or millions, of dollars in developing for a specific platform. -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- > "Michael Coles" <michaelcoAToptonlineDOTnet> wrote in message > news:erbBpcRbKHA.5156(a)TK2MSFTNGP05.phx.gbl... >> You do realize that extended stored procs (unmanaged C++ code) are >> deprecated and will be going away at some point? They have been replaced >> by SQL CLR, which supports .NET code written in C#, with which you can >> roll your own custom "cursor-like" objects all day long. I don't quite >> understand your infatuation with inheriting from and extending cursors to >> create your own custom cursors -- cursors tend to be one of the worst >> possible way to get things done in SQL. > >> BTW, your dates seem to be a teeny weeny little bit off. The direct >> descendants of SQL weren't implemented until the 1970s, and SQL did not >> become a standard until 1986. Dr. Codd didn't even publish his seminal >> paper on the relational model until 1970. Perhaps you're confusing SQL >> (circa 1986) with COBOL (circa 1959)? > > I was just joking of course :-) My point was sqlserver is a very backwards > compatible product. This has advantages if you need backwards compatbility > but if you don't it's just one HUGE disadvantage, holding large amounts of > the product back in the past. > > Michael > |