Export (0) Print
Expand All

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 AdventureWorks2008R2;
    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 AdventureWorks2008R2;
    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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
SELECT CustomerID, TerritoryID, AccountNumber, rowguid, ModifiedDate
FROM Sales.Customer
ORDER BY CustomerID ASC
GO
NoteNote

To view the column names for a table, you can use sp_help or one of the following queries: SELECT name FROM sys.columns WHERE OBJECT_ID IN (SELECT OBJECT_ID ('table_name')) or SELECT TOP 0 * FROM table_name.

Community Additions

ADD
Show:
© 2015 Microsoft