undocumented

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]

Now that my SQL Server 2014 CTP1 virtual machine is ready, I started to play with it and some new features and differences with the previous versions are starting to appear. What I want to write about today is a T-SQL enhancement to DDL statements that brings in some new interesting considerations. SQL Server 2014 now supports a new T-SQL syntax that allows defining an index in the CREATE TABLE statement without having to issue separate CREATE INDEX statements. [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]

Yesterday the CSS team made the QUERYTRACEON hint publicly documented. This means that now it’s officially supported and you can use it in production code. After reading the post on the CSS blog, I started to wonder whether there is some actual use in production for this query hint, given that it requires the same privileges as DBCC TRACEON, which means you have to be a member of the sysadmin role. [Read More]

In order to avoid collation conflict issues with TempDB, all user databases on a SQL Server instance should be set to the same collation. Temporary tables and table variables are stored in TempDB, that means that, unless explicitly defined, all character-based columns are created using the database collation, which is the same of the master database. If user databases have a different collation, joining physical tables to temporary tables may cause a collation conflict. [Read More]