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

Find out more

You may also like

  • Article

    Quick SQL Prompt tip - restore your tabs, even if you closed them by mistake

    It’s sometimes too easy to close SQL Server Management Studio (SSMS) without saving your work. It’s also easy to get rid of tabs and scripts that you later wish you had back. SQL Prompt can help with both these problems. Take a look at the Options for SQL Prompt and you’ll find a set of

  • Article

    Q&A from our recent ‘What’s New in SQL Toolbelt’ webinar

    I recently gave a Redgate University webinar with Chris Kerswell and Thomas Walsh. Giving webinars as a team is terrific, as it shows how we work at Redgate. Chris is a Sales Engineer, Tom is a Software Engineer, and I’m a DevOps Advocate. Behind the scenes, we often work together across these groups to solve

  • Article

    How to implement a T-SQL code formatting standard using SQL Prompt

    To what extent should a team attempt to impose a uniform SQL coding format? It can sometimes seem like a futile task. All programmers have a preferred formatting style, to which they profess an unwavering attachment. Check the code of ten programmers, though, and you will likely discover at least 15 different formats, since few

  • Article

    Enhancing your database development using coding standards

    Development and IT departments face increasing demands to deliver changes faster, often with increasing complexity and less tolerance for downtime, and without a corresponding growth in team size to handle the extra work. Standardizing database development practices by establishing coding standards can help achieve this by removing blockers to understanding code, easing the implementation of

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly