QUOTED_IDENTIFIER, Filtered Indexes, SSMS & SQLCMD

So I’ve been working on a project that utilizes filtered indexes and decided I needed to build an automated deployment routine.  I had built a set of build scripts, and utilizing SQLCMD mode in SSMS to allow for include files, built a master script to perform the entire build. 

During development and initial testing I ran this in SSMS and everything worked fine, but for automation purposes I used SQLCMD.EXE.  As soon as I started using SQLCMD Is started to get errors indicating that the QUOTED_IDENTIFIER was incorrectly set for indexed views, filtered indexes etc. 

Now I’m aware that indexed views among other objects are picky about their settings and the error message let me know that this was the case for filtered indexes as well.  However, the question was, why hadn’t it come up before?  In looking through the code I only found one reference where QUOTED_IDENTIFIER was set to OFF and that was in the create database script.  Even after changing that the errors were still showing up.

It turns out that by default, a connection through SSMS sets QUOTED_IDENTIFIER to ON, while a connection through SQLCMD sets it to OFF.  In SQLCMD it does this after each connect statement, so setting it at the beginning of a script may not cover it if you subsequently run a connect statement after it.  The best practice is to run the set statements after each connect.

Reading through the Remarks section of the following MSDN article,  SET QUOTED_IDENTIFIER (Transact-SQL), states that the defaults are set differently for different connection types (DB-Library, SQL Server Native Client, ODBC etc.) and thus indicates that SQLCMD and SSMS are probably using different connection types.

Debunking the Cult of the Machine SID

So this isn’t especially data oriented, but for any developer who is constantly creating new VMs for development or testing this info might come as a pleasant surprise.

Over the years, when cloning VMs, sometimes by simply copying the VHD files and attaching them to a new machine, we’ve been told that we’ll cause problems because we aren’t creating machines with unique SIDs.  As a result, we’ve had to use sysprep, sysinternals NewSID (discontinued) or the VM software of our choice’s cloning tool.  Turns out however, our obsession with SIDs was all for nought.  The following technet blog post by the author NewSID details why duplicate SIDs don’t matter:

The Machine SID Duplication Myth (and Why Sysprep Matters)

Note that it is still official Microsoft policy to require cloned machines to be syspreped, and as such I would recommend following that policy in a production environment, but for us developers, copy away and stop worrying about your SIDs!