SQL Server Performance Forum – Threads Archive

SELECT * vs SELECT column list

Hi, I recommend the dev team to use SELECT with an explicit list of columns. The developers agree that it is more efficient (we take the only columns you need) and more readable (we know what the SELECT bring back just by reading the stored proc). For the sake of technical discussion, a developer asked me if there is any other real advantage of the SELECT column list over SELECT *. Is there any difference between the two methods in terms of performance, compatibility? (We use SQL2K, C#, ADO.NET) Thanks in advance

Check: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2864 Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

there is a big performance issue between using SELECT * and SELECT column name.
u can itself verify the time of execution by running both query on a table having more than 10 lac records and more than say 10 columns.
if select * takes 1 min 10 sec to retrive all the records than select cloumn1 query will take hardly 10 secs to process.
but it is recommended to use always where clause with SELECT query on a table with 1 gb records. if not possible then only use plain select query.
u can read more on select query in this site in tips forums.

Performance on a "covered" query is excellent. A "covered" query is one where the columns in the SELECT columnlist and the WHERE columnlist are present in an index on the table. By using a "covered" query the data pages do not have to be accessed. In SQL 2005 it is my understanding that we gain the ability to add columns to the index level (faster queries, less data page access) without the overhead pain. Dbu

The less data you ask for, the faster it will be send over the network. This is nothing abstract or highly sophisticated, but simple and understandable. What more reasons do you need? —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Also remeber that the text, ntext, or image data type cannot be selected as DISTINCT. You may have a ‘SELECT DISTINCT * FROM table1’ query and it will work perfectly. One day somebody will add a text column to table1 and the above query will not execute anymore. I’ve seen this happen. —
Marek Grzenkowicz

]]>