T-SQL Clippy

Ed Elliott, 2015-12-10

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?

the clippy awesomeish action

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:

<?xml version=”1.0″ encoding=”utf-16″?>

<Settings xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema“>

<Clippy>

<StartEnabled>true</StartEnabled>

<CallDelayMilliSeconds>500</CallDelayMilliSeconds>

</Clippy>

</Settings>

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!

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads