Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Erland Sommarskog on 9 Nov 2009 07:29 Michael C (mike(a)nospam.com) writes: > SQL server is full of limitations that don't make sense. We've only > recently got the ability to do something as basic as define a variable > of infinite length string. When we got CLR integration we were given a > limit of 8K of memory to work with (E I G H T K!!!!!!). When we got > the ability to define our own aggregates we couldn't pass any parameters > into that aggregate. > > I think the cause for all this is backwards compatibility. Some limitations or funky behaviour is certainly due to backwards compatibility issue. But a more common reason is that even Microsoft has limited resources and at some point they have to ship. As for the limitation to 8K in the CLR, this restriction has been lifted in SQL 2008, so that you can define CLR UDTs and CLR aggregates that can produce more than 8000 bytes. > Hopefully soon we get a brand new product based on LINQ. That is very unlikely. After all, SQL is the standard for relational databases, not LINQ. And LINQ also buys you lots of limitations. For instance, apparently you cannot use table-valued parameters with LINQ. -- 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: klem s on 9 Nov 2009 12:56 Thank you all for your help
From: Michael C on 9 Nov 2009 22:52 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CBE89529BEFAYazorman(a)127.0.0.1... > Some limitations or funky behaviour is certainly due to backwards > compatibility issue. But a more common reason is that even Microsoft has > limited resources and at some point they have to ship. That's true, I think there is also a mindset in the sqlserver team. The dot net team seems to have a policy of giving users as much flexibility as possible where sqlserver team seems to restrict users. That's obviously not the only reason but it's got to be a contributing factor. > As for the limitation to 8K in the CLR, this restriction has been lifted > in SQL 2008, so that you can define CLR UDTs and CLR aggregates that can > produce more than 8000 bytes. That I know but to place an 8K restriction on this is a perfect of example of the sqlserver team mindset I was talking about. This sort of thing is unheard of in anything else modern I have used. >> Hopefully soon we get a brand new product based on LINQ. > > That is very unlikely. After all, SQL is the standard for relational > databases, not LINQ. So what you're saying is we can't sell cars because we are a motorcycle shop. :-) I think all the elements are in place for a LINQ database. Obviously we have linq, we have the .net framework as a language to replace store procs etc and we have an IDE in visual studio that could be modified to suit a linq DB. I think this could sit along side sqlserver as a seperate product. > And LINQ also buys you lots of limitations. For > instance, apparently you cannot use table-valued parameters with LINQ. I don't think that is true. The equivelant of a table in Linq is an IEnumerable and you can pass an IEnumerable parameter into and out of any LINQ query in all sorts of ways with far more flexibility than sql. For example, an IEnumerable can be a simple list of integers or it can be an object which is the eqivelant of a row. It can even be an object with a grouping of subobjects (eg customer with many invoices). The way a result set from a query in sql can only be a certainly number of rows and columns (no sub collections etc) seems very limiting. Michael
From: Erland Sommarskog on 11 Nov 2009 17:40 Michael C (mike(a)nospam.com) writes: > That's true, I think there is also a mindset in the sqlserver team. The > dot net team seems to have a policy of giving users as much flexibility > as possible where sqlserver team seems to restrict users. That's > obviously not the only reason but it's got to be a contributing factor. That must explain ahy in VB .Net can write: Using cn As SqlConnection = setup_connection(), _ cmd As SqlCommand = cn.CreateCommand() But I in C# has to write: using (SqlConnection cn = setup_connection()) { using (SqlCommand cmd = cn.CreateCommand()) { > That I know but to place an 8K restriction on this is a perfect of example > of the sqlserver team mindset I was talking about. This sort of thing is > unheard of in anything else modern I have used. So in any other product you work with there is a perfect functionality matrix? Everything works with everything else? >> That is very unlikely. After all, SQL is the standard for relational >> databases, not LINQ. > > So what you're saying is we can't sell cars because we are a motorcycle > shop. :-) No, I'm saying that selling cars with a joystick instead of a steering wheel is dead in the water. >> And LINQ also buys you lots of limitations. For >> instance, apparently you cannot use table-valued parameters with LINQ. > > I don't think that is true. The equivelant of a table in Linq is an > IEnumerable and you can pass an IEnumerable parameter into and out of any > LINQ query in all sorts of ways with far more flexibility than sql. For > example, an IEnumerable can be a simple list of integers or it can be an > object which is the eqivelant of a row. It can even be an object with a > grouping of subobjects (eg customer with many invoices). The way a result > set from a query in sql can only be a certainly number of rows and columns > (no sub collections etc) seems very limiting. OK, can you then provide a sample in a C# program using LINQ that calls a stored procedure with a table-valued parameter? Or write in LINQ a query that in Northwind database (or AdventureWorks) retrieves this information from the first order of each customer: CustomerID, CustomerName, order date, number of articles, and the total order order amount. -- 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 19 Nov 2009 23:46
"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CC0F0D571492Yazorman(a)127.0.0.1... > That must explain ahy in VB .Net can write: > > Using cn As SqlConnection = setup_connection(), _ > cmd As SqlCommand = cn.CreateCommand() > > But I in C# has to write: > > using (SqlConnection cn = setup_connection()) { > using (SqlCommand cmd = cn.CreateCommand()) { That's is about as minor an issue as you can get. Doing what you've described would be handy in C# but it doesn't stop you doing something. While you picked on something completely trivial you've missed all the massive amounts of extensibility they give you in c#. As an example, I can write my own connection object which connects to "Mikes Propriotry File(c)" and do anything I like with it. In sqlserver if you've got an object, eg a cursor, then you're pretty much stuck with what they give you. What extensibility they give you is usually extremely restrictive. > So in any other product you work with there is a perfect functionality > matrix? Everything works with everything else? I never said there was. What I meant is that I have never worked with any product (except sqlserver of course) that does something as silly as give the programmer only 8k to work with. I don't think you understand what a HUGE step back into the 80s that that is. This stands out as pretty much the silliest restriction I have ever encountered in my time as a programmer. > No, I'm saying that selling cars with a joystick instead of a steering > wheel is dead in the water. There was a time when people thought selling cars with headlights was a bad idea. Maybe we'll never sell cars with a joystick but I don't think that linq is comparable to a joystick. LINQ has really taken off and could easily become a real competitor to sql. You've got to take into account that you can pretty much write standard sql but back to front (select statement last) but have the huge advantage of writing the less friendly but more functional version eg: From i in SomeCollect Where i.STATE = 'VIC' select i.Postcode or SomeCollection.Where(i => i.State == Vic).Select(i => i.POstcode) > OK, can you then provide a sample in a C# program using LINQ that calls > a stored procedure with a table-valued parameter? But that's not linq, that's calling a stored proc in sqlserver. In a pure linq environment you just pass in an IEnumerable instance. > Or write in LINQ a query that in Northwind database (or AdventureWorks) > retrieves this information from the first order of each customer: > CustomerID, CustomerName, order date, number of articles, and the total > order order amount. I think I see what you're friend was talking about, you can't use a table variable when working with linq that is used as a wrapper for sqlserver? Linq to sql I think they call it. This is just crappy auto generated code, the limitation is in what generates the code, not linq itself. IMO, linq to sql isn't going to go anywhere. If you're going to ignore linq-to-sql and write code yourself to call a stored proc then surely you can pass in a table variable. I haven't used sql2008 so don't know about this feature. If this is a limitation then the limitation is in ado.net, not linq itself. If ado.net can pass a table variable to sqlserver then linq can. Michael |