Depending on your line of work, you may some time stumble over JSON data. JSON is a data format specifically designed to work well with Javascripts on web pages, so it’s fairly common in web service applications. If you’ve managed to land JSON data on your server and need to convert it to XML, here’s an idea how to.
Category: T-SQL
Catalog views: Database objects
Catalog views are system views that expose most facets of the server and its databases in a tabular form. In this tutorial, we’ll take a closer look at database objects and how they’re represented in a database’s catalog views.
Ben-Gan on virtual auxiliary table of numbers
Check out this interesting article from SQL Server superstar
Table value functions vs Inline functions
You may not know that there are two different ways of writing user-defined functions that return a recordset: Table value functions and Inline functions, and they both come with a number of benefits and limitations with regard to performance and programmability.
Typed XML data
Did you know that you can define an XML schema for every XML column or variable that you want? Defining XML schemas is not only good practice, because it validates the data as soon as you store it in the column or variable, but it also improves XML query performance.
Top 10 T-SQL worst practices
As part of an improvement process (any process, not just database development), you can start by asking yourself “How could I make this process as bad as possible”. In fact, when you think of how to royally screw something up, all you have to do is flip the answer around, and there you have a good starting point for improving your process, work, whatever.
Here are a few T-SQL “worst practices” to get you started on improving your database design and development practices. I’ve personally seen all of these in the field, and I think you might recognize some of them, too.
Parsing XML data
You’ll be hard-pressed not to stumble over XML data in your daily work. Good thing then, SQL Server contains built-in XML parsing logic – there’s a native xml datatype, built-in XPath support and all the tools you need to store and transform data from or to XML data. However, this is an extensive topic, so this article will cover the basics of parsing XML data into a recordset, just to wet your apetite.
Turning semicolon delimited data into a table
Here’s a handy conversion tool that I built for myself in order to convert semi-colon delimited data (very common in files, etc) into a table. It comes in the form of a scalar function, that converts a varchar(max) to an XML value.
Creating XML data from a recordset
SQL Server has a wide range of XML features natively built into the platform, and this includes methods to create XML from tabular data, working with XML data and, of course, turning XML data back into a recordset. This primer will go through some common methods of creating XML from a regular SELECT query.
A short introduction to application roles
Application roles provide a practical way to assign application-specific permissions in your database and to make sure that your applications always use a defined login. Not to be confused with actual roles, application roles are more like users in the database.