Re: asterisk in select_list in queries...

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 10/08/04 

Date: Fri, 8 Oct 2004 21:00:18 +0100

Best practice is to avoid using SELECT * in queries (except in an EXISTS
subquery or other subqueries that don't return data and aren't referenced by
an outer query).

Listing the column names makes sense in an N-Tier environment because you
want to make efficient use of network resources by returning to the client
only the data that is actually needed. This is an important difference from
a desktop, ISAM database like FoxPro where you have to retrieve a whole
record whether all the data is required or not.

Listing only the required columns also increases the opportunities for SQL
Server to optimize your query by making use of indexes.

Also, listing column names improves reliability and ease of maintenance. If
you later add another column you don't want to break existing code that
doesn't require that column. Use a column list and then just modify the code
that needs to reference the new column. If you use an asterisk in your
SELECT list then potentially more code could need modification and more code
would need to be unit-tested for each schema change.

Shortcuts? In Query Analyzer you can drag a list of column names from the
Object Browser into the query window. That can save you a lot of typing. 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Re: Querry not given right results
    ... the outer query, not on the inner query filtering PART#. ... >>Let's concentrate on the subquery starting in line 4. ... within that subquery you reference the ... >instance of TABLE1 ...
    (microsoft.public.access.queries)
  • Re: Querry not given right results
    ... Let's concentrate on the subquery starting in line 4. ... within that subquery you reference the instance of TABLE1 ... instance of TABLE1 in the inner query. ...
    (microsoft.public.access.queries)
  • Re: Can some explain this to me?
    ... the subquery, but it can also reference any column in any table from ... resolved against the outer query table person. ... inside a transaction and if the count is different roll the ...
    (comp.databases.ms-sqlserver)
  • Re: Subquery Sum Containing Parameters
    ... subquery to reference a date and add the records that are ... query preceeding. ... .RefreshStyle = xlOverwriteCells ...
    (microsoft.public.excel.programming)
  • Re: Jet SQL and Virtual tables/subqueries qiestions
    ... subquery, but it's curious... ... repeated trips through the Query Editor's "graphical" side did ... The Jet Expression Evaluator (what functions & stuff are actually ... within brackets within the query. ...
    (microsoft.public.access.queries)