Overuse of the asterisk in sql

[Lotruth]

Is there any downside to using the asterisk in a select sql statement if you dont really need to? Sometimes I only need one field, but I use the asterisk out of laziness.

 

[gol4]

If you are dealing with small amounts of data in a single user environment, not really but when you begin to work with larger recordsets in mulituser environments it becomes important

 

[cmmrfrds]

I believe in most database engines that the use of a wildcard will cause the engine to bypass the indexes and do full table scans. You would need to test both ways by timing on a large resultset. I would avoid the wildcard unless necessary.

 

[r937]

cmmrfrds, your advice to avoid using the asterisk is good, but the reason you gave is not

select * just returns all columns of selected rows

the rows that are selected, and the query execution path to those rows, and whether indexes are used or not, is determined entirely by other factors, such as conditions in the WHERE clause

rudy

 

[cmmrfrds]

Rudy you are right, I didn't read the question close enough I jumped to the conclusion that it was talking about the wildcard in the Like expression in the where clause. On the original question, selecting unnecessary columns is especially a bad practice when running on a network and retrieving large resultsets.