select tablename.* vs. tablename.columnname 1

[Steffi1013]

I am wondering what the performance difference is, if any, to doing a select tablename.* versus select tablename.columnname. I have always thought that it is always better to specify each specific column rather than doing a select * from any table.

 

[mrdenny]

Steffi,
You are correct it is always better to read only the columns that you need instead of all columns from a table. The reason is to make your record set smaller, to reduce load on the disks, and to reduce load on the CPU.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mike718]

Steffi,

I agree with Denny. You should only use Select * if you require a complete recordset back. I noticed that you are using tablename.columnname format. You really do not have to use this method unless you are joining 2 tables with the same column names. If you are just using a select statement on one table you can say

Select a
from tablename

The reason for this is because the table name is already specified in the from. Now if you have 2 tables with the same column name you would want to specify the tablename in the select statement so the servr knows which table you are refering to:

select tablea.a, tableb.a
from tablea, tableb

 

[SQLSister]

I'm of the don't use select * school, too. First, you often do not need every field in the table. To return data you don't need is inefficent both for the SQL Server and for the network.

Second, anytime you have an inner join, select * is automatically inefficent as two column have exactly the same data.

Third, Select * in a union query is actively dangerous. In a union query, the fields must be in the correct order to match up and must have exactly the same number of fields. Anyone adds a field to one of the tabel or rearranges them in Enterprise Manager and your query no longer works. Same with Select * in the select part of an insert statment.

Fourth, and this is personal opinion, I believe it leads to the inappropriate reuse of code. Write one stored procedure with Select * and then use it in 12 differnt places even though each of those places wants a different subset of the data. No problem at design time. It was fast and I'm on to the next thing. But wait til there are 12 million records in the table and see how slow your system is.

Now mind, I have no objections to the reuse of code when done appropriately. But when code reuse makes the application run less efficiently, then it is inappropriate no matter how much coding time it saves you.

Some people like to use Select * because it saves them typing time. To this I say that you should use the object browser and drag the column names over from it. All you have to do is have the table expanded and the columns expanded within that. Then dragging the word "columns" will drag all the columns at once. It puts them all on one line, so I take the extra few seconds to break that down to lines I don't have to scroll across to read. Yeah this is afew seconds slower than typing Select *, but much faster and more accurate than typing all the column names. Making an application permanently slower because you are too lazy to spend a few extra seconds in creation of the program is the mark of a poor programmer.



Questions about posting. See faq183-874

 

[Steffi1013]

Thank you all for your answers. It helped a lot. I usually do not use select * for any query if at all possible. I felt that it slowed performance down and specifying the columns helped in that.

If there are any specific things that doing a select * versus select tablename.columnname hinders, I would be interested in that also. Performance, table scans, etc...

 

[SQLSister]

It may presnet you from using the indexes properly. For instance if you have a covering index and you only need those three fields in the index, a select with the column names would use the index and select * would probably do a table scan.

try it yourself in query analyzer and look at the differnt execution plans it s creates for the two queries.

Questions about posting. See faq183-874

 

[vongrunt]

Suppose there are two tables: A and B. Both have column named "foo". Query is using SELECT * or SELECT A.*, B.* or SELECT A.*, B.foo, whatever. Execute this query from ADO layer and attempt to retrieve foo column:

Set oRS = connObj.Execute( "select * from A inner join B blah blah" )
fooValue = oRS("foo").value

Here comes $32 question: does fooValue belong to table A or table B? :X

 

[r937]

you cannot successfully use "select star" if the query has a GROUP BY

rudy

http://r937.com/

SQL Consulting

 

[mrdenny]

vongrunt the foo column should be the first one that the ADO layer comes across. IE the one to the closest to the left when looking at the results in QA.

However that being said according to good database design it shouldn't matter which one you pull, if the columns have the same name they should hold the same data, and should be the join point.

But all columns should still be specified if for no other reason than in some future version of ADO it might not be able to handle that for some stupid reason (like it shouldn't really have to).

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[vongrunt]

... the foo column should be the first one that the ADO layer comes across.

Just curious... try this query over Northwind demo database:

select A.*, B.* from Employees A inner join Employees B on 
A.EmployeeID=10-B.EmployeeID
order by A.EmployeeID

... and retrieve EmployeeID. I got 9 (table B).

However that being said according to good database design it shouldn't matter which one you pull, if the columns have the same name they should hold the same data, and should be the join point.

Perhaps... This sounds like matter of naming convention. Some people use column names like EmployeeLastName, CustomerLastName, some stick to LastName and repeat this name all over the database. Personally I think that any naming convention is good - as long as it is applied consistently.