Composite projects in SSDT are a really useful way to logically split databases up while still being able to deploy into a database as a whole. Even if you have a fairly simple database in a single SSDT project you should put your unit tests somewhere. What I like to do is have my solution configured like: Main.dbproj - actual production code and reference data Main.UnitTests.dbproj - unit test code + references to tSQLt etc The unit tests have a “Same Database” reference pointing to the main project so it is as if my tests were in the same project.

I have been pretty busy over january and the first half of feburary and haven’t had time to write any blogs, I did have an article on simple talk published: https://www.simple-talk.com/sql/sql-tools/connected-development-with-sql… A couple of exciting things did happen, firstly I was allowed back into the friends of redgate programme - I like the redgate guys and am pleased to be back! Secondly I have been accepted into the Microsoft ALM Ranger programme which means I can help deliver some tooling and documentation as part of a team instead of beavering away by myself.

Code Coverage What is code coverage? When you write some code and then test it, how sure are you that you have tested the whole thing? Code coverage gives you an idea of how well tested a bit of code is. If you have lots of branches in your code (not something I am advocating) it is important to make sure you test it all so we can use code coverage to get an idea of how much of a particular piece of code has been tested (or not).

In the final part of this 3 part series on what SSDT actually is I am going to talk about the documented API. What I mean by documented is that Microsoft have published the specification to it so that it is available to use rather than the documentation is particularly good - I warn you it isn’t great but there are some places to get some help and I will point them out to you.

In part 1 of this series available here I introduced my drawing of what I think SSDT is and talked about the first major category the development ide and how it can help us. Just to recap for each subject on the image I give a bit of blurb and then some links to further reading. In this part (part 2 would you believe) I will give an overview of the second major category which is that it is a deployment utility.

I often get asked the questions “What is SSDT" and I have wanted to have a single reference as to what it is as it is actually pretty big. To be clear I am just looking at the database projects version of SSDT what was originally SSDT rather than the BI tools that came with SSDT-BI and are now being merged with SSDT – Perhaps I will expand this to include those one day but probably not.

You sometimes want to do things like split a table into two or move a column into another table and when you use SSDT or the compare / merge type of deployments it can be hard to migrate the data in a single deploy as you can’t insert the data into a table that doesn’t exist and you can’t drop the data before it has bee migrated. To fix this we can use pre/post deploy scripts in SSDT.

I love writing c# code using visual studio and resharper, it is a really nice experience and resharper does a lot of boiler plate stuff and offers re-writes for you that mean you can concentrate on writing the useful bits of code. T-Sql Clippy is no where near as great as resharper but hopefully starts to show what we could actually do with t-sql and ssdt :). What is it? There are some things that we can do using the ScriptDom and the rest of the DacFx to help people write better t-sql code, so the things it does today is:

“where ISNULL(column, ‘DontMatch’) = ‘Match’” - is kind of a bad thing to do in SQL as it means that any index on column can’t be used. It is a really simple thing that is a waste of time, fixing it is pretty simple but I see it a lot and so I thought it would be a perfect candidate for some SSDT Dev Pack t-sql re-writing :). So the dev pack now has the ability to re-write any non-sargable IsNull statements into ones that are sargable (if you don’t know what sargable means and you are a SQL dev, look it up, it is really important!

Update new download location is https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5… Duplicate indexes, one of the most pointless things you can do to a table that just adds pure overhead. No one likes them so SQL Developers do your DBA’s a favour and stop them from ever being deployed :) This new tool for the SSDT Dev Pack adds a menu item (Tools–>SSDT Dev Pack –> Find Duplicate Indexes) what it does is scan all the projects in the solution (it doesn’t follow “this database” references to examine them, maybe a future version) and then print to the output window a list of all the duplicate indexes based on the table, the columns and included columns - I don’t check anything else so you might actually want a duplicate in some circumstances but these should be very few and far between.