create table

Description

• Creates new tables and optional integrity constraints.

• Defines computed columns.

• Defines table, row, and partition compression levels.

• Defines encrypted columns and decrypt defaults on encrypted columns.

• Defines the table’s partition property. 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})
		[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}}]
			lob_compression = off | compression_level
	[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

Example 1

Creates the foo table using the @@spid global variable with the default parameter:

create table foo (
      a      int
    , b      int      default @@spid
)

Example 2

Creates the titles table:

create table titles (
      title_id       tid             not null
    , title          varchar (80)    not null
    , type           char (12)       not null
    , pub_id         char (4)        null
    , price          money           null
    , advance        money           null
    , total_sales    int             null
    , notes          varchar (200)   null
    , pubdate        datetime        not null
    , contract       bit             not null
)

Example 3

Creates a table mytable using for load:

create table mytable (
      col1    int
    , col2    int
    , col3    (char 50)
)
partitioned by roundrobin 3 for load

The new table is unavailable to any user activities until it is unpartitioned.

1. Load the data into mytable, using bcp in.

2. Unpartition mytable.

The table is now available for any user activities.

Example 4

Creates the compute table. The table name and the column names, max and min, are enclosed in double quotes because they are reserved words. The total score column name is enclosed in double quotes because it contains an embedded blank. Before creating this table, you must set quoted_identifier on.

create table "compute" (
      "max"            int
    , "min"            int
    , "total score"    int
)

Example 5

Example 29

Creates a virtually hashed table named orders on the pubs2 database on the order_seg segment:

create table orders (
      id    int default NULL
    , age    int
    , primary key using
          clustered (id,age) = (10,1) with max 100 key
    , name    varchar(30)
)
on order_seg

The layout for the data is:

• The order_seg segment starts on page ID 51200.

• The ID for the first data OAM page is 51201.

• The maximum rows per page is 42.

• The row size is 45.

• The root index page of the overflow clustered region is 51217.

Usage

• create table creates a table and optional integrity constraints. The table is created in the currently open database unless you specify a different database in the create table statement. You can create a table or index in another database, if you are listed in the sysusers table and have create table permission in the database.

• Space is allocated to tables and indexes in increments of one extent, or eight pages, at a time. Each time an extent is filled, another extent is allocated. To see the amount of space allocated and used by a table, use sp_spaceused.

• The maximum length for in-row Java columns is determined by the maximum size of a variable-length column for the table’s schema, locking style, and page size.

• create table performs error checking for check constraints before it creates the table.

• When using create table from CIS with a column defined as char (n) NULL, CIS creates the column as varchar (n) on the remote server.

• Use the asc and desc keywords after index column names to specify the sort order for the index. Creating indexes so that columns are in the same order specified in the order by clause of queries eliminates the sorting step during query processing.

• If an application inserts short rows into a data-only-locked table and updates them later so that their length increases, use exp_row_size to reduce the number of times that rows in data-only-locked tables are forwarded to new locations.

• The location information provided by the at keyword is the same information that is provided by sp_addobjectdef. The information is stored in the sysattributes table.

• Unless you state otherwise, Adaptive Server:

  • Sets data compression to NULL when you create a table.

  • Preserves the existing compression level when you modify a table.

  • Sets all partitions to the compression level specified in the create table clause.

• You can create a table with table-level compression but leave some partitions uncompressed, which allows you to maintain uncompressed data in an active partitions format, and to periodically compress the data when appropriate.

• Adaptive Server supports partition-level compression for all forms of partitioning except round-robin partitions.

• Columns marked as not compressed are not selected for row or page compression. However, in-row columns (including materialized computed columns) are eligible for compression:

  • All fixed-length data smaller than 4 bytes is ineligible for row compression. However, Adaptive Server may compress these datatypes during page-index compression.

  • All data, fixed or with a variable length of 4 bytes or larger, is eligible for row compression.

• By default, Adaptive Server creates uncompressed nonmaterialized computed columns.

• Adaptive Server first compresses the columns eligible for compression at the row level. If the compressed row is longer than the uncompressed row, Adaptive Server discards the compressed row and stores the uncompressed row on disk, ensuring that compression does not waste space.

• Data pages may simultaneously contain compressed and uncompressed data rows.

• You may compress fixed-length columns.

• You can use the with exp_row_size clause to create compressed data-only-locked (DOL) tables only for fixed-length rows. You cannot use the with exp_row_size clause on allpages-locked (APL) tables.

• If you specify an expected row size, but the uncompressed row length is smaller than the expected row size, Adaptive Server does not compress the row.

• After you enable compression for a table, all bcp and DML operations that are executed on the table compress the data.

• Compression may allow you to store more rows on a page, but it does not change the maximum row size of a table. However, it can change the effective minimum row size of a table.

• Use not compressed for columns that could be row- or page-index compressed, but for which the nature of the column makes compression inapplicable or meaningless (for example, columns that use the bit datatype, encryption, or a timestamp column).

• Compressing a table does not compress its indexes.

• You cannot compress:

  • System tables

  • Worktables

  • In-row Java columns

  • Nonmaterialzed computed columns

  • IDENTITY columns

  • Timestamps added for data transfer

  • All datatypes; see the Compression Users Guide for a list of unsupported datatypes

  • Encrypted columns

• You cannot create a table for compression if the minimum row size exceeds the size of the maximum user data row for the configured locking scheme and page size combination. For example, you cannot create a data-only-locked table with a 2K page size that includes column c1 with a char(2007) datatype because it exceeds the maximum user data row size. For row and page compression, Adaptive Server performs the same row size check as for a new table.

• You cannot create a table for row or page compression that has only short, fixed-length columns smaller than 4 bytes.

Compressing data on LOB tables include these restrictions. You cannot:

• Compress computed text columns

• Issue LOB compression clauses (for example, lob_compression =) on regular columns, XML data

• Use LOB compression for system and worktables

• Temporary tables are stored in the temporary database, tempdb.

• The first 13 characters of a temporary table name must be unique per session. Such tables can be accessed only by the current Adaptive Server session. They are stored in tempdb..objects by their names plus a system-supplied numeric suffix, and they disappear at the end of the current session or when they are explicitly dropped.

• Temporary tables created with the “tempdb..” prefix are shareable among Adaptive Server user sessions. Create temporary tables with the “tempdb..” prefix from inside a stored procedure only if you intend to share the table among users and sessions. To avoid inadvertent sharing of temporary tables, use the “#” prefix when creating and dropping temporary tables in stored procedures.

• Temporary tables can be used by multiple users during an Adaptive Server session. However, the specific user session usually cannot be identified because temporary tables are created with the “guest” user ID of 2. If more than one user runs the process that creates the temporary table, each user is a “guest” user, so the uid values are all identical. Therefore, there is no way to know which user session in the temporary table is for a specific user. The system administrator can add the user to the temporary table using create login, in which case the individual uid is available for that user’s session in the temporary table.

• You can associate rules, defaults, and indexes with temporary tables, but you cannot create views on temporary tables or associate triggers with them.

• When you create a temporary table, you can use a user-defined datatype only if the type is in tempdb..systypes. To add a user-defined datatype to tempdb for only the current session, execute sp_addtype while using tempdb. To add the datatype permanently, execute sp_addtype while using model, then restart Adaptive Server so that model is copied to tempdb.

• A table “follows” its clustered index. If you create a table on one segment, then create its clustered index on another segment, the table migrates to the segment where the index is created.

• You can make inserts, updates, and selects faster by creating a table on one segment and its nonclustered indexes on another segment, if the segments are on separate physical devices. See Chapter 12, “Using clustered or nonclustered indexes,” in Transact-SQL User’s Guide.

• Use sp_rename to rename a table or column.

• After renaming a table or any of its columns, use sp_depends to determine which procedures, triggers, and views depend on the table, and redefine these objects.

  WARNING! If you do not redefine these dependent objects, they no longer work after Adaptive Server recompiles them.

• A check constraint limits the values a user can insert into a column in a table. A check constraint specifies a search_condition that any non-null value must pass before it is inserted into the table. A search_condition can include:

  • A list of constant expressions introduced with in

  • A range of constant expressions introduced with between

  • A set of conditions introduced with like, which can contain wildcard characters

  An expression can include arithmetic operators and Transact-SQL built-in functions. The search_condition cannot contain subqueries, aggregate functions, or a host variable or parameter. Adaptive Server does not enforce check constraints for temporary tables.

• A column-level check constraint can reference only the column in which it is defined; it cannot reference other columns in the table. Table-level check constraints can reference any column in the table.

• create table allows multiple check constraints in a column definition.

• check integrity constraints offer an alternative to using rules and triggers. They are specific to the table in which they are created, and cannot be bound to columns in other tables or to user-defined datatypes.

• check constraints do not override column definitions. If you declare a check constraint on a column that allows null values, you can insert NULL into the column, implicitly or explicitly, even though NULL is not included in the search_condition. For example, if you create a check constraint specifying “pub_id in (“1389”, “0736”, “0877”, “1622”, “1756”)” or “@amount > 10000” in a table column that allows null values, you can still insert NULL into that column. The column definition overrides the check constraint.

To specify the locking scheme for a table, use the keyword lock and one of the following locking schemes:

• Allpages locking, which locks data pages and the indexes affected by queries

• Datapages locking, which locks only data pages

• Datarows locking, which locks only data rows

If you do not specify a locking scheme, the default locking scheme for the server is used. The server-wide default is set with the configuration parameter lock scheme.

You can use alter table to change the locking scheme for a table.

• sp_help displays information about tables, listing any attributes (such as cache bindings) assigned to the specified table and its indexes, giving the attribute’s class, name, integer value, character value, and comments.

• sp_depends displays information about the views, triggers, and procedures in the database that depend on a table.

• sp_helpindex reports information about the indexes created on a table.

• sp_helpartition reports information about the table’s partition properties.

• Before you create a table with partitions, you must prepare the disk devices and segments that you will use for the partitions.

• Range partitioning is dependent on sort order. If the sort order is changed, you must repartition the table for the new sort order.

• Range-partition bounds must be in ascending order according to the order in which the partitions are created.

• A column of text, unitext, image, or bit, Java datatype, or computed column cannot be part of a partition key, but a partitioned table can include columns with these datatypes. A composite partition key can contain up to 31 columns.

• For range and hash partitions, the partition key can be a composite key with as many as 31 columns. In general, however, a table with more than four partition columns becomes hard to manage and is not useful.

• Bound values for range and list partitions must be compatible with the corresponding partition key datatype. If a bound value is specified in a compatible but different datatype, Adaptive Server converts the bound value to the partition key’s datatype. Adaptive Server does not support:

  • Explicit conversions.

  • Implicit conversions that result in data loss.

  • NULL as a boundary in a range-partitioned table.

  • Conversions from nonbinary datatypes to binary or varbinary datatypes.

• You can use NULL in a value list for list-partitioned tables.

• You can partition a table that contains text and image columns, but partitioning has no effect on the way Adaptive Server stores the text and image columns because they reside on their own partition.

• You cannot partition remote tables.

• Adaptive Server considers NULL to be lower than any other partition key value for a given parition key column.

• computed_column_expression can reference only columns in the same table.

• The deterministic property of computed_column_expression significantly affects data operations. See “Deterministic property” in the Transact-SQL Users Guide.

• Computed columns cannot have default values, and cannot be identity or timestamp columns.

• You can specify nullability only for materialized computed columns. If you do not specify nullability, all computed columns are, by default, nullable. Virtual computed columns are always nullable.

• Triggers and constraints, such as check, rule, unique, primary key, or foreign key) support only materialized computed columns. You cannot use them with virtual computed columns.

