July 17, 2011 at 9:58 pm
Comments posted to this topic are about the item JSON.SQL: A CLR-resident JSON serializer/deserializer for SQL Server
July 18, 2011 at 2:16 am
This is a fascinating bit of code that attempts an approach that would provide very useful facilities for any database that was integrating with a JSON-savvy application. I love the idea of doing JSON bulk load. JSON serialiser/deserialisers are written to perform a lot of different tasks, and there is room for several in the Database developers armoury.
I did find existing examples of JSON parsers and loaders on the Web, including one in native T-SQL that is one of the more, er, complex and lengthy T-SQL procedures I've seen
I suspect that this is a description is either mine from Simple-Talk[/url], or Ric Vander Ark's from SSC[/url]. In both cases, the amount of source code to do the work is probably less than is in yours if you include what's in JSON.NET! (OK, each one performs very different functions). John Galt has done an interesting and very compact CLR that requires no external JSON library.
Best wishes,
Phil Factor
July 18, 2011 at 3:14 am
Great stuff. JSON is well on its way to replacing XML for one simple reason - you can access the data from anywhere. This is not the case with XML web services for example. That nice asmx service that works fine on your local machine will not work for people who want to consume the data from another domain. This is what the same-origin policy ( ) is all about. Yes, it works in compiled languages like C# but a pure Javascript/jQuery solution will not work, unless you use JSON.
July 18, 2011 at 3:20 am
Great article.
We're starting to use JSON more and more so I can image I want to use code like this in the near future.
July 18, 2011 at 6:03 am
Thank you for the article. I've been a long time proponent of JSON. I've implemented something similiar in the CLR and I have a couple suggestions for handling datasets over 100 rows to get the performance up, size down, memory utilization down and retain complex data types:
1) Put a Data map at the start of your structure that stores the names of the columns size and type. Cast and validate on the client side if necessary.
2) Remove all the Key names from every entry and utilize the map and utilize standard nested arrays
Thank you,
Blaine
July 18, 2011 at 6:25 am
Thanks so much for the article. We are constantly looking for ways to handle json data especially as some of the xml api are getting depreicated. Thanks!
July 18, 2011 at 7:46 am
Great article, made for a very informative read. Many thanks!
July 18, 2011 at 9:38 am
I'm inclined not to use this approach, for these reasons:
1. As the author indicated, using the CLR within SQL Server opens up a number of potential performance and security problems. The author's code is quite admirable, but, I feel, ought to be executed outside the SQL Server environment.
2. DBAs generally are not facile at writing .Net code. Such would typically be a task for .Net developers, not DBAs. The author mentioned reviewing a T-SQL-based approach to JSON serialisation/deserialisation, but rejected it because of its length/complexity. Still, I wonder if revisiting a T-SQL solution might be worthwhile, in part for the reason of making it more accessible to DBAs?
3. There are many shops which use SQL Server as their standard RDBMS, but do not use .Net for application development. Also, the future of .Net is uncertain, with rumours abounding Microsoft is considering its replacement by HTML5/JavaScript. Again, why not reconsider a T-SQL-based solution for JSON serialisation/deserialisation, as SQL itself is in no danger of being replaced by another language/framework, and is more universally understood by DBAs and developers than is .Net?
July 18, 2011 at 12:26 pm
Where did you hear that rumor? And by .Net do you mean ASP.Net or ... just Silverlight or ....?
July 18, 2011 at 1:08 pm
John, the rumours involve both .Net and Silverlight. Another announcement - supposedly clarifying what was said at the D9 conference - is due from Microsoft in September, I believe.
Here are a couple of relevant links. As you might expect, the .Net/Silverlight developer communities are not happy.
July 18, 2011 at 2:43 pm
Phil:
Ouch, OK, got me on that one, Phil (I can call you Phil, right?) I agree my solution is overall *more* lines of code, but it's OOP, something that one can only at best be emulated in T-SQL with a lot of EXEC calls. Better lots of small OO code snippets than one big nested splat of code. Not that I'm calling YOUR code "one big nested splat of code". I would never do that, Phil. (Can I call you Phil?)
July 18, 2011 at 2:52 pm
Craig-315134:
I agree with #1 in general. But sometimes you gotta do what you gotta do.
I couldn't disagree more with #2. If .NET isn't accessible to SQL Server DBAs, it should be, and that's on the oenus of the DBA in question. I expect DBAs to code CLR, and we expect our .NET developers to know SQL. Expert levels not required, but TRYING to be an expert at what you don't know (i.e. continuous ongoing expanding etc. education) is. ESPECIALLY in this job market. Seems foolish to me to limit myself or anybody else: that's why as a DBA, I still know CLR. Better predators catch more rabbits.
On #3, I assume every technology I ever know has competition that will some day some way destroy it. That's why as a DBA I also know Oracle, ParAccel, and Netezza and dabble in VoltDB and a couple of others. All technology is transitory: just like companies, people, and the leftovers in the fridge. Specifically, I think the .NET fear is (a) overblown and (b) limited mostly to the GUI aspects of Win 8 -- which I agree are a potential strategic nightmare for existing MS customers. Or, maybe it will die the way PowerCerv's class library for PowerBuilder -- all the rage in 1994 -- died.
Then again, strategic nightmares have paid my bills for the last 25 years. Go strategic nightmares!
July 18, 2011 at 2:54 pm
BlaineM:
Thanks! Got a plan for v1.1 now.
July 18, 2011 at 3:32 pm
Bret,
(I can call you Bret, right?)
In an ideal IT world (run by either Bret or Craig), developers would know T-SQL, and DBAs would know .Net.
But in practice, I haven't usually found thing works out quite so ideally. Most developers in the MS shops I've seen know .Net, of course, and also a fair amount of T-SQL. But working with the CLR is just beyond what many DBAs are accustomed to doing.
Again, that's not to say they shouldn't learn - but often, the demands and requirements of the DBA's job mitigate against it. And some DBAs really want to avoid programming altogether - due to a lack of interest, a lack of aptitude, or both. (They had their reasons for becoming DBAs, after all, and not developers.)
Re SQL Server's 'shelf life': while all good things must eventually come to an end, as you pointed out, T-SQL is apparently still far from its 'sell-by date'. The same cannot be said (at least at this time) with the same surety, of .Net. This was one of my reasons for suggesting a T-SQL-based solution might be preferable to a .Net one.
Regardless, I think you've crafted some truly wonderful code, and I'm sure any number of people will be taking advantage of it. Thanks.
July 19, 2011 at 1:15 am
Hey Bret,
In my experience with .Net CLR programming I have found using CLR SP/Functions to be very useful but with that being said I have also stuck to a rule never call the database from within any CLR OOP code. I have found the context switching to be very intensive and why not let the database do the heavy lifting when it comes to doing the actual insertion of the data.
With that said, I would use your example in a heart beat if we were going to do any JSON serialization and deserialization. It seems to be a solid solution to a problem that is going to become larger with a lot of people migrating to JSON and away from XML. My guess is that Microsoft will see this and add it to the next, next version of SQL just like the late addition of XML to SQL 2005.
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply