Choosing All Columns

The asterisk (*) has the following special meanings in SELECT statements:

• When specified without a qualifier, an asterisk (*) is resolved as a reference to all columns in all tables or views specified in the FROM clause. The following example retrieves all product information stored in the Product table:

  USE AdventureWorks;
  GO
  SELECT *
  FROM Production.Product
  ORDER BY Name
  GO
  

• When qualified with a table or view name, an asterisk (*) is resolved as a reference to all the columns in the table or view. The following example uses the asterisk to reference all the columns in the Product table:

  USE AdventureWorks;
  GO
  SELECT s.UnitPrice, p.*
  FROM Production.Product p
     JOIN
       Sales.SalesOrderDetail s
     ON (p.ProductID = s.ProductID)
  ORDER BY p.ProductID
  GO
  

When an asterisk (*) is used, the order of the columns in the result set is the same as the order in which they were specified in the CREATE TABLE, ALTER TABLE, or CREATE VIEW statements.

Because SELECT * finds all columns currently in a table, changes in the structure of a table (by adding, removing, or renaming columns) are automatically reflected each time a SELECT * statement is executed.

If a SELECT is used in an application or script that has logic dependent on the number of columns in the result set, it is better to specify all the columns in the select list instead of specifying an asterisk. If columns are later added to the table or views referenced by the SELECT statement, the application is shielded from the change if the columns were listed individually. If an asterisk (*) was specified, the new columns become a part of the result set and may affect the logic of the application or script. You should avoid using the asterisk (*), especially against catalog views, dynamic management views, and system table-valued functions. Future upgrades and releases of Microsoft SQL Server may add columns and change the order of columns to these views and functions. These changes may break applications that expect a particular order and number of columns.

The following example retrieves all columns in the Customer table and displays them in the order in which they were defined when the Customer table was created.

USE AdventureWorks;
GO
SELECT *
FROM Sales.Customer
ORDER BY CustomerID ASC
GO

To get exactly the same results, explicitly list all the column names in the table, in order, after the SELECT statement.

USE AdventureWorks;
GO
SELECT CustomerID, TerritoryID, AccountNumber, CustomerType, rowguid, ModifiedDate
FROM Sales.Customer
ORDER BY CustomerID ASC
GO

See Also

Other Resources

SELECT (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance