(Data Definition Language, DDL por sus siglas en inglés) es un lenguaje proporcionado por el sistema de gestión de base de datos que permite a los usuarios de la misma llevar a cabo las tareas de definición de las estructuras que almacenarán los datos así como de los procedimientos o funciones que permitan consultarlos.
En la siguiente tabla, se muestran las instrucciones DDL que admite SQL Server Compact.
Instrucciones DDL admitidas
Instrucción
|
Función
|
CREATE DATABASE
|
Crea una nueva base de datos y el archivo usado para almacenarla.
|
CREATE TABLE
|
Crea una nueva tabla.
|
ALTER TABLE
|
Modifica la definición de una tabla alterando, agregando o eliminando
columnas y restricciones.
|
CREATE INDEX
|
Crea un índice en una tabla determinada.
|
DROP INDEX
|
Quita uno o varios índices de la base de datos actual.
|
DROP TABLE
|
Quita la definición de una tabla y todos sus datos, índices y
restricciones.
|
INSTRCRCCION CREATE
SQL Server Transact-SQL contiene las siguientes instrucciones CREATE. Utilice las instrucciones CREATE para definir nuevas entidades. Por ejemplo, utilice CREATE TABLE para agregar una nueva tabla a una base de datos.
CREATE TABLE (Transact-SQL)
Crea una nueva tabla en SQL Server.
SINTASIS
--Disk-Based CREATE TABLE Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name .
] table_name
[ AS FileTable ]
( { <column_definition> |
<computed_column_definition>
|
<column_set_definition> | [ <table_constraint> ]
| [ <table_index> ] [ ,...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 ] ]
[ <column_index> ]
<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 )
}
<column_index> ::=
INDEX index_name [ CLUSTERED |
NONCLUSTERED ]
[ WITH ( <index_option> [
,... n ] ) ]
[ ON { partition_scheme_name
(column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name
| partition_scheme_name | "NULL" } ]
<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 )
]
<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_index > ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...
n ] )
[ WITH ( <index_option> [
,... n ] ) ]
[ ON { partition_scheme_name
(column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON {
filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<table_option> ::=
{
[DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( {
<partition_number_expression> | <range> }
[ , ...n ] ) ]]
[ FILETABLE_DIRECTORY = <directory_name> ]
[ FILETABLE_COLLATE_FILENAME = {
<collation_name> | database_default } ]
[ FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME =
<constraint_name> ]
[ FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME =
<constraint_name> ]
[ FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME =
<constraint_name> ]
}
<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>
--Memory optimized CREATE TABLE Syntax
CREATE TABLE
[database_name . [schema_name ] . | schema_name . ] table_name
( { <column_definition>
| [ <table_constraint> ] [ ,... n ]
| [ <table_index> ] [ ,... n ]
} )
[ WITH ( <table_option> [ ,... n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
[ CONSTRAINT constraint_name ] DEFAULT memory_optimized_constant_expression ]
| [ IDENTITY [ ( 1, 1 ) ]
]
[ <column_constraint> ]
[ <column_index> ]
<data type> ::=
[type_schema_name . ] type_name [ (precision [ , scale ]) ]
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY { NONCLUSTERED HASH WITH (BUCKET_COUNT = bucket_count) | NONCLUSTERED } }
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY { { NONCLUSTERED HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
| NONCLUSTERED (column [ ASC | DESC ] [ ,... n ] ) } }
<column_index> ::=
INDEX index_name
{ [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) | [ NONCLUSTERED ] }
<table_index> ::=
INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
| [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] ) }
<table_option> ::=
{
[MEMORY_OPTIMIZED = {ON | OFF}]
| [DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}]
EJEMPLO
CREATE TABLE (Transact-SQL)
Se aplica a: SQL Server (SQL Server 2008 hasta la versión actual).
Para obtener la sintaxis de Base de datos
SQL de Azure, vea CREATE TABLE (Base de datos SQL de Azure).
Sintaxis
--Disk-Based CREATE TABLE Syntax
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
[ AS FileTable ]
( { <column_definition> | <computed_column_definition>
| <column_set_definition> | [ <table_constraint> ]
| [ <table_index> ] [ ,...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 ] ]
[ <column_index> ]
<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 )
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<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 )
]
<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_index > ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] )
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<table_option> ::=
{
[DATA_COMPRESSION = { NONE | ROW | PAGE }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ]]
[ FILETABLE_DIRECTORY = <directory_name> ]
[ FILETABLE_COLLATE_FILENAME = { <collation_name> | database_default } ]
[ FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = <constraint_name> ]
[ FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = <constraint_name> ]
[ FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = <constraint_name> ]
}
<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>
--Memory optimized CREATE TABLE Syntax
CREATE TABLE
[database_name . [schema_name ] . | schema_name . ] table_name
( { <column_definition>
| [ <table_constraint> ] [ ,... n ]
| [ <table_index> ] [ ,... n ]
} )
[ WITH ( <table_option> [ ,... n ] ) ]
[ ; ]
<column_definition> ::=
column_name <data_type>
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
[
[ CONSTRAINT constraint_name ] DEFAULT memory_optimized_constant_expression ]
| [ IDENTITY [ ( 1, 1 ) ]
]
[ <column_constraint> ]
[ <column_index> ]
<data type> ::=
[type_schema_name . ] type_name [ (precision [ , scale ]) ]
<column_constraint> ::=
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY { NONCLUSTERED HASH WITH (BUCKET_COUNT = bucket_count) | NONCLUSTERED } }
< table_constraint > ::=
[ CONSTRAINT constraint_name ]
{ PRIMARY KEY { { NONCLUSTERED HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
| NONCLUSTERED (column [ ASC | DESC ] [ ,... n ] ) } }
<column_index> ::=
INDEX index_name
{ [ NONCLUSTERED ] HASH WITH (BUCKET_COUNT = bucket_count) | [ NONCLUSTERED ] }
<table_index> ::=
INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ ,... n ] ) WITH (BUCKET_COUNT = bucket_count)
| [ NONCLUSTERED ] (column [ ASC | DESC ] [ ,... n ] ) }
<table_option> ::=
{
[MEMORY_OPTIMIZED = {ON | OFF}]
| [DURABILITY = {SCHEMA_ONLY | SCHEMA_AND_DATA}]
}
No hay comentarios:
Publicar un comentario