select

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 ::=
Note: For details on select_list, see the “Parameters” section.
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"
Note: See the XML Services book for syntax, examples, and usage information for the select...for_xml_clause.

Parameters

Examples

Usage

Before you write queries using the ANSI inner and outer join syntax, read Outer Joins in the Transact-SQL Users Guide.

See also:
  • avg, count, isnull, max, min, sum in Reference Manual: Building Blocks

  • sp_cachestrategy, sp_chgattribute, sp_dboption in Reference Manual: Procedures

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

The permission checks for select differ based on your granular permissions settings.

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

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

62

Audit option

select

Command or access audited

select from a table

Information in extrainfo
  • Roles – current active roles

  • Keywords or optionsselect, select into, or readtext

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

InformationValues
Event

63

Audit option

select

Command or access audited

select from a view

Information in extrainfo
  • Roles – current active roles

  • Keywords or optionsselect, select into, or readtext

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

Related reference
alter database
compute Clause
create database
create index
create table
create trigger
delete
disk init
group by and having Clauses
insert
order by clause
set
union operator
update
where clause