• If a user-defined function in a computed column definition is dropped or becomes invalid, any computed column operations that call that function fail.

• You can encrypt these datatypes:

  • int, smallint, tinyint

  • unsigned int, unsigned smallint, unsigned tinyint

  • bigint, unsigned bigint

  • decimal, numeric

  • float4, float8

  • money, smallmoney

  • date, time, smalldatetime, datetime, bigdatetime

  • char, varchar

  • unichar, univarchar

  • binary, varbinary

  • bit

• The underlying datatype of encrypted data on disk is varbinary. Null values are not encrypted.

• create table displays an error if you:

  • Specify a computed column based on an expression that references one or more encrypted columns.

  • Use the encrypt and compute parameters on the same column.

  • List an encrypted column in the partition clause

• During create table, alter table, and select into operations, Adaptive Server calculates the maximum internal length of the encrypted column. The database owner must know the maximum length of the encrypted columns before he or she can make decisions about schema arrangements and page sizes.

• You can create an index on an encrypted column if you specify the encryption key without any initialization vector or random padding. Adpative Server issues an error if you execute create index on an encrypted column with an initialization vector or random padding.

• You can define referential integrity constraints on encrypted columns when:

  • Both referencing and referenced columns are encrypted.

  • The key you use to encrypt the columns specifies init_vector null and you have not specified pad random.

