Phil Factor

5 October 2020

• SQL Prompt
• SQL Code Completion and IntelliSense
• SQL Code Analysis

Questions about SQL Prompt you were too shy to ask

1. What’s in SQL Prompt?
2. Why did it take so long for SQL Code editors to feature ‘IntelliSense’?
3. What are ‘ranked suggestions’ and why are they a good idea?
4. Why should I Enable Code Analysis?
5. I need a report of all the Code Analysis issues rather than just having lots of green underlines in the code…
6. What is ‘Auto-fix’?
7. What’s your advice for a team that wants to introduce SQL Prompt?
8. I’m a DBA doing operations work on existing databases…Why would I need SQL Prompt?
9. As a manager, how can I justify the cost of providing SQL Prompt to my dev teams?

What’s in SQL Prompt?

To answer that one succinctly is tricky, just because there is so much. To make a start, here is a map of the overall functionality. To find out more about a particular utility, just take a look at The SQL Prompt Functionality Finder to get the details of how to find it, and what it does.

Why did it take so long for SQL Code editors to feature ‘IntelliSense’?

Intelligent code completion (‘IntelliSense’ is Microsoft’s name for it), took a long time to spread from procedural code to SQL. It’s been common function of the code editor for every integrated development environment for procedural code since Visual Basic and Turbo Pascal were introduced. It is great to have, because nobody wants to have to memorise the descriptions of functions, and their parameter lists. It’s no fun, either, having to refer to syntax diagrams the whole time.

It’s taken a while to come to SQL because it is hard to implement. SQL is different from Pascal or Basic because it isn’t doing the same thing as the typical procedural language: SQL queries just define what the result should be, not how to get it, using a syntax that is as similar as possible to English speech. The procedural details are sorted out ‘behind the scenes’, by the query optimizer. SQL also deals with a dynamic system rather than static code. SQL Code isn’t really linear: the relevance of the choices at some points in a SQL statement are based on what you are intending to type later on in the query or statement as, for example, with the list of columns that you would generally wish to specify directly after the table to which they belong.

What are ‘ranked suggestions’ and why are they a good idea?

Suggestions for code completion are displayed in a list from which you can select. The range of choices at any point in the code can be great, or very restricted. It isn’t simple, and the solution isn’t just to list out every possibility in alphabetical order. That is often information-overload, with the wrong information at the top.

Take an example: at the start of a batch, you’ve got one of fifty-eight keywords that would start a batch. Should you order them alphabetically? You’re likely to choose a SELECT statement, but you could well be starting with USE or a DECLARE. There is an order of probability so maybe you need the frequent stuff at the top of the list so you can just stab at the one you want without scrolling. If you then type in an ‘S’, then it is probably going to be SET or SELECT, though if you want to bet on rank outsiders, there is SAVE, SETUSER or SHUTDOWN.

It’s SELECT that you want, so you type, or pick SELECT * FROM because you’re a bit lost and you haven’t opened the Object Explorer to see what tables and views are available. At this point you want to see a picklist of these types of objects. You could even be selecting from a table-valued function, so your list ought to include those object types next. You might, I suppose, want to specify the schema prefix for the table, and it is possible that you need to specify the database, but we’re beginning to get away from the everyday code. If you want to do more esoteric code, you won’t mind scrolling to get it, because it still saves you time and it prevents typos.

There are several variables at work here that will affect the order in which object names and object types are suggested. There is the skill-level of the user, the type of work they are doing and the database environment they’re using. I’d also add the preferences that the programmer has for the way of working. Do they, for example, prefer alphabetic order or the order of likelihood? How much information overload can they cope with?

SQL Prompt has traditionally sorted the list using a combination of object type and alphabetic sorting. It has improved over the years in the way that it ensures that only relevant suggestions are included. From version 10 onwards, however, SQL Prompt attempts to place the most relevant suggestions at the top of the list of suggestions, using a combination of the most likely object type, how well the object name matches what the user has typed, and what suggestions have been previously used. You can switch the feature on and off. It is called ‘ranked suggestions’.

Why should I Enable Code Analysis? The SQL Code that I write is splendid.

Well, so is mine but only on a good day, and not when I’m under pressure or I’m trying to fix something else. I might as well confess: not even someone who codes SQL all day can remember the broad sweep of all the code smells, deprecated syntax and so on. I’ve written a lot of SQL over the years, but I still keep “SQL Code Analysis” switched on. It points out the SQL “code smells, in the code within the query tab, and I’m surprised how often I see those warnings.

I’ve written previously about the major or the interesting issues that Code Analysis looks for, so I won’t go on about them here but instead refer you to a few explanatory articles:

