Partitioning is not only a great way to improve performance in large tables, but you can also use it to manage the physical storage of different chunks of data without keeping it in different tables.

Continue Reading…

As of SQL Server 2005, you can handle errors using a TRY-CATCH block, similar to “real” programming languages. This enables you to trap most common errors and handle them, instead of having your entire batch or procedure fail with an error message.

Continue Reading…

Last week, we looked at how to construct a visual representation of a hierarchy stored as a parent-child table. The obvious next step is to accumulate values stored on those nodes using this hierarchy.

Continue Reading…

Found this piece of brilliance on the Twitterwebs. Wish I would have thought of it first.

 

Enjoy!

When you’re designing reports, they can often be based on hiearchies represented by “nodes” in a parent-child setup. To the end-user, the parent-child representation doesn’t provide very much readability, so you need to output this information in a human-readable form, for instance in a table where the names/titles are indented.

Continue Reading…

NULL is an undefined value and as such, it has a special status as values and datatypes go in SQL Server. There are quite a few potential pitfalls to watch out for when you’re dealing with data that can contain NULL values.

Continue Reading…

This week’s post is a requirement that I see very regularly as a developer. You get a plaintext string containing one or more ranges. Each range is comma delimited, and the start and end values of the range are separated by a dash. The string could look something like this, for example: 100-120,121-499,510,520,790-999.

Wouldn’t it be practical if we could construct a table value function that returns one row for each range, with columns for the start and end of each range?

Continue Reading…

Check out this deepdive on table variables that I found the other day:

SQL in the Wild: A trio of table variables

The article deals with some common misconceptions about table variables and it’s considerably more in-depth than my post on different types of temp tables.

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..

Continue Reading…

I personally find the UNPIVOT syntax in T-SQL a bit unwieldy and not entirely easy to read. In this article, I’m going to show you an alternative way to perform UNPIVOT, using the CROSS APPLY operator.

Continue Reading…