A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms174979(v=sql.100) below:

CREATE TABLE (Transact-SQL) | Microsoft Learn

Syntax 
CREATE TABLE 
    [ database_name . [ schema_name ] . | schema_name . ] table_name 
    ( { <column_definition> | <computed_column_definition> 
        | <column_set_definition> }
        [ <table_constraint> ] [ ,...n ] ) 
    [ ON { partition_scheme_name (partition_column_name) | filegroup 
        | "default" } ] 
    [ { TEXTIMAGE_ON { filegroup | "default" } ] 
    [ FILESTREAM_ON { partition_scheme_name | filegroup 
        | "default" } ]
    [ WITH ( <table_option> [ ,...n ] ) ]
[ ; ]

<column_definition> ::=column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ] 

    [ SPARSE ] 


    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 

<data type> ::= 
[ type_schema_name . ] type_name 
    [ (precision [ ,scale ] | max | 
        [ { CONTENT | DOCUMENT } ] xml_schema_collection) ] 

<column_constraint> ::= 

[ CONSTRAINT constraint_name ] 
{     { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 
        [ 
            WITH FILLFACTOR = fillfactor  
          | WITH ( < index_option > [ , ...n ] ) 
        ] 
        [ ON { partition_scheme_name (partition_column_name) 
            | filegroup | "default" } ]
  | [ FOREIGN KEY ] 
        REFERENCES [ schema_name . ] referenced_table_name [ (ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
  | CHECK [ NOT FOR REPLICATION ] (logical_expression ) 
} 

<computed_column_definition> ::=column_name AS computed_column_expression 
[ PERSISTED [ NOT NULL ] ]
[ 
    [ CONSTRAINT constraint_name ]
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [ 
            WITH FILLFACTOR = fillfactor 
          | WITH ( <index_option> [ , ...n ] )
        ]
    | [ FOREIGN KEY ] 
        REFERENCES referenced_table_name [ (ref_column ) ] 
        [ ON DELETE { NO ACTION | CASCADE } ] 
        [ ON UPDATE { NO ACTION } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] (logical_expression ) 
    [ ON { partition_scheme_name (partition_column_name) 
        | filegroup | "default" } ]
] 

<column_set_definition> ::=column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

< table_constraint > ::=
[ CONSTRAINT constraint_name ] 
{ 
    { PRIMARY KEY | UNIQUE } 
        [ CLUSTERED | NONCLUSTERED ] 

        (column [ ASC | DESC ] [ ,...n ] ) 
        [ 
            WITH FILLFACTOR = fillfactor 
           |WITH ( <index_option> [ , ...n ] ) 
        ]
        [ ON { partition_scheme_name (partition_column_name)
            | filegroup | "default" } ] 
    | FOREIGN KEY 
        ( column [ ,...n ] ) 
        REFERENCES referenced_table_name [ (ref_column [ ,...n ] ) ] 
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
        [ NOT FOR REPLICATION ] 
    | CHECK [ NOT FOR REPLICATION ] (logical_expression ) 
} 

<table_option> ::=
{
    DATA_COMPRESSION = { NONE | ROW | PAGE }
      [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
      [ , ...n ] ) ]
}

<index_option> ::=
{ 
    PAD_INDEX = { ON | OFF } 
  | FILLFACTOR =fillfactor 
  | IGNORE_DUP_KEY = { ON | OFF } 
  | STATISTICS_NORECOMPUTE = { ON | OFF } 
  | ALLOW_ROW_LOCKS = { ON | OFF} 
  | ALLOW_PAGE_LOCKS ={ ON | OFF} 
  | DATA_COMPRESSION = { NONE | ROW | PAGE }
       [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
       [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>
Arguments

SQL Server 2008 can have up to 2 billion tables per database. A table that has a defined column set, can have up to 30,000 columns with a maximum of 1024 non-sparse + computed columns. Tables that do not have column sets are limited to 1024 columns. The number of rows and total size of the table are limited only by the available storage. The maximum number of bytes per row is 8,060. This restriction is relaxed for tables with varchar, nvarchar, varbinary, or sql_variant columns that cause the total defined table width to exceed 8,060 bytes. The lengths of each one of these columns must still fall within the limit of 8,000 bytes, but their combined widths may exceed the 8,060 byte limit in a table. For more information, see Row-Overflow Data Exceeding 8 KB.

Each table can contain a maximum of 999 nonclustered indexes, and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.

Space is generally allocated to tables and indexes in increments of one extent at a time. When the table or index is created, it is allocated pages from mixed extents until it has enough pages to fill a uniform extent. After it has enough pages to fill a uniform extent, another extent is allocated every time the currently allocated extents become full. For a report about the amount of space allocated and used by a table, execute sp_spaceused.

The Database Engine does not enforce an order in which DEFAULT, IDENTITY, ROWGUIDCOL, or column constraints are specified in a column definition.

When a table is created, the QUOTED IDENTIFIER option is always stored as ON in the metadata for the table, even if the option is set to OFF when the table is created.

Temporary Tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible to all sessions. Temporary tables cannot be partitioned.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SQL statements reference the temporary table by using the value specified for table_name in the CREATE TABLE statement, for example:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);

If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, the Database Engine must be able to distinguish the tables created by the different users. The Database Engine does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb is made up of the table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped by using DROP TABLE:

A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it is not defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure. This is shown in the following example.

CREATE PROCEDURE dbo.Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (2);
SELECT Test2Col = x FROM #t;
GO
CREATE PROCEDURE dbo.Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (1);
SELECT Test1Col = x FROM #t;
EXEC Test2;
GO
CREATE TABLE #t(x INT PRIMARY KEY);
INSERT INTO #t VALUES (99);
GO
EXEC Test1;
GO

Here is the result set.

(1 row(s) affected)

Test1Col    
----------- 
1           

(1 row(s) affected)

Test2Col    
----------- 
2

When you create local or global temporary tables, the CREATE TABLE syntax supports constraint definitions except for FOREIGN KEY constraints. If a FOREIGN KEY constraint is specified in a temporary table, the statement returns a warning message that states the constraint was skipped. The table is still created without the FOREIGN KEY constraints. Temporary tables cannot be referenced in FOREIGN KEY constraints.

We recommend using table variables instead of temporary tables. Temporary tables are useful when indexes must be created explicitly on them, or when the table values must be visible across multiple stored procedures or functions. Generally, table variables contribute to more efficient query processing. For more information, see table (Transact-SQL).

Partitioned Tables

Before creating a partitioned table by using CREATE TABLE, you must first create a partition function to specify how the table becomes partitioned. A partition function is created by using CREATE PARTITION FUNCTION. Second, you must create a partition scheme to specify the filegroups that will hold the partitions indicated by the partition function. A partition scheme is created by using CREATE PARTITION SCHEME. Placement of PRIMARY KEY or UNIQUE constraints to separate filegroups cannot be specified for partitioned tables. For more information, see Partitioned Tables and Indexes.

PRIMARY KEY Constraints UNIQUE Constraints FOREIGN KEY Constraints DEFAULT Definitions CHECK Constraints Additional Constraint Information

For a report on a table and its columns, use sp_help or sp_helpconstraint. To rename a table, use sp_rename. For a report on the views and stored procedures that depend on a table, use sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities.

Nullability Rules Within a Table Definition

The nullability of a column determines whether that column can allow a null value (NULL) as the data in that column. NULL is not zero or blank: NULL means no entry was made or an explicit NULL was supplied, and it typically implies that the value is either unknown or not applicable.

When you use CREATE TABLE or ALTER TABLE to create or alter a table, database and session settings influence and possibly override the nullability of the data type that is used in a column definition. We recommend that you always explicitly define a column as NULL or NOT NULL for noncomputed columns or, if you use a user-defined data type, that you allow the column to use the default nullability of the data type. Sparse columns must always allow NULL.

When column nullability is not explicitly specified, column nullability follows the rules shown in the following table.

Alias data type

The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help.

CLR user-defined type

Nullability is determined according to the column definition.

System-supplied data type

If the system-supplied data type has only one option, it takes precedence. timestamp data types must be NOT NULL.

When any session settings are set ON by using SET:

When neither of the ANSI_NULL_DFLT options is set for the session and the database is set to the default (ANSI_NULL_DEFAULTis OFF), the default of NOT NULL is assigned.

If the column is a computed column, its nullability is always automatically determined by the Database Engine. To find out the nullability of this type of column, use the COLUMNPROPERTY function with the AllowsNull property.

Note

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server both default to having ANSI_NULL_DFLT_ON set to ON. ODBC and OLE DB users can configure this in ODBC data sources, or with connection attributes or properties set by the application.

Data Compression

System tables cannot be enabled for compression. When you are creating a table, data compression is set to NONE, unless specified otherwise. If you specify a list of partitions or a partition that is out of range, an error will be generated. For a more information about data compression, see Creating Compressed Tables and Indexes.

To evaluate how changing the compression state will affect a table, an index, or a partition, use the sp_estimate_data_compression_savings stored procedure.

Permissions

Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

If any columns in the CREATE TABLE statement are defined to be of a CLR user-defined type, either ownership of the type or REFERENCES permission on it is required.

If any columns in the CREATE TABLE statement have an XML schema collection associated with them, either ownership of the XML schema collection or REFERENCES permission on it is required.

Examples A. Using PRIMARY KEY constraints

The following example shows the column definition for a PRIMARY KEY constraint with a clustered index on the EmployeeID column of the Employee table (allowing the system to supply the constraint name) in the AdventureWorks sample database.

EmployeeID int
PRIMARY KEY CLUSTERED
B. Using FOREIGN KEY constraints

A FOREIGN KEY constraint is used to reference another table. Foreign keys can be single-column keys or multicolumn keys. This following example shows a single-column FOREIGN KEY constraint on the SalesOrderHeader table that references the SalesPerson table. Only the REFERENCES clause is required for a single-column FOREIGN KEY constraint.

SalesPersonID int NULL
REFERENCES SalesPerson(SalesPersonID)

You can also explicitly use the FOREIGN KEY clause and restate the column attribute. Note that the column name does not have to be the same in both tables.

FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)

Multicolumn key constraints are created as table constraints. In the AdventureWorks database, the SpecialOfferProduct table includes a multicolumn PRIMARY KEY. The following example shows how to reference this key from another table; an explicit constraint name is optional.

CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
 (ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)
C. Using UNIQUE constraints

UNIQUE constraints are used to enforce uniqueness on nonprimary key columns. The following example enforces a restriction that the Name column of the Product table must be unique.

Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
D. Using DEFAULT definitions

Defaults supply a value (with the INSERT and UPDATE statements) when no value is supplied. For example, the AdventureWorks database could include a lookup table listing the different jobs employees can fill in the company. Under a column that describes each job, a character string default could supply a description when an actual description is not entered explicitly.

DEFAULT 'New Position - title not formalized yet'

In addition to constants, DEFAULT definitions can include functions. Use the following example to get the current date for an entry.

DEFAULT (getdate())

A niladic-function scan can also improve data integrity. To keep track of the user that inserted a row, use the niladic-function for USER. Do not enclose the niladic-functions with parentheses.

DEFAULT USER
E. Using CHECK constraints

The following example shows a restriction made to values that are entered into the CreditRating column of the Vendor table. The constraint is unnamed.

CHECK (CreditRating >= 1 and CreditRating <= 5)

This example shows a named constraint with a pattern restriction on the character data entered into a column of a table.

CONSTRAINT CK_emp_id CHECK (emp_id LIKE 
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' 
OR emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]')

This example specifies that the values must be within a specific list or follow a specified pattern.

CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')
OR emp_id LIKE '99[0-9][0-9]')
F. Showing the complete table definition

The following example shows the complete table definitions with all constraint definitions for table PurchaseOrderDetail created in the AdventureWorks database. Note that to run the sample, the table schema is changed to dbo.

CREATE TABLE [dbo].[PurchaseOrderDetail]
(
    [PurchaseOrderID] [int] NOT NULL
        REFERENCES Purchasing.PurchaseOrderHeader(PurchaseOrderID),
    [LineNumber] [smallint] NOT NULL,
    [ProductID] [int] NULL 
        REFERENCES Production.Product(ProductID),
    [UnitPrice] [money] NULL,
    [OrderQty] [smallint] NULL,
    [ReceivedQty] [float] NULL,
    [RejectedQty] [float] NULL,
    [DueDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL
        CONSTRAINT [DF_PurchaseOrderDetail_rowguid] DEFAULT (newid()),
    [ModifiedDate] [datetime] NOT NULL 
        CONSTRAINT [DF_PurchaseOrderDetail_ModifiedDate] DEFAULT (getdate()),
    [LineTotal]  AS (([UnitPrice]*[OrderQty])),
    [StockedQty]  AS (([ReceivedQty]-[RejectedQty])),
CONSTRAINT [PK_PurchaseOrderDetail_PurchaseOrderID_LineNumber]
    PRIMARY KEY CLUSTERED ([PurchaseOrderID], [LineNumber])
    WITH (IGNORE_DUP_KEY = OFF)
) 
ON [PRIMARY];
G. Creating a table with an xml column typed to an XML schema collection

The following example creates a table with an xml column that is typed to XML schema collection HRResumeSchemaCollection. The DOCUMENT keyword specifies that each instance of the xml data type in column_name can contain only one top-level element.

USE AdventureWorks;
GO
CREATE TABLE HumanResources.EmployeeResumes 
   (LName nvarchar(25), FName nvarchar(25), 
    Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );
H. Creating a partitioned table

The following example creates a partition function to partition a table or index into four partitions. Then, the example creates a partition scheme that specifies the filegroups in which to hold each of the four partitions. Finally, the example creates a table that uses the partition scheme. This example assumes the filegroups already exist in the database.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000) ;
GO
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg) ;
GO
CREATE TABLE PartitionTable (col1 int, col2 char(10))
ON myRangePS1 (col1) ;
GO

Based on the values of column col1 of PartitionTable, the partitions are assigned in the following ways.

Partition

1

2

3

4

Values

col 1 <= 1

col1 > 1 AND col1 <= 100

col1 > 100 AND col1 <= 1,000

col1 > 1000

I. Using the uniqueidentifier data type in a column

The following example creates a table with a uniqueidentifier column. The example uses a PRIMARY KEY constraint to protect the table against users inserting duplicated values, and it uses the NEWSEQUENTIALID() function in the DEFAULT constraint to provide values for new rows. The ROWGUIDCOL property is applied to the uniqueidentifier column so that it can be referenced using the $ROWGUID keyword.

CREATE TABLE dbo.Globally_Unique_Data
(guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
    Employee_Name varchar(60)
CONSTRAINT Guid_PK PRIMARY KEY (guid) );
J. Using an expression for a computed column

The following example shows the use of an expression ((low + high)/2) for calculating the myavg computed column.

CREATE TABLE dbo.mytable 
( low int, high int, myavg AS (low + high)/2 ) ;
K. Creating a computed column based on a user-defined type column

The following example creates a table with one column defined as user-defined type utf8string, assuming that the type's assembly, and the type itself, have already been created in the current database. A second column is defined based on utf8string, and uses method ToString() of type(class)utf8string to compute a value for the column.

CREATE TABLE UDTypeTable 
( u utf8string, ustr AS u.ToString() PERSISTED ) ;
L. Using the USER_NAME function for a computed column

The following example uses the USER_NAME() function in the myuser_name column.

CREATE TABLE dbo.mylogintable
( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;
M. Creating a table that has a FILESTREAM column

The following example creates a table that has a FILESTREAM column Photo. If a table has one or more FILESTREAM columns, the table must have one ROWGUIDCOL column.

CREATE TABLE dbo.EmployeePhoto
    (
    EmployeeId int NOT NULL PRIMARY KEY,
    ,Photo varbinary(max) FILESTREAM NULL
    ,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
        UNIQUE DEFAULT NEWID()
    )
N. Creating a table that uses row compression

The following example creates a table that uses row compression.

CREATE TABLE T1 
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);

For additional data compression examples, see Creating Compressed Tables and Indexes.

O. Creating a table that has sparse columns and a column set

The following examples show to how to create a table that has a sparse column, and a table that has two sparse columns and a column set. The examples use the basic syntax. For more complex examples, see Using Sparse Columns and Using Column Sets.

To create a table that has a sparse column, execute the following code.

CREATE TABLE T1
(c1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL ) ;

To create a table that has two sparse columns and a column set named CSet, execute the following code.

CREATE TABLE T1
(c1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL,
C3 int SPARSE NULL,
CSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ;

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4