RJD SQL

Tuesday, April 10, 2018

I promised myself that I would start blogging about SQL this year, and there's no better place to start then T-SQL Tuesday! Unfortunately I missed that this month the submissions are due on GMT, so I apologize for the rushed nature of this post.

This month's topic is "My Essential SQL Server Tools" and is being hosted by Jens Vestergard.

SQL Prompt is the most crucial tool that I use on a daily basis. I have become almost wholly dependent upon the auto complete, which is leaps and bounds ahead of Intellisense. We have a number of databases with 10s or 100s of thousands of objects, which make Intellisense crawl and eventually crash SSMS(Thanks 32-bit memory limit). SQLPrompts autocomplete doesn't work well in those databases either, but it does allow you to exclude them based on a wild card match, which allows SSMS to proceed uncrashed.

SQL Prompt also contains a feature which allows you to define and apply code formatting templates. The most useful application for this is formatting queries generated by the application, or by a fellow developer whose code doesn't conform to your specific OCD idiosyncrasies. I find this very helpful to increase readability and thus allow for easier debugging. It has a pretty powerful set of options for you to fiddle with to get your preferred code style exactly right.

A final essential feature are the execution warnings that force you to go through a dialog box when executing DELETE or UPDATE statements without a where clause as well as creating or altering a procedure that includes those statements. We've actually mandated that any employee with write access to a production database have SQL prompt installed, and have this feature enabled. It's definitely less annoying to have to click "OK" a few times a week than it would be to restore a production database from backup while client support is breathing down your neck.

It also offers many quality of life improvements such as tab coloring, tab history, and code snippets that are definitely beneficial, but don't quite make it to the "Essential to my day" standard

In the category of free tools that I use (almost) every day there is one standout. It's a frequent occurrence that I'm presented with a query and the question, "Why is this query slow?". Professional courtesy prevents me from responding "So many reasons, that query is an abomination, please make the hurting stop", so I turn to SentryOne Plan Explorer for a less emotional answer. It offers a simple intuitive interface for digging into execution plans and makes the most expensive operations easy to find.

I'm a big believer in collaborative source control, it allows our team to make better design decisions and catch many bugs prior to code going to QA. GitHub pull requests offer an excellent platform for this sort of collaboration. We can easily offer comments on specific lines of code or larger architectural decisions. It also integrates nicely with our project management software, allowing us to easily see WHY we wrote the code that we did. The power of GitHub is matched only by its learning curve. The command line can be more than a little bit intimidating, so SourceTree provides much needed training wheels as one acclimates to the complexities of a DVCS.