Hypothetical performance makes it possible not to use SELECT *

Question

In the introduction, I know (like you!) That SELECT *production use is bad, but I supported a script written by someone else. And, I also know that this question is low in specificity ... But a hypothetical scenario.

Let's say I have a script that selects everything from a table of 20 fields. Let's say typical customer information.

Then let's say that I'm a good developer, I shorten SELECT *to SELECTout of 13 specific fields that I actually use at the end of the display.

What kind of performance, if any, can be expected by explicitly specifying the fields compared to SELECT *?

I will say this, both queries use the same exact indexes. A more specific request does not have access to the coverage index, which the other request cannot use if you are interested.

I do not expect miracles, like adding an index focused on a more specific query. I'm just curious.

Answer 1

This depends on three factors: the underlying storage and retrieval mechanism used by your database, the nature of the 7 columns you leave, and the number of rows returned to the result set.

7 ( ) , , " ", , . "" (, BLOB , ) / , . , .

, , , , , , .

Answer 2

, , - . - , , , , , .

, ( ), , , .

, , , .

Answer 3

, , .

:

• *
• , . ( ).
• .

- 72 - CLOB. where.

:

Run  *     Key   Non-key
1   .647  .020  .028
2   .599  .041  .014
3   .321  .019  .027
avg .522  .027  .023

-, , . ( .) * 95% !

, . . , , , 95%, select *! , .

Answer 4

13 vs 20 , 7 , CLOB/BLOB , , .

I/O - ( I/O), , 13/20 ( ), ( ), , ( , 20 , , , ).

, SELECT * , - .

SELECT * , ( , , , ). , ( , ). , SELECT * , , , ( ) .

Answer 5

?

, .

, ? , , , . , , , SELECT * SELECT.

Answer 6

* , . ( , , , !), , , . . , ? , , , , . ? , SQL Server , , . , select * , , . - select * .