Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael Coles on 27 Nov 2009 15:58 "Jeffrey Williams" <jeff.williams3188(a)verizon.net> wrote in message news:exkzh$5bKHA.4024(a)TK2MSFTNGP06.phx.gbl... > As I replied on another branch - I actually think his dream already > exists. I know that everything he is asking for is already available from > Intersystems Cache. There you go Michael C, Jeffrey has your OODBMS that surfaces procedural C#/.NET/LINQ as its native language, handles hierarchical data with the greatest of ease, efficiently returns objects to the client without having to deal with all those "squares", and automatically optimizes queries without requiring you to define "indexes" or performing other administrative tasks to optimize performance. -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ----------------
From: Erland Sommarskog on 27 Nov 2009 17:48 Michael C (mike(a)nospam.com) writes: > Custom aggregates is a good example. While sqlserver can do it, it is > kludgy and difficult. You need to recompile and add your assembly back > into sqlserver. You can't pass parameters into the custom aggregates > which means you need to write different custom aggregates where normally > you would pass in a parameter. As an example, I wrote a custom aggregate > to comma seperate varchar values when grouping. This worked well and was > use by all the programmers here. But then someone wanted it seperated by > a space so I had to write a whole new aggregate. Then someone wanted it > sorted, then sorted descending, then one to remove duplicates, and the > remove duplicates sorted descding seperate by a semi colon. 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. -- 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 29 Nov 2009 17:55 "Jeffrey Williams" <jeff.williams3188(a)verizon.net> wrote in message news:exkzh$5bKHA.4024(a)TK2MSFTNGP06.phx.gbl... > As I replied on another branch - I actually think his dream already > exists. I know that everything he is asking for is already available from > Intersystems Cache. It looks interesting I will have a look into it. Thanks for the tip. I'm not sure that it gives me what I want though? I don't think I can write linq or C# into a stored proc and I can't add a reference to a dot net dll directly into the database? Cheers, Michael
From: Michael C on 29 Nov 2009 18:00 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CD05E6B6D01FYazorman(a)127.0.0.1... > And in the end you would maybe end up with as many "silly" restrictions > as you have in SQL Server. You need to ship at some point. 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. > Yes, it could, but today it isn't, but LINQ is only something that > generates > something else, which brings us to: Of course, but the whole point I'm making is that something should be written. > If LINQ is not able to generate the code needed to pass a TVP, it isn't. > My source says that LINQ does not have this capability. You claim it has, > but you don't decline to back up your statement with working code. The > code you provided tells me nada. 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. > (And why not install SQL 2008? That would save you from the limitation > with 8000 bytes in a UDA.) My boss craps himself when I say I need to install a service pack. Besides, we have just rolled into month 24 of a 24 month project so it's a bit late for big changes now. :-) Michael
From: Michael C on 29 Nov 2009 18:06
"Michael Coles" <admin(a)geocodenet.com> wrote in message news:F34B74AD-D2DC-448B-B7BE- > Unfortunately for you proper indexing is another tool used to express your > intent to the optimizer. If you did not properly index the underlying > tables then you only did half the job to begin with. You have many > options that you can take advantage of to better define your intent and to > help the optimizer achieve better performance, but you don't have to use > them. As you point out above, your performance may suffer but it will > complete eventually. The indexing was fine but the table design was probably not ideal. There was a large amount of "old" data and a small amount of "current" data. To grab just the current data and then filter on that was slowing it down. By creating an indexed view I told sqlserver to keep a copy of the current data only. > Exactly. At some point someone convinced MS that .NET was worth it. Now > you just have to do the same... Seems like a big task. I'd be suprised if they hadn't considered this and already made a decision one way or the other. > Have you used FOR XML to return hierarchical data from SQL Server? It's > well-optimized, and I have absolutely no problem returning XML from a > stored proc when performance is an issue. I haven't ued it but it seems like a bit of a VB6 style solution to me. Anything using XML isn't going to be that efficient. (BTW, seems like I'm telling sqlserver how to do things again :-) > If you really want to do your joins in the client you definitely have the > option to read all of your "square blocks of data" as you call them back > and perform the joins on the client with your own C# nested loops. You > may run into bandwidth limitations when you start pulling gigabytes of > data across the wire and performing custom nested loops on the client to > "marry them up", but on the plus side this could be the foundation for > your very own custom database engine... That's my point, it's another imperfect solution. > So in reality you want a hierarchical DBMS as opposed to an OODBMS or a > relational/SQL DBMS? Unfortunately SQL is optimized for relational data, > not OO or hierarchical data, although there are ways to work around the > limitations. There are definitely hierarchical DBMSs available if that's > where your needs are, but you'll face other limitations there. I don't think I want a hierarchical database, just to be able to return hierarchical data. > At any rate this has been an interesting conversation, but I think we've > eventually gotten to the root of your complaint here -- essentially you > don't want a SQL DBMS, you want a hierarchical DBMS. You already know > that the users here can't really rewrite SQL Server to your > specifications, but you've also been informed that you can file your > recommendations at http://connect.microsoft.com to get them heard by the > people who can rewrite it to your specifications. > > So I think I've come to the end of my portion of this thread. You'll have > to sell MS on your vision of rewriting SQL Server as a hierarchical DBMS > or an OODBMS or whatever model you would like them to implement. Ok, fair enough. Thanks for the interesting conversation. :-) Michael |