create table

Creates new tables and optional integrity constraints, defines computed columns and table, row, index, and partition compression levels. Defines encrypted columns, decrypt defaults, and partition properties. Removes residual data from deletions.

Note: Syntax for creating table partitions is listed separately. See the syntax for partitions.

Syntax

create table [[database.[owner].]table_name (column_name datatype
	[default {constant_expression  | user | null}]
	[{identity | null | not null}]
		[ in row [(length)] | off row ]
		[[constraint constraint_name]
		{{unique | primary key}
		[clustered | nonclustered] [asc | desc]
		[with {fillfactor = pct,
				max_rows_per_page = num_rows,}
				reservepagegap = num_pages]
				dml_logging = {full | minimal}
				[deferred_allocation | immediate_allocation])
		[on segment_name]
		| references [[database.]owner.]ref_table
			[(ref_column)]
			[match full]
			| check (search_condition)}]}
	[[encrypt [with [database.[owner].]key_name] 
		[decrypt_default constant_expression | null]]
						[not compressed]
		[compressed = {compression_level | not compressed}
	[[constraint [[database.[owner].]key_name]
		{unique | primary key} 
			[clustered | nonclustered]
			 (column_name [asc | desc]
				[{, column_name [asc | desc]}...])
			[with {fillfactor = pct 
				max_rows_per_page = num_rows,
				reservepagegap = num_pages}]
			[on segment_name]
		| foreign key (column_name [{,column_name}...])
			references [[database.]owner.]ref_table
				[(ref_column [{, ref_column}...])]
				[match full]
		| check (search_condition) ...}
	[{, {next_column | next_constraint}}...])
	[lock {datarows | datapages | allpages}]
	[with {max_rows_per_page = num_rows, 
			exp_row_size = num_bytes, 
			reservepagegap = num_pages,
			identity_gap = value
			transfer table [on | off],
			dml_logging = {full | minimal},
			compression = {none | page | row},
            "erase residual data" {on | off}}],
			lob_compression = off | compression_level,
            index_compression [={NONE | PAGE}
	[on segment_name]
	[partition_clause]
	[[external table] at pathname]
	[for load]
	compression_clause::=
		with compression = {none | page | row}
Use this syntax for partitions:
partition_clause::=	partition by range (column_name[, column_name]...) 
		 ([partition_name] values <= ({constant | MAX} 
			[, {constant | MAX}] ...) 
				[compression_clause] [on segment_name] 
			[, [partition_name] values <= ({constant | MAX} 
				[, {constant | MAX}] ...)
				[compression_clause] [on segment_name]]...)

	| partition by hash (column_name[, column_name]...)
		{ (partition_name 
				[compression_clause] [on segment_name] 
			[, partition_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 
				[compression_clause] [on segment_name]]...) 
		| number_of_partitions 
			[on (segment_name[, segment_name]...)]}
Use this syntax for computed columns
create table [[database.[owner].] table_name
	 (column_name {compute | as} 
		computed_column_expression 
			[[materialized] [not compressed]] | [not materialized]}
Use this syntax to create a virtually hashed table
create table [database.[owner].]table_name 
. . .
	| {unique | primary key}
	using clustered
	(column_name [asc | desc] [{, column_name [asc | desc]}...])=
	(hash_factor [{, hash_factor}...])
		with max num_hash_values key

Parameters

Examples

Usage

See also sp_addmessage, sp_addsegment, sp_addtype, sp_bindmsg, sp_chgattribute, sp_commonkey, sp_depends, sp_foreignkey, sp_help, sp_helpjoins, sp_helpsegment, sp_primarykey, sp_rename, sp_spaceused in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Entry-level compliant.

Transact-SQL extensions include:
  • Use of a database name to qualify a table or column name

  • IDENTITY columns

  • The not null column default

  • The asc and desc options

  • The reservepagegap option

  • The lock clause

  • The on segment_name clause

    See System and User-Defined Datatypes in Reference Manual: Building Blocks for datatype compliance information.

Permissions

Any user can create temporary tables and new tables with logging disabled.

The following describes permission checks for create table that differ based on your granular permissions settings.

SettingDescription
Enabled

With granular permissions enabled, you must have the create table privilege to create a table. To create a table for another user, you must have the create any table privilege.

Disabled

With granular permissions disabled, you must be the database owner, a user with sa_role, or a user with the create table privilege to create a table. To create a table for another user, you must have sa_role.

Auditing

Values in event and extrainfo columns of sysaudits are:

InformationValues
Event

10

Audit option

create

Command or access audited

create table

Information in extrainfo
  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

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

  • If the with option for with transfer table [on | off] is:
    • on – the SAP ASE server prints WITH TRANSFER TABLE ON in the extra info in the audit record.

    • off – the SAP ASE server prints WITH TRANSFER TABLE OFF.

Related reference
alter table
create default
create existing table
create index
create rule
create schema
create trigger
create view
drop index
drop rule
drop table
execute
grant
if...else
load database
select
while
disk init
alter database
create database