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.

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.

New ScriptDom visualizer now also shows the tokens that are found in a sql script. It also highlights the part of the query that is covered by the token so it is really easy to see where you are in the script. Honestly if you are doing anything with the T-SQL Script Dom this is really useful to help reverse engineer t-sql into ScriptDom objects :) Grab it from: https://github.com/GoEddie/ScriptDomVisualizer/tree/master/release

Update new download location is https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5… Bert likes his T-SQL keywords in UPPERCASE, Ernie likes his in lowercase which causes some internal struggle as it is actually one person “Mr Bert Ernie” anyway I digress, whether you are working by yourself or in a team you sometimes want to change the case of all the keywords in some code. If you are using SSDT you can use my new SSDT-DevPack tool to do it for you:

SSDT is great but one thing that is not so great is the performance of the publish. I am not complaining, I understand it does a lot and is well worth the wait when publishing however, when you are writing and testing code having this process really kills productivity: 1. Write code 2. Build projects 3. Publish projects 4. Wait for publish to complete 5. Check publish messages for errors 6.

The ScriptDom is cool, it lets you parse T-SQL, play around with the AST (tree of statements) and then generate T-SQL again (probably in a different format etc). This is really cool but if you round-trip the T-SQL into an AST and back you lose some important information like comments so, if you do something like: var original = @"create procedure [dbo].[get_employee](@employee_id int) as --ssssss select 100; select * from a a1 where a1.

When developing stored procedures in SSDT it is important to know when you have written a query that is potentially slow or has a high IO or CPU cost but there is no real way other than examining the the tables you are querying and understanding the data volumes and indexes whether they could be a problem. The idea of this new tool is to highlight in either yellow or red (yellow is quite expensive, red is really expensive) any statements that have a high cost.