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] [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}
index_compression_clause::=
with index_compression = {none | page}
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"
Null values are considered equal for the purposes of the keyword distinct: only one NULL is selected, no matter how many are encountered.
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.
n is an unsigned 32-bit value between 0 through 232-1 (4GB-1 or 4,294,967,295). Zero indicates “no” rows.
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.
“*”, 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 (int | smallint | tinyint | precision)
If you specify int, smallint, or tinyint, the resulting column is an integer. If you specify precision, the result is numeric datatype.
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.
@variable = expression [, @variable = expression ...]
You cannot combine variable assignment with any other select_list option.
0 – the row is not compressed.
1 through 9 – the SAP ASE server uses ZLib compression. Generally, the higher the compression number, the more the SAP ASE server compresses the LOB data, and the greater the ratio between compressed and uncompressed data (that is the greater the amount of space savings, in bytes, for the compressed data versus the size of the uncompressed data).
However, the amount of compression depends on the LOB content, and the higher the compression level , the more CPU-intensive the process. That is, level 9 provides the highest compression ratio but also the heaviest CPU usage.
100 – the SAP ASE server uses FastLZ compression. The compression ratio that uses the least CPU usage; generally used for shorter data.
101 – the SAP ASE server uses FastLZ compression. A value of 101 uses slightly more CPU than a value of 100, but uses a better compression ratio than a value of 100.
The compression algorithm ignores rows that do not use LOB data.
By default, a LOB column in the target table inherits the storage property of the corresponding LOB column 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, the column then automatically uses off-row storage unless you change the setting by specifying in row [(length)].
full – the SAP ASE server logs all transactions
minimal – the SAP ASE server does not log row or page changes
If partition_name is omitted, the SAP ASE server creates a name in the form table_name_partition_id. The SAP ASE server truncates partition names that exceed the allowed maximum length.
none – the data in this table or partition is not compressed. For partitions, none indicates that data in this partition remains uncompressed even if the table compression is altered to row or page compression.
row – compresses one or more data items in an individual row. The SAP ASE server stores data in a row-compressed form only if the compressed form saves space compared to an uncompressed form. Set row compression at the partition or table level.
page – when the page fills, existing data rows that are row-compressed are then compressed using page-level compression to create page-level dictionary, index, and character-encoding entries. Set page compression at the partition or table level.
The SAP ASE server compresses data at the page level only after it has compressed data at the row level, so setting the compression to page implies both page and row compression.
NONE – indexes on the specified table are not compressed. Indexes that are specifically created with index_compression = PAGE are compressed.
PAGE – all indexes on the specified table are compressed. Indexes that are specifically created with index_compression = NONE are not compressed.
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.
select 5 x, 2 y, "the product is", 5*2 Result
x y Result ------- ------- ------------------ ----------- 5 2 the product is 10
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
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.
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.
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 CIS is enabled, you cannot use prefetch for remote servers.
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 CIS is enabled, you cannot use holdlock for remote servers.
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.
T1 left join T2 T2 right join T1
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 this table with group by (expression is almost always a column name):
| Aggregate Function | Result |
|---|---|
| sum ([all | distinct] expression) | Total of the values in the numeric column. |
| avg ([all | distinct] expression) | Average of the values in the numeric column. |
| count ([all | distinct] expression) | Number of (distinct) non-null values in the column returned as an integer. |
| count_big ([all | distinct] expression) | Number of distinct non-null values in the column returned as a bigint. |
| count (*) | Number of selected rows returned as an integer. |
| count_big (*) | Number of selected rows returned as a bigint. |
| max (expression) | Highest value in the column. |
| min (expression) | Lowest value in the column. |
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.
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.
Using select max with order by can return more than one row in the result set.
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.
If CIS is enabled, compute is not forwarded to remote servers.
With SAP ASE versions earlier than 15.7, 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.
Also, with SAP ASE versions earlier than 15.7, if the select statement in the stored procedure is not used to define a cursor, the SAP ASE server ignores the for read only | update option. See the Embedded SQL™ documentation for more information about using stored procedures to declare cursors.
With SAP ASE 15.7 and later, if select for update is set, you can use the for update option at the language level outside of a stored procedure, but within a transaction. Such a select need not refer to a cursor. When you use select for update in datarows-locked tables, the selected rows are exclusively locked for the duration of the transaction.
For information about read-only or updatable cursors, see the Transact-SQL User’s Guide.
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, the SAP ASE 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 CIS is enabled, you cannot use at isolation for remote servers.
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
select pub_id, pub_name, city, state from publishers
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
select type as Type, price as Price from titles
select title_id, title, price into bus_titles lock datarows with reservepagegap = 10 from titles where type = "business"
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
select title, price from titles readpast where type = "news" and price between $20 and $30
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
select top 3 au_lname from authors
select "Author_name" = au_fname + " " + au_lname into #tempnames from authors
select type, price, advance from titles order by type desc compute avg (price), sum (advance) by type compute sum (price), sum (advance)
select type, price, advance from titles compute sum (price), sum (advance)
select * into coffeetabletitles from titles where price > $20
select * into newtitles from titles where 1 = 0
select title_id, title from titles (index title_id_ind prefetch 16) where title_id like "BU%"
select sales_east.syb_identity, sales_west.syb_identity from sales_east, sales_west
select *, row_id = identity (10) into newtitles from titles
select pub_id, pub_name from publishers at isolation read uncommitted
begin tran select type, avg (price) from titles group by type at isolation repeatable read
select ord_num from salesdetail (index salesdetail parallel 3)
select au_id, titles.title_id, title, price from titleauthor inner join titles on titleauthor.title_id = titles.title_id and price > 15
select au_fname, au_lname, pub_name from authors left join publishers on authors.city = publishers.city
select identity into newtable with identity_gap = 20 from oldtable
For more information about identity gaps, see Managing Identity Gaps in Tables in the Transact-SQL Users Guide.
select * into bay_area_authors with compression = row from authors where postalcode like '94%'
select * into titles_2 (title not compressed, advance not compressed) with compression = page from titles
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
select * into sales_report partition by range (qty) (smallorder values <= (500) on seg1, bigorder values <= (5000) on seg2) from sales_detail
select lio_avg, qtext from sysquerymetrics order by lio_avg
select title_id, title, price into bus_titles with dml_logging = minimal from titles
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 SAP ASE 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.
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 the SAP ASE server.
Data from remote SAP ASE servers can be retrieved through the use of remote procedure calls. See create procedure and execute for more information.
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.
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 the Transact-SQL Users Guide.
avg, count, isnull, max, min, sum in Reference Manual: Building Blocks
sp_cachestrategy, sp_chgattribute, sp_dboption in Reference Manual: Procedures
ANSI SQL – Compliance level: Entry-level compliant.
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
The permission checks for select differ based on your granular permissions settings.
| Setting | Description |
|---|---|
Enabled | With granular permissions enabled, you must be the table or view owner. You must be a user with select permission on the table or view. |
Disabled | With granular permissions disabled, you must be the table or view owner or a user with sa_role. You must be a user with select permission on the table or view. 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:
| Information | Values |
|---|---|
Event | 62 |
Audit option | select |
Command or access audited | select from a table |
Information in extrainfo |
|
| Information | Values |
|---|---|
Event | 63 |
Audit option | select |
Command or access audited | select from a view |
Information in extrainfo |
|