There are a lot actions you can take that do more harm than good in SQL Server (did somebody say shrinking your database files every day?) that don’t get the same attention. Still, SELECT * can cause headaches and we’re going to take a look at some of those headaches today.
If you’re investigating a table to get an idea of the columns, data, or pulling data for a report, running a SELECT * query probably isn’t going to be the end of the world. If your SQL server is under enough stress that running one of these queries in SSMS takes the server down, I would guess that there are other problems going on.
If you’re just getting a feel for a table and don’t want to click into the Database name > Tables folder > Table > Columns:
At least try to only run something like SELECT TOP 10 * or a query along those lines. I’ve had my moments where I ran a SELECT * to scan data in a table before deciding to run another query a few minutes later…but couldn’t. I’m pressing F5 but the results window isn’t changing. Why not? Because minutes later, the original SELECT * query was still running even though I had stopped paying attention long ago. Apologize to your SQL Server and try not to do that again.
SELECT * Wastes Resources
CPU, memory, and network traffic is going to take an unnecessary hit if you’re using SELECT * when it’s not needed. If you decide to make a peanut butter and jelly sandwich, do you also take out the pasta and steak and put that on the counter, too? Do you turn on the stove and fire up the grill? Of course not. That would be a waste of time and energy.
Using the StackOverflow2013 for our example, let’s say we want to see every Id, DisplayName, and CreationDate from the Users table. Let’s SET STATISTICS IO ON, create ourselves an index, and run a couple of queries:
SET STATISTICS IO ON;
GO
CREATE INDEX IX_CreationDate_DisplayName ON Users (
CreationDate
,DisplayName
);
GO
If we run SELECT * first, we’ll get the following:
SELECT * FROM Users;
GO
SQL Server Won’t Read Your Mind
One big table scan. SQL Server had no use for the index we created because it cared more about gathering up the whole table. Makes sense because that’s what the query said even though in our minds we didn’t care about most of the information.
If the unnecessary columns being returned are small then this isn’t as big of a deal. On the other hand, the Users table has an AboutMe column that is an nvarchar(max) type. There’s potential for that to end up being an enormous amount of data to return. Not fun.
What happens if we specify the only columns we care about?
SELECT Id, CreationDate, DisplayName FROM Users;
GO
Much better. SQL Server is able to use the index we created, we have less logical reads, and the query finishes in half the time. Even if it’s a one off query you’re running versus writing a stored procedure that’s going to be reused, it’s better to not be lazy and make your query specific.
Written in the Stars
Using SELECT * is going to make it hard on SQL Server due to the resource load it causes as well as the inability to utilize indexes that may be available. It’s worth getting into the habit of explicitly noting the columns you want and not querying for data you don’t need. SQL Server performance will be better and your scripts will be easier to read.
Thanks for reading!
Chad Callihan 