CREATE TABLE (Transact-SQL)
Cria uma nova tabela em SQL Server 2012.
.gif "Ícone de vínculo de tópico")
Convenções da sintaxe Transact-SQL
Sintaxe
CREATE TABLE
[ database_name . [ schema_name ] . | schema_name . ] table_name
[ AS FileTable ]
( { <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 ] )
]
[ 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_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>
Argumentos
database_name
É o nome do banco de dados no qual a tabela é criada. O database_name deve especificar o nome de um banco de dados existente. Se não for especificado, o database_name assumirá como padrão o banco de dados atual. O logon da conexão atual deve ser associado à ID de usuário existente no banco de dados especificado por database_name, e essa ID de usuário deve ter permissões CREATE TABLE.schema_name
É o nome do esquema ao qual a nova tabela pertence.table_name
É o nome da nova tabela. Os nomes de tabelas devem seguir as regras de identificadores. table_name pode ter no máximo 128 caracteres, exceto para nomes de tabelas temporárias locais (nomes com o prefixo de sinal numérico (#)) que não podem exceder 116 caracteres.AS FileTable
Cria a nova tabela como uma FileTable. Você não especifica colunas porque uma FileTable tem um esquema fixo. Para obter mais informações sobre FileTables, consulte FileTables (SQL Server).column_name
É o nome de uma coluna da tabela. Os nomes das colunas devem seguir as regras de identificadores e devem ser exclusivos na tabela. column_name pode ter até 128 caracteres. column_name pode ser omitido para colunas que são criadas com um tipo de dados timestamp. Se column_name não for especificado, o nome de uma coluna timestamp assume o padrão de timestamp.computed_column_expression
É uma expressão que define o valor de uma coluna computada. Uma coluna computada é uma coluna virtual que não está fisicamente armazenada na tabela, a menos que a coluna esteja marcada como PERSISTED. A coluna é computada a partir de uma expressão que usa outras colunas da mesma tabela. Por exemplo, uma coluna computada pode ter a definição: cost AS price * qty. A expressão pode ser o nome de uma coluna não computada, constante, função, variável e qualquer combinação dessas, conectada por um ou mais operadores. A expressão não pode ser uma subconsulta nem conter um tipo de dados do alias.As colunas computadas podem ser usadas em listas de seleção, cláusulas WHERE, cláusulas ORDER BY ou em qualquer outro local em que expressões regulares possam ser usadas, com as seguintes exceções:
As colunas computadas devem ser marcadas como PERSISTED para participar de uma restrição FOREIGN KEY ou CHECK.
Uma coluna computada poderá ser usada como uma coluna de chave em um índice ou como parte de uma restrição PRIMARY KEY ou UNIQUE, se o valor for definido por uma expressão determinística e o tipo de dado do resultado for permitido nas colunas de índice.
Por exemplo, se a tabela tiver as colunas de inteiros a e b, a coluna computada a+b poderá ser indexada, mas não a coluna computada a+DATEPART(dd, GETDATE()), pois o valor pode ser alterado em chamadas subsequentes.
Uma coluna computada não pode ser o destino de uma instrução INSERT ou UPDATE.
.gif "Observação")
ObservaçãoCada linha de uma tabela pode ter valores diferentes para as colunas envolvidas em uma coluna computada; sendo assim, a coluna computada pode não ter o mesmo valor para cada linha.
Com base nas expressões usadas, o Mecanismo de Banco de Dados determina automaticamente a nulidade das colunas computadas. O resultado da maioria das expressões será considerado nulo mesmo se somente colunas não nulas estejam presentes, pois a falta de fluxo ou excesso de fluxo produzirá também resultados NULL. Use a função COLUMNPROPERTY com a propriedade AllowsNull para investigar a nulidade de qualquer coluna computada de uma tabela. Uma expressão que permite valor nulo pode tornar-se uma expressão não nula, basta especificar ISNULL com a constante check_expression, sendo que a constante é um valor não nulo substituído por um resultado nulo. A permissão REFERENCES no tipo é necessária para colunas computadas com base em expressões do tipo de dados CLR definido pelo usuário.
PERSISTED
Especifica que o Mecanismo de Banco de Dados do SQL Server armazenará fisicamente os valores computados na tabela e atualizará os valores quando for atualizada qualquer outra coluna da qual a coluna computada depende. Marcar uma coluna computada como PERSISTED permite a criação de um índice em uma coluna computada que seja determinística, mas não precisa. Para obter mais informações, consulte Índices em colunas computadas. Qualquer coluna computada usada como colunas de particionamento de uma tabela particionada deve ser marcada como PERSISTED. computed_column_expression deve ser determinístico quando PERSISTED é especificado.ON { <partition_scheme> | filegroup | "default" }
Especifica o esquema de partição ou grupo de arquivos no qual a tabela é armazenada. Se <partition_scheme> for especificado, a tabela será uma tabela particionada cujas partições são armazenadas em um conjunto de um ou mais grupos de arquivos especificados em <partition_scheme>. Se filegroup for especificado, a tabela será armazenada no grupo de arquivos nomeado. O grupo de arquivos deve existir no banco de dados. Se "default" for especificado ou se ON não for especificado, a tabela será armazenada no grupo de arquivos padrão. O mecanismo de armazenamento de uma tabela como especificado em CREATE TABLE não pode ser alterado posteriormente.ON {<partition_scheme> | filegroup | "default"} também pode ser especificado em uma restrição PRIMARY KEY ou UNIQUE. Essas restrições criam índices. Se filegroup for especificado, o índice será armazenado no grupo de arquivos nomeado. Se "default" for especificado ou se ON não for especificado, o índice será armazenado no mesmo grupo de arquivos que a tabela. Se a restrição PRIMARY KEY ou UNIQUE criar um índice clusterizado, as páginas de dados da tabela serão armazenadas no mesmo grupo de arquivos que o índice. Se CLUSTERED for especificado ou se a restrição de alguma outra forma criar um índice clusterizado, e for especificado um <partition_scheme> diferente do <partition_scheme> ou do filegroup da definição da tabela, ou vice-versa, somente a definição da restrição será preservada, as demais serão ignoradas.
ObservaçãoNesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e deve ser delimitado, como em ON "default" ou ON [default]. Se "default" for especificado, a opção QUOTED_IDENTIFIER deverá ser ON para a sessão atual. Essa é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).
ObservaçãoDepois de criar uma tabela particionada, considere definir a opção LOCK_ESCALATION da tabela como AUTO. Isso pode melhorar a simultaneidade ao permitir que os bloqueios escalem para o nível da partição (HoBT) em vez da tabela. Para obter mais informações, consulte ALTER TABLE (Transact-SQL).
TEXTIMAGE_ON { filegroup| "default" }
Indica que as colunas text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) e de tipo de dados CLR definido pelo usuário (incluindo geometria e geografia) são armazenadas no grupo de arquivos especificado.TEXTIMAGE_ON não será permitido se não houver ma coluna de valor grande na tabela. TEXTIMAGE_ON não poderá ser especificado se <partition_scheme> for especificado. Se "default" for especificado ou se TEXTIMAGE_ON não for especificado, as colunas de valores grandes serão armazenadas no grupo de arquivos padrão. O armazenamento de qualquer dado de coluna de valor grande especificado em CREATE TABLE não poderá ser alterado posteriormente.
ObservaçãoNesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e deve ser delimitado, como em TEXTIMAGE_ON "default" ou TEXTIMAGE_ON [default]. Se "default" for especificado, a opção QUOTED_IDENTIFIER deverá ser ON para a sessão atual. Essa é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).
FILESTREAM_ON { partition_scheme_name | filegroup | "default" }
Especifica o grupo de arquivos para obter dados de FILESTREAM.Se a tabela contiver dados FILESTREAM e estiver particionada, a cláusula FILESTREAM_ON deverá ser incluída e especificar um esquema de partição de grupos de arquivos FILESTREAM. Esse esquema de partição deve usar a mesma função de partição e as colunas de partição do esquema de partição da tabela; caso contrário, ocorrerá um erro.
Se a tabela não estiver particionada, a coluna FILESTREAM não poderá ser particionada. Dados FILESTREAM da tabela devem ser armazenados em um único grupo de arquivos. Esse grupo de arquivos é especificado na cláusula FILESTREAM_ON.
Se a tabela não estiver particionada e a cláusula FILESTREAM_ON não foi especificada, será usado o grupo de arquivos FILESTREAM cuja propriedade DEFAULT esteja configurada. Se não houver um grupo de arquivos FILESTREAM, ocorrerá um erro.
Como em ON e TEXTIMAGE_ON, o valor definido com o uso de CREATE TABLE para FILESTREAM_ON não poderá ser alterado, exceto nestes casos:
Uma instrução CREATE INDEX converte um heap em um índice clusterizado. Nesse caso, um outro grupo de arquivos FILESTREAM, um esquema de partição ou NULL pode ser especificado.
Uma instrução DROP INDEX converte um índice clusterizado em um heap. Nesse caso, um outro grupo de arquivos FILESTREAM, um esquema de partição ou "default" pode ser especificado.
O grupo de arquivos da cláusula FILESTREAM_ON <filegroup>, ou cada grupo de arquivos FILESTREAM nomeado no esquema de partição, deve ter um arquivo definido para o grupo de arquivos. Esse arquivo deve ser definido por uma instrução CREATE DATABASE ou ALTER DATABASE; caso contrário, ocorrerá um erro.
Para acessar tópicos relacionados sobre FILESTREAM, consulte Dados de objeto binário grande (Blob) (SQL Server).
[ type_schema_name**.** ] type_name
Especifica o tipo de dados da coluna e o esquema ao qual ele pertence. O tipo de dados pode ser um destes:Um tipo de dados do sistema.
Um tipo de alias com base em um tipo de dados do sistema do SQL Server. Os tipos de dados do alias são criados com a instrução CREATE TYPE antes que possam ser usados em uma definição de tabela. A atribuição NULL ou NOT NULL para um tipo de dados do alias pode ser substituída durante a instrução CREATE TABLE. Contudo, não é possível alterar a especificação do comprimento; o comprimento de um tipo de dados do alias não pode ser especificado na instrução CREATE TABLE.
Um tipo de dados CLR definido pelo usuário. Tipos de dados CLR definidos pelo usuário são criados com a instrução CREATE TYPE antes que eles possam ser usados em uma definição de tabela. Para criar uma coluna baseada em um tipo de dados CLR definido pelo usuário, é necessária a permissão REFERENCES para o tipo.
Se type_schema_name não for especificado, o Mecanismo de Banco de Dados do SQL Server fará referência a type_name na seguinte ordem:
O tipo de dados de sistema SQL Server.
O esquema padrão do usuário atual no banco de dados atual.
O esquema dbo no banco de dados atual.
precision
É a precisão do tipo de dados especificado. Para obter mais informações sobre valores de precisão válidos, consulte Precisão, escala e comprimento.scale
É a escala do tipo de dados especificado. Para obter mais informações sobre valores de escala válidos, consulte Precisão, escala e comprimento.max
Válido apenas para os tipos de dados varchar, nvarchar e varbinary para armazenar 2^31 bytes de caracteres e dados binários, e 2^30 bytes de dados Unicode.CONTENT
Especifica que cada instância do tipo de dados xml em column_name pode conter vários elementos de nível superior. CONTENT aplica-se apenas ao tipo de dados xml e poderá ser especificado somente se xml_schema_collection também for especificado. Caso não seja especificado, CONTENT será o comportamento padrão.DOCUMENT
Especifica que cada instância do tipo de dados xml em column_name pode conter apenas um elemento de nível superior. DOCUMENT aplica-se apenas ao tipo de dados xml e poderá ser especificado somente se xml_schema_collection também for especificado.xml_schema_collection
Aplica-se apenas ao tipo de dados xml para associar uma coleção de esquema XML ao tipo. Antes de digitar uma coluna xml para um esquema, o esquema deve ser criado primeiramente no banco de dados com o uso de CREATE XML SCHEMA COLLECTION.DEFAULT
Especifica o valor fornecido para a coluna quando um valor não for fornecido explicitamente durante uma inserção. As definições DEFAULT podem ser aplicadas a qualquer coluna, com exceção daquelas definidas como timestamp ou das colunas com a propriedade IDENTITY. Se um valor padrão for especificado para uma coluna de tipo definido pelo usuário, o tipo deverá oferecer suporte a uma conversão implícita de constant_expression para o tipo definido pelo usuário. As definições DEFAULT serão removidas quando a tabela for descartada. Somente um valor constante, como uma cadeia de caracteres, uma função de escalar (seja de sistema, definida pelo usuário ou CLR) ou NULL, pode ser usado como padrão. Para manter a compatibilidade com versões anteriores do SQL Server, um nome de restrição pode ser atribuído a um DEFAULT.constant_expression
É uma constante, NULL ou uma função de sistema usada como valor de coluna padrão.IDENTITY
Indica que a nova coluna é uma coluna de identidade. Quando uma nova linha é adicionada à tabela, o Mecanismo de Banco de Dados fornece um valor incremental exclusivo para a coluna. Geralmente, as colunas de identidade são usadas com restrições PRIMARY KEY para servir como o identificador exclusivo de linha para a tabela. A propriedade IDENTITY pode ser atribuída a colunas tinyint, smallint, int, bigint, decimal(p,0) ou numeric(p,0). Apenas uma coluna de identidade pode ser criada por tabela. Padrões associados e restrições DEFAULT não podem ser usados com uma coluna de identidade. Devem ser especificados tanto o valor de semente como o de incremento ou nenhum dos dois. Se nenhum for especificado, o padrão será (1,1).seed
É o valor usado para a primeira linha carregada na tabela.increment
É o valor de incremento adicionado ao valor de identidade da linha anterior carregada.NOT FOR REPLICATION
Na instrução CREATE TABLE, a cláusula NOT FOR REPLICATION pode ser especificada para a propriedade IDENTITY e para restrições FOREIGN KEY e CHECK. Se essa cláusula for especificada para a propriedade IDENTITY, os valores não serão incrementados em colunas de identidade quando os agentes de replicação executarem inserções. Se essa cláusula for especificada para uma restrição, ela não será aplicada quando os agentes de replicação executarem operações insert, update ou delete.ROWGUIDCOL
Indica que a nova coluna é uma coluna de GUID de linha. Somente uma coluna uniqueidentifier por tabela pode ser designada como a coluna ROWGUIDCOL. A aplicação da propriedade ROWGUIDCOL permite que a coluna seja referenciada usando $ROWGUID. A propriedade ROWGUIDCOL pode ser atribuída somente a uma coluna uniqueidentifier. As colunas de tipo de dados definido pelo usuário não podem ser designadas com ROWGUIDCOL.A propriedade ROWGUIDCOL não impõe exclusividade dos valores armazenados na coluna. ROWGUIDCOL também não gera automaticamente valores para novas linhas inseridas na tabela. Para gerar valores exclusivos para cada coluna, use a função NEWID ou NEWSEQUENTIALID em instruções INSERT ou use essas funções como o padrão para a coluna.
SPARSE
Indica que a coluna é uma coluna esparsa. O armazenamento de colunas esparsas é otimizado para valores nulos. As colunas esparsas não podem ser designadas como NOT NULL. Para obter restrições adicionais e mais informações sobre colunas esparsas, consulte Usar colunas esparsas.FILESTREAM
Válido somente para colunas varbinary(max). Especifica o armazenamento FILESTREAM para dados BLOB varbinary(max).A tabela também deve ter uma coluna do tipo de dados uniqueidentifier com o atributo ROWGUIDCOL. Essa coluna não deve permitir valores nulos e deve ter uma restrição de coluna única UNIQUE ou PRIMARY KEY. O valor de GUID da coluna deve ser fornecido por um aplicativo ao inserir dados ou por uma restrição DEFAULT que usa a função NEWID ().
A coluna ROWGUIDCOL não pode ser descartada e as restrições relacionadas não podem ser alteradas enquanto houver uma coluna FILESTREAM definida para a tabela. A coluna ROWGUIDCOL poderá ser descartada somente depois que a última coluna FILESTREAM for descartada.
Quando o atributo de armazenamento FILESTREAM é especificado para uma coluna, todos os valores da coluna são armazenados em um contêiner de dados FILESTREAM no sistema de arquivos.
COLLATE collation_name
Especifica o agrupamento da coluna. O nome do agrupamento tanto pode ser um nome de agrupamento do Windows como um nome de agrupamento SQL. collation_name é aplicável somente para colunas dos tipos de dados char, varchar, text, nchar, nvarchar e ntext. Se não for especificado, à coluna será atribuído o agrupamento do tipo de dados definido pelo usuário, se a coluna for de um tipo de dados definido pelo usuário, ou o agrupamento do banco de dados atual.Para obter mais informações sobre os nomes de agrupamento do Windows e do SQL, consulte Nome de agrupamento do Windows e Nome de agrupamento do SQL.
Para obter mais informações sobre a cláusula COLLATE, consulte COLLATE (Transact-SQL).
CONSTRAINT
É uma palavra-chave opcional que indica o início da definição de uma restrição PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY ou CHECK.constraint_name
É o nome de uma restrição. Nomes de restrição devem ser exclusivos no esquema ao qual a tabela pertence.NULL | NOT NULL
Determinam se são permitidos valores nulos na coluna. NULL não é estritamente uma restrição, mas pode ser especificado simplesmente como NOT NULL. NOT NULL poderá ser especificado para colunas computadas somente se PERSISTED também for especificado.PRIMARY KEY
É uma restrição que impõe a integridade de entidade para uma coluna ou colunas especificadas por meio de um índice exclusivo. Somente uma restrição PRIMARY KEY pode ser criada por tabela.UNIQUE
É uma restrição que fornece integridade de entidade para uma coluna ou colunas especificadas por meio de um índice exclusivo. Uma tabela pode ter várias restrições UNIQUE.CLUSTERED | NONCLUSTERED
Indica que um índice clusterizado ou não clusterizado será criado para a restrição PRIMARY KEY ou UNIQUE. O padrão das restrições PRIMARY KEY é CLUSTERED e das restrições UNIQUE é NONCLUSTERED.Em uma instrução CREATE TABLE, CLUSTERED pode ser especificado apenas para uma restrição. Se CLUSTERED for especificado para uma restrição UNIQUE e uma restrição PRIMARY KEY também for especificada, PRIMARY KEY adotará o padrão de NONCLUSTERED.
FOREIGN KEY REFERENCES
É uma restrição que fornece integridade referencial para os dados na coluna ou colunas. As restrições FOREIGN KEY requerem que cada valor na coluna exista na coluna ou colunas referenciadas correspondentes na tabela referenciada. As restrições FOREIGN KEY podem fazer referência somente a colunas que sejam restrições PRIMARY KEY ou UNIQUE na tabela ou colunas referenciadas em um UNIQUE INDEX na tabela referenciada. As chaves estrangeiras em colunas computadas também devem ser marcadas como PERSISTED.[ schema_name**.**] referenced_table_name]
É o nome da tabela referenciada pela restrição FOREIGN KEY e o esquema ao qual ela pertence.( ref_column [ ,... n ] )
É uma coluna, ou lista de colunas, da tabela referenciadas pela restrição FOREIGN KEY.ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Especifica qual ação ocorrerá nas linhas da tabela criada se essas linhas tiverem uma relação referencial e a linha referenciada for excluída da tabela pai. O padrão é NO ACTION.NO ACTION
O Mecanismo de Banco de Dados gera um erro e a ação de exclusão na linha da tabela pai é revertida.CASCADE
As linhas correspondentes serão excluídas da tabela de referência se aquela linha for excluída da tabela pai.SET NULL
Todos os valores que compõem a chave estrangeira serão definidos como NULL se a linha correspondente na tabela pai for excluída. Para que essa restrição seja executada, as colunas de chave estrangeira devem ser permitir valor nulo.SET DEFAULT
Todos os valores que compõem a chave estrangeira são definidos com seus valores padrão se a linha correspondente na tabela pai for excluída. Para que essa restrição seja executada, todas as colunas de chave estrangeira devem ter definições padrão. Se a coluna for anulável e não houver nenhum valor padrão explícito definido, NULL se tornará o valor padrão implícito para a coluna.
Não especifique CASCADE se a tabela for incluída em uma publicação de mesclagem que usa registros lógicos. Para obter mais informações sobre registros lógicos, consulte Agrupar alterações a linhas relacionadas com registros lógicos.
ON DELETE CASCADE não poderá ser definido se um gatilho INSTEAD OF ON DELETE já existir na tabela.
Por exemplo, no banco de dados do AdventureWorks2012 , a tabela ProductVendor tem uma relação referencial com a tabela Vendor. A chave estrangeira ProductVendor.BusinessEntityID referencia a chave primária Vendor.BusinessEntityID.
Se uma instrução DELETE for executada em uma linha da tabela Vendor, e uma ação ON DELETE CASCADE for especificada para ProductVendor.BusinessEntityID, Mecanismo de Banco de Dados verifica se há uma ou mais linhas dependentes na tabela ProductVendor. Se existir alguma, as linhas dependentes da tabela ProductVendor serão excluídas, além da linha referenciada na tabela Vendor.
Entretanto, se NO ACTION for especificada, o Mecanismo de Banco de Dados gerará um erro e reverterá a ação de exclusão da linha do Vendor se houver pelo menos uma linha da tabela ProductVendor que a referencie.
ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Especifica a ação que ocorre nas linhas da tabela alterada, quando essas linhas têm uma relação referencial e a linha referenciada for atualizada na tabela pai. O padrão é NO ACTION.NO ACTION
O Mecanismo de Banco de Dados gera um erro, e a ação de atualizar na linha da tabela pai é revertida.CASCADE
As linhas correspondentes são atualizadas na tabela de referência quando aquela linha é atualizada na tabela pai.SET NULL
Todos os valores que compõem a chave estrangeira são definidos como NULL quando a linha correspondente na tabela pai é atualizada. Para que essa restrição seja executada, as colunas de chave estrangeira devem ser anuláveis.SET DEFAULT
Todos os valores que compõem a chave estrangeira são definidos como seus valores padrão quando a linha correspondente na tabela pai é atualizada. Para que essa restrição seja executada, todas as colunas de chave estrangeira devem ter definições padrão. Se a coluna for anulável e não houver nenhum valor padrão explícito definido, NULL se tornará o valor padrão implícito para a coluna.
Não especifique CASCADE se a tabela for incluída em uma publicação de mesclagem que usa registros lógicos. Para obter mais informações sobre registros lógicos, consulte Agrupar alterações a linhas relacionadas com registros lógicos.
ON UPDATE CASCADE, SET NULL ou SET DEFAULT não poderá ser definido se um gatilho INSTEAD OF de ON UPDATE já existir na tabela que está sendo alterada.
Por exemplo, no banco de dados AdventureWorks2012 , a tabela ProductVendor tem uma relação referencial com a tabela Vendor: a chave estrangeira ProductVendor.BusinessEntity faz referência à chave primária Vendor.BusinessEntityID.
Se uma instrução UPDATE for executada em uma linha da tabela Vendor e uma ação ON UPDATE CASCADE for especificada para ProductVendor.BusinessEntityID, o Mecanismo de Banco de Dados verificará se há uma ou mais linhas dependentes na tabela ProductVendor. Se existir alguma, as linhas dependentes da tabela ProductVendor serão atualizadas, além da linha referenciada na tabela Vendor.
Entretanto, se NO ACTION for especificada, o Mecanismo de Banco de Dados gerará um erro e reverterá a ação de alteração da linha de Vendor se houver pelo menos uma linha da tabela ProductVendor que a referencie.
CHECK
É uma restrição que impõe a integridade de domínio limitando os possíveis valores que podem ser inseridos em uma ou mais colunas. As restrições CHECK em colunas computadas também devem ser marcadas como PERSISTED.logical_expression
É uma expressão lógica que retorna TRUE ou FALSE. Tipos de dados do alias não podem fazer parte da expressão.column
É uma coluna, ou lista de colunas, entre parênteses, usada em restrições de tabela para indicar as colunas usadas na definição da restrição.[ ASC | DESC ]
Especifica a ordem na qual a coluna ou colunas que participam de restrições de tabela são classificadas. O padrão é ASC.partition_scheme_name
É o nome do esquema de partição que define os grupos de arquivos sobre os quais as partições de uma tabela particionada serão mapeadas. O esquema de partição deve existir no banco de dados.[ partition_column_name**.** ]
Especifica a coluna que servirá de base para o particionamento de uma tabela particionada. A coluna deve corresponder àquela especificada na função de partição que partition_scheme_name está usando em termos de tipo, comprimento e precisão dos dados. Uma coluna computada que participa de uma função de partição deve ser marcada explicitamente como PERSISTED..gif "Observação importante")
ImportanteRecomendamos a especificação de NOT NULL na coluna de particionamento das tabelas particionadas e também de tabelas não particionadas que servem de origem e destino para as operações ALTER TABLE...SWITCH. Isso garante que toda restrição CHECK de colunas de particionamento não terão que verificar se existem valores nulos.
WITH FILLFACTOR **=**fillfactor
Especifica o quanto o Mecanismo de Banco de Dados deve preencher cada página de índice usada para armazenar os dados de índice. Os valores de fillfactor especificados pelo usuário podem ser de 1 a 100. Se um valor não for especificado, o padrão será 0. Os valores de fator de preenchimento 0 e 100 são iguais em todos os aspectos.ImportanteA documentação de WITH FILLFACTOR = fillfactor como a única opção de índice aplicável às restrições PRIMARY KEY ou UNIQUE é mantida para fins de compatibilidade com versões anteriores, mas isso não será documentado dessa maneira em futuras versões.
column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
É o nome do conjunto de colunas. Um conjunto de colunas é uma representação em XML sem-tipo que combina todas as colunas esparsas de uma tabela em uma saída estruturada. Para obter mais informações sobre conjuntos de colunas, consulte Usar conjuntos de colunas.< table_option> ::=
Especifica uma ou mais opções de tabela.DATA_COMPRESSION
Especifica a opção de compactação de dados para a tabela, o número de partição ou o intervalo de partições especificado. As opções são as seguintes:NONE
A tabela ou as partições especificadas não são compactadas.ROW
A tabela ou as partições especificadas são compactadas usando a compactação de linha.PAGE
A tabela ou as partições especificadas são compactadas usando a compactação de página.
Para obter mais informações sobre compactação, consulte Compactação de dados.
ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )
Especifica as partições às quais se aplica a configuração DATA_COMPRESSION. Se a tabela não for particionada, o argumento ON PARTITIONS gerará um erro. Se a cláusula ON PARTITIONS não for fornecida, a opção DATA_COMPRESSION será aplicada a todas as partições de uma tabela particionada.<partition_number_expression> pode ser especificado das seguintes maneiras:
Forneça o número de uma partição, por exemplo: ON PARTITIONS (2).
Forneça os números de partição para várias partições individuais separadas por vírgulas, por exemplo: ON PARTITIONS (1, 5).
Forneça os intervalos e as partições individuais, por exemplo: ON PARTITIONS (2, 4, 6 TO 8).
<range> pode ser especificado como números de partição separados pela palavra TO, por exemplo: ON PARTITIONS (6 TO 8).
Para definir tipos diferentes de compactação de dados para partições diferentes, especifique a opção DATA_COMPRESSION mais de uma vez, por exemplo:
WITH ( DATA_COMPRESSION = NONE ON PARTITIONS (1), DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5) )<index_option> ::=
Especifica uma ou mais opções de índice. Para obter uma descrição completa dessas opções, consulte CREATE INDEX (Transact-SQL).PAD_INDEX = { ON | OFF }
Quando ON, a porcentagem de espaço livre especificada por FILLFACTOR será aplicada às páginas de nível intermediário do índice. Quando OFF ou se o valor de FILLFACTOR não foi especificado, as páginas de nível intermediário são preenchidas até próximo de sua capacidade, deixando espaço suficiente para pelo menos uma linha do tamanho máximo que o índice pode ter, considerando o conjunto de chaves em páginas intermediárias. O padrão é OFF.FILLFACTOR **=**fillfactor
Especifica uma porcentagem que indica quanto Mecanismo de Banco de Dados deve preencher o nível folha de cada página de índice durante a criação ou alteração do índice. fillfactor deve ser um valor inteiro de 1 a 100. O padrão é 0. Os valores de fator de preenchimento 0 e 100 são iguais em todos os aspectos.IGNORE_DUP_KEY = { ON | OFF }
Especifica a resposta de erro quando uma operação de inserção tenta inserir valores de chave duplicados em um índice exclusivo. A opção IGNORE_DUP_KEY aplica-se apenas a operações de inserção depois que o índice é criado ou recriado. A opção não tem nenhum efeito ao executar CREATE INDEX, ALTER INDEX ou UPDATE. O padrão é OFF.ON
Uma mensagem de aviso ocorrerá quando valores de chave duplicada forem inseridos em um índice exclusivo. Ocorrerá falha somente nas linhas que violarem a restrição de exclusividade.OFF
Ocorrerá uma mensagem de erro quando os valores de chave duplicados forem inseridos em um índice exclusivo. Toda a operação INSERT será revertida.
IGNORE_DUP_KEY não pode ser definido como ON para índices criados em uma exibição, índices não exclusivos, índices XML, índices espaciais e índices filtrados.
Para exibir IGNORE_DUP_KEY, use sys.indexes.
Na sintaxe compatível com versões anteriores, WITH IGNORE_DUP_KEY é equivalente a WITH IGNORE_DUP_KEY = ON.
STATISTICS_NORECOMPUTE = { ON | OFF }
Quando ON, as estatísticas de índice desatualizadas não serão recalculadas automaticamente. Quando OFF, a atualização automática de estatísticas será habilitada. O padrão é OFF.ALLOW_ROW_LOCKS = { ON | OFF }
Quando ON, bloqueios de linha serão permitidos quando você acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de linha são usados. No caso de OFF, não são usados bloqueios de linha. O padrão é ON.ALLOW_PAGE_LOCKS = { ON | OFF }
Quando ON, bloqueios de página serão permitidos quando você acessar o índice. O Mecanismo de Banco de Dados determina quando os bloqueios de página são usados. Quando definidos como OFF, os bloqueios de página não são usados. O padrão é ON.FILETABLE_DIRECTORY = directory_name
Especifica o nome do diretório de FileTable compatível com o Windows. Esse nome deve ser exclusivo entre todos os nomes de diretórios de FileTable no banco de dados. A comparação de exclusividade não diferencia maiúsculas de minúsculas, independentemente das configurações de agrupamento. Se esse valor não for especificado, o nome de filetable será usado.FILETABLE_COLLATE_FILENAME = { collation_name | database_default}
Especifica o nome do agrupamento a ser aplicado à coluna Name da FileTable. O agrupamento não deve diferenciar maiúsculas de minúsculas para ser compatível com a semântica de nomenclatura de arquivos do Windows. Se esse valor não for especificado, o agrupamento padrão do banco de dados será usado. Se o agrupamento padrão do banco de dados diferenciar maiúsculas de minúsculas, será gerado um erro e a operação CREATE TABLE falhará.collation_name
O nome de um agrupamento sem diferenciação de maiúsculas e minúsculas.database_default
Especifica que o agrupamento padrão do banco de dados deve ser usado. Esse agrupamento não deve diferenciar maiúsculas de minúsculas.
FILETABLE_PRIMARY_KEY_CONSTRAINT_NAME = constraint_name
Especifica o nome a ser usado para a restrição de chave primária que é criada automaticamente no FileTable. Se esse valor não for especificado, o sistema gerará um nome para a restrição.FILETABLE_STREAMID_UNIQUE_CONSTRAINT_NAME = constraint_name
Especifica o nome a ser usado para a restrição exclusiva que é criada automaticamente na coluna stream_id no FileTable. Se esse valor não for especificado, o sistema gerará um nome para a restrição.FILETABLE_FULLPATH_UNIQUE_CONSTRAINT_NAME = constraint_name
Especifica o nome a ser usado para a restrição exclusiva que é criada automaticamente nas colunas parent_path_locator e name no FileTable. Se esse valor não for especificado, o sistema gerará um nome para a restrição.
Comentários
Para obter informações sobre o número de tabelas, colunas, restrições e índices permitidos, consulte Especificações de capacidade máxima do SQL Server.
Geralmente, o espaço é alocado a tabelas e índices em incrementos de uma extensão por vez. Durante a criação de uma tabela ou de um índice, são alocadas páginas de extensões misturadas até que haja páginas suficientes para preencher um extensão uniforme. Quando houver páginas suficiente para preencher um extensão uniforme, outra extensão será alocada sempre que as extensões já alocadas ficarem cheias. Para um obter um relatório sobre a quantidade de espaço alocado e usado por uma tabela, execute sp_spaceused.
O Mecanismo de Banco de Dados não impõe uma ordem para especificar DEFAULT, IDENTITY, ROWGUIDCOL ou restrições de coluna em uma definição de coluna.
Quando uma tabela é criada, a opção QUOTED IDENTIFIER sempre é armazenada como ON nos metadados da tabela, mesmo que a opção esteja definida como OFF quando a tabela é criada.
Tabelas temporárias
Você pode criar tabelas temporárias locais e globais. Tabelas temporárias locais são visíveis apenas na sessão atual e tabelas temporárias globais são visíveis em todas as sessões. Não é possível particionar tabelas temporárias.
Inclua um prefixo nos nomes de tabelas temporárias locais usando um único sinal numérico (#table_name) os de tabelas temporárias globais com dois sinais numéricos (##table_name).
As instruções SQL fazem referência à tabela temporária usando o valor especificado para table_name na instrução CREATE TABLE, por exemplo:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY);
INSERT INTO #MyTempTable VALUES (1);
Se mais de uma tabela temporária for criada em um procedimento armazenado ou lote, elas devem ter nomes diferentes.
Se uma tabela temporária local for criada em um procedimento armazenado ou aplicativo que pode ser executado ao mesmo tempo por vários usuários, o Mecanismo de Banco de Dados deve ser capaz de distinguir as tabelas criadas por usuários distintos. O Mecanismo de Banco de Dados faz isso acrescentando internamente um sufixo numérico a cada nome de tabela temporária local. O nome completo de uma tabela temporária como ele foi armazenado na tabela sysobjects em tempdb é composto pelo nome da tabela especificado na instrução CREATE TABLE e o sufixo numérico gerado pelo sistema. Para permitir o sufixo, o table_name especificado para um nome temporário local não pode exceder 116 caracteres.
As tabelas temporárias serão descartadas automaticamente se não se ultrapassarem o escopo, a menos que sejam explicitamente descartadas com o uso de DROP TABLE:
Uma tabela temporária local criada em um procedimento armazenado será descartada automaticamente quando o procedimento armazenado for encerrado. A tabela pode ser referenciada por qualquer procedimento armazenado aninhado executado pelo procedimento armazenado que criou a tabela. A tabela não pode ser referenciada pelo processo que chamou o procedimento armazenado que criou a tabela.
Todas as outras tabelas temporárias locais serão descartadas automaticamente ao término da sessão atual.
As tabelas temporárias globais serão descartadas automaticamente ao término da sessão que criou e quando todas as demais tarefas pararem de fazer referência a ela. A associação entre uma tarefa e uma tabela será mantida apenas enquanto durar uma única instrução Transact-SQL. Ou seja, a tabela temporária global será descartada ao término da última instrução Transact-SQL que estava referenciando ativamente a tabela ao término da sessão de criação.
Uma tabela temporária local criada em um procedimento armazenado ou gatilho pode ter o mesmo nome de uma tabela temporária que foi criada antes de o procedimento armazenado ou gatilho ser chamado. Contudo, se uma consulta fizer referência a uma tabela temporária e houver duas tabelas temporárias com o mesmo nome, não há definição de qual tabela servirá de base para a consulta. Procedimentos armazenados aninhados também podem criar tabelas temporárias com o mesmo nome de uma tabela temporária que foi criada pelo procedimento armazenado que o chamou. Entretanto, para que modificações sejam resolvidas na tabela que foi criada no procedimento aninhado, a tabela deve ter a mesma estrutura, com os mesmos nomes de colunas, da tabela criada pelo procedimento que o chamou. Isso é mostrado no exemplo a seguir.
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
Aqui está o conjunto de resultados.
(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2
Quando você cria tabelas temporárias locais ou globais, a sintaxe CREATE TABLE suporta definições de restrições, exceto para restrições FOREIGN KEY. Se for especificada uma restrição FOREIGN KEY em uma tabela temporária, a instrução retornará uma mensagem de aviso informando que a restrição foi ignorada. A tabela ainda será criada sem as restrições FOREIGN KEY. Tabelas temporárias não podem ser referenciadas em restrições FOREIGN KEY.
Se uma tabela temporária for criada com uma restrição nomeada e se for criada dentro do escopo de uma transação definida pelo usuário, somente um usuário de cada vez poderá executar a instrução que cria a tabela temporária. Por exemplo, se um procedimento armazenado criar uma tabela temporária com uma restrição de chave primária nomeada, o procedimento armazenado não poderá ser executado simultaneamente por vários usuários.
Tabelas particionadas
Antes de criar uma tabela particionada usando CREATE TABLE, crie uma função de partição para especificar como a tabela será particionada. Uma função de partição é criada com o uso de CREATE PARTITION FUNCTION. Em seguida, crie um esquema de partição para especificar os grupos de arquivos que manterão as partições indicadas pela função de partição. Um esquema de partição é criado com o uso de CREATE PARTITION SCHEME. O uso das restrições PRIMARY KEY ou UNIQUE para separar grupos de arquivos não pode ser especificado para tabelas particionadas. Para obter mais informações, consulte Tabelas e índices particionados.
Restrições PRIMARY KEY
Uma tabela pode conter apenas uma restrição PRIMARY KEY.
O índice gerado pela restrição PRIMARY KEY não pode fazer com que o número de índices na tabela exceda 999 índices não clusterizados e 1 índice clusterizado.
Se CLUSTERED ou NONCLUSTERED não estiver especificado para uma restrição PRIMARY KEY, CLUSTERED será usado, se não houver índices clusterizados especificados para restrições UNIQUE.
Todas as colunas definidas em uma restrição PRIMARY KEY devem ser definidas como NOT NULL. Se a nulidade não for especificada, todas as colunas participantes de uma restrição FOREIGN KEY devem ter sua nulidade definida como NOT NULL.
Se a chave primária for definida em uma coluna de tipo de dados CLR definido pelo usuário, a implementação do tipo deverá oferecer suporte a uma ordenação binária. Para obter mais informações, consulte Tipos CLR definidos pelo usuário.
Restrições UNIQUE
Se CLUSTERIZADO ou NONCLUSTERED não for especificado para uma restrição UNIQUE, NONCLUSTERED será usado por padrão.
Cada restrição UNIQUE gera um índice. O número de restrições UNIQUE não pode fazer com que o número de índices na tabela exceda 999 índices não clusterizados e 1 índice clusterizado.
Se a restrição unique for definida em uma coluna de tipo de dados CLR definido pelo usuário, a implementação do tipo deverá oferecer suporte a uma ordenação binária ou com base no operador. Para obter mais informações, consulte Tipos CLR definidos pelo usuário.
Restrições FOREIGN KEY
Quando um valor diferente de NULL é inserido na coluna de uma restrição FOREIGN KEY, o valor deve existir na coluna referenciada; caso contrário, será retornada uma mensagem de erro de violação de chave estrangeira.
Restrições FOREIGN KEY serão aplicadas à coluna precedente, a menos que sejam especificadas colunas de origem.
Restrições FOREIGN KEY só podem fazer referência a tabelas que estão no mesmo banco de dados e no mesmo servidor. A integridade referencial em todos os bancos de dados deve ser implementada por gatilhos. Para obter mais informações, consulte CREATE TRIGGER (Transact-SQL).
As restrições FOREIGN KEY podem fazer referência a outra coluna da mesma tabela. Isso se chama autorreferência.
A cláusula REFERENCES de uma restrição FOREIGN KEY no nível de coluna pode listar apenas uma coluna de referência. Essa coluna deve ter o mesmo tipo de dados da coluna na qual a restrição foi definida.
O número de colunas de referência da cláusula REFERENCES de uma restrição FOREIGN KEY deve ser igual ao número de colunas da lista de colunas de restrição. O tipo de dados de cada coluna de referência também deve ser igual ao da coluna correspondente na lista de colunas.
CASCADE, SET NULL ou SET DEFAULT não podem ser especificados se uma coluna do tipo timestamp integrar a chave estrangeira ou a chave referenciada.
CASCADE, SET NULL, SET DEFAULT e NO ACTION podem ser combinados nas tabelas que tenham relacionamentos referenciais entre si. Se o Mecanismo de Banco de Dados encontrar NO ACTION, parará e reverterá as ações CASCATA, SET NULL e SET DEFAULT. Quando uma instrução DELETE provoca uma combinação de ações CASCADE, SET NULL, SET DEFAULT e NO ACTION, todas as ações CASCADE, SET NULL e SET DEFAULT são aplicadas antes que o Mecanismo de Banco de Dados verifique se existe alguma NO ACTION.
O Mecanismo de Banco de Dados não tem um limite predefinido quanto ao número de restrições FOREIGN KEY que uma tabela pode conter que referenciam outras tabelas nem quanto ao número de restrições FOREIGN KEY que são propriedade de outras tabelas que fazem referência à tabela específica.
Entretanto, o número real de restrições FOREIGN KEY que pode ser usado é limitado pela configuração do hardware e pelo design do banco de dados e do aplicativo. Recomendamos que uma tabela não contenha mais de 253 restrições FOREIGN KEY e que ela não seja referenciada por mais de 253 restrições FOREIGN KEY. O limite real pode depender mais ou menos do aplicativo e do hardware. Considere o custo da imposição de restrições FOREIGN KEY ao criar o banco de dados e os aplicativos.
Restrições FOREIGN KEY não são impostas a tabelas temporárias.
As restrições FOREIGN KEY podem fazer referência somente a colunas em restrições PRIMARY KEY ou UNIQUE na tabela ou em uma UNIQUE INDEX na tabela referenciada.
Se a chave estrangeira for definida em uma coluna de tipo de dados CLR definido pelo usuário, a implementação do tipo deverá oferecer suporte a uma ordenação binária. Para obter mais informações, consulte Tipos CLR definidos pelo usuário.
As colunas que participam de uma relação de chave estrangeira devem ser definidas com o mesmo comprimento e a mesma escala.
Definições DEFAULT
Uma coluna pode ter apenas uma definição DEFAULT.
Uma definição DEFAULT pode conter valores constantes, funções, funções niladic SQL-92 ou NULL. A tabela a seguir mostra as funções niladic e os valores que elas retornam para o padrão durante uma instrução INSERT.
funções niladic SQL-92
Valor retornado
CURRENT_TIMESTAMP
Data e hora atuais.
CURRENT_USER
Nome de usuário que está executando uma inserção.
SESSION_USER
Nome de usuário que está executando uma inserção.
SYSTEM_USER
Nome de usuário que está executando uma inserção.
USER
Nome de usuário que está executando uma inserção.
constant_expression em uma definição DEFAULT não pode fazer referência a uma outra coluna da tabela, ou a outras tabelas, exibições ou procedimentos armazenados.
Não é possível criar definições DEFAULT em colunas com um tipo de dados timestamp ou colunas com uma propriedade IDENTITY.
Não é possível criar definições DEFAULT para colunas com tipos de dados do alias se o tipo de dados do alias estiver associado a um objeto padrão.
Restrições CHECK
Uma coluna pode ter qualquer número de restrições CHECK e os critérios podem incluir diversas expressões lógicas combinadas com AND e OR. Várias restrições CHECK são validadas na ordem de criação.
A avaliação do critério de pesquisa deve usar uma expressão Booleana como base e não pode fazer referência a outra tabela.
A restrição CHECK no nível de coluna pode fazer referência somente à coluna restrita, e a restrição CHECK no nível de tabela pode fazer referência somente às colunas da mesma tabela.
CHECK CONSTRAINTS e regras oferecem a mesma função de validação dos dados durante instruções INSERT e UPDATE.
Se existirem uma regra e uma ou mais restrições CHECK para uma coluna, ou colunas, todas as restrições serão avaliadas.
Não é possível definir restrições CHECK em colunas text, ntext ou image.
Informações adicionais sobre restrições
Um índices criado para uma restrição não pode ser descartado pelo uso de DROP INDEX; a restrição deve ser descartada com o uso de ALTER TABLE. Um índice criado para uma restrição e usado por ela pode ser reconstruído usando ALTER INDEX...REBUILD. Para obter mais informações, consulte Reorganizar e recriar índices.
Os nomes de restrições devem seguir as regras para identificadores, exceto que o nome não pode começar com uma tecla de cerquilha (#). Se o constraint_name não for fornecido, um nome gerado pelo sistema será atribuído à restrição. O nome da restrição aparece em qualquer mensagem de erro sobre violações de restrição.
Quando uma restrição for violada em uma instrução INSERT, UPDATE ou DELETE, a instrução será encerrada. No entanto, quando SET XACT_ABORT for definido como OFF, a transação, se a instrução integrar uma transação explícita, continuará a ser processada. Quando SET XACT_ABORT for definido como ON, a transação inteira será revertida. Também é possível usar a instrução ROLLBACK TRANSACTION com a definição de transação verificando-se a função de sistema @@ERROR.
Quando ALLOW_ROW_LOCKS = ON e ALLOW_PAGE_LOCK = ON, os bloqueios em nível de linha, página e tabela serão permitidos quando você acessar o índice. O Mecanismo de Banco de Dados escolhe o bloqueio apropriado e pode escalar o bloqueio de uma linha ou página para um bloqueio de tabela. Quando ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, somente um bloqueio em nível de tabela é permitido ao acessar o índice.
Se uma tabela tiver restrições FOREIGN KEY ou CHECK e gatilhos, os critérios da restrição serão avaliados antes da execução do gatilho.
Para obter um relatório de uma tabela e suas colunas, use sp_help ou sp_helpconstraint. Para renomear uma tabela, use sp_rename. Para obter um relatório de exibições e procedimentos armazenados que dependem de uma tabela, use sys.dm_sql_referenced_entities e sys.dm_sql_referencing_entities.
Regras de nulidade em uma definição de tabela
A nulidade de uma coluna determina se ela permite valor nulo (NULL) como dados nessa coluna. NULL não é zero nem em branco: NULL significa que nenhuma entrada foi feita e nem um NULL explícito foi fornecido e, normalmente, implica que o valor é desconhecido ou não se aplica.
Quando você usa CREATE TABLE ou ALTER TABLE para criar ou alterar uma tabela, as configurações de banco de dados e de sessão influenciam e, possivelmente, substituem a nulidade do tipo de dados que é usado em uma definição de coluna. Recomendamos que você sempre defina explicitamente uma coluna como NULL ou NOT NULL para colunas não computadas ou, se usar um tipo de dados definido pelo usuário, permita que a coluna use a nulidade padrão do tipo de dados. Colunas esparsas sempre têm que permitir NULL.
Quando a nulidade da coluna não é especificada explicitamente, ela segue as regras mostradas na tabela a seguir
Tipo de dados da coluna |
Regra |
|---|---|
Tipo de dados de alias |
O Mecanismo de Banco de Dados usa a nulidade especificada durante a criação do tipo de dados. Para determinar a nulidade padrão do tipo de dados, use sp_help. |
Tipo de dados CLR definido pelo usuário |
A nulidade é determinada de acordo com a definição de coluna. |
Tipo de dados fornecido pelo sistema |
Se o tipo de dados fornecido pelo sistema tiver apenas uma opção, ele prevalece. Os tipos de dados timestamp devem ser NOT NULL. Quando qualquer configuração de sessão é definida como ON com o uso de SET:
|
Quando nenhuma das opções ANSI_NULL_DFLT estiver definida para a sessão e o banco de dados estiver configurado com o padrão (ANSI_NULL_DEFAULT = OFF), o padrão de NOT NULL será atribuído.
Se a coluna for uma coluna computada, seu nulidade sempre será determinada automaticamente pelo Mecanismo de Banco de Dados. Para descobrir o nulidade desse tipo de coluna, use a função COLUMNPROPERTY com a propriedade AllowsNull.
| Observação |
|---|
O padrão do driver ODBC do SQL Server e do Microsoft OLE DB Provider for SQL Server é ter ANSI_NULL_DFLT_ON definida como ON. Os usuários de ODBC and OLE DB podem configurar essa opção nas fontes de dados ODBC ou usando os atributos ou as propriedades de conexão definidas pelo aplicativo. |
Compactação de dados
Não é possível habilitar as tabelas do sistema para compactação. Quando você cria uma tabela, a compactação de dados é definida como NONE, a menos que especificada de outra maneira. Se for especificada uma lista de partições ou uma partição fora do intervalo, um erro será gerado. Para obter mais informações sobre compactação de dados, consulte Compactação de dados.
Para avaliar como a alteração do estado de compactação afetará uma tabela, um índice ou uma partição, use o procedimento armazenado sp_estimate_data_compression_savings.
Permissões
Requer a permissão CREATE TABLE no banco de dados e a permissão ALTER no esquema no qual a tabela está sendo criada.
Se alguma coluna da instrução CREATE TABLE for definida com um tipo de dados CLR definido pelo usuário, a propriedade do tipo ou a permissão REFERENCES é necessária.
Se alguma coluna da instrução CREATE TABLE tiver uma coleção de esquemas XML associada a ela, a propriedade da coleção de esquemas XML ou a permissão REFERENCES é necessária.
Qualquer usuário pode criar tabelas temporárias no tempdb.
Exemplos
A.Criar uma restrição PRIMARY KEY em uma coluna
O exemplo a seguir mostra a definição de coluna para uma restrição PRIMARY KEY com um índice clusterizado na coluna EmployeeID da tabela Employee. Como não foi especificado um nome de restrição, o sistema fornecerá o nome da restrição.
CREATE TABLE dbo.Employee (EmployeeID int
PRIMARY KEY CLUSTERED);
B.Usando restrições FOREIGN KEY
Uma restrição FOREIGN KEY é usada para fazer referência a outra tabela. Chaves estrangeiras podem ser chaves de coluna única ou chaves de várias colunas. O próximo exemplo mostra uma restrição FOREIGN KEY de coluna única na tabela SalesOrderHeader que faz referência à tabela SalesPerson. Somente a cláusula REFERENCES é necessária para uma restrição FOREIGN KEY de coluna única.
SalesPersonID int NULL
REFERENCES SalesPerson(SalesPersonID)
Também é possível usar explicitamente a cláusula FOREIGN KEY e declarar novamente o atributo de coluna. Observe que o nome da coluna não tem que ser o mesmo em ambas as tabelas.
FOREIGN KEY (SalesPersonID) REFERENCES SalesPerson(SalesPersonID)
As restrições de chaves de várias colunas são criadas como restrições de tabela. No banco de dados AdventureWorks2012 , a tabela SpecialOfferProduct contém uma PRIMARY KEY de várias colunas. O exemplo a seguir mostra como fazer referência a essa chave a partir de outra tabela; um nome de restrição explícito é opcional.
CONSTRAINT FK_SpecialOfferProduct_SalesOrderDetail FOREIGN KEY
(ProductID, SpecialOfferID)
REFERENCES SpecialOfferProduct (ProductID, SpecialOfferID)
C.Usando restrições UNIQUE
As restrições UNIQUE são usadas para impor exclusividade a colunas de chave não primária. O exemplo a seguir impõe uma restrição indicando que a coluna Name da tabela Product deve ser exclusiva.
Name nvarchar(100) NOT NULL
UNIQUE NONCLUSTERED
D.Usando definições DEFAULT
Os padrões fornecem um valor (com as instruções INSERT e UPDATE) quando nenhum valor é fornecido. Por exemplo, o banco de dados AdventureWorks2012 poderia conter uma tabela de pesquisa listando os diversos cargos que os funcionários podem ocupar na empresa. Na coluna que descreve cada trabalho, uma cadeia de caracteres padrão poderia fornecer uma descrição se não for inserida explicitamente uma descrição real.
DEFAULT 'New Position - title not formalized yet'
Além de constantes, definições DEFAULT podem incluir funções. Use o exemplo a seguir para obter a data atual para uma entrada.
DEFAULT (getdate())
Um exame da função niladic também pode melhorar a integridade dos dados. Para monitorar o usuário que inseriu uma linha, use a função niladic para USER. Não coloque as funções niladic entre parênteses.
DEFAULT USER
E.Usando restrições CHECK
O exemplo a seguir mostra uma restrição composta pelos valores inseridos na coluna CreditRating da tabela Vendor. A restrição não é nomeada.
CHECK (CreditRating >= 1 and CreditRating <= 5)
Este exemplo mostra uma restrição nomeada com um padrão de restrição nos dados de caracteres inseridos em uma coluna de uma tabela.
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]')
Este exemplo especifica que os valores devem estar em uma lista específica ou seguir o padrão especificado.
CHECK (emp_id IN ('1389', '0736', '0877', '1622', '1756')
OR emp_id LIKE '99[0-9][0-9]')
F.Mostrando a definição de tabela completa
O próximo exemplo mostra as definições de tabela completas com todas as definições de restrição para a tabela PurchaseOrderDetail, criada no banco de dados AdventureWorks2012 . Observe que para executar a amostra, o esquema de tabela foi alterado para 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.Criando uma tabela com uma coluna de xml digitada para uma coleção de esquemas XML
O exemplo seguinte cria uma tabela com uma coluna xml que é digitada para a coleção de esquemas XML HRResumeSchemaCollection. A palavra-chave DOCUMENT especifica que cada instância do tipo de dados xml em column_name pode conter apenas um elemento de nível superior.
USE AdventureWorks2012;
GO
CREATE TABLE HumanResources.EmployeeResumes
(LName nvarchar(25), FName nvarchar(25),
Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );
H.Criando uma tabela particionada
O exemplo a seguir cria uma função de partição para dividir uma tabela ou índice em quatro partições. Em seguida, o exemplo cria um esquema de partição que especifica os grupos de arquivos que conterão cada uma das quatro partições. Finalmente, o exemplo cria uma tabela que usa o esquema de partição. Este exemplo supõe que os grupos de arquivos já existam no banco de dados.
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
Com base nos valores da coluna col1 de PartitionTable, as partições são atribuídas das seguintes maneiras.
Grupo de arquivos |
test1fg |
test2fg |
test3fg |
test4fg |
|---|---|---|---|---|
Partição |
1 |
2 |
3 |
4 |
Valores |
col 1 <= 1 |
col1 > 1 AND col1 <= 100 |
col1 > 100 AND col1 <= 1,000 |
col1 > 1000 |
I.Usando o tipo de dados uniqueidentifier em uma coluna
O exemplo a seguir cria uma tabela com uma coluna uniqueidentifier. O exemplo usa uma restrição PRIMARY KEY para proteger a tabela, evitando que os usuários insiram valores duplicados, e usa a função NEWSEQUENTIALID() da restrição DEFAULT para fornecer valores para novas linhas. A propriedade ROWGUIDCOL é aplicada à coluna uniqueidentifier de forma que ela possa ser referenciada pela palavra-chave $ROWGUID.
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.Usando uma expressão para uma coluna computada
O exemplo a seguir mostra o uso de uma expressão ((low + high)/2) para calcular a coluna computada myavg.
CREATE TABLE dbo.mytable
( low int, high int, myavg AS (low + high)/2 ) ;
K.Criando uma coluna computada com base em uma coluna de tipo de dados definido pelo usuário
O exemplo a seguir cria uma tabela com uma coluna definida como tipo de dados definido pelo usuário utf8string, supondo que o assembly do tipo, e o próprio tipo, já foram criados no banco de dados atual. Uma segunda coluna é definida com base em utf8string e usa o método ToString() de type(class) utf8string para computar um valor para a coluna.
CREATE TABLE UDTypeTable
( u utf8string, ustr AS u.ToString() PERSISTED ) ;
L.Usando uma função USER_NAME para uma coluna computada
O exemplo a seguir usa a função USER_NAME() na coluna myuser_name.
CREATE TABLE dbo.mylogintable
( date_in datetime, user_id int, myuser_name AS USER_NAME() ) ;
M.Criando uma tabela que possui uma coluna FILESTREAM
O exemplo a seguir cria uma tabela com uma coluna FILESTREAM Photo. Se uma tabela tiver uma ou mais colunas FILESTREAM, ela deve ter uma coluna ROWGUIDCOL.
CREATE TABLE dbo.EmployeePhoto
(
EmployeeId int NOT NULL PRIMARY KEY
,Photo varbinary(max) FILESTREAM NULL
,MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
UNIQUE DEFAULT NEWID()
);
N.Criando uma tabela que usa a compactação de linha
O exemplo a seguir cria uma tabela que usa compactação de linha.
CREATE TABLE dbo.T1
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);
Para obter exemplos adicionais de compactação de dados, consulte Compactação de dados.
O.Criando uma tabela que tem colunas esparsas e um conjunto de colunas
Os exemplos a seguir mostram como criar uma tabela com uma coluna esparsa e uma coluna com duas colunas esparsas e um conjunto de colunas. Os exemplos usam a sintaxe básica. Para obter exemplos mais complexos, consulte Usar colunas esparsas e Usar conjuntos de colunas.
Este exemplo cria uma tabela com uma coluna esparsa.
CREATE TABLE dbo.T1
(c1 int PRIMARY KEY,
c2 varchar(50) SPARSE NULL ) ;
Este exemplo cria uma tabela com duas colunas esparsas e um conjunto de colunas nomeado CSet.
CREATE TABLE T1
(c1 int PRIMARY KEY,
c2 varchar(50) SPARSE NULL,
c3 int SPARSE NULL,
CSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ) ;
Consulte também
Referência
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)