• You cannot encrypt a computed column, and an encrypted column cannot appear in the expression defining a computed column. You cannot specify an encrypted column in the partition_clause of create table.

  See Chapter 3, “Encrypted Data,” in the User Guide for Encrypted Columns.

• You cannot use create table on the segment that includes a virtually hashed table, since a virtually hashed table must take only one exclusive segment, which cannot be shared by other tables or databases.

• Virtually hashed tables must have unique rows. Virtually hashed tables do not allow multiple rows with the same key column values because Adaptive Server cannot keep one row in the hash region and another with the same key column value in the overflow clustered region.

• truncate table is not supported. Use delete from table_name instead.

• SQL92 does not allow two unique constraints on a relation to have the same key columns. However, the primary key clause for a virtually hashed table is not a standard unique constraint, so you can declare a separate unique constraint with the same key columns as the virtually hashed keys.

• Because you cannot create a virtually hashed clustered index after you create a table, you also cannot drop a virtually hashed clustered index.

• You must create a virtually hashed table on an exclusive segment. You cannot share disk devices you assign to the segments for creating a virtually hashed table with other segments.

• You cannot create two virtually hashed tables on the same exclusive segment. Adaptive Server supports 32 different segments per database. Three segments are reserved for the default, system, and log segments, so the maximum number of virtually-hashed tables per database is 29.

