23 October 2017
23 October 2017

A day in the life of a developer with SQL Prompt

I’ve a a busy day ahead of me, working on a new reporting query, plus reviewing some initial database designs for the team. We’ve done the morning stand-up meeting, I’ve had my morning coffee, SQL Server Management Studio and SQL Prompt are open, and I’m ready to start work!

10.00AM – Prompt Tab History

I need to finish off a ‘customer orders’ reporting query that I started last night, before getting distracted. Hmm, I can’t find it among my query tabs in SSMS, and SQL Prompt automatically restores any tabs I left open last time. Did I accidentally close it without saving it? Never mind, I retrieve it quickly using the Search function in SQL Prompt’s Closed Tab History:

One click to reopen it, and I can restart exactly where I left off

10.15AM – Prompt Code completion

The reporting query needs to show order totals from customers, broken down by order delivery location. I’ve almost forgotten what it’s like to have to fill in table names and join conditions without “Prompting”. I’ve even configured SQL Prompt to auto-alias the tables:


Next, I’ll sort out that SELECT *. I only need a few of the columns for this report, not all of them. It’s easy enough using SQL Prompt’s column picker, or I could use Prompts Expand wildcard feature, just by pressing the Tab keyboard button and then deleting the unnecessary columns. However, since I only want 3 columns (out of more than 50), I’ll just go old school and just drag them in from SSMS Object Explorer.

I need the total quantity ordered, by customer delivery postcode, and I also need the year the customer opened their account with us. SQL Prompt even lets me automatically fill in the GROUP BY clause to include all non-aggregated columns :

Add an ORDER BY clause and it looks like I’ve got the first-cut finished:

11.00AM – Prompt Actions

I don’t like those unqualified column names in the SELECT list, nor their ugly square brackets. No problem. I just highlight the code, open the SQL Prompt Actions list, and run the Qualify object names action. This both qualifies the columns to their parent table, and removes the square brackets.


While I’m at it, I also run the Insert semicolons action.

11.15AM – A Prompt custom SQL style

That’s the skeleton of the query in place, but the style is all wrong. Fortunately, I’ve already defined my own custom SQL formatting style (Phil Factor shared his custom style with me, and I adapted it)

A couple of quick tap on the keyboard (Ctrl K, Ctrl Y) later, and the code looks just as I like.

11.30AM – A Prompt Snippet

I need to run a few tests locally to make sure everything looks good. Our team use a simple ‘timings’ test harness to get for execution times for SQL batches, procedures and functions, and write the times to a table. We’ve saved this test harness as a SQL Prompt Snippet, so I highlight the code that I want to test, and call up the timings snippet from the Actions list.

SQL Prompt injects my selected code into the timings snippet. I fill in the value for the $routine$ placeholder, and run the code.

I’m happy with that, so now I just need to submit the script to version control. Before I do though, I remember that my tech lead, while she’s happy for us to work in our own styles, wants us all to use a standard SQL format for version control. I switch the Prompt Active Style back to the team style, and then commit the changes.

A good morning’s work – time for lunch!

1.30PM – Prompt database refactoring

A colleague has asked me to review the initial design of the Name-and-address tables for our customer tracking application.

I can see a few problems with this design. The first and simplest thing I want to do is rename the Customers table to Person, since the latter matches the naming of the Primary Key, and complies better with our table naming conventions.

If I use SQL Prompt’s Smart Rename refactoring, it will detect any dependent objects affected by the change. In this case, it’s detected that it needs to drop and recreate the Foreign Key in the Address table and alter the SelectCustomers stored procedure.

SQL Prompt generates a script that makes all the required changes safely, within a transaction. Much faster and less messy this way, rather than making the changes individually, and hoping you catch all the dependencies. I’ve had to use Prompt’s Find Invalid Objects feature once or twice, to get out of that corner.

