Tag: SQL Prompt

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about a few of the items, and this post continues on with a feature that was added last year to SQL Prompt, Code Analysis.

Code Analysis

One of the big leaps forward for computer science, in my opinion, was the development of various static code analysis (SCA) tools. These are automated programs that examine the structure of source code and look for potential issues with the way the algorithms are implemented. This was originally the job of a fellow programmer, and still is in many cases, but humans make mistakes, and reviewing someone else’s code is a tedious, somewhat boring task. Over time, many humans become worse at it as we look for certain issues, but may ignore others.

Over time, SCA tools have included scanning for potential security issues, such as buffer overruns, which can easily permeate many systems if the developers do not follow coding practices designed to avoid issues. I wish we had such advances in SQL tools, but they’re not here yet.

In SQL Prompt v9, Redgate added some SCA features. These were a set of rules that are used to scan your T-SQL code for potential issues. such as casting data types without specifying a length, or as the Caves of Code Analysis post shows, forgetting to qualify an object. In my example below, the green squiggly line below the code represents an SCA finding, and as I’ve hovered over the line, I see the warning about an old style join.

Each of the rules is designed to highlight some issue that is known to be a potential problem. For the most part, these are useful tools and you should use these to examine your code. Some, however, aren’t useful and can be annoying.

There is a dialog that allows you to enable or disable any of the rules, which are divided into different types. I often disable ST002, which deals with aliases. I prefer the old style equal sign as opposed to the AS syntax. This isn’t a code issue, so I don’t need the warning.

This is a basic set of SCA tooling for T-SQL code, but it does serve to educate newer developers about the dangers of using certain patterns in their code. It also reminds experienced people if they’ve done something like used COUNT() in a test instead of EXISTS(). Those types of changes can often improve the overall quality of your code.

Give SQL Prompt a try today and I’m sure you’ll be pleased with just how quicker you can write code and learn about the potential issues you’ve been including in your code.

I enjoy themes, and when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it. I wrote about Code Snippet Cove recently, and this post continues to move across the map.

Formatting Forest

Walking through a forest can be daunting. Living in Colorado, I’ve had the chance to hike and explore the mountains of the state. If you’re near the top and can view landmarks, it seems easy. Walking through some forests, when you can’t see a peak, you get a little worried about which direction to go without a trail. It’s serious, as people die every year in my state because they get lost in the woods.

Writing T-SQL isn’t a life or death endeavor, but it can be frustrating when we see code that’s formatted in a way that we don’t expect. I’ve seen truly ugly T-SQL code (from my perspective) in the SQLServerCentral forums, and I often need to copy and paste it into SSMS to get a sense of what’s happening. I used to reformat by hand, but CTRL+K,Y is an ingrained SQL Prompt habit that lets me get code into a style that I can work with.

The part of the treasure map that talks about formatting is long and detailed, and there are lots of resources that can help you format the code better.  Likely, however, you’ll want to open the formatting dialog (shown below) and play with settings to get what you want.

One of the best features, at least for me, is that I can quickly switch styles from my own to any other, such as a corporate standard. With a right click, I can choose another style, CTRL+K,Y to get to some other format, and then reverse that later.

I do this before committing to Version Control, as you should. If you haven’t tried SQL Prompt formatting, get an eval and give it a try. It’s amazing. This alone saves me a ton of time when writing code.

In the next post, I’ll continue on to the Caves of Code Analysis.

I’ve been away a bit in the last week with personal and family commitments. As a result, I feel a bit out of touch with work, and I have some catching up to do. However, when I ran across the SQL Prompt Treasure Island, I had to take a few minutes and go through it.

Code Snippet Cove

I love snippets. I first loved the idea in Visual Studio and Query Analyzer a long time ago, but SQL Prompt has taken this to a new level. I’ve used snippets quite often to make life easier and speed up development.

This part of the island talks about all the tokens that are available. There are all sorts, but some of my favorite are using $CURSOR$ to ensure the cursor gets dropped where I want it. This lets me immediately start typing.

I love $DATE$ and $USER$ for pre-populating comment sections of code, and there are plenty more metadata/environment type tokens that you can use, including the $PASTE$ for the clipboard.

While the tokens are great, I think that just the Snippets themselves, with custom placeholders, such as $userrole$, which I use for adding security to object creates, are incredibly handy. I’ve even used partial snippets for joins. For one job, we constantly needed to put together  these tables: Product, ProductCategory, OrderHeader, OrderLine, and Address. We had a snippet that just contained these tables with the appropriate ON clauses for joins.

Explore snippets, and you might never want to work without SQL Prompt again.

In the next post, I’ll take at look at the rest of the map.