How to Put SQL Column Names Onto Multiple Lines in SSMS

Published on: 2017-06-27

SQL in 60 Seconds #1

A few keystrokes and BAM! A mostly formatted query

SQL in 60 Seconds is a series where I share SQL tips and tricks that you can learn and start using in less than a minute.

Have you ever copied and pasted a query into SQL Server Management Studio and been annoyed that the list of column names in the SELECT statement were all on one line?

There are 30 columns here. Ugh.

You can make the query easier to read by putting each column name onto its own line.

Simply open the Find and Replace window (CTRL + H) and type in ,(:Wh)* for the Find value and ,nt for the Replace value (in some versions of SSMS you may have better luck using ,(:Wh|t| )* in the Find field). Make sure “Use Regular Expressions” is checked and press Replace All:

Make sure the regular expression icon/box is checked
A few keystrokes and BAM! A mostly formatted query

The magic you just used is a Regular Expression, and Microsoft has its own flavor used in SSMS and Visual Studio. Basically, we found text that

  • began with a comma (,)
  • followed by any whitespace (:Wh) (line break, tab, space, etc…)
  • (in newer versions of SSMS we add |t| to indicate or tab or space)
  • and replaced it with a comma (,) and a new line (n) and tab (t).

Sure, this trick isn’t going to give you the same output as if you used a proper SQL formatter, but this technique is free and built straight into SSMS.

 

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

5 Business Problems You Can Solve With Temporal Tables

Published on: 2017-06-20

It’s 4:30 pm on Friday and Mr. Manager comes along to tell you that he needs you to run some important ad-hoc analysis for him.

Previously this meant having to stay late at the office, writing cumbersome queries to extract business information from transactional data.

Lucky for you, you’ve recently started using Temporal Tables in SQL Server ensuring that you’ll be able to answer your boss’s questions and still make it to happy hour for $3 margaritas.

Sound like a plan? Keep reading below!

The Data

For these demos, we’ll be using my imaginary car rental business data. It consists of our temporal table dbo.CarInventory and our history table dbo.CarInventoryHistory:

I’ve upgraded my business — we now have FOUR Chevy Malibus available for you to rent

Business Problem #1 — “Get me current inventory!”

To get our current inventory of rental cars, all we have to do is query the temporal table:

That’s it.

I know this query seems lame — it’s just a SELECT FROM statement. There are no FOR SYSTEM TIME clauses, WHERE statements, and no other interesting T-SQL features.

But that’s the point! Have you ever had to get the “current” rows out of a table that is keeping track of all transactions? I’m sure it involved some GROUP BY statements, some window functions, and more than a few cups of coffee.

Temporal tables automatically manage your transaction history, providing the most current records in one table (dbo.CarInventory) and all of the historical transactions in another (dbo.CarInventoryHistory). No need for complicated queries.

Business Problem #2 — “How many miles on average do our customers drive each of our cars?”

In this example, we use FOR SYSTEM_TIME ALL and a plain old GROUP BY to get the data we need:

Some cars get driven a lot more. Causation or correlation?

FOR SYSTEM_TIME ALL returns all rows from both the temporal and history table. It’s equivalent to:

Once again, there isn’t anything too fancy going on here — but that’s the point. With temporal tables, your data is organized to make analysis easier.

Business Problem #3 — “How many cars do we rent out week over week?”

Here at Wagner Car Rentals we want to figure out how often our cars are being rented and see how those numbers change from week to week.

In this query, we are using FOR SYSTEM_TIME FOR/TO on our temporal table to specify what data we want in our “CurrentWeek” and “PreviousWeek” subqueries.

FOR/TO returns any records that were active during the specified range(BETWEEN/AND does the same thing, but its upper bound datetime2 value is inclusive instead of exclusive).

Business Problem #4 — “What color cars are rented most often?”

We’re thinking of expanding our fleet of rental vehicles and want to purchase cars in the most popular colors so we can keep customers happy (and get more of their business!). How can we tell which color cars get rented most often?

Here we use CONTAINED IN because we want to get precise counts of how many cars were rented and returned in a specific date range (if a car wasn’t returned — stolen, wrecked and totaled, etc… — we don’t want to purchase more of those colors in the future).

Business Problem #5 — “Jerry broke it. FIX IT!”

The computer systems that we use at Wagner Car Rentals are a little…dated.

Instead of scanning a bar code to return a car back into our system, our employees need to manually type in the car details. The problem here is that some employees (like Jerry) can’t type, and often makes typos:

Having inconsistent data makes our reporting much more difficult, but fortunately since we have our temporal table tracking row-level history, we can easily correct Jerry’s typos by pulling the correct values from a previous record:

Typos fixed!

Although we could have fixed this issue without using a temporal table, it shows how having all of the row-level transaction history makes it possible to repair incorrect data in more difficult scenarios. For even hairier situations, you can even roll-back your temporal table data.

Conclusion

Temporal tables are easy to setup and make writing analytical queries a cinch.

Hopefully writing queries against temporal tables will prevent you from having to stay late in the office the next time your manager asks you to run some ad-hoc analysis.

 

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Behind the Scenes of an Online Presentation

Published on: 2017-06-13

Charlie is an attentive audience member

Last week I presented my session “DBAs vs Developers: JSON in SQL Server 2016” at the online GroupBy Conference.

As I prepared for the event, I thought about all of the things that were different about getting ready for an online versus an in-person event.

Thinking that others might be interested in seeing what I do to get ready for an online talk, I filmed myself as I prepared for presentation day and put together this “behind the scenes” video.

Check it out, along with my actual talk on JSON in SQL Server 2016, in the videos below!

Slides and demo code from the presentation is available at https://bertwagner.com/presentations/

And the presentation video itself:

And slide deck:

 

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!