• You cannot use the alter table or drop clustered index commands on virtually hashed tables.

• Virtually hashed tables must use all-pages locking.

• The key columns and hash factors of a virtually hashed table must use the int datatype.

• You cannot include text or image columns in virtually hashed tables, or columns with datatypes based on the text or image datatypes.

• You cannot create a partitioned virtually hashed table.

• Table-level logging settings defined by create table also apply to tables created via select into.

• Although you can create tables with minimal logging in databases using full durability, the databases do not use minimal logging for these tables. Adaptive Server allows you to set these tables to minimal logging so you can use these databases as templates for other databases with durability set to no_recovery, where minimal logging takes effect in the dependent database.

You can keep the hash factor for the first key as 1. The hash factor for all the remaining key columns is greater than the maximum value of the previous key allowed in the hash region multiplied by its hash factor.

Adaptive Server allows tables with hash factors greater than 1 for the first key column to have fewer rows on a page. For example, if a table has a hash factor of 5 for the first key column, after every row in a page, space for the next four rows is kept empty. To support this, Adaptive Server requires five times the amount of table space.

If the value of a key column is greater than or equal to the hash factor of the next key column, the current row is inserted in the overflow clustered region to avoid collisions in the hash region.

For example, t is a virtually hashed table with key columns id and age, and corresponding hash factors of (10,1). Because the hash value for rows (5, 5) and (2, 35) is 55, this may result in a hash collision.

However, because the value 35 is greater than or equal to 10 (the hash factor for the next key column, id), Adaptive Server stores the second row in the overflow clustered region, avoiding collisions in the hash region.

In another example, if u is a virtually hashed table with a primary index and hash factors of (id1, id2, id3) = (125, 25, 5) and a max hash_value of 200:

• Row (1,1,1) has a hash value of 155 and is stored in the hash region.

• Row (2,0,0) has a hash value 250 and is stored in overflow clustered region.

• Row (0,0,6) has a hash factor of 6 x 5, which is greater than or equal to 25, so it is stored in the overflow clustered region.

• Row (0,7,0) has a hash factor of 7 x 25, which is greater than or equal to 125, so it is stored in the overflow clustered region

• You cannot include a referential integrity constraint that references a column on a local temporary database unless it is from a table on the same local temporary database. create table fails if it attempts to create a reference to a column on a local temporary database from a table in another database.

• You cannot encrypt a column with an encryption key stored in a local temporary database unless the column’s table resides on the same local temporary database. alter table fails if it attempts to encrypt a column with an encryption key on the local temporary database and the table is in another database.