Can Select * Usage Ever Be Justified

Can select * usage ever be justified?

I'm quite happy using * in audit triggers.

In that case it can actually prove a benefit because it will ensure that if additional columns are added to the base table it will raise an error so it cannot be forgotten to deal with this in the audit trigger and/or audit table structure.

(Like dotjoe) I am also happy using it in derived tables and column table expressions. Though I habitually do it the other way round.

WITH t
     AS (SELECT *,
                ROW_NUMBER() OVER (ORDER BY a) AS RN
         FROM   foo)
SELECT a,
       b,
       c,
       RN
FROM   t; 

I'm mostly familiar with SQL Server and there at least the optimiser has no problem recognising that only columns a,b,c will be required and the use of * in the inner table expression does not cause any unnecessary overhead retrieving and discarding unneeded columns.

In principle SELECT * ought to be fine in a view as well as it is the final SELECT from the view where it ought to be avoided however in SQL Server this can cause problems as it stores column metadata for views which is not automatically updated when the underlying tables change and the use of * can lead to confusing and incorrect results unless sp_refreshview is run to update this metadata.

What are the pros and cons of using `select table_name.*`?

When your program knows about the structure of your table at compile-time or through configuration, using select * is not a good idea: any change in the structure of your table could break the structure of the results coming back from the query, ultimately causing run-time errors.

However, there are cases when * is indispensable. Specifically, if your program "learns" dynamically of the structure of your tables by reading metadata coming back from a query, using the "all columns" request lets your program pick up changes to your tables dynamically.

It goes without saying that using * for ad-hoc queries in your favorite flavor of SQL Studio/SQLPlus/etc. is very common and convenient.

What implication does select * from foo have?

What happens if you don't specify columns, is that the DB Engine has to query the master table data for the column list. This query is really fast, but causes a minor performance issue. As long as you're not doing a sloppy SELECT * with a JOIN statement or nested queries, you should be fine. However, note the small performance impact of letting the DB Engine doing a query to find the columns.

SELECT * - pros /cons

In general, the use of SELECT * is not a good idea.

Pros:

• When you add/remove columns, you don't have to make changes where you did use SELECT *
• It is shorter to write
• Also see the answers to: Can select * usage ever be justified?

Cons:

• You are returning more data than you need. Say you add a VARBINARY column that contains 200k per row. You only need this data in one place for a single record - using SELECT * you can end up returning 2MB per 10 rows that you don't need
• Explicit about what data is used
• Specifying columns means you get an error when a column is removed
• The query processor has to do some more work - figuring out what columns exist on the table (thanks @vinodadhikary)
• You can find where a column is used more easily
• You get all columns in joins if you use SELECT *
• You can't use ordinal referencing (though using ordinal references for columns is bad practice in itself)
• Also see the answers to: What is the reason not to use select *?

SQL select statement with either ( * ) or (fields names) which performs better?

Simple answer: * is slow, named fields are fast.

Fewer named fields are faster.

Smaller datafields are faster than bigger ones.

Never select blobs of you do not have to.

Subquery using Exists 1 or Exists *

No, SQL Server is smart and knows it is being used for an EXISTS, and returns NO DATA to the system.

Quoth Microsoft:
http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4

The select list of a subquery
introduced by EXISTS almost always
consists of an asterisk (*). There is
no reason to list column names because
you are just testing whether rows that
meet the conditions specified in the
subquery exist.

To check yourself, try running the following:

SELECT whatever
  FROM yourtable
 WHERE EXISTS( SELECT 1/0
                 FROM someothertable 
                WHERE a_valid_clause )

If it was actually doing something with the SELECT list, it would throw a div by zero error. It doesn't.

EDIT: Note, the SQL Standard actually talks about this.

ANSI SQL 1992 Standard, pg 191 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

3) Case:

a) If the <select list> "*" is simply contained in a <subquery> that
is immediately contained in an <exists predicate>, then the <select list> is
equivalent to a <value expression>
that is an arbitrary <literal>.

Mysql php querying improvement over SELECT star...beginner easy

The asterisk means that you're always selecting all the columns from this table. So if you only want the entry of tomato3, your query should read : SELECT tomato3 FROM tomatoes WHERE tomato1='whatever'

Select next 1000

I would say no. And if you're wanting to do some sort of pagination you can use a query object (see link) or something similar to the below X++, which is pretty much the common convention.

while select salesTable
{
    i++;
    if (i>1000)
        break;
    info(strFmt("%1: %2", i, salesTable.SalesId));
}

Or

// Less often used method below. Usually for a specific purpose.
select salesTable;
while (salesTable && i < 1000)
{
    i++;
    info(strFmt("%1: %2", i, salesTable.SalesId));

    next salesTable;
}

I would think the simplest way to accomplish what you're trying with the RecId would be to sort by RecId Asc then just track the last RecId that you selected instead of maxOf. I'd think you may get poor performance though if you just arbitrarily did it on any table.

---

Related Topics