4. Команды sql

   1. Оператор create table

Оператор используется для создания новых таблиц базы данных.

CREATE TABLE имя_таблицы ( имя_столбца тип_данных [NULL | NOT NULL] [CONSTRAINTS], имя_столбца тип_данных[NULL|NOT NULL]  [CONSTRAINTS] , ..... );

CREATE TABLE

    [ database_name . [ schema_name ] . | schema_name . ] table_name

        ( { <column_definition> | <computed_column_definition> }

     [ <table_constraint> ] [ ,...n ] )

[ ON { partition_scheme_name ( partition_column_name ) | filegroup

| "default" } ]

[ { TEXTIMAGE_ON { filegroup | "default" } ]

[ ; ]

<column_definition> ::=

column_name <data_type>

    [ COLLATE collation_name ]

    [ 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 ] )

        ]

     [ ON { partition_scheme_name ( partition_column_name )

| filegroup | "default" } ]

   | [ 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 )

]

< 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 )

}

<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}

}