I’m an outspoken advocate of always using a clustered index on each and every table you create as a matter of best practice. But even I will agree that there’s a case for using the odd heap now and then.
update
The 2014 Swedish SQL Server usergroup challenge
Sunday came early this week. In this longer-than-usual post, I’m going to show you the details of my contribution to the 2014 edition of the annual Swedish SQL Server usergroup challenge.
Using MERGE as a single INSERT or UPDATE
The MERGE statement is a very powerful way to combine INSERT, UPDATE and/or DELETE in a single statement. But there’s more than meets the eye. There are situations where you could use a MERGE statement to perform just one of those tasks.
Inline variable assignment in UPDATE statements
Ok, here’s a brain-twister. Not only can you assign values to a column in an UPDATE statement using variables, but you can assign values to variables as well. It’s really not as complicated as it may sound, but there are a few trapdoors to avoid.
Cool MERGE features you may not know about
The MERGE statement is a really powerful way to create what’s called “upserts”. In this article, I’ll take a more detailed look at how you can make the best use of MERGE and I’ll also show you some cool tricks I picked up along the way.
Using MERGE to insert, delete and update all-in-one
As of SQL Server 2008, there’s a new powerful consolidation statement in the DML toolbox: MERGE. Using MERGE, you can perform so-called “upserts”, i.e. one statement that performs an insert, delete and/or update in a single statement. And, more importantly, with just a single join.
Using OUTPUT with DML statements
The OUTPUT clause allows you to combine DML statements with a kind of SELECT statement on the rows affected by the DML operation. This is a powerful way to visualize what records were touched by your statement, or an easy way to build an auditing mechanism.
sqlsunday.com 