Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael C on 29 Nov 2009 18:16 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CD0613B93454Yazorman(a)127.0.0.1... > Yes, it's true that the declarative model is not perfect, but we sometimes > have to help the optimizer quite a bit. Sometimes? We have to tell sqlserver how to do stuff in almost everything we do in sqlserver. A beginner will write inefficient tables, views, indexes and queries because they don't know how to tell sqlserver how to do things. > But still SQL is a lot better that you would get if you would with a C# > program. That's certainly true, although I would suspect it would run this example in less than 30 seconds (assuming the data was in a text file). You can actually have indexes in linq, you just need to put your data into a hashtable or some other kind of lookup. > Well, in this particular example the correct plan is trivial, but > for more complex plans it isn't, and this is an area where SQL beats C#. That's true. Michael
From: Michael C on 29 Nov 2009 18:23 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CD0F23F262CFYazorman(a)127.0.0.1... > Rather than using an UDA, you can use FOR XML PATH for this. Here is > an example: > > select CustomerID, > substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1) > -- strip the last ',' from the list > from > Customers c cross apply > (select convert(nvarchar(30), OrderID) + ',' as [text()] > from Orders o > where o.CustomerID = c.CustomerID > order by o.OrderID > for xml path('')) as Dummy(OrdIdList) > go > > It's not really what you call intuitive, but it's very useful. Thanks but I had a look at all the various options as there was a website I found that listed about 10 or so different methods. I found the clr method to be the simplest and easiest to use. I was after something that was reusable and I got the bonus of speed with dot net. If someone needs a new aggregate to concatenate strings then I just add a new one. Michael
From: Erland Sommarskog on 30 Nov 2009 08:24 Michael C (mike(a)nospam.com) writes: > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message > news:Xns9CD0F23F262CFYazorman(a)127.0.0.1... >> Rather than using an UDA, you can use FOR XML PATH for this. Here is >> an example: >> >> select CustomerID, >> substring(OrdIdList, 1, datalength(OrdIdList)/2 - 1) >> -- strip the last ',' from the list >> from >> Customers c cross apply >> (select convert(nvarchar(30), OrderID) + ',' as [text()] >> from Orders o >> where o.CustomerID = c.CustomerID >> order by o.OrderID >> for xml path('')) as Dummy(OrdIdList) >> go >> >> It's not really what you call intuitive, but it's very useful. > > Thanks but I had a look at all the various options as there was a > website I found that listed about 10 or so different methods. I found > the clr method to be the simplest and easiest to use. I was after > something that was reusable and I got the bonus of speed with dot net. > If someone needs a new aggregate to concatenate strings then I just add > a new one. For SQL 2005 you have the problem with the size limitation for the UDA. For SQL 2008, the above solution still has the bonus that you can easily sort the resulting lists. But the XML solution is certainly a bit strange. You also need some more trickery to handle entitization. That is, in the above and "&" in the data will become & in the output. -- 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: Erland Sommarskog on 30 Nov 2009 17:52 Michael C (mike(a)nospam.com) writes: > We would have restrictions but there is a difference between restrictions > and silly restrictions. Having an 8K limit on dot net code is a silly > restriction. Not having generics in the *first* release of a product is > quite a valid restriction. The second release had generics. And the second release of SQLCLR had unlimited sizes for UDTs and UDAs. And in SQL Server, like it or not, 8000 bytes is a dividing line, between regular object and large object. Doing something in large objects is one more feature. > That's all there is to the code, it's such a simple problem to solve that > the code is minimal. As I said, if ADO.net can do it then linq can do it. > You stated ado.net can do it so it is possible. So why don't you do it then? Just because ADO .Net can do it, does not mean that LINQ knows that ADO .Net can do it, or how it does it. Even less so if you bring EF. -- 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: Erland Sommarskog on 30 Nov 2009 17:57
Michael C (mike(a)nospam.com) writes: > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message > news:Xns9CD0613B93454Yazorman(a)127.0.0.1... >> Yes, it's true that the declarative model is not perfect, but we >> sometimes have to help the optimizer quite a bit. > > Sometimes? We have to tell sqlserver how to do stuff in almost > everything we do in sqlserver. A beginner will write inefficient tables, > views, indexes and queries because they don't know how to tell sqlserver > how to do things. Database design and physical design, that is indexes requires skills. SQL Server is not an AI thing that creates indexes on its own. Once the right indexes are in place, and the database design is good, you should not need that many hints. But there is quite a few gotachs to make things more difficult, so even the knowledgeable can slip. >> But still SQL is a lot better that you would get if you would with a C# >> program. > > That's certainly true, although I would suspect it would run this > example in less than 30 seconds (assuming the data was in a text file). Yes, C# stored procedure that would read the temp table into a dataset could probably beat the T-SQL solution with quite a margin. C# is after all better apt to run loops than T-SQL. -- 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 |