30 January 2017
30 January 2017

Quick SQL Prompt tip – picking the columns you need for speedy results

One of the poor practices that I see so many people doing in code is using SELECT *. This is poor practice because it takes more time to complete for clients, it’s an unnecessary use of resources on the client, server and network, and it can fill the buffer cache with rarely-used data.

For example, if someone wants the title of a document and the product it goes with, I’ll see queries like this:

sql-prompt-column-tip-1

There are a lot of fields in these tables, and most aren’t used. I can see this if I use the Expand Wildcards feature of SQL Prompt, which is what some people will do. Or they’ll drag all the columns from the Object Explorer over and use them in this query. That results in more columns than can even fit on a screen:

sql-prompt-column-tip-2

SQL Prompt can help me fix this with the column picker. Rather than get all fields from all tables, I can remove the * and start typing a character.

Let’s say I know I want the Title. If I type t, I get this:

sql-prompt-column-tip-3

While I can certainly hit TAB to get Title, I may want more fields. Notice the tool tip at the bottom; CTRL+Left Arrow will give me the column picker. When I type that combination I get this:

sql-prompt-column-tip-4

Now I can specifically get a few columns by checking them. Using the down arrow, I can move through the columns and use the spacebar to check or uncheck them, as I’ve done below:

sql-prompt-column-tip-5

Now I have just the columns I need added to the query. When I type TAB, only those items selected will appear.

sql-prompt-column-tip-6

If you’ve got wide tables, with lots of columns, this is a quick way to easily get a few columns into your query without removing your hands from the keyboard.

This is another way in which SQL Prompt can really speed up your coding. I’d encourage you to try this out the next time you’re writing a query with a number of joins and a large number of columns in the tables.

I cover the column picker functionality and more in the new series of #SuperSQLTips videos. Take a look and you’ll find handy tricks from other Data Platform MVPs and SQL Server experts help you write SQL more quickly and accurately:

If you’re not a SQL Prompt user, download a free trial and see how much more productive you can be writing code every day.

Like this? Share it with other SQL professionals using the social sharing buttons below. Simply tweet: I’ve just read @way0utwest’s blog on using the column picker with #SQLPrompt – see the #SuperSQLTip at http://rd.gt/2jUVjau.

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