Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael C on 24 Nov 2009 18:45 "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9CCDE487CBBCBYazorman(a)127.0.0.1... > 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 true, it would be a lot of work for MS (or whoever). Nothing they couldn't achieve however. > Again, you can of course write classes etc. But C# is just a language. > SQL Server is far more than just a language. Aren't all the elements there is C# to be the front end for a database? Triggers would be events, stored procs would be functions, views would be linq statements, tables would be collections etc. Imagine writing your application in C# and stepping through code and straight into a C# stored proc (insert sound of Homer Simpson drooling here :-) > 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? No, but you can use a filestream to parse the data sequentially from the disk. Strings are an in memory object but there are plenty of classes for dealing with stuff that is not. >> 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. I just said "There is absolutely no reason sqlserver would have an 8K limit today, except for hangovers from the past." and you replied that the reason it's 8K is because that's the way it is and changing it would be a major change. Didn't you just say exactly what I said in different words? :-) > 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. Ok, fair point. But C# 1.0 was a brand new language and the restriction wasn't really that silly. They could have held out to put it in but decided to release without it. On the other hand sqlserver is 20+ years old and shouldn't have 8k restrictions on anything. > ADO .Net can do it. LINQ can't. If ADO.Net can do it then linq can do, you would just cast whatever collection you have into whatever ADO.net requires. > Well, since I'm SQL person and think TVPs are a great feature, I think > that is quite a limitation. It's not a limitation. It can be done very easily with the most minor of code. Michael
From: Michael Coles on 24 Nov 2009 21:54 > Aren't all the elements there is C# to be the front end for a database? > Triggers would be events, stored procs would be functions, views would be > linq statements, tables would be collections etc. Imagine writing your > application in C# and stepping through code and straight into a C# stored > proc (insert sound of Homer Simpson drooling here :-) There have actually been several front-end languages for database access invented over the years. SQL is just one (albeit a very widely adopted) possibility. You can definitely use C# as a front end for a database; in fact, people do exactly this every day when they write .NET Windows and Web client applications. If you want C# as the native language for a database you're going to need to rearchitect the DBMS. C# would probably make a better native language for an object-oriented database as opposed to a database derived from the relational model though. If you want to create a DBMS whose native language is C#, I would say go for it--you may strike gold. Usually these things are created to solve a problem--is there a particular problem you're trying to solve with this idea (other than your dislike of SQL, that is)? -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "Michael C" <mike(a)nospam.com> wrote in message news:OvRR2AWbKHA.4688(a)TK2MSFTNGP06.phx.gbl... > "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message > news:Xns9CCDE487CBBCBYazorman(a)127.0.0.1... >> 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 true, it would be a lot of work for MS (or whoever). Nothing they > couldn't achieve however. > >> Again, you can of course write classes etc. But C# is just a language. >> SQL Server is far more than just a language. > > Aren't all the elements there is C# to be the front end for a database? > Triggers would be events, stored procs would be functions, views would be > linq statements, tables would be collections etc. Imagine writing your > application in C# and stepping through code and straight into a C# stored > proc (insert sound of Homer Simpson drooling here :-) > >> 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? > > No, but you can use a filestream to parse the data sequentially from the > disk. Strings are an in memory object but there are plenty of classes for > dealing with stuff that is not. > >>> 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. > > I just said "There is absolutely no reason sqlserver would have an 8K > limit today, except for hangovers from the past." and you replied that the > reason it's 8K is because that's the way it is and changing it would be a > major change. Didn't you just say exactly what I said in different words? > :-) > >> 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. > > Ok, fair point. But C# 1.0 was a brand new language and the restriction > wasn't really that silly. They could have held out to put it in but > decided to release without it. On the other hand sqlserver is 20+ years > old and shouldn't have 8k restrictions on anything. > >> ADO .Net can do it. LINQ can't. > > If ADO.Net can do it then linq can do, you would just cast whatever > collection you have into whatever ADO.net requires. > >> Well, since I'm SQL person and think TVPs are a great feature, I think >> that is quite a limitation. > > It's not a limitation. It can be done very easily with the most minor of > code. > > Michael >
From: Michael C on 24 Nov 2009 22:04 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:OW%23bv% > 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. 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. In comparison with C# and linq this would be very straight forward. Its really just calling a function which is something very basic. It's interesting that you're selling me the CLR as a good feature (which it is), yet having a C#/Linq database is only 1 step away where we directly write C# code in Management Studio. Wouldn't that be a good feaure also? > 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. That is true of course and sqlserver is certainly the area where you get crusified if you don't continue to support it. Image if they did to sqlserver what they did to VB6? :-) That brings up an interesting point though. They dumped VB6 for C#/vb.net and while this did upset those who used VB6 it did produce a far far superior product. Obviously they couldn't do this with sqlserver without being lynched but they could bring out a new product to sit along side sqlserver. Michael
From: Michael C on 24 Nov 2009 22:43 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:FC039687-313A-4350-9FDE- > There have actually been several front-end languages for database access > invented over the years. SQL is just one (albeit a very widely adopted) > possibility. You can definitely use C# as a front end for a database; in > fact, people do exactly this every day when they write .NET Windows and > Web client applications. I think I was using the wrong term when I said front end. The term you used "Native Language" is much better. > If you want C# as the native language for a database you're going to need > to rearchitect the DBMS. C# would probably make a better native language > for an object-oriented database as opposed to a database derived from the > relational model though. Quite possibly, although I've heard that most attempts at making an OOP database have failed. I'd be interested to see why. > If you want to create a DBMS whose native language is C#, I would say go > for it--you may strike gold. Usually these things are created to solve a > problem--is there a particular problem you're trying to solve with this > idea (other than your dislike of SQL, that is)? Don't get me wrong, I do like sqlserver but I think it is getting a bit aged and the attempts at giving it more modern features are a bit kludgy. The problem I am trying to solve is just to modernise sqlserver, add a few features and make things more consistant. As for me doing it there is no chance of that, this is something that would require a big team of developers to get working effectively. Can you imagine the complexity of creating optimised indexes? And that is just one part of the database. Michael
From: Michael Coles on 24 Nov 2009 22:47
"Michael C" <mike(a)nospam.com> wrote in message news:uo9R%23vXbKHA.1652(a)TK2MSFTNGP05.phx.gbl... > "Michael Coles" <admin(a)geocodenet.com> wrote in message news:OW%23bv% >> 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. > > 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. I think there are some advantages that could be realized by adding additional parameters to SQL CLR custom aggregates, and from what I understand that is on the radar for future versions of SQL Server. In the meantime you can work around the issue by using SQL CLR UDTs and passing them to the aggregate. Adding the ability to pass multiple parameters to a SQL CLR UDA seems like quite a distance from completely rewriting the entire DBMS engine to support a new procedural front-end language. > In comparison with C# and linq this would be very straight forward. Its > really just calling a function which is something very basic. Again, if you really want a DBMS with procedural C# as it's native language the relational model is probably not your best bet. If you want an OODBMS with a procedural interface there are actually choices out there. I believe IBM implements such a DBMS in their Notes line > It's interesting that you're selling me the CLR as a good feature (which > it is), yet having a C#/Linq database is only 1 step away where we > directly write C# code in Management Studio. Wouldn't that be a good > feaure also? Oh I'm not selling you on CLR. It certainly has areas in which it can be used to enhance and extend SQL Server's functionality; however, I'm just pointing out to you that you have the option to talk directly to SQL Server in C# on the server. That seemed to be your main point, so I'm simply pointing out that hey, there it is! >> 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. > > That is true of course and sqlserver is certainly the area where you get > crusified if you don't continue to support it. Image if they did to > sqlserver what they did to VB6? :-) That brings up an interesting point > though. They dumped VB6 for C#/vb.net and while this did upset those who > used VB6 it did produce a far far superior product. Obviously they > couldn't do this with sqlserver without being lynched but they could bring > out a new product to sit along side sqlserver. Well, for one thing VB6 was comparatively cheap back in the day--I remember picking up a copy of VB6 Pro at a trade show for about $50 way back in the day. SQL Server is significantly more expensive. Throw in licensing costs, etc., and you are now implementing an enterprise solution to run the business on. The main issue that VB6ers had was primarily with support, particularly support for the COM-based technologies developed with it/for it back in the day. But that's another discussion for another day. In the meanwhile, what product do you want to sit alongside SQL Server? I doubt MS is willing to split their development resources down the middle to create an entirely new enterprise-class DBMS just so they can bypass SQL and use C# as the native language. I mean, have you seen the economy lately? From what I understand the SQL Server team is focusing on improving SQL Server--I could be wrong, but I doubt there's a lot of interest in cutting the budget in half to create a competing enterprise DBMS. :) -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- |