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