Select * - Why or Why Not?

Question

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.

Answer 1

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.

Answer 2

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