Turn your list into human-readable intervals

If you’ve worked with reporting, you’ve probably come across the following problem. You have a list of values, say “A, B, C, D, K, L, M, N, R, S, T, U, Z” that you want to display in a more user-friendly, condensed manner, “A-D, K-N, R-U, Z”.

Today, we’re going to look at how you can accomplish this in T-SQL, and what this has to do with window functions and gaps and islands.

Continue reading

Human-readable ranges of integers or dates

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.

Continue reading

Splitting a range string into a table

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

Analyzing partition usage and skewing

I sometimes want to know how my data is spread across different partitions in a table or index – after all, this can affect performance and storage a great deal, and if the data is really badly skewed, most or all of it could be stuck in a single partition, rendering the partitioning scheme pretty much useless in the first place.

You can use dynamic management views to find out how your data is spread across different partitions, and how those partitions are delimited, in “plain english”. Here’s how!

Continue reading

Working with intervals

At one point or another, you’re going to come across intervals when working in SQL Server. You could say that an interval is where you don’t have a single value, but actually a range of values, commonly delimited within a start and an end value. This range could be a group of accounts, versions of dimension members (in an SCD) or date/time intervals.

Continue reading