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.
SELECT *
queries biting colleagues who join multiple query sets withUNION ALL
statements in stored procedures and views because they “forget” to update a myriad of objects after anALTER 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 aTOP 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 😬select *
in a single table derived table (such as one with window functions) is about the only place I would use it