There are other aspects of this design that need work, and best to do them now before the web of dependencies grow.For example, the design incorporates a TypeOfAddress column, correctly recognizing that a person might have more than one type of address (work address, shipping address, home address), but it doesn’t give us an easy, maintainable way to control the acceptable values for this field. If we moved the TypeOfAddress column really into its own table, then stocking a dropdown on the web form would simply require a reference to the table.

Also, the design doesn’t seem to account for the possibility that two customers might have the same address, or that one customer could sell a place to another customer. Considering all this, I think the design should look more like the following:

Hmm, it looks like a big refactoring job, but if I use SQL Prompt’s Split table refactoring to help us on our way, it might not be too bad. As a first step, I’ll split the Address table, copying over the Address_id column as a FOREIGN KEY, and moving the other required columns, into a new Abode table:

Again, SQL Prompt will generate a transactional script to make all the required changes, and offers warnings if it can’t automatically preserve data.

In this case, the script does migrate the data correctly, since it copies the data over from the columns we’re moving before dropping them. Of course, I always want to check and fine-tune these auto-generated scripts, when data is involved. There are just too many possibilities with a table script for a tool to get it 100% right every time. In this case, I need to adjust the script to add a new Primary Key (Abode_id) to the Abode table and make a few other tweaks, but in double quick time, I’ve arrived at a script that successfully splits the table!

I use Prompt’s Split Table refactoring in the same way to generate a script to copy the TypeOfAddress column from Abode into a new AddressType table.

Phew, I’ve earned a cup of tea! All I need to do now is run some tests to make sure it all works!

3.30PM – The SQL Prompt Safety net: execution warnings, tab coloring

It’s been a busy day. I run a bunch of unit tests, so that I can verify that my refactoring scripts works exactly as intended, and preserve all the data correctly.

Everything is looking OK, but I just want to run a complete set of before- and after- tests. I start from the original two tables, Person and Address, fill them with standard test data sets, then run our suite of unit tests and performance tests.

Now, I’m going to reset the data, run the table refactoring scripts, and then re-run the same unit and performance tests. SQL Prompt throws up an execution warning, because I’m issuing a DELETE with no WHERE clause, which will wipe all the data in the table.

In this case, it’ OK, though instinctively I glance up to my Prompt tab coloring to make sure I’m connected to my local development instance (green) and not the shared test instance (brown); I’ve made that mistake once before, and disrupted a lot of tests; the team have only just stopped reminding me about it!

Reassured, I reload the tables with fresh data then run the table splits.

All the unit tests pass, both before and after, and I can’t see any regressions on query performance, when running the sample workload. I generate the new set off DDL scripts for all the tables, and commit them to version control along with the migration scripts that I used.

A productive day, thanks to SQL Prompt.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    How you can improve your SQL with code analysis in SQL Prompt

    What is code analysis? Code analysis is a formal automated process of scanning a piece of software code and deducing potential problems, issues and faults that may not be apparent to programmers at first glance. These could include mistakes that are easy to make for but hard to detect (such as copying and pasting something

  • Article

    Driving up database coding standards using SQL Prompt

    Most of us in the data management industry will have learned to adapt, in recent years, to ‘agile’ development and deployment practices. Many organizations have invested heavily in the tools and processes they hope will allow them to deliver new functionality to users more frequently and reliably, while also maintaining quality standards. To achieve this,

  • Article

    Quickly rename variables all at once with SQL Prompt

    You’re going to run into situations where someone has made poor choices in naming objects, or maybe the business logic has changed and the object name no longer makes sense. Either way, easily and quickly taking care of renaming the objects within scripts can be a pain. Let’s take this code as an example: The

  • Article

    SQL Prompt Hidden Gems: The SSMS Results Pane

    Within SSMS, when executing a query, we can view the query results in a grid, or tabular, format (Ctrl-D), as plain text (Ctrl-T), or we can write them to a plain-text reporting (.rpt) file using Ctrl-Shift-F. However, over my 20 plus years as a SQL programmer, I’ve often needed to take these results and use

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly