Is There a Difference Between Select * and Select [List Each Col]

Is there a difference between Select * and Select [list each col]

Generally, it's better to be explicit, so Select col1, col2 from Table is better. The reason being that at some point, an extra column may be added to that table, and would cause unneeded data to be brought back from the query.

This isn't a hard and fast rule though.

Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc

One reason that selecting specific columns is better is that it raises the probability that SQL Server can access the data from indexes rather than querying the table data.

Here's a post I wrote about it: The real reason select queries are bad index coverage

It's also less fragile to change, since any code that consumes the data will be getting the same data structure regardless of changes you make to the table schema in the future.

Select * from table OR select id,field1, field2, field3 from table - best practice?

Use select col1, col2, col3 from table instead of select * from table1. This has numerous advantages, as mentioned here and here.

Also see:
http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/26/60271.aspx

Is there a difference between Select * and Select [list each col]

Is there a difference between SELECT * and SELECT ALL?

SELECT ALL means ALL rows, i.e including duplicate rows. (The opposite is SELECT DISTINCT, where duplicate rows are removed.) ALL is the default, and most people write just SELECT instead of SELECT ALL.

SELECT * means all columns.

Note: When it comes to e.g. UNION suddenly DISTINCT is the default. So just UNION means UNION DISTINCT, i.e. duplicate rows are removed. Here you have to specify UNION ALL to keep duplicate rows.

Is there a runtime difference between SELECT * and SELECT explicit columns?

No. The run-time difference is going be based on the volume of data returned. This is the same for the two queries, because the same columns are referenced.

Often, columns are listed explicitly so only the needed columns are included. That can benefit performance, and well as making the code more robust with respect to changes in the table structure.

The difference between SELECT * and SELECT A.*

.* means, select all columns of a table

SELECT A.*,B.Id from A,B select all columns of A and only Id column of B.

Is there a performance difference between select * from tablename and select column1, column2 from tablename?

The answer is YES in general! Because for small databases you don't see a performance difference ... but with biggest databases could be relevant differences if you use the unqualified * selector as shorthand!

In general, it's better to instantiate each column from which you want to retrieve data!

I can suggest you to read the official document about how to optimize SELECT and other statements!

What is the difference between SELECT * and returning all columns by specfying each in the Select statement / Union query returns Duplicates

the second important point I came across is to specify each column
name instead of SELECT * I want to know how does that make a
difference

Later down the road, someone adds a field to the table. Any queries that use "SELECT *" suddenly gain a new field. Sometimes this is correct. Sometimes this yields unexpected results. In most cases, it is a better practice to always explicitly name the columns rather than use wildcards.

Secondly, if you don't actually need all columns, you can improve performance by limiting the columns returned. Even further, in some databases, a query can be satisfied by an INDEX without going to the table, so if you use column lists, it allows that optimization to happen. With SELECT * it cannot.

---

Related Topics