I mentioned in Conditional INSERT/UPDATE Race Condition that most “UPSERT” code is defective and can lead to constraint violations and data integrity issues in a multi-user environment .  In this post, I’ll show how to prevent duplicate key errors and data problems with the MERGE statement too.  You might want to peruse Conditional INSERT/UPDATE Race Condition before reading this for a background on these concurrency concerns. Background on MERGE Microsoft introduced the ANSI-standard MERGE statement in SQL Server 2008.  MERGE is very powerful in that it can perform multiple actions in a single statement that previously required separate INSERT/UPDATE/DELETE statements.  MERGE is...

I’ve used sp_detach_db and sp_attach_db to relocate database files for many years.  I know that sp_attach_db was deprecated in SQL 2005 but, like most DBAs, I’ve continued to use sp_attach_db mostly out of habit.  I want to share with you why I’ve decided to change my ways. Planned File Relocation Let’s say you want to move the log file from to a separate drive.  The following script shows how to accomplish in SQL Server 2000 using sp_attach_db.  The only sp_attach_db parameters required are the database name, primary data file path and the log file that was moved from the original location. ...

I developed a custom report for SQL Server Management Studio that wraps the partition details and row counts query I previously posted.  Visit the Codeplex project site for details.  You can download just the released RDL file or, if you want to customize the report to your liking, download the full project source code.  Alternatively you can create a new report project of your own and add the RDL file as an existing project item. I welcome any feedback you might have, either here or via the Codeplex project discussion page.  It’s been a while since I’ve done any Reporting Services...