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 stand-up meeting, I’ve had my morning coffee. SQL Server Management Studio and SQL Prompt are open. 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.
-- Total orders, by PostCode USE WideWorldImporters GO SELECT [Quantity], [AccountOpenedDate], [DeliveryPostalCode] FROM Sales.OrderLines ol INNER JOIN Sales.Orders o ON o.OrderID = ol.OrderID INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID
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:
-- Total orders, by PostCode USE WideWorldImporters GO SELECT SUM([Quantity]) AS TotalQuantity, YEAR([AccountOpenedDate]) AS AccountOpenedYear, [DeliveryPostalCode] FROM Sales.OrderLines ol INNER JOIN Sales.Orders o ON o.OrderID = ol.OrderID INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID GROUP BY YEAR([AccountOpenedDate]), c.DeliveryPostalCode ORDER BY AccountOpenedYear, TotalQuantity DESC
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.
-- Total orders, by PostCode USE WideWorldImporters; GO SELECT SUM(ol.Quantity) AS TotalQuantity, YEAR(c.AccountOpenedDate) AS AccountOpenedYear, c.DeliveryPostalCode FROM Sales.OrderLines ol INNER JOIN Sales.Orders o ON o.OrderID = ol.OrderID INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID GROUP BY YEAR(c.AccountOpenedDate), c.DeliveryPostalCode ORDER BY AccountOpenedYear, TotalQuantity DESC;
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 taps on the keyboard (Ctrl K, Ctrl Y) later, and the code looks just as I like.
-- Total orders, by PostCode USE WideWorldImporters; GO SELECT Sum(ol.Quantity) AS TotalQuantity, Year(c.AccountOpenedDate) AS AccountOpenedYear, c.DeliveryPostalCode FROM Sales.OrderLines ol INNER JOIN Sales.Orders o ON o.OrderID = ol.OrderID INNER JOIN Sales.Customers c ON c.CustomerID = o.CustomerID GROUP BY Year(c.AccountOpenedDate), c.DeliveryPostalCode ORDER BY AccountOpenedYear, TotalQuantity DESC;
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.