Should both of these CTEs perform the same?

Question

If we have a SQL view VIEW_MYTABLE with, say, 50 columns.

Option 1:

with CTE_MYQUERY1 as (
  select [VIEW_MYTABLE].*
  from [VIEW_MYTABLE]
  /*some complex where clause*/
)

Option 2:

with CTE_MYQUERY2 as (
  select [COLUMN_1], [COLUMN_2], [COLUMN_3], ...., [COLUMN_10]
  from [VIEW_MYTABLE]
  /*some complex where clause*/
)

As per my understanding, select with columns defined is always faster than the select * statement. Note that in this second query I am selecting only 10 of the 50 columns in the view.

I am getting both result as same? Can anyone also let me know how CTE works internally, does it first generate result set and then fed it to the subsequent query (SELECT query in my case)?

Answer 1

I would expect absolutely no discernible difference in runtimes between these two queries.

However, I would still advocate against SELECT *, and not for performance reasons. There is a long-established myth that SELECT * is less efficient because the engine has to go look up the column names in the metadata, but the truth is that there is still a lookup to validate the column names you've written, and the additional cost of retrieving the names is going to be unnoticeable by humans regardless of the result set size.

The reasons I advocate against SELECT * is that:

• it is unlikely you need all of the columns from the table (or all rows, but that's a different story). If you are pulling back more columns than you need, you are doing unnecessary I/O and perhaps forcing SQL Server to perform a table/clustered index scan when it could have performed a scan on a much skinnier index.

• even if you do need all of the columns, using SELECT * can cause hard-to-detect problems in your code later. What if someone inserts a column into the middle of the table? Drops a column? Adds a column? Renames a column? Some of these will be caught immediately but I've demonstrated cases where this can cause all kinds of hard-to-debug problems.

  • Bad habits to kick : using SELECT * / omitting the column list

---

As for how CTEs work in general, that's a pretty broad question here. I'd start with these articles:

http://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms190766(v=sql.100)