On my current project we are using SQL Server Data Tools (SSDT) to manage our database schema and one of the tasks we need to do often is insert data into that schema once deployed; the typical method employed to do this is to leverage Post-Deployment scripts and that is exactly what we are doing.

Our requirement is a little different though, our data is split up into various buckets that we need to selectively deploy on a case-by-case basis. I was going to use a SQLCMD variable for each bucket (defaulted to some value other than “Yes”) to define whether it should be deployed or not so we could use something like this in our Post-Deployment script:

IF ($(DeployBucket1Flag) = 'Yes')
BEGIN
   :r .\Bucket1.data.sql
END
IF ($(DeployBucket2Flag) = 'Yes')
BEGIN
   :r .\Bucket2.data.sql
END
IF ($(DeployBucket3Flag) = 'Yes')
BEGIN
   :r .\Bucket3.data.sql
END

That works fine and is, I’m sure, a very common technique for doing this. It is however slightly ugly because we have to litter our deployment with various SQLCMD variables. My colleague James Rowland-Jones (whom I’m sure many of you know) suggested another technique – bitmasks. I won’t go into detail about how this works (James has already done that at Using a Bitmask - a practical example) but I’ll summarise by saying that you can deploy different combinations of the buckets simply by supplying a different numerical value for a single SQLCMD variable. Each bit of that value’s binary representation signifies whether a particular bucket should be deployed or not. This is better demonstrated using the following simple script (which can be easily leveraged inside your Post-Deployment scripts):

/* $(DeployData) is a SQLCMD variable that would, if you were using this in SSDT, be declared in the SQLCMD variables section of your 
project file. It should contain a numerical value, defaulted to 0.
 
In this example I have declared it using a :setvar statement. Test the affect of different values
by changing the :setvar statement accordingly.
 
Examples:
:setvar DeployData 1   will deploy bucket 1
:setvar DeployData 2   will deploy bucket 2 
:setvar DeployData 3   will deploy buckets 1 & 2
:setvar DeployData 6   will deploy buckets 2 & 3
:setvar DeployData 31  will deploy buckets 1, 2, 3, 4 & 5
*/
:setvar DeployData 0
DECLARE  @bitmask VARBINARY(MAX) = CONVERT(VARBINARY,$(DeployData));
IF (@bitmask & 1 = 1)
BEGIN     PRINT 'Bucket 1 insertions';
END
IF (@bitmask & 2 = 2)
BEGIN     PRINT 'Bucket 2 insertions';
END
IF (@bitmask & 4 = 4)
BEGIN     PRINT 'Bucket 3 insertions';
END
IF (@bitmask & 8 = 8)
BEGIN     PRINT 'Bucket 4 insertions';
END
IF (@bitmask & 16 = 16)
BEGIN     PRINT 'Bucket 5 insertions';
END

An example of running this using DeployData=6

The binary representation of 6 is 110. The second and third significant bits of that binary number are set to 1 and hence buckets 2 and 3 are “activated”.

Hope that makes sense and is useful to some of you!

@Jamiet

P.S. I used the awesome HTML Copy feature of Visual Studio’s Productivity Power Tools in order to format the T-SQL code above for this blog post.