DBCC

Some days ago I was talking with my friend Davide Mauri about the uniquifier that SQL Server adds to clustered indexes when they are not declared as UNIQUE. We were not completely sure whether this behaviour applied to duplicate keys only or to all keys, even when unique. The best way to discover the truth is a script to test what happens behind the scenes: -- ============================================= -- Author: Gianluca Sartori - @spaghettidba -- Create date: 2014-03-15 -- Description: Checks whether the UNIQUIFIER column -- is added to a column only on -- duplicate clustering keys or all -- keys, regardless of uniqueness -- ============================================= USE tempdb GO IF OBJECT_ID('sizeOfMyTable') IS NOT NULL DROP VIEW sizeOfMyTable; GO -- Create a view to query table size information -- Not very elegant, but saves a lot of typing CREATE VIEW sizeOfMyTable AS SELECT OBJECT_NAME(si. [Read More]

Back in 2011 I showed a method to discover the resultset definition of DBCC undocumented commands. At the time, SQL Server 2012 had not been released yet and nothing suggested that the linked server trick could stop working on the new major version. Surprisingly enough it did. If you try to run the same code showed in that old post on a 2012 instance, you will get a quite explicit error message: [Read More]

Some months ago I posted a script on a SQLServerCentral forum to help a member automating the execution of DBCC CHECKDB and send and e-mail alert in case a consistency error is found. The original thread can be found here. I noticed that many people are actually using that script and I also got some useful feedback on the code itself, so I decided to write this post to make an enhanced version available to everyone. [Read More]

Lots of blog posts and discussion threads suggest piping the output of DBCC commands to a table for further processing. That’s a great idea, but, unfortunately, an irritatingly high number of those posts contains an inaccurate table definition for the command output. The reason behind this widespread inaccuracy is twofold. On one hand the output of many DBCC commands changed over time and versions of SQL Server, and a table that was the perfect fit for the command in SQL Server 2000 is not perfect any more. [Read More]