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. I am particularly pleased about the ALM Rangers as I have been thinking about who is actually interested in better development practices and automated deployments for SQL Server and whereas I did originally think it would be SQL developers, I feel that the majority of SQL devs and especially DBA’s are more focused on other things than improving their working practices so this is hopefully a way to help more people write and deploy code for SQL Server and databases in general :)

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. What I mean by deployment utility is that we can take our code and deploy it to a SQL Server (hopefully that is fairly obvious).

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. The overall process is:

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.

Update new download location is https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5…

In-line TVF’s are awesome, they are a great way to reuse T-SQL code without the performance penalties of multi line TVF’s or horror of horrors Scalar Functions. Taking select statements and turning them into in-line TVF’s is actually really really simple using the TSql ScriptDom so here is another tool for the SSDT Dev Pack that lets you highlight a select statement in SSDT and generate a TVF and replace the statement with a call to the TVF.