BACKGROUND IMAGE: iSTOCK/GETTY IMAGES
The autocomplete feature in development tools has been around for longer than a decade.It’s like a dishwasher --...
once you have it, you can’t go back. This feature predicts a word or phrase users are entering into a field and displays a list of available objects before they’ve finished typing. In development environments, once you select an object, the tool shows a list of available methods and properties
Naturally, database professionals wanted a similar tool for database coding. A third-party tool for SQL Server, SQL Prompt from Red Gate Software Ltd. was the first -- and packed with useful features, it remains the best. After years of development, Microsoft finally rolled out its own tool, called IntelliSense, in SQL Server Management Studio (SSMS) 2008, but its functionality is fairly limited. Though SQL Prompt and SSMS IntelliSense do essentially the same job, each has a markedly different look and feel.
IntelliSense in SSMS first appeared in the beta of SQL Server 2005, but was removed when that version of the database was released to manufacturing. It was finally introduced in SQL Server 2008, but many users were disappointed to discover that it didn’t work against SQL Server 2000 and 2005 databases. The feature allows you to hit Ctrl + space bar or Ctrl + J while typing to display available objects -- databases, schemas, roles, tables and other objects. If you are working with tables, it’s best to first select the schema; this will filter out all that's not relevant and display just the tables belonging to the schema, as in Figure 1.
Figure 1. IntelliSense displays a list of tables belonging to the Person schema.
Once you select the table, you can go back and delete *, hit Ctrl + J and start selecting columns. Unfortunately, you get many SQL functions and also columns from other tables in the list. I find it easier to create an alias for the table, and then type the alias. IntelliSense will only display columns in the table you are working with, as shown in Figure 2.
Figure 2. The SELECT statement creates an alias called “ad” for the Person.Address table. IntelliSense detects that and when you type “ad.”, it shows a list of columns in the Person.Address table.
But once it gets to this point, the tool is less useful. IntelliSense doesn’t provide a way to filter the members of the list, and sometimes it doesn’t display anything at all.
I have no doubt that Microsoft will improve the tool in future versions, but right now it’s lacking advanced features and support for older platforms that we need.
Now let’s take a look at the third-party tool for SQL Server, Red Gate’s SQL Prompt, now in version 5. The Standard Edition provides powerful and easy-to-use code completion features, and the Professional Edition has many code-formatting and code-refactoring features. These high-end features were previously in a discontinued product called SQL Refactor.
SQL Prompt is not a separate tool; it’s actually an add-on for SSMS. When you connect to a new database, SQL Prompt reads object metadata from the database and caches them -- you’ll see a small pop-up window in the bottom-right corner. SQL Prompt can take from several seconds to several minutes to read the database, depending on the network latency and the number of objects stored.
Once you start typing code, SQL Prompt keeps monitoring and pops up dialog boxes with possible selections, as shown in Figure 3.
Figure 3. The SQL Prompt dialog box shows a list of tables on the left and list of columns and their data types for the selected table on the right.
In the SELECT statement in Figure 3 you get the list of tables in the database and column details about the table. Once you select the table, move the cursor to the right side of the asterisk. A message tells you to “Press TAB to expand wildcard.” Once you hit the Tab key, the SELECT statement gets changed to this:
SELECT AddressID ,
This is very useful; it is considered a good practice to list columns explicitly rather than use a wildcard. If you don’t want all columns, remove the ones you don’t want. If instead you just want to select a few columns, delete the * wildcard and hit Ctrl + space bar. SQL Prompt shows you a list of columns in the table. You can keep selecting the ones you want and hit Enter each time or click on the Column Picker tab and select them individually, as in Figure 4.
Figure 4. After selecting the Column Picker tab, select what columns should be included in the query.
Another useful and time-saving feature is code snippets. These snippets are shortcuts that, when selected, get expanded into a predefined chunk of code. For example, when you type “ssf”, you get a notification that says “Press TAB to insert snippet”. Once you hit the Tab key, “ssf” gets replaced with “SELECT * FROM”. SQL Prompt comes with a few dozen snippets; modify them or add your own for a quick way of injecting code you frequently type. For example, I defined a snippet to inject SET NOCOUNT ON when I type “snc”, as in Figure 5.
Figure 5. After I type “snc,” SQL Prompt recognizes that it’s a snippet, and it displays the script attached to the snc keyboard shortcut.
I also like that the pop-up dialog box groups object by type rather than listing everything in alphabetical order like IntelliSense does. Moreover, it allows you to switch from All Suggestions into a filtered list and shows only tables or views or function, for example. In addition to the above-mentioned features, the Standard Edition has many other features such as Find Invalid Objects, Search, or Column Dependencies.
Professional Edition adds a few extra features. One of the more useful ones is Smart Rename. Suppose you want to change the name of a table or a column in SSMS. The change itself is easy, but more often than not you need to first drop dependent objects, such as indexes or foreign keys, and once you make the change, you need to identify and modify all stored procedures and views that reference the changed object and modify them to use the new name. The Smart Rename feature does all the heavy lifting for you. Just right-click a table or column, select Smart Rename, and SQL Prompt will analyze database dependencies and generate scripts for the name change and also script updates of the dependent objects to use the new name. If you do a lot of name changing, this feature can be a huge time saver.
Another useful feature in the Professional Edition is Format SQL. Of course, properly formatted code is much easier to work with. But often we end up reading legacy code written by somebody else, and that can be hard to follow. SQL Prompt makes code formatting just a matter of seconds. It comes with a default formatting style, but you can modify many formatting options and then save them into custom styles. The following screenshot shows the uspGetManagerEmployees stored procedure from the Microsoft’s AdventureWorks sample database, as shown in Figure 6.
Figure 6. A stored procedure code is shown before SQL Prompt formatting was applied.
The code is not formatted well, and it is not easy to read. With SQL Prompt, hit Ctrl + K, Ctrl +Y and the code is neatly formatted, as shown in Figure 7.
Figure 7. The same stored procedure from Figure 6 is shown after SQL Prompt formatting was applied.
As you can see, the code is now much easier to read and understand.
SQL Prompt costs $195 for Standard and $295 for Professional Edition. It does get cheaper per developer in a five-pack bundle and even cheaper in a 10-pack bundle. Another option is to look into SQL Prompt Bundle and SQL Toolbelt from Red Gate. Each option gets you SQL Prompt with additional software programs at a significant discount.
So how can you justify purchasing this third-party tool for SQL Server to the higher-ups? SQL Prompt is all about saving you time, reducing the boring and repetitive part of coding, as well as making it easier to read existing database code. Download the 14-day trial, use it and try out all the features. Once you get a feel for how much time it will save you and how much easier it will be to work with SQL code, you should be able to build a solid case and convince management to purchase this tool.
That’s not to completely discount IntelliSense. The free tool can still be useful, even with its fewer features; but again, only on SQL Server 2008. Hopefully, we will see some improvements in the next release of SSMS. And even if you have SQL Prompt, I would still recommend that you spend some time using IntelliSense and learning how it works. You will most likely be in situations in which you’ll work on computers that do not have this third-party tool for SQL Server installed.
ABOUT THE AUTHOR
Roman Rehak is principal database architect at MyWebGrocer in Colchester, Vt. He specializes in SQL Server development, database performance tuning, ADO.NET and writing database tools.