What is the influence of "select *" on the idea of ​​building an implementation plan?

Question

I heard that using "select *" will add the time SQL Server takes to build a query execution plan, as it needs to figure out which columns are in the referenced tables. Does the same apply to a query based on a view instead of a table?

Answer 1

Insignificant time. Yes, if it applies to a table, it refers to a view.

But over 12 years of SQL execution, I have not yet seen that the query is accelerated by explicitly naming the columns instead of using them *.

, *, , , , , ,

*, ( ", " ) - " "; , . ; , .

: , ( ), , . , * column1, c2, d3, foobar , , create view db.

, ( ). .

Answer 2

, , :

• , .
• , , , .

Answer 3

, :

CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY, value1 INT, value2 INT, aux_value VARCHAR(200))
CREATE INDEX ix_test_values ON (value1, value2)

:

SELECT  value1, value2
FROM    t_test
WHERE   value1 BETWEEN 10 AND 20

SQL Server ix_test_values. , , , INDEX SCAN.

:

SELECT  *
FROM    t_test
WHERE   value1 BETWEEN 10 AND 20

SQL Server id aux_value value1 value2. , SQL Server .

4 10 , , , .

Answer 4

, 'select *' , SQL Server , , .

. SQL Server :

SELECT ID, col1, col2, col3
  FROM table

, , , col1, col2 .. - ? !: D

, . -, , :

SELECT * FROM table

, SELECT * ( ), , , , . , - - , , :)

Answer 5

; . "select *" . , .

Answer 6

, ; * .

Using select * can return more data, then what you really need, depending on the data, can be significant. Then there are problems when, if you delete a column, your query may still work; however, the consumer code may fail. You really lose the ability to determine if anyone is using this column.