Best Practice rules

• Avoid using constants in an ORDER BY clause
• Insert Statement Without Column List (BP004)
• The Sins of SELECT * (BP005)
• TOP without ORDER BY in a SELECT statement (BP006)
• Using a Variable-length Datatype Without Explicit Length: The Whys and Wherefores (BP007/8)
• The ‘= NULL’ Mistake and other SQL NULL Heresies
• Why you Should Avoid Using the @@IDENTITY Function
• The risks of using EXECUTE (‘SQL Script’)
• Why you Should Always Specify Whether a Column Accepts Nulls
• Neglecting to Use, or Misusing, the RETURN Keyword (BP016)
• Table does not have clustered index (BP021)
• Avoid use of the MONEY and SMALLMONEY datatypes (BP022)
• The Dangers of using Float or Real Datatypes
• Problems Caused by Use of the SQL_VARIANT Datatype
• The Whys and Wherefores of Untrusted or Disabled Constraints

Performance Rules

• Procedures that lack schema-qualification (PE001)
• When to use the SELECT…INTO statement (PE003)
• A Hint is Used (PE004-7)
• The SET NOCOUNT problem (PE008 and PE009)
• Changing SET options in a Procedure or Trigger leads to Recompilation (PE012)
• Finding Correlated Rows Using EXISTS or COUNT
• Misuse of the scalar user-defined function as a constant (PE017)
• Consider using [NOT] EXISTS instead of [NOT] IN with a subquery (PE019)
• INSERT INTO a permanent table with ORDER BY (PE020)

Style Rules

• Old-style join syntax (ST001)
• avoid non-standard column aliases (ST002 and DEP021)
• avoiding the old-style TOP clause (ST006)
• Choosing Between Table Variables and Temporary Tables (ST011, ST012)

Deprecated syntax rules

• Tracking use of Deprecated SQL Server Features
• Never Create Columns with ANSI_PADDING set to OFF

Execution rules

• Problems with adding NOT NULL columns or making nullable columns NOT NULL (EI028)
• Avoid using the ISNUMERIC Function (E1029)

I need a report of all the Code Analysis issues rather than just having lots of green underlines in the code…

If you click on ‘Show List of Code Analysis Issues’ in the Prompt menu, you’ll see a resizable and dockable list of all issues in your script. It displays all the issues that currently exist in your script on the current query pane, in the order that they appear in the code, ordered by the line number on which the issue was detected. The total count of issues is shown at the top of the window. You can also get to this by hitting ALT Enter, when the idea icon is visible, to get the context menu, and then picking the ‘Show all issues’ option

After you make a change in your script, you can refresh the list by clicking the Refresh button. You can also download the list of issues in the CSV format by clicking the Export button so you can export the list to an application such as Excel.

What is ‘Auto-fix’?

In retrospect, it seems obvious. If SQL Prompt’s code analysis is pointing out that you’ve left SELECT * in your code, why doesn’t it just correct it? After all, SQL Prompt knows the column list. If you have a TOP clause without an ORDER BY, then why not add the ORDER BY? If you specify the numeric or decimal datatype without the precision or scale why doesn’t SQL Prompt add it (18 and 0 are the defaults)?

In fact, SQL Prompt now does these sorts of “auto-fixes” for certain code issues. In cases where an auto-fix is possible, SQL Prompt highlights the lightbulb icon, which appears in the sidebar whenever code analysis spots a problem, with a yellow background. Right-click on it, and you will see a context menu that allows you to do the fix. At the moment, just a small number of code issues have auto-fix, but there will be more.

I get the same urge do the fix when doing a code review. It is often less effort to just change things rather than explain exactly why something is wrong. Luckily, SQL Prompt occasionally does both!

What’s your advice for a team that wants to introduce SQL Prompt?

It works well ‘out of the box’. It depends on the users and how they like to work, and on their role. The most important thing is to learn how to switch features on and off, and to fit the tool to the way that your team likes to work.

It’s worth spending a bit of time familiarizing yourself with the “lie of the land”; I once compared SQL Prompt to Treasure Island because it isn’t always obvious where all the functionality is buried. The problem is that the SQL Prompt team have, for years, reacted to the suggestions of users on UserVoice, and some of the functionality, such as removing the unnecessary square brackets is there because users wanted it rather than because it fitted into a logical menu structure. I like to learn the keystrokes rather than rely on the menu. I’ve documented all the keystrokes I know about here in a wallchart.

