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:
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 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:
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:
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:
Now I have just the columns I need added to the query. When I type TAB, only those items selected will appear.
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
You may also like
-
Article
Quickly rename variables all at once with SQL Prompt
Grant Fritchey demonstrates a quick and easy way to rename all variables in a SQL script.
-
Article
Enhancing your database development using coding standards
Updated June 2020 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
-
Article
How to Compare Two SQL Prompt Styles
A PowerShell function that will compare two SQL formatting styles, saved in JSON, and produce a report showing the differences between the options they use to lay out your SQL code.
-
Article
Quick SQL Prompt tip – script objects as ALTER in two clicks
Working in a large database can be difficult at times. While many of us might learn the meanings and definitions of most objects, it’s easy to forget the exact ways in which some objects work, or what the behavior is in certain calls. This is one place where having tools that assist you like SQL
-
