select

Description

Retrieves rows from database objects.

Syntax

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]
	[for_xml_clause]

select_list ::=

into_clause ::= 
	into [[database.] owner.] table_name 
		[(colname encrypt [with [database.[owner].]keyname] [, 
			colname encrypt_clause ...])]
			| [compressed = compression_level | not compressed]
			[in row [(length)] | off row ]
		[{[external table at]
			'server_name.[database].[owner].object_name’ 
			| external directory at ‘pathname’ 
			| external file at ‘pathname’ [column delimiter ‘string’]}]
		[on segment_name]
		dml_logging = (full | minimal)
		[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] 
					[compression_clause] [on segment_name] 
				[, [partition_name] values <= ({constant | MAX} 
					[, {constant | MAX}] ...) [on segment_name]]...)
					[compression_clause] [on segment_name] 

		| partition by hash (column_name[, column_name]...)
			{ (partition_name [on segment_name] 
					[compression_clause] [on segment_name] 
				[, partition_name [on segment_name]]...) 
					[compression_clause] [on segment_name] 
			| number_of_partitions 
				[on (segment_name[, segment_name] ...)]} 

		| partition by list (column_name) 
			 ([partition_name] values (constant[, constant] ...) 
				[compression_clause] [on segment_name]
				[, [partition_name] values (constant[, constant] ...) 
					[compression_clause] [on segment_name] 

		| partition by roundrobin 
			{ (partition_name [on segment_name] 
				[, partition_name [on segment_name]]...) 
				[compression_clause] [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 
		| compression = {none | page | row}
		| lob_compression = off | compression_level]

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

compression_clause::=
		with compression = {none | page | row}

where_clause ::= 
	where search_conditions
	for update [of column_list

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"

Parameters

Examples

Example 1

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   

Example 2

Selects all rows from specific columns of the publishers table:

select pub_id, pub_name, city, state from publishers 

Example 3

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  

Example 4

Selects all rows from specific columns of the titles table, substituting column names:

select type as Type, price as Price
from titles

Example 5

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"

Example 6

Encrypts the creditcard column when selecting into the bigspenders table:

select creditcard, custid, sum(amount) 
     into #bigspenders (creditcard
     encrypt with cust.database.new_cc_key) from daily_xacts 
     group by creditcard having sum(amount) > $5000

Example 7

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

Example 8

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 

Example 9

Selects the top 3 rows from au_lname from the authors table:

select top 3 au_lname from authors

Example 10

Concatenates two columns and places the results into the temporary table #tempnames:

select "Author_name" = au_fname + " " + au_lname
    into #tempnames
    from authors

Example 11

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)

Example 12

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)

Example 13

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

Example 14

Creates the newtitles table, an empty copy of the titles table:

select * into newtitles from titles 
where 1 = 0

Example 15

Gives an optimizer hint:

select title_id, title
    from titles (index title_id_ind prefetch 16)
    where title_id like "BU%"

Example 16

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

Example 17

Creates the newtitles table, a copy of the titles table with an IDENTITY column:

select *, row_id = identity (10)
into newtitles from titles

Example 18

Specifies a transaction isolation level for the query.

select pub_id, pub_name
from publishers
at isolation read uncommitted

Example 19

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

Example 20

Gives an optimizer hint for the parallel degree for the query:

select ord_num from salesdetail 
     (index salesdetail parallel 3)

Example 21

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

Example 22

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

Example 23

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 Users Guide.

Example 24

Creates a new table, bay_area_authors with row-level compression, and populates it with information about authors that live in the San Francisco Bay Area:

select * into bay_area_authors
with compression = row
from authors
where postalcode like '94%'

Example 25

Creates a new table named titles_2 that compresses all columns except title and advance:

select * into titles_2
(title not compressed,
advance not compressed)
with compression = page
from titles

Example 26

Set the select for update configuration parameter, perform a select for update, then an update within the same transaction:

sp_configure 'select for update', 1

Parameter Name              Default  Memory Used  Config Value  Run Value 
    Unit             Type
--------------------------- --------  ----------  -----------  ----------
    ------------------- --------------------
select for update       0            0           1             1
    not applicable   dynamic
(1 row affected)
Resulting configuration value and memory use have not changed from previous values: new configuration value 1, previous value 1.
(return status = 0)

begin tran

select c_int, c_bigdatetime from basetbl1 where c_int > 90 for update of c_bigint

c_int           c_bigdatetime
-----------     -----------------------------
91              Sep 14 2009  9:00:00.000000PM
92              Sep 15 2009  9:00:00.000000PM
93              Sep 16 2009  9:00:00.000000PM
94              Sep 17 2009  9:00:00.000000PM
95              Sep 18 2009  9:00:00.000000PM
96              Sep 19 2009  9:00:00.000000PM
97              Sep 20 2009  9:00:00.000000PM
98              Sep 21 2009  9:00:00.000000PM
99              Sep 22 2009  9:00:00.000000PM
100             Sep 23 2009  9:00:00.000000PM
(10 rows affected)

update basetbl1 set c_bigint = 5000 where c_int > 90

(10 rows affected)
commit tran
go

Example 27

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

Example 28

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

Example 29

Selects the titles table into the pubs3 database:

select title_id, title, price
into bus_titles
with dml_logging = minimal
from titles

Usage

• 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 CIS 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 can be used with an archive 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 CIS 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
  

• Destination tables you create with select into do not inherit any configuration from the source table. That is, if the table from which you pull data is configured for row-level compression, the table that results from the select into is not configured for compression unless you explicitly state the compression level.

• You can indicate column-, partition-, and table-level compression for the destination table (the compression level of the partition overrides the compression level of the table).

• When you compress the target table, Adaptive Server selects the compression level for columns (if they qualify), regardless of the compression level of the corresponding columns in the source table.

• The select into command can include a list of columns that are not compressed on the target table.

• You cannot encrypt compressed columns on a target table.

• max_rows_per_page applies only to allpages-locked compressed tables; you cannot use it on data-only-locked tables.

• You can use exp_row_size on allpages- and data-only-locked tables with variable-length columns. However, the table must be able to expand updates on the same page without causing page splits on allpages-locked tables or or row forwarding on data-only-locked tables.

  exp_row_size computes space limits identically for compressed and uncompressed tables. However, because compression results in a page with a lot of free space, the space set aside for exp_row_size may cause far fewer rows to fit on the page than would fit on this page if you did not set exp_row_size.

  You cannot use exp_row_size on tables with fixed-length columns, even though Adaptive Server may convert some fixed-length columns to variable-length columns during the compression process.

• fillfactor applies determines the space used on the data pages after compression. This is relevant only when you are creating clustered indexes, an operation which requires a sort of the data pages.

• For Adaptive Server 15.7 and later, if the configuration parameter select for update is set to 1, the rows selected by select for update will be exclusively locked provided that the command is executed on a datarows-locked table within a transaction context, or in chained mode. If select for update is run within a cursor context, the cursor open and fetch statements must be within the context of a transaction.

• Rows selected with select for update, within or outside of a cursor context, retain an exclusive lock until the transaction is complete.

• Limitations of select for update:

  • select for update is not valid in a sub-query block.

  • select for update is applicable only when the select returns rows directly from a base table and not from a work table. You cannot use select for update with queries that have aggregates or group by, computed, union, having, or distinct clauses.

  • More rows may qualify for the actual transaction update than with select for update. These rows may appear in the updated set. Prevent such “phantom” rows by using isolation level 3.

  • During select processing, concurrent select for update tasks may attempt to lock the the same set of rows in different order, causing application deadlocks. However, once select for update completes, subsequent updates to the set of rows are not blocked, and encounter no deadlocks.

  • All existing restrictions on updateable cursors apply to select for update both within and outside of the cursor context. The only difference is that with select for update, the order by clause is supported with an updateable cursor. The limitations and restrictions on updateable cursors apply to both language and execute cursors.

  • All tables referenced by select for update must be from the same database.

  • select for update is supported only on tables with datarows locked.

• By default, LOB columns in the target table inherit the storage property of the corresponding LOB columns in the select list. If the target table’s LOB column is produced as a result of an expression, such as the convert(text, column) built-in function, then the column automatically uses off-row storage.

• 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 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.

• If you use the encrypt clause without specifying a key name, Adaptive Server uses the database default key to encrypt the data in the target column.

• If a column in the source table is encrypted and you do not specify the encrypt clause for the target column, Adaptive Server decrypts the data in the source table and inserts plain text data in the target column.

• If you specify encryption for the target column with the same key used for the source column data, and if the key does not use an initialization vector or random padding, then Adaptive Server copies the data from the source column to the target column as cipher text, without intermediate decryption and reencryption operations.

• If, however, you specify encryption for the target column using a different key from that used for the source column, or if the key uses an initialization vector or padding during encryption, Adaptive Server performs a decryption and encryption operation for each selected row of the encrypted column.

• 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.

When the source text of a stored procedure or trigger is stored in the system table syscomments, a query using select * is stored in syscomments expanding the column list referenced in the select *.

For example, a select * from a table containing the columns col1 and col2 is stored as:

select <table>.col1, <table>.col2 from <table>

In Adaptive Server version 12.5.4, the expanding of the column-list has been enhanced to check whether identifiers (table-names, column-names and so on) comply with the rules for identifiers.

For example, if a table includes the columns col1 and 2col, the second column-name starts with a number, which can only be included by using brackets in the create table statement.

When performing a select * in a stored procedure or trigger from this table, the text in syscomments looks similar to:

select <table>.col1, <table>[2col] from <table>

For all identifiers used in the text that exands a select *, brackets are added when the identifier does not comply with the rules for identifiers.

You must add brackets around identifiers to make sure Adaptive Server can use the SQL-text while performing an upgrade to a more recent release.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

Transact-SQL extensions include:

• 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

Permissions

select permission defaults to the owner of the table or view, who can transfer it to other users.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also