Choosing All Columns

The asterisk (*) has a special meaning in SELECT statements:

• When specified without a qualifier, it is resolved as a reference to all columns in all tables or views specified in the FROM clause. This example retrieves all book information stored in the Shippers table:

  USE Northwind

  GO
SELECT *

  FROM Shippers

  ORDER BY CompanyName

  GO

    

• When qualified with a table or view name, it is resolved as a reference to all the columns in the table or view. This example uses the asterisk to reference all the columns in the Shippers table:

  USE Northwind

  GO

  SELECT Orders.OrderID, Shippers.*

  FROM Shippers

     JOIN

       Orders

     ON (Shippers.ShipperID = Orders.ShipVia)

  ORDER BY Orders.OrderID

  GO

When * 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 (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 rather than specify 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 * was specified, the new columns become a part of the result set and may affect the logic of the application or script.

This example retrieves all columns in the publishers table and displays them in the order in which they were defined when the publishers table was created:

USE Northwind

GO

SELECT *

FROM [Order Details]

ORDER BY OrderID ASC

GO

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

USE Northwind

GO

SELECT OrderID, ProductID, UnitPrice, Quantity, Discount

FROM [Order Details]

ORDER BY OrderID ASC

GO

Note To find out the column names for a table, use sp_help, use SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = table, or use SELECT TOP 0 * FROM table.

See Also

SELECT