Obscure Real Data for Testing
Very often we have a good set of production data but it cannot be used for training or testing because of data confidentiality. And the solution sometimes is just to tweak pieces of the data so it cannot be linked to the original data. Here is one method for obscure data in SQL Server 2005. […]
UPSERT and More with MERGE
One very common requirement is to merge source data to a target table. For example, merge the daily changes to accounts data from branch office table to central accounts table. On SQL Server version 2005 and prior this was accomplished using separate INSERT and UPDATE statements. This involved checking if key column exists to perform […]
Row Concatenation with FOR XML PATH
Many times it is needed for reporting purposes to summarize normalized data into groups or lists of values. This is also known as rows concatenation. Some reporting and client side tools support this directly. Here is one approach to solve this in T-SQL using FOR XML PATH. CREATE TABLE Products ( sku INT PRIMARY KEY, product_desc VARCHAR(35)); CREATE […]
Grouping with ROW_NUMBER
The new ranking functions in SQL Server 2005 have great impact on how problems can be solved. Here is just another example of that. Given a table with events at different venues (all event dates are unique), find the duration intervals of start/end date of uninterrupted performances at a given venue. Solving this without the […]
Updates with CTE
Performing updates on columns based on values from another table is a very common need. Using the ANSI UPDATE normally requires multiple subqueries, which can be very inefficient especially if multiple filters have to be applied. The Microsoft specific UPDATE with JOIN is one solution. However, common table expressions provide a very elegant alternative, which […]
Extracting List Item from Delimited String
Performing string manipulations is not the best use of T-SQL. But there are a lot of operations that can be done using pure SQL. Here is one example. Given an input string that has list elements separated by delimiter, slice the list into individual element, and provided index parameter return the matching item based on […]