Transact-SQL Reference

Specifies the columns to be returned by the query.


    [ TOP n [ PERCENT ] [ WITH TIES ] ]
    < select_list >

< select_list > ::=

    {    *
        | { table_name | view_name | table_alias }.*
        |     { column_name | expression | IDENTITYCOL | ROWGUIDCOL }
            [ [ AS ] column_alias ]
        | column_alias = expression
    }    [ ,...n ]



Specifies that duplicate rows can appear in the result set. ALL is the default.


Specifies that only unique rows can appear in the result set. Null values are considered equal for the purposes of the DISTINCT keyword.


Specifies that only the first n rows are to be output from the query result set. n is an integer between 0 and 4294967295. If PERCENT is also specified, only the first n percent of the rows are output from the result set. When specified with PERCENT, n must be an integer between 0 and 100.

If the query includes an ORDER BY clause, the first n rows (or n percent of rows) ordered by the ORDER BY clause are output. If the query has no ORDER BY clause, the order of the rows is arbitrary.


Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP ...WITH TIES can only be specified if an ORDER BY clause is specified.

< select_list >

The columns to be selected for the result set. The select list is a series of expressions separated by commas.

Specifies that all columns from all tables and views in the FROM clause should be returned. The columns are returned by table or view, as specified in the FROM clause, and in the order in which they exist in the table or view.
table_name | view_name | table_alias.*
Limits the scope of the * to the specified table or view.
Is the name of a column to return. Qualify column_name to prevent an ambiguous reference, such as occurs when two tables in the FROM clause have columns with duplicate names. For example, the Customers and Orders tables in the Northwind database both have a column named ColumnID. If the two tables are joined in a query, the customer ID can be specified in the select list as Customers.CustomerID.
Is a column name, constant, function, any combination of column names, constants, and functions connected by an operator(s), or a subquery.
Returns the identity column. For more information, see IDENTITY (Property), ALTER TABLE, and CREATE TABLE.

If the more than one table in the FROM clause has a column with the IDENTITY property, IDENTITYCOL must be qualified with the specific table name, such as T1.IDENTITYCOL.

Returns the row global unique identifier column.

If the more than one table in the FROM clause with the ROWGUIDCOL property, ROWGUIDCOL must be qualified with the specific table name, such as T1.ROWGUIDCOL.

Is an alternative name to replace the column name in the query result set. For example, an alias such as "Quantity", or "Quantity to Date", or "Qty" can be specified for a column named quantity.

Aliases are used also to specify names for the results of expressions, for example:

USE Northwind
SELECT AVG(UnitPrice) AS 'Average Price'
FROM [Order Details]

column_alias can be used in an ORDER BY clause. However, it cannot be used in a WHERE, GROUP BY, or HAVING clause. If the query expression is part of a DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE clause.

INTO Clause

Creates a new table and inserts the resulting rows from the query into it.

The user executing a SELECT statement with the INTO clause must have CREATE TABLE permission in the destination database. SELECT...INTO cannot be used with the COMPUTE. For more information, see Transactions and Explicit Transactions.

You can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause.


[ INTO new_table ]



Specifies the name of a new table to be created, based on the columns in the select list and the rows chosen by the WHERE clause. The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, and value as the corresponding expression in the select list.

When a computed column is included in the select list, the corresponding column in the new table is not a computed column. The values in the new column are the values that were computed at the time SELECT...INTO was executed.

In this release of SQL Server, the select into/bulkcopy database option has no effect on whether you can create a permanent table with SELECT INTO. The amount of logging for certain bulk operations, including SELECT INTO, depends on the recovery model in effect for the database. For more information, see Using Recovery Models.

In previous releases, creating a permanent table with SELECT INTO was allowed only if select into/bulkcopy was set.

select into/bulkcopy is available for backward compatibility purposes, but may not be supported in future releases. Refer to the Recovery Models and Backward Compatibility and ALTER DATABASE topics for more information.