SQL Server Performance Forum – Threads Archive

why not to use select * from table

Just i want the differenc between the select * from tablex and select column1,column2 from tablex dashpatil

---

Select * will select data from all the columns. If you want specified columns, then you can specify it. But there wont be any performance issue when using * and all_Columns Madhivanan Failing to plan is Planning to fail

---

SELECT * is a sign of laziness compared to SELECT <column list >. SELECT * causes much unnecessary traffic over the network. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

>>SELECT * causes much unnecessary traffic over the network. I dont understand What happens when you explicitely list all the columns instead of *? Madhivanan Failing to plan is Planning to fail

---

I remember a bug in ADO, where a SELECT * would fail, when you have a BLOB column in that table which is not referenced at the last position in a SELECT statement.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

---

1. The chance is for specific functionality you won’t need to return all columns from the table. If you need just a few, there is a chance index covering all columns from the query. Even if not, you save network traffic that way.
2. Even if you need to return all columns from the table what happens when column is dropped from the table or new column is added in case of "select *" query? How would client application handle changed recordset structure?

---

The other difference is shear speed. If you only need Column1, Column2 in the results and have say Column3 in the WHERE clause, the system would be able to completely resolve the query by using an index on Column3, Column1, Column2. Meaning it wouldn’t have to figure out which rows to use from an index, and then go do a bookmark lookup of the resulting values to get the results to return. If there are several different queries for different values that all end up hitting the source table, that is a lot of contention on the same table, so it is slower individually, plus the additional time for contention and blocking just makes it worse.
Hope it helps,
Dalton Blessings aren’t so much a matter of recieving them as they are a matter of recognizing what you have received.

---

]]>