This is a real-world problem that I came across the other day. In a reporting scenario, I wanted to output a number of values in an easy, human-readable way for a report. But just making a long, comma-separated string of numbers doesn’t really make it very readable. This is particularly true when there are hundreds of values.
So here’s a powerful pattern to solve that task.
In a sense, you could call me lazy. If there’s a script that will perform a task for me, I’d rather use that script than reinvent another wheel. Then again, if needs be, I’d rather spend a day writing such a script, rather than spending ten minutes just getting the job done.
Somehow, that makes me a happier developer.
The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.
But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how:
Back in 2014 I wrote a blog post on how to calculate a median value using the NTILE window function. It’s far from the best performing solution there is, but it worked on SQL Server 2008, before the introduction of OFFSET-FETCH i SQL Server. In this post, I’m going to look at creating a generalized function that calculates the median (or any percentile) of a series of values.
Today’s post illustrates a pretty cool application of SQL Server’s built-in XML and XQuery support, used to parse parenthesis-delimited expressions. You may want to get your reading glasses on for this one..
A common reporting scenario is that your report has to concatenate an aggregate of string values from rows. Many other database platforms even have built-in aggregate functions that will concatenate text for you (like LISTAGG() on Oracle). In this post, we’ll take a look at how you can achieve the same results in T-SQL, using the APPLY operator and the XML datatype.
XML documents are, by design, at best like an entire relational database, stored in a single column. Worst-case, they don’t even come with a schema. Small wonder then, that XML queries take a good deal of CPU and I/O performance to complete. The solution to this problem is to index them, pretty much like you would index regular tables.
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.
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.
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.