Most teams start with the code autocompletion suggestions but, as useful as it is, there is such a thing as “too much IntelliSense”. For the full sensory load, you can have both the SSMS IntelliSense and all the SQL Prompt features all on at once. If you are used to SSMS IntelliSense, then switch over entirely to SQL Prompt, maybe easing yourselves gradually into the advanced features that aren’t in SSMS IntelliSense.

It is worth learning how to manage suggestions; I like to invoke the suggestions box only when I need a suggestion using CTRL <spacebar>. I tend to use the object definition box when I’m particularly perplexed. If you’ve documented your database with extended properties, you’ll be delighted to see that if you mouse-move over any object, you’ll see that object’s documentation. This is wonderful if you are obliged to maintain a database you didn’t write.

Sometimes, you’ll discover the most time-saving features by surprise when you start using IntelliSense. Louis Davidson writes here, in SQL Prompt Hidden Gem: Auto-fill the GROUP BY clause, how useful they can be, but it takes time to find them. It isn’t obvious either how to make the best use of the helper for table aliases and how to alter them once they’ve been added.

After taming IntelliSense, the first thing I did was to spend excessive time fine-tuning the SQL code formatting to exactly how I liked it. It is valuable to reach a team standard. It is amazing how much time that saves.

Next, I’d find how to manage the code analysis rules so that they apply to your setting, and how to share them with members of your team.

I would then start adding snippets, and modifying many of the built-in ones, so that they represent your common repetitive tasks. The built-in snippets are intended to inspire you to adapt them, because they need to fit you like a glove. An ops DBA is going to want very different snippets to a developer, probably involving DMVs and DMFs, or involving access-management DCL code. A developer will probably want examples of such things that generally require the help of Professor Google, such as pesky MERGE statements, OpenJSON tricks, and XML manipulation.

I’m a DBA doing operations work on existing databases, most of which are brought in. Why would I need SQL Prompt? It is a developer’s tool isn’t it?

Well, just let’s take one of the features: snippets. DBAs are great hoarders of useful queries. I’m a big fan of Glenn Berry’s diagnostic information queries. If I were doing production work with SSMS, I’d use SQL Prompt.

I’d kick out the existing snippets and replace them with your own collection of diagnostic queries. The secret is, I believe, to give each one a three/four-letter acronym for a shortcut so that, for example ‘In-memory OLTP Index Usage (Query 76)’ would be ioiu, and Buffer Usage Breakdown (Query 68) might be bub. I have a PowerShell script that will convert a collection of SQL queries to snippets, though the assignment of the snippets takes a few minutes. You can, of course, have several snippet collections though swapping them around is rather a hassle.

If snippets are a hassle for you, then Prompt’s Tab History (a.k.a. Tab Magic) could help. This puts all the tabs you’ve ever used into a database which in prompt 10 is at:

%localappdata%\Red Gate\SQL Prompt x\ SavedTabs.db (where x is the version number).

In my case it is C:\Users\PhilF\AppData\Local\Red Gate\SQL Prompt 10\SavedTabs.db. If you need to move or copy it, all you need to do is to copy that file to the same directory on the new PC and that will move all of your saved tabs across.

There are a few Prompt features that were put in especially for DBAs. Have a look at the context menu on the object browser pane and you’ll see the ‘Tab Color’ menu item that allows you to color code your query panes according to the type of server you’re accessing.

A feature that I reckon is very much geared to DBAs is the facility to save the grid-version of the results pane into Excel. This makes it easier to build up reports, pivot tables and graphs. Most developers would blink in puzzlement as to why you’d ever want to do that.

As a manager, how can I justify the cost of providing SQL Prompt to my dev teams?

I’m not sure if many people buy the idea that it means that you can hire Devs and DBAs with fewer skills. It is more certain that, with Prompt, a competent developer can become useful more quickly with SQL Server development, so it helps the experienced end-to-end developer. Where Prompt really scores is in the hands of the experienced database developer, because it removes many of the chores involved in formatting code and reviewing code.

Each user seems to use SQL Prompt in a slightly different way and to find different ways of saving time, so it is hard to be precise. I find it great for setting up test harnesses, where one must compare the results of a process with a result that you know is correct. Other people find the Tab History to be a lifesaver because it makes it very hard to lose or forget code. Others would never want to be without the snippets. It is an open-ended tool with a lot of ways tucked in there to help productivity.

Another way that SQL Prompt can help is to make it a lot easier for a whole team to adopt the same standards for formatting code and agreeing on what code standards should be adopted. It also gives immediate advantages and feedback to the team when the team documents their code properly. Documentation in extended properties for each database objects results immediately it in showing up in object tooltips when you mouse-move over the name of an object such as a database, schema, table or column. It tells the developer immediately what the object does.