T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()

I’ve recently been working on a data migration project and have found myself utilizing the ROW_NUMBER() function in SQL Server a lot. This function has been around since SQL Server 2005 and at its core, provides a way to provide sequential numbering for rows returned by a query. One of the requirements for an export process was […]

Data Management – Finding, Removing Duplicate Rows Using SQL and Some Prevention Tips

Duplicate data is a common problem and in my experience comes in two main flavours; exact duplicate data and similar data. Exact duplicate data is when a data source contains the same values across all fields in the record. Similar data is when some of the fields match and to the naked eye can be classed […]

T-SQL – Determine the Difference in Values Between Columns in Different Rows

Have you ever needed to calculate the difference between columns in two or more rows in a table using a T-SQL query? For example you might want to run some trend analysis or audit data for changes. Thankfully using ROW_NUMBER() and Common Table Expressions (CTE’s), this can easily be achieved. –create our table DECLARE @TableRows […]

SQL – Using CASE with ORDER BY in Stored Procedures

When writing SQL it is possible to use ORDER BY with CASE conditions to help provide more flexibility over the returned order of a record set and this is especially useful when writing stored procedures which require a dynamic ORDER BY clause. This method does not require any dynamic SQL build up strings executed either […]

How to Produce CSV Format Using T-SQL

There may be some requirements where you need to produce a csv output from a table. There are some good, efficient ways to do this and there are also some less efficient ways. Let us now look at one optimized approach and a couple of lesser optimized approaches 😉 SQL performs best working with sets This is […]