In datawarehousing, a common scenario you may have encountered is the phenomenon of late-arriving dimension members. Imagine a situation where you get dimension members and fact data from two entirely different data sources, which may or may not provide data at the same time. Some fact data will probably be ready to load before its corresponding dimension member(s) arrive. How do you tag your fact data, and what consequence does that have for the integrity of your data warehouse?
Minimally logged operations
Today, we’re going to look at which database operations can be performed with what’s known as “minimal logging”, which can result in a considerable performance boost. Included is a quick overview of transaction logging and what it’s good for.
Introduction to partitioning
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.
Error handling using TRY-CATCH
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.
Accumulating values in a parent-child hierarchy
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.
An indented representation of a parent-child hierarchy
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.
Working with NULL values
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.
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?
Temp table myths
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.
Parsing parenthesis expressions
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..