Common Table Expressions (CTE) in SQL Server 2005 and Up

Thanks to the team at TechRepublic.com and BOL, I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Learn more about CTEs ¬†at TechRepublic. Here meat of the article a sample: ;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking) AS (SELECT Product , SaleDate , SalePrice , Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) FROM SalesHistory ) DELETE FROM SalesCTE WHERE Ranking > 1 Very easy and handy! Check out BOL for all kinds of information regarding Dense_Rank(), NEW_ID(), and PARTITION BY. While you’re there, check out RANK(), NTILE(), and ROW_NUMBER() I’m sure … Continue reading Common Table Expressions (CTE) in SQL Server 2005 and Up

Using NOCOUNT, ANSI Warnings and Temp Tables in SQL Server

Here are a few useful code fragments that I use all the time. You see too many Rows Affected Messages. First, NOCOUNT. If you don’t want SQL Server to return the Row Count (20 row(s) affected) after you execute a query, here is the code: — Turn off the rows affected message SET NOCOUNT ON Once you have executed your queries, you can enable the row count again SET NOCOUNT OFF ANSI Warnings interrupt your inserts Next, do you get annoying ANSI errors when inserting data? You can remove those warnings using one simple command. SET ANSI_WARNINGS OFF If there … Continue reading Using NOCOUNT, ANSI Warnings and Temp Tables in SQL Server