Everyone knows that SELECT * is not a best practice and we always try to avoid it. Some considerable issues related to it are;
- Unnecessary columns will be transferred from server to client consuming resources.
- Application written may face issues when dropping or introducing columns.
However, when reading the table by SQL Server engine, in most cases, no difference in terms of performance. The reason is, it has to read all data pages for getting records regardless of the number of columns referred in the statement. Have a look on following query and output of it.
As you see, both statements require same number of data pages to be read for satisfying the requests. However this is not always true, have a look on this;
Now it is different. The reason for this is, types of data pages used with this table. This table has an xml column and it is maintained not with data pages, with text/image pages. Because of this, when reading columns excluding columns like xml, max type columns, text, images, engine needs to read only data pages, reducing number of IO operations. This introduces the third reason for not using SELECT *, hence, do not use SELECT * when the table has columns that requires different data page types other than data pages.