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 as duplicate data but in fact it isn't - it's similar data and therefore … [Read more...] about Data Management – Finding, Removing Duplicate Rows Using SQL and Some Prevention Tips
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 TABLE ( DateAdded SMALLDATETIME, Points … [Read more...] about T-SQL – Determine the Difference in Values Between Columns in Different Rows
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 using EXEC or sp_executeSQL Lets take this example … [Read more...] about SQL – Using CASE with ORDER BY in Stored Procedures
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 true and should always be the first thought when designing a q … [Read more...] about How to Produce CSV Format Using T-SQL
T-SQL – Derived Tables Demonstrated and Explained
T-SQL is a very powerful language when dealing with sets and in this post, I am going to be demonstrating how to use derived tables. Derived tables are not the same as a temporary or physical tables where the fields and data types are declared. Nor are they subqueries. Instead, as the name implies, the table is derived from an existing table. I liken them to views but a view … [Read more...] about T-SQL – Derived Tables Demonstrated and Explained