Should both of these CTOs do the same?

Question

If we have a VIEW_MYTABLE SQL 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 I understand it, select with certain columns is always faster than the select * operator. Note that in this second query, I select only 10 of the 50 columns in the view.

Do I get both results the same? Can someone also tell me how the CTE works internally, it first generates a result set and then passes it to the next query ( SELECT query in my case)?

Answer 1

I would not expect absolutely no noticeable run-time difference between the two queries.

However, I would still oppose SELECT *, and not for performance reasons. There is a long-standing myth that SELECT * is less efficient because the engine should look for column names in the metadata, but in truth, there is still a search to check the column names that you wrote, and the extra cost of getting the names will be invisible for people regardless of the size of the result set.

The reasons I oppose SELECT * are as follows:

• it is unlikely that you need all the columns from the table (or all the rows, but that's a different story). If you pull in more columns than you need, you do unnecessary I / O and possibly force SQL Server to scan the indexes of the table / cluster when it could scan with a much narrower index.

• even if you need all the columns, using SELECT * can cause problems with detecting a problem in your code later. What if someone inserts a column in the middle of the table? Drops of a column? Adds a column? Renames a column? Some of them will be detected immediately, but I have shown cases where this can cause all kinds of debugging problems.

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/bad-habits-to-kick-using-select-omitting-the-column-list.aspx

---

As for how CTEs work in general, this is a pretty broad question. I would start with these articles:

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

http://msdn.microsoft.com/en-us/magazine/cc163346.aspx

http://msdn.microsoft.com/en-us/library/ms190766%28v=sql.100%29.aspx

Answer 2

The basic SELECT * method can hurt performance by causing the request to spend time getting much more data than is really necessary. But this SELECT in the main part of the query determines what data is retrieved. * A (non-recursive) general table expression can be considered as a kind of one-time view. Any columns in the CTE that do not reference a query that uses it will ultimately be ignored. Just as when requesting a view, the engine does not necessarily capture every column in the view, just every column that you requested.

I assume that you get the same performance with both CTEs because the query that uses them that you left outside the examples is identical in both cases. Because of this, the extra columns that your first option refers to do not affect what data is retrieved by the full query.

* Added: For clarity, this is only the case for SELECT s. WHERE and JOIN will influence which columns should be read wherever they appear.

Answer 3

Do not return more columns or rows of data to the client than necessary. This simply increases disk I / O on the server and network traffic, which wraps up performance. In SELECT do not use SELECT * to return rows; always specify in the SELECT exactly which columns are needed to return for this particular query, and not for a column larger. In most cases, be sure to include the WHERE to reduce the number or lines sent only to those lines that clients must execute immediately.

In my opinion, the big difference will be in your complex WHERE , like where the main actions, involved indices, etc.

All that said, I believe that the second will work better in almost all scenarios.

Check out this detailed article by Steve Jones on SQL Central .