Tables

When you create a table, you name its columns and supply a datatype for each column. You can also specify whether a particular column can hold null values, or specify integrity constraints for columns in the table.

The create table command builds a new table in the currently open database.

There can be as many as 2,000,000,000 tables per database.

The limits for the length of object names or identifiers are 255 bytes for regular identifiers, and 253 bytes for delimited identifiers. This limit applies to most user-defined identifiers, including table name, column name, index name and so on.

For variables, “@” counts as 1 byte, and names can be up to 254 bytes long.

The maximum number of columns in a table depends on many factors, including, your server’s logical page size and whether the tables are configured for allpages or data-only locking.

Use the create table command to define each column in a table.

create table also:

Provides the column name and datatype and specifies how each column handles null values.
Specifies which column, if any, has the IDENTITY property.
Defines column-level integrity constraints and table-level integrity constraints. Each table definition can have multiple constraints per column and per table.

For example, the create table statement for the titles table in the pubs2 database is:

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

See the Reference Manual: Commands.

Note: The on segment_name extension to create table allows you to place your table on an existing segment. segment_name points to a specific database device or a collection of database devices. Before creating a table on a segment, see a system administrator or the database owner for a list of segments that you can use. Certain segments may be allocated to specific tables or indexes for performance reasons, or for other considerations.

Examples of Creating Tables

If you use these examples, be sure you first created a sample database first (such as newpubs) otherwise these changes will affect another database, like pubs2 or pubs3.

The simplest form of create table is:

create table table_name 
(column_name datatype)

For example, to create a table named names with one column named “some_name,” and a fixed length of 11 bytes, enter:

create table names 
(some_name char(11)) 
drop table names

If you have set quoted_identifier on, both the table name and the column names can be delimited identifiers. Column names must be unique within a table, but you can use the same column name in different tables in the same database.

There must be a datatype for each column. The word “char” after the column name in the example above refers to the datatype of the column—the type of value that column will contain.

The number in parentheses after the datatype determines the maximum number of bytes that can be stored in the column. You give a maximum length for some datatypes. Others have a system-defined length.

Put parentheses around the list of column names, and commas after each column definition. The last column definition does not need a comma after it.

Note: You cannot use a variable in a default if the default is part of a create table statement.

For complete documentation of create table, see the Reference Manual: Commands.