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:

• Find non-sargable isnull predicates and re-writes them so that they are sargable
• Find the old style of inequality operator != and re-writes them to <>
• Find order by statements that use ordinals rather than column names and replace them with column names
• If you use the query costs highlighter it can show the actual cost rather than just highlighting the text

What does it look like?

What you see here is t-sql clippy has examined each statement, come up with a list of things it could do to automatically re-write the code and put a little circle in the margin with the number of things it can do to each statement. If you right click on the circle you see this little menu that lets you change each individual thing or all of the different things in each category. You can see in this example that it can change some != into <>’s, re-write some isnulls so that they are sargable and replace ordinal column numbers in an order by with the column names.

How to enable it?

To enable it there is a tools menu “Tools–>SSDT Dev Pack–>Enable T-Sql Clippy” - it has a check box that shows you whether or not it is enabled. It is also possible to enable it via a config file so that it is always on, ping me if you want the details.

Performance

I am basically paranoid about slowing down visual studio so I think I have struck a good balance between showing suggestions in a timely manor and not killing performance, the long and the short of it is (this has been through lots of cycles in my brain) is that it shouldn’t slow down using visual studio but as a trade off if you do not see the suggestions you need to scroll the text out of view and back again or press page up (this isn’t my idea it is basically how the add-in for visual studio works) - If people find it useful then I will likely work on making it more responsive whilst not sapping the performance but it isn’t straight forward.

If you have a super fast box or a super slow box we can control how reactive it is by using the config file %UserProfile%\SSDTDevPack\Config.xml adding something like:

true 500

Change the CallDelayMilliSeconds to something low if you have a fast box and something high if you have a slow box.

there are some basic things that it does do to help such as ignore all types of files except for SSDT code files so if you do something like add a text file then it probably won’t show you any suggestions.

How to get it?

It is now published on the Visual Studio Gallery so head on over to https://visualstudiogallery.msdn.microsoft.com/435e7238-0e64-4667-8980-5… and grab it from there.

Any questions please shout!

---

Comments:

Markus

December 14, 2015 - 13:57

Clippy

Hi, I tried Clippy. I think it’s helpful. It makes suggestions that the default t-sql rulesets don’t. However I think it’s not comfortable enough as long one has to scroll down and up again in order to see the hints. If it only works this way, I can’t see anyone use it, simply because they do not see it. Nobody will get used to scroll down and up on each and every codefile he /she creates. So if this behaviour could be improved then I think it’s very useful.
On the other hand - if you could put your rules and checks together in a custom Code Analysis ruleset you share, then it might have the same benefit with out of the box functionality, right?
Regards, Markus

Ed Elliott

December 17, 2015 - 08:16

Hi Markus, Yes I agree with

Hi Markus, Yes I agree with you it needs work on when it displays - what I have today is hopefully a good trade off between perf and usability but I will definitely work on improving it :)

The code analysis rulesets is a good idea.

ed

Danae Lopez

January 25, 2017 - 11:43

T-Sql Clippy not working

Hi Ed, I am trying out SSDT Dev Pack and when I enable T-Sql Clippy I can’t see any recommendations even when I have added some queries to my script that should trigger them. Eg. Select * from table where column != ’’ or Select col1, col2 from table order by 1,2.
T-SQLClippy seems to be running as I can see a circle displaying that 2 recommendations are available but those recommendation are:
When is it beer time?
When is it lunch time?

Any idea what the issue might be?

I have downloaded the add on from the VS gallery.

Regards,

Danae

Ed Elliott

February 10, 2017 - 16:06

whoops that shouldn’t happen!

whoops that shouldn’t happen! If you raise an issue on github and include some t-sql that demo’s it i’ll take a look.