Retrieves rows from database objects.
select ::= select [ all | distinct ] [top unsigned_integer] select_list [into_clause ] [from_clause ] [where_clause ] [group_by_clause] [having_clause ] [order_by_clause ] [compute_clause ] [read_only_clause ] [isolation_clause ] [browse_clause ] [plan_clause ]
select_list ::=
For details on select_list,
see the “Parameters” section.
into_clause ::=
into [ [ database.] owner.] table_name
[ { [ external table at ]
‘server_name.[database].[owner].object_name’
| external directory at ‘pathname’
| external file at ‘pathname’ [column deliminter ‘string’ ] } ]
[ on segment_name ]
[ partition_clause ]
[ lock { datarows | datapages | allpages } ]
[ with [, into_option[, into_option] ...] ] ]
| into existing table table_name
partition_clause ::=
partition by range ( column_name[, column_name ]...)
( [ partition_name ] values <= ( { constant | MAX }
[, { constant | MAX } ] ...) [ on segment_name ]
[, [ partition_name ] values <= ( { constant | MAX }
[, { constant | MAX } ] ...) [ on segment_name ] ]...)
| partition by hash (column_name[, column_name ]...)
{ ( partition_name [ on segment_name ]
[, partition_name [ on segment_name ] ]...)
| number_of_partitions
[ on (segment_name[, segment_name ] ...) ] }
| partition by list (column_name)
( [ partition_name ] values ( constant[, constant ] ...)
[ on segment_name ]
[, [ partition_name ] values ( constant[, constant ] ...)
[ on segment_name ] ] ...)
| partition by roundrobin
{ ( partition_name [ on segment_name ]
[, partition_name [ on segment_name ] ]...)
| number_of_partitions
[ on ( segment_name [, segment_name ]...) ] }
into_option ::= | max_rows_per_page = num_rows | exp_row_size = num_bytes | reservepagegap = num_pages | identity_gap = gap
from_clause ::= from table_reference [,table_reference]...
table_reference ::= table_view_name | ANSI_join
table_view_name ::=
[[database.]owner.] {{table_name | view_name}
[as] [correlation_name]
[index {index_name | table_name }]
[parallel [degree_of_parallelism]]
[prefetch size ][lru | mru]}
[holdlock | noholdlock]
[readpast]
[shared]
ANSI_join ::= table_reference join_type join table_reference join_conditions join_type ::= inner | left [outer] | right [outer] join_conditions ::= on search_conditions
where_clause ::= where search_conditions
group_by_clause ::= group by [all] aggregate_free_expression [, aggregate_free_expression]...
having_clause ::= having search_conditions
order_by_clause ::= order by sort_clause [, sort_clause]...
sort_clause ::=
{ [[[database.]owner.]{table_name.|view_name.}]column_name
| select_list_number
| expression }
[asc | desc]
compute_clause ::= compute row_aggregate(column_name) [, row_aggregate(column_name)]... [by column_name [, column_name]...]
read_only_clause ::=
for {read only | update [of column_name_list]}
isolation_clause ::=
at isolation
{ read uncommitted | 0 }
| { read committed | 1 }
| { repeatable read | 2 }
| { serializable | 3 }
browse_clause ::= for browse
plan_clause ::= plan "abstract plan"
includes all rows in the results. all is the default.
includes only unique rows in the results. distinct must be the first word in the select list. distinct is ignored in browse mode.
Null values are considered equal for the purposes of the keyword distinct: only one NULL is selected, no matter how many are encountered.
is used with select...into statements to limit the number of rows inserted in the target table. This is different from set rowcount, which is ignored during a select...into.
When used with delete, update, or in a view, you cannot specify ordering. If there is an implied order on the table from a clustered index, that order applies; otherwise, the results are unpredictable, as they can be in any order.
When used with cursors, top n limits the overall size of the result set. Specifying set cursor rowcount limits the results of a single fetch.
When a view definition contains select top n and a query with a where clause uses it, the results may be inconsistent.
consists of one or more of the following items:
“*”, representing all columns in create table order.
A list of column names in the order in which you want to see them. When selecting an existing IDENTITY column, you can substitute the syb_identity keyword, qualified by the table name, where necessary, for the actual column name.
A specification to add a new IDENTITY column to the result table:
column_name = identity(precision)
A replacement for the default column heading (the column name), in one of these forms:
column_heading = column_name
column_name column_heading
column_name as column_heading
The column heading can be enclosed in quotation marks for any of these forms. The heading must be enclosed in quotation marks if it is not a valid identifier (that is, if it is a reserved word, if it begins with a special character, or if it contains spaces or punctuation marks).
An expression (a column name, constant, function, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators, or a subquery).
A built-in function or an aggregate.
Any combination of the items listed above.
The select_list can also assign values to variables, in the form:
@variable = expression [, @variable = expression ...]
You cannot combine variable assignment with any other select_list option.
except when used with existing table, creates a new table based on the columns specified in the select list and the rows chosen in the where clause. See “Using select into”.
indicates that the type of the external object is a table, directory, or file. If you do not indicate a file, directory, or table, select into assumes that you are using a table.
You cannot specify an external location when using any
part of the partition_clause.
Partitions can be created only on tables on the current server and
database.
indicates that you are selecting into a table or view found on the remote server_name.
indicates the full, operating system-specific path name of the external file or directory you are selecting into. All directories in path_name must be accessible to Adaptive Server.
indicates the delimiter that you are using to separate columns after converting the column’s data to string format. string can have as many as 16 characters. If you do not specify a delimiter, select into uses the tab character.
indicates that you are selecting data into a proxy or other existing table. You cannot select data into a nonproxy table that has indexes. The column list in the select list must match the type, length, and number in the target table.
specifies that the table is to be created on the named segment. Before the on segment_name option can be used, the device must be initialized with disk init, and the segment must be added to the database with sp_addsegment. See your System Administrator or use sp_helpsegment for a list of the segment names available in your database.
specifies records are to be partitioned according values in the partitioning column or columns. Each partitioning column value is compared with sets of user-supplied upper and lower bounds to determine partition assignment.
when used in the partition_clause, specifies a partition key column.
specifies the name of a new partition on which table records are to stored. Partition names must be unique within the set of partitions on a table or index. Partition names can be delimited identifiers if set quoted_identifier is on. Otherwise, they must be valid identifiers.
If partition_name is omitted, Adaptive Server creates a name in the form table_name_partition_id. Adaptive Server truncates partition names that exceed the allowed maximum length.
specifies the inclusive upper bound of values for a named partition. Specifying a constant value for the highest partition bound imposes an implicit integrity constraint on the table. The keyword MAX specifies the maximum value in a given datatype.
when used in the partition_clause, specifies the name of the segment on which to place the partition. When using on segment_name, the logical device must already have been assigned to the database with create database or alter database, and the segment must have been created in the database with sp_addsegment. See your System Administrator or use sp_helpsegment for a list of the segment names available in your database.
specifies records are to be partitioned by a system-supplied hash function. The function computes the hash value of the partition keys that specify the partition to which records are assigned.
specifies records are to be partitioned according to literal values specified in the named column. The partition key contains only one column. You can list up to 250 constants as the partition values for each list partition.
specifies records are to be partitioned in a sequential manner. A round-robin partitioned table has no partitioning key. Neither the user nor the optimizer knows in which partition a particular record resides.
specifies the locking scheme to be used for a table created with a select into command. The default is the server-wide setting for the configuration parameter lock scheme.
limits the number of rows on data pages for a table created with select into. Unlike fillfactor, the max_rows_per_page value is maintained when data is inserted or deleted. max_rows_per_page is not supported on data-only-locked tables.
specifies the expected row size for a table created with the select into command. Valid only for datarows and datapages locking schemes and only for tables that have variable-length rows. Valid values are 0, 1, and any value greater than the minimum row length and less than the maximum row length for the table. The default value is 0, which means that a server-wide default is used.
specifies a ratio of filled pages to empty pages that is to be left as select into allocates extents to store data. This option is valid only for the select into command. For each specified num_pages, one empty page is left for future expansion of the table. Valid values are 0 – 255. The default value is 0.
specifies that the query should silently skip rows with exclusive locks, without waiting and without generating a message.
specifies the identity gap for the table. This value overrides the system identity gap setting for this table only.
is the identity gap amount.
If you are creating a table in a select into statement from a table that has a specific identity gap setting, the new table does not inherit the identity gap setting from the parent table. Instead, the new table uses the identity burning set factor setting. To give the new table a specific identity_gap setting, specify the identity gap in the select into statement. You can give the new table an identity gap that is the same as or different from the parent table.
indicates which tables and views to use in the select statement. from required except when the select list contains no column names (that is, it contains constants and arithmetic expressions only):
select 5 x, 2 y, "the product is", 5*2 Result
x y Result ------- ------- ------------------ ----------- 5 2 the product is 10
At most, a query can reference 50 tables and 14 worktables (such as those created by aggregate functions). The 50-table limit includes:
Tables (or views on tables) listed in the from clause
Each instance of multiple references to the same table (self-joins)
Tables referenced in subqueries
Tables being created with into
Base tables referenced by the views listed in the from clause
lists tables and views used in the select statement. Specify the database name if the table or view is in another database, and specify the owner’s name if more than one table or view of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.
If there is more than one table or view in the list, separate their names by commas. The order of the tables and views following the keyword from does not affect the results.
You can query tables in different databases in the same statement.
Table names and view names can be given correlation names (aliases), either for clarity or to distinguish the different roles that tables or views play in self-joins or subqueries. To assign a correlation name, give the table or view name, then a space, then the correlation name, like this:
select pub_name, title_id from publishers pu, titles t where t.pub_id = pu.pub_id
All other references to that table or view (for example, in a where clause) must use the correlation name. Correlation names cannot begin with a numeral.
specifies the index to use to access table_name. You cannot use this option when you select from a view, but you can use it as part of a select clause in a create view statement.
specifies a parallel partition or index scan, if Adaptive Server is configured to allow parallel processing.
specifies the number of worker processes that will scan the table or index in parallel. If set to 1, the query executes serially.
specifies the I/O size, in kilobytes, for tables bound to caches with large I/Os configured. You cannot use this option when you select from a view, but you can use it as part of a select clause in a create view statement. sp_helpcache shows the valid sizes for the cache an object is bound to or for the default cache. To configure the data cache size, use sp_cacheconfigure.
When using prefetch and designating the prefetch size (size), the minimum is 2K and any power of two on the logical page size up to 16K. prefetch size options in kilobytes are:
Logical page size |
Prefetch size options |
|---|---|
2 |
2, 4, 8 16 |
4 |
4, 8, 16, 32 |
8 |
8, 16, 32, 64 |
16 |
16, 32, 64, 128 |
The prefetch size specified in the query is only a suggestion. To allow the size specification, configure the data cache at that size. If you do not configure the data cache to a specific size, the default prefetch size is used.
If Component Integration Services is enabled, you cannot use prefetch for remote servers.
specifies the buffer replacement strategy to use for the table. Use lru to force the optimizer to read the table into the cache on the MRU/LRU (most recently used/least recently used) chain. Use mru to discard the buffer from cache and replace it with the next buffer for the table. You cannot use this option when you select from a view, but you can use it as part of a select clause in a create view statement.
makes a shared lock on a specified table or view more restrictive by holding it until the transaction completes (instead of releasing the shared lock as soon as the required data page is no longer needed, whether or not the transaction has completed).
The holdlock option applies only to the table or view for which it is specified, and only for the duration of the transaction defined by the statement in which it is used. Setting the transaction isolation level 3 option of the set command implicitly applies a holdlock for each select statement within a transaction. The keyword holdlock is not permitted in a select statement that includes the for browse option. You cannot specify both a holdlock and a noholdlock option in a query.
If Component Integration Services is enabled, you cannot use holdlock for remote servers.
prevents the server from holding any locks acquired during the execution of this select statement, regardless of the transaction isolation level currently in effect. You cannot specify both a holdlock and a noholdlock option in a query.
instructs Adaptive Server to use a shared lock (instead of an update lock) on a specified table or view. This allows other clients to obtain an update lock on that table or view. You can use the shared keyword only with a select clause included as part of a declare cursor statement. For example:
declare shared_crsr cursor for select title, title_id from titles shared where title_id like "BU%"
You can use the holdlock keyword in conjunction with shared after each table or view name, but holdlock must precede shared.
an inner or outer join that uses the ANSI syntax. The from clause specifies the tables to be joined.
includes only the rows of the inner and outer tables that meet the conditions of the on clause. The result set of a query that includes an inner join does not include any null-supplied rows for the rows of the outer table that do not meet the conditions of the on clause.
includes all the rows from the outer table whether or not they meet the conditions of the on clause. If a row does not meet the conditions of the on clause, values from the inner table are stored in the joined table as null values. The where clause of an ANSI outer join restricts the rows that are included in the query result.
left joins retain all the rows of the table reference listed on the left of the join clause. The left table reference is referred to as the outer table or row-preserving table.
In the queries below, T1 is the outer table and T2 is the inner table:
T1 left join T2 T2 right join T1
right joins retain all the rows of the table reference on the right of the join clause (see example above).
used to set the conditions for the rows that are retrieved. A search condition can include column names, expressions, arithmetic operators, comparison operators, the keywords not, like, is null, and, or, between, in, exists, any, and all, subqueries, case expressions, or any combination of these items. See where clause for more information.
finds a value for each group. These values appear as new columns in the results, rather than as new rows.
When group by is used with standard SQL, each item in the select list must either have a fixed value in every row in the group or be used with aggregate functions, which produce a single value for each group. Transact-SQL has no such restrictions on the items in the select list. Also, Transact-SQL allows you to group by any expression (except by a column alias); with standard SQL, you can group by a column only.
You can use the aggregates listed in Table 1-36 with group by (expression is almost always a column name):
See group by and having clauses for more information.
A table can be grouped by any combination of columns—that is, groups can be nested within each other. You cannot group by a column heading; you must use a column name, an expression, or a number representing the position of the item in the select list.
includes all groups in the results, even those that do not have any rows that meet the search conditions. See group by and having clauses for an example.
is an expression that includes no aggregates.
sets conditions for the group by clause, similar to the way that where sets conditions for the select clause. There is no limit on the number of conditions that can be included.
You can use a having clause without a group by clause.
If any columns in the select list do not have aggregate functions applied to them and are not included in the query’s group by clause (illegal in standard SQL), the meanings of having and where are somewhat different.
In this situation, a where clause restricts the rows that are included in the calculation of the aggregate, but does not restrict the rows returned by the query. Conversely, a having clause restricts the rows returned by the query, but does not affect the calculation of the aggregate. See group by and having clauses for examples.
sorts the results by columns. In Transact-SQL, you can use order by for items that do not appear in the select list. You can sort by a column name, a column heading (or alias), an expression, or a number representing the position of the item in the select list (the select_list_number). If you sort by select list number, the columns to which the order by clause refers must be included in the select list, and the select list cannot be * (asterisk).
sorts results in ascending order (the default).
sorts results in descending order.
used with row aggregates (sum, avg, min, max, count, and count_big) to generate control break summary values. The summary values appear as additional rows in the query results, allowing you to see detail and summary rows with one statement.
You cannot use a select into clause with compute.
If you use compute by, you must also use an order by clause. The columns listed after compute by must be identical to or a subset of those listed after order by, and must be in the same left-to-right order, start with the same expression, and not skip any expressions.
For example, if the order by clause is order
by a, b, c, the compute by clause
can be any (or all) of these:
compute by a, b, c compute by a, b compute by a
The keyword compute can be used without by to generate grand totals, grand counts, and so on. order by is optional if you use compute without by. See compute clause for details and examples.
If Component Integration Services is enabled, compute is not forwarded to remote servers.
specifies that a cursor result set is read-only or updatable. You can use this option only within a stored procedure and only when the procedure defines a query for a cursor. In this case, the select is the only statement allowed in the procedure. It defines the for read only or for update option (instead of the declare cursor statement). This method of declaring cursors provides the advantage of page-level locking while fetching rows.
If the select statement in the stored procedure is not used to define a cursor, Adaptive Server ignores the for read only | update option. See the Embedded SQL™ documentation for more information about using stored procedures to declare cursors. For information about read-only or updatable cursors, see the Transact-SQL User’s Guide.
is the list of columns from a cursor result set defined as updatable with the for update option.
specifies the isolation level (0, 1, 2 or 3) of the query. If you omit this clause, the query uses the isolation level of the session in which it executes (isolation level 1 by default). The at isolation clause is valid only for single queries or within the declare cursor statement. Adaptive Server returns a syntax error if you use at isolation:
With a query using the into clause
Within a subquery
With a query in the create view statement
With a query in the insert statement
With a query using the for browse clause
If there is a union operator in the query, you must specify the at isolation clause after the last select. If you specify holdlock, noholdlock, or shared in a query that also specifies at isolation read uncommitted, Adaptive Server issues a warning and ignores the at isolation clause. For the other isolation levels, holdlock takes precedence over the at isolation clause. For more information about isolation levels, see the Transact-SQL User’s Guide.
If Component Integration Services is enabled, you cannot use at isolation for remote servers.
specifies isolation level 0 for the query.
specifies isolation level 1 for the query.
specifies transaction isolation level 2 for the query.
specifies isolation level 3 for the query.
must be attached to the end of a SQL statement sent to Adaptive Server in a DB-Library browse application. See the Open Client DB-Library Reference Manual for details.
specifies the abstract plan to use to optimize the query. It can be a full or partial plan, specified in the abstract plan language. See Chapter 30, “Creating and Using Abstract Plans,” in the Performance and Tuning Guide for more information.
Selects all rows and columns from the publishers table:
select * from publishers
pub_id pub_name city state ------ --------------------------- -------------------- ----- 0736 New Age Books Boston MA 0877 Binnet & Hardley Washington DC 1389 Algodata Infosystems Berkeley CA
Selects all rows from specific columns of the publishers table:
select pub_id, pub_name, city, state from publishers
Selects all rows from specific columns of the publishers table, substituting one column name and adding a string to the output:
select "The publisher's name is", Publisher = pub_name, pub_id from publishers
Publisher pub_id ----------------------- ----------------------------- ------ The publisher’s name is New Age Books 0736 The publisher’s name is Binnet & Hardley 0877 The publisher’s name is Algodata Infosystems 1389
Selects all rows from specific columns of the titles table, substituting column names:
select type as Type, price as Price from titles
Specifies the locking scheme and the reserve page gap for select into:
select title_id, title, price into bus_titles lock datarows with reservepagegap = 10 from titles where type = "business"
Selects only the rows that are not exclusively locked. If any other user has an exclusive lock on a qualifying row, that row is not returned:
select title, price from titles readpast where type = "news" and price between $20 and $30
Selects specific columns and rows, placing the results into the temporary table #advance_rpt:
select pub_id, total = sum (total_sales) into #advance_rpt from titles where advance < $10000 and total_sales is not null group by pub_id having count(*) > 1
Concatenates two columns and places the results into the temporary table #tempnames:
select "Author_name" = au_fname + " " + au_lname into #tempnames from authors
Selects specific columns and rows, returns the results ordered by type from highest to lowest, and calculates summary information:
select type, price, advance from titles order by type desc compute avg(price), sum(advance) by type compute sum(price), sum(advance)
Selects specific columns and rows, and calculates totals for the price and advance columns:
select type, price, advance from titles compute sum(price), sum(advance)
Creates the coffeetabletitles table, a copy of the titles table which includes only books priced over $20:
select * into coffeetabletitles from titles where price > $20
Creates the newtitles table, an empty copy of the titles table:
select * into newtitles from titles where 1 = 0
Updates the existing authors table to include only books priced over $20:
select * into authors from titles where price > $20
Gives an optimizer hint:
select title_id, title from titles (index title_id_ind prefetch 16) where title_id like "BU%"
Selects the IDENTITY column from the sales_east and sales_west tables by using the syb_identity keyword:
select sales_east.syb_identity, sales_west.syb_identity from sales_east, sales_west
Creates the newtitles table, a copy of the titles table with an IDENTITY column:
select *, row_id = identity(10) into newtitles from titles
Specifies a transaction isolation level for the query.
select pub_id, pub_name from publishers at isolation read uncommitted
Selects from titles using the repeatable read isolation level. No other user can change values in or delete the affected rows until the transaction completes:
begin tran select type, avg(price) from titles group by type at isolation repeatable read
Gives an optimizer hint for the parallel degree for the query:
select ord_num from salesdetail (index salesdetail parallel 3)
Joins the titleauthor and the titles tables on their title_id columns. The result set only includes those rows that contain a price greater than 15:
select au_id, titles.title_id, title, price from titleauthor inner join titles on titleauthor.title_id = titles.title_id and price > 15
The result set contains all the authors from the authors table. The authors who do not live in the same city as their publishers produce null values in the pub_name column. Only the authors who live in the same city as their publishers, Cheryl Carson and Abraham Bennet, produce a non-null value in the pub_name column:
select au_fname, au_lname, pub_name from authors left join publishers on authors.city = publishers.city
Create a new table (newtable) from the existing table (oldtable) with an identity gap, you specify it in the select into statement:
select identity into newtable with identity_gap = 20 from oldtable
For more information about identity gaps, see “Managing Identity Gaps in Tables” in Chapter 7, “Creating Databases and Tables” in the Transact-SQL User’s Guide.
Creates a new table sales_report from an existing table sales_detail. The new table is partitioned by range on the qty column.
select * into sales_report partition by range(qty) (smallorder values <= (500) on seg1, bigorder values <= (5000) on seg2) from sales_detail
Use this query to find the statements that incur too many IOs as the candidates for tuning.
select lio_avg, qtext from sysquerymetrics order by lio_avg
The keywords in the select statement, as in all other statements, must be used in the order shown in the syntax statement.
The maximum number of expressions in a select statement is 4096.
The keyword all can be used after select for compatibility with other implementations of SQL. all is the default. Used in this context, all is the opposite of distinct. All retrieved rows are included in the results, whether or not some are duplicates.
Except in create table, create view, and select into statements, column headings may include any characters, including blanks and Adaptive Server keywords, if the column heading is enclosed in quotes. If the heading is not enclosed in quotes, it must conform to the rules for identifiers.
The character string indicated by like cannot be longer than 255 bytes.
You cannot use the select...for browse option on tables containing more than 255 columns.
Column headings in create table, create view, and select into statements, as well as table aliases, must conform to the rules for identifiers.
To insert data with select from a table that has null values in some fields into a table that does not allow null values, you must provide a substitute value for any NULL entries in the original table. For example, to insert data into an advances table that does not allow null values, this example substitutes “0” for the NULL fields:
insert advances select pub_id, isnull(advance, 0) from titles
Without the isnull function, this command would insert all the rows with non-null values into the advances table, and produce error messages for all rows where the advance column in the titles table contained NULL.
If you cannot make this kind of substitution for your data, you cannot insert data containing null values into the columns with the NOT NULL specification.
Two tables can be identically structured, and yet be different as to whether null values are permitted in some fields. Use sp_help to see the null types of the columns in your table.
The default length of the text, unitext, or image data returned with a select statement is 32K. Use set textsize to change the value. The size for the current session is stored in the global variable @@textsize. Certain client software may issue a set textsize command on logging in to Adaptive Server.
Data from remote Adaptive Servers can be retrieved through the use of remote procedure calls. See create procedure and execute for more information.
A select statement used in a cursor definition (through declare cursor) must contain a from clause, but it cannot contain a compute, for browse, or into clause. If the select statement contains any of the following constructs, the cursor is considered read-only and not updatable:
distinct option
group by clause
Aggregate functions
union operator
If you declare a cursor inside a stored procedure with a select statement that contains an order by clause, that cursor is also considered read-only. Even if it is considered updatable, you cannot delete a row using a cursor that is defined by a select statement containing a join of two or more tables. See declare cursor for more information.
If a select statement that assigns a value to a variable returns more than one row, the last returned value is assigned to the variable. For example:
declare @x varchar(40) select @x = pub_name from publishers print @x
(3 rows affected) Algodata Infosystems
Before you write queries using the ANSI inner and outer join syntax, read “Outer Joins” in Chapter 4, “Joins: Retrieving Data From Several Tables,” in the Transact-SQL User’s Guide.
select into is a two-step operation. The first step creates the new table, and the second step inserts the specified rows into the new table.
You can select into a Component Integration
Services existing table.
Because the rows inserted by select into operations are not logged, select into commands cannot be issued within user-defined transactions, even if the ddl in tran database option is set to true. Page allocations during select into operations are logged, so large select into operations may fill the transaction log.
If a select into statement fails after creating a new table, Adaptive Server does not automatically drop the table or deallocate its first data page. This means that any rows inserted on the first page before the error occurred remain on the page. Check the value of the @@error global variable after a select into statement to be sure that no error occurred. Use the drop table statement to remove the new table, then reissue the select into statement.
The name of the new table must be unique in the database and must conform to the rules for identifiers. You can also select into temporary tables (see Examples 7, 8, and 11).
Any rules, constraints, or defaults associated with the base table are not carried over to the new table. Bind rules or defaults to the new table using sp_bindrule and sp_bindefault.
select into does not carry over the base table’s max_rows_per_page value, and it creates the new table with a max_rows_per_page value of 0. Use sp_chgattribute to set the max_rows_per_page value.
The select into/bulkcopy/pllsort option must be set to true (by executing sp_dboption) in order to select into a permanent table. You do not have to set the select into/bulkcopy/pllsort option to true in order to select into a temporary table, since the temporary database is never recovered.
After you have used select into in a database, you must perform a full database dump before you can use the dump transaction command. select into operations log only page allocations and not changes to data rows. Therefore, changes are not recoverable from transaction logs. In this situation, issuing the dump transaction statement produces an error message instructing you to use dump database instead.
By default, the select into/bulkcopy/pllsort option is set to false in newly created databases. To change the default situation, set this option to true in the model database.
select into runs more slowly while a dump database is taking place.
You can use select into to create a duplicate table with no data by having a false condition in the where clause (see Example 12).
You must provide a column heading for any column in the select list that contains an aggregate function or any expression. The use of any constant, arithmetic or character expression, built-in functions, or concatenation in the select list requires a column heading for the affected item. The column heading must be a valid identifier or must be enclosed in quotation marks (see Examples 7 and 8).
Datatypes and nullability are implicitly assigned to literal values when select into is used, such as:
select x = getdate() into mytable
This results in a non-nullable column, regardless of whether allow nulls by default is on or not. It depends upon how the select commands are used and with what other commands within the syntax.
The convert syntax allows you to explicitly specify the datatype and nullability of the resulting column, not the default.
Wrap getdate with a function that does result in a null, such as:
select x = nullif(getdate(), "1/1/1900") into mytable
Or, use the convert syntax:
select x = convert(datetime null, getdate()) into mytable
You cannot use select into inside a user-defined transaction or in the same statement as a compute clause.
To select an IDENTITY column into a result table, include the column name (or the syb_identity keyword) in the select statement’s column_list. The new column observes the following rules:
If an IDENTITY column is selected more than once, it is defined as NOT NULL in the new table. It does not inherit the IDENTITY property.
If an IDENTITY column is selected as part of an expression, the resulting column does not inherit the IDENTITY property. It is created as NULL if any column in the expression allows nulls; otherwise, it is created as NOT NULL.
If the select statement contains a group by clause or aggregate function, the resulting column does not inherit the IDENTITY property. Columns that include an aggregate of the IDENTITY column are created NULL; others are NOT NULL.
An IDENTITY column that is selected into a table with a union or join does not retain the IDENTITY property. If the table contains the union of the IDENTITY column and a NULL column, the new column is defined as NULL. Otherwise, it is defined as NOT NULL.
You cannot use select into to create a new table with multiple IDENTITY columns. If the select statement includes both an existing IDENTITY column and a new IDENTITY specification of the form column_name = identity(precision), the statement fails.
If Component Integration Services is enabled, and if the into table resides on Adaptive Server, Adaptive Server uses bulk copy routines to copy the data into the new table. Before doing a select into with remote tables, set the select into/bulkcopy database option to true.
For information about the Embedded SQL command select into host_var_list, see the Open Client Embedded SQL Reference Manual.
Use the convert command to change the nullability of a target column into which you are selecting data. For example, the following selects data from the titles table into a target table named temp_titles, but converts the total_sales column from null to not null:
select title, convert (char(100) not null, total_sales) total_sales into #tempsales from titles
The lock option, used with select...into, allows you to specify the locking scheme for the table created by the command. If you do not specify a locking scheme, the default locking scheme, as set by the configuration parameter lock scheme, is applied.
When you use the lock option, you can also specify the space management properties max_rows_per_page, exp_row_size, and reservepagegap.
You can change the space management properties for a table created with select into, using sp_chgattribute.
The partitions_clause, when used with select...into, allows you to specify the partition properties of the table created by the command. (See create table for more information.) If you do not specify a partition type, Adaptive Server creates an unpartitioned table. If any row to be inserted does not satisfy the criteria for any partition in the target table, select...into fails.
The index, prefetch and lru | mru options specify the index, cache and I/O strategies for query execution. These options override the choices made by the Adaptive Server optimizer. Use them with caution, and always check the performance impact with set statistics io on. For more information about using these options, see the Performance and Tuning Guide.
The parallel option reduces the number of worker threads that the Adaptive Server optimizer can use for parallel processing. The degree_of_parallelism cannot be greater than the configured max parallel degree. If you specify a value that is greater than the configured max parallel degree, the optimizer ignores the parallel option.
When multiple worker processes merge their results, the order of rows that Adaptive Server returns may vary from one execution to the next. To get rows from partitioned tables in a consistent order, use an order by clause, or override parallel query execution by using parallel 1 in the from clause of the query.
A from clause specifying parallel is ignored if any of the following conditions is true:
The select statement is used for an update or insert.
The from clause is used in the definition of a cursor.
parallel is used in the from clause within any inner query blocks of a subquery.
The select statement creates a view.
The table is the inner table of an outer join.
The query specifies min or max on the table and specifies an index.
An unpartitioned clustered index is specified or is the only parallel option.
The query specifies exists on the table.
The value for the configuration parameter max scan parallel degree is 1 and the query specifies an index.
A nonclustered index is covered. For information on index covering, see Chapter 9, “How Indexes Work” in the Performance and Tuning Guide.
The table is a system table or a virtual table.
The query is processed using the OR strategy. For an explanation of the OR strategy, see the Performance and Tuning Guide.
The query returns a large number of rows to the user.
The readpast option allows a select command to access the specified table without being blocked by incompatible locks held by other tasks. readpast queries can only be performed on data-only-locked tables.
If the readpast option is specified for an allpages-locked table, the readpast option is ignored. The command operates at the isolation level specified for the command or session. If the isolation level is 0, dirty reads are performed, and the command returns values from locked rows and does not block. If the isolation level is 1 or 3, the command blocks when pages with incompatible locks must be read.
The interactions of session-level isolation levels and readpast on a table in a select command are shown in Table 1-37.
select commands that specify readpast fail with an error message if they also include any of the following:
An at isolation clause, specifying 0 or read uncommitted
An at isolation clause, specifying 3 or serializable
The holdlock keyword on the same table
If at isolation 2 or at isolation repeatable read is specified in a select query that specifies readpast, shared locks are held on the readpast tables until the statement or transaction completes.
If a select command with the readpast option encounters a text column that has an incompatible lock on it, readpast locking retrieves the row, but returns the text column with a value of null. No distinction is made, in this case, between a text column containing a null value and a null value returned because the column is locked.
ANSI SQL – Compliance level: Entry-level compliant.
The following are Transact-SQL extensions:
select into to create a new table
lock clauses
compute clauses
Global and local variables
index clause, prefetch, parallel and lru | mru
holdlock, noholdlock, and shared keywords
“column_heading = column_name”
Qualified table and column names
select in a for browse clause
The use, within the select list, of columns that are not in the group by list and have no aggregate functions
at isolation repeatable read | 2 option
select permission defaults to the owner of the table or view, who can transfer it to other users.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
62 |
select |
select from a table |
|
63 |
select |
select from a view |
|
Commands compute clause, create index, create trigger, delete, group by and having clauses, insert, order by clause, set, union operator, update, where clause
Functions avg, count, isnull, max, min, sum
System procedures sp_cachestrategy, sp_chgattribute, sp_dboption
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
