1

Is there really any difference between these two queries?

SELECT TOP 1000 [COL_1], [COL_2], [...], [COL_n] FROM [database].[schema].[table]
SELECT TOP 1000 * FROM [database].[schema].[table]

Obviously, I will create future problems if I write code like that, but if I really need to see all the columns in my ad-hoc SSMS query window, does it matter? I tried comparing query plans, but saw no differences between the two.

Edit: Make N's match since SELECT 1 vs SELECT 1000 was not the point of this question. Also, the suggested post briefly mentions using SELECT *:

I only use it with ad-hoc scrap queries, typically when learning the schema of an object

But that question is focused on enduring code. My question's scope is restricted to ad-hoc statements that will not outlive the current SSMS session.

4
  • 1
    As with so many things in technology, “it depends”. I regularly see SELECT * queries biting colleagues who join multiple query sets with UNION ALL statements in stored procedures and views because they “forget” to update a myriad of objects after an ALTER TABLE. If the project (or query) is kept really simple, then it’s largely a non-issue to use an asterisk to save time with a TOP 1. That said, I don’t use asterisks with production code because my mentors over the years really did not like to see it and I’ve learned that specificity results in fewer bugs. Your mileage may vary 😬
    – matigo
    Oct 6 '21 at 14:16
  • Related
    – i-one
    Oct 6 '21 at 14:36
  • 4
    Does this answer your question? What's a good use case for SELECT * in production code?
    – mustaccio
    Oct 6 '21 at 15:55
  • Using select * in a single table derived table (such as one with window functions) is about the only place I would use it Oct 6 '21 at 21:47
3

in my ad-hoc SSMS query window, does it matter?

In this context no, it doesn't matter. But not using SELECT * is a good habit to get into anyway, to reduce the temptation to lazily copy such queries into code destined for production.

For the avoidance of doubt: while it is perfectly acceptable for quick inspection/diagnostics via SSMS or similar tools, for reasons well documented elsewhere you should avoid SELECT * where possible in production code.

This suggests a significant exception to “SELECT * is fine in SSMS”: if you are experimenting with TSQL that will end up becoming permanent in code elsewhere, you need to test with the non-SELECT * version anyway as you might hit an edge case that makes unexpected plan/performance differences between the two, so you are better off starting without SELECT * rather than potentially making more work for yourself later.

I tried comparing query plans, but saw no differences between the two.

If you are selecting out all the columns then SELECT * will expand out into the same query anyway, so the plans will always be the same.

1

Well, other than the fact that the top query will return 1000 rows while the bottom will return only 1 (I assume that's not your question), no, I wouldn't expect to see a performance or query plan difference between the two in most normal circumstances, especially for ad-hoc SSMS queries like you describe.

The clear exception is when you don't actually need all the columns; if you only select a few specific columns, not only will you be retrieving less data, but SQL can make better query plan choices, and possibly use more efficient indexes, based on what you're actually going to use.

But I definitely would steer away from SELECT * in production application code and stored procedures, mostly for reasons other than performance:

  • Queries that use SELECT * can break when columns are added or removed from a table.
  • Queries that use SELECT * are making assumptions as to the column order that will be returned.
  • As @matigo mentioned in comments, UNION statements are especially vulnerable to changes in column number or order

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.