CREATE TABLE (Transact-SQL)
Cria uma tabela nova.
.gif "Ícone de vínculo de tópico")
Sintaxe
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>
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. Nomes de tabelas devem obedecer às regras para identificadores. table_name pode ter um máximo de 128 caracteres, exceto no caso de nomes de tabelas temporárias locais (nomes com prefixo de um único sinal numérico [#]) que não podem exceder 116 caracteres.column_name
É o nome de uma coluna da tabela. Os nomes de colunas devem obedecer às regras para identificadores e devem ser exclusivos na tabela. column_name pode conter até 128 caracteres. column_name pode ser omitido nas colunas criadas com um tipo de dados timestamp. Se column_name não for especificado, o nome de uma coluna timestamp assumirá o padrão 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 um nome de coluna não computada, uma constante, uma função, uma variável e qualquer combinação desses elementos conectados por um ou mais operadores. A expressão não pode ser uma subconsulta nem conter tipos de dados 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:
Uma coluna computada não pode ser usada como uma definição de restrição DEFAULT ou FOREIGN KEY ou com uma definição de restrição NOT NULL. Entretanto, uma coluna computada poderá ser usada como uma coluna de chave em um índice ou como parte de qualquer restrição PRIMARY KEY ou UNIQUE se o valor da coluna computada for definido por uma expressão determinística e o tipo de dados do resultado for permitido em 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 poderá ser alterado em invocações subseqüentes.
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 poderá permitir valor nulo mesmo se estiverem presentes somente colunas não nulas, pois a falta ou o 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 NULL. 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 Criando índices em colunas computadas. Quaisquer colunas computadas usadas como colunas de particionamento de uma tabela particionada devem ser marcadas explicitamente como PERSISTED. O parâmetro computed_column_expression deverá ser determinístico quando PERSISTED for 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" }
São palavras-chave que indicam que as colunas text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) e de tipo de dados CLR definido pelo usuário 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 quaisquer dados de coluna de valor grande especificados 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 mesmas colunas de partição que o 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 que tiver a propriedade DEFAULT 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. Neste caso, um outro grupo de arquivos FILESTREAM, 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 Projetando e implementando armazenamento FILESTREAM.
[ 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 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 no 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:
Tipo de dados do sistema do SQL Server.
O esquema padrão do usuário atual no banco de dados atual.
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 esquemas 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 padrão para a coluna.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á imposta quando os agentes de replicação executarem operações insert, update ou delete. Para obter mais informações, consulte Controlando restrições, identidades e gatilhos com NOT FOR REPLICATION.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. Quando a propriedade ROWGUIDCOL é aplicada, é possível fazer referência à coluna usando $ROWGUID. A propriedade ROWGUIDCOL somente pode ser atribuída a uma coluna uniqueidentifier. A palavra-chave ROWGUIDCOL não será válida se o nível de compatibilidade do banco de dados for inferior a 65. Para obter mais informações, consulte sp_dbcmptlevel (Transact-SQL). Colunas com 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 conhecer outras restrições e obter mais informações sobre as colunas esparsas, consulte Usando 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 GUID da coluna deve ser fornecido por um aplicativo durante a inserção de dados ou por uma restrição DEFAULT que utilize 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 pode ser um nome de agrupamento do Windows ou um nome de agrupamento do SQL. collation_name aplica-se somente às 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. Para obter mais informações, consulte Restrições.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 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 permitir valor nulo e não houver nenhum valor padrão explícito definido, NULL se tornará o valor padrão implícito da 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 Agrupando 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 AdventureWorks, a tabela ProductVendor tem uma relação referencial com a tabela Vendor. A chave estrangeira ProductVendor.VendorID faz referência à chave primária Vendor.VendorID.
Se uma instrução DELETE for executada em uma linha da tabela Vendor e uma ação ON DELETE CASCADE for especificada para ProductVendor.VendorID, 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 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 acontece nas linhas da tabela alterada quando essas linhas têm uma relação referencial e a linha referenciada é 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 permitir valor nulo.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 uma coluna permitir valor nulo e não houver nenhum valor padrão explícito definido, NULL se tornará o valor padrão implícito da 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 Agrupando alterações a linhas relacionadas com registros lógicos.
ON UPDATE CASCADE não poderá ser definido se um gatilho INSTEAD OF já existir na tabela que está sendo alterada.
Por exemplo, no banco de dados AdventureWorks, a tabela ProductVendor tem uma relação referencial com a tabela Vendor: a chave estrangeira ProductVendor.VendorID faz referência à chave primária Vendor.VendorID.
Se uma instrução UPDATE for executada em uma linha da tabela Vendor e uma ação ON UPDATE CASCADE for especificada para ProductVendor.VendorID, 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, bem como a 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 nos 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")
ImportanteÉ recomendável especificar NOT NULL na coluna de particionamento de tabelas particionadas e também de tabelas não particionadas que sejam origens e destinos de operações ALTER TABLE...SWITCH. Isso assegura que as restrições CHECK em colunas de particionamento não precisem procurar valores nulos. Para obter mais informações, consulte Transferindo dados de forma eficaz usando a alternância de partição.
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 não tipado que combina todas as colunas esparsas de uma tabela em uma saída estruturada. Para obter mais informações sobre conjuntos de colunas, consulte Usando 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 com a compactação de página.
Para obter mais informações sobre compactação, consulte Criando tabelas e índices compactados.
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 o Mecanismo de Banco de Dados deve preencher do 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 duplicados forem inseridos em um índice exclusivo. Haverá 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. No caso de OFF, não são usados bloqueios de página. O padrão é ON.
Comentários
O SQL Server 2008 pode ter até 2 bilhões de tabelas por banco de dados. Uma tabela contendo um conjunto de colunas definido pode ter até 30.000 colunas, com o máximo de 1024 colunas não esparsas + computadas. As tabelas que não têm conjuntos de colunas são limitadas a 1024 colunas. O número de linhas e o tamanho total da tabela são limitados apenas pelo armazenamento disponível. O número de máximo de bytes por linha é 8.060. Essa restrição é mais branda para tabelas com colunas varchar, nvarchar, varbinary ou sql_variant, que fazem com que a largura total definida da tabela exceda 8.060 bytes. O comprimento de cada uma dessas colunas ainda deve se encaixar no limite de 8.000 bytes, mas suas larguras combinadas podem exceder o limite de 8.060 bytes em uma tabela. Para obter mais informações, consulte Dados de estouro de linha excedendo 8 KB.
Cada tabela pode conter no máximo 999 índices não clusterizados e 1 índice clusterizado. Estão incluídos os índices gerados para dar suporte às restrições PRIMARY KEY e UNIQUE definidas para a tabela.
Geralmente, o espaço é alocado para 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 uma extensão uniforme. Quando houver páginas suficientes 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) e, para tabelas temporárias globais, inclua 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 possa ser executado ao mesmo tempo por vários usuários, o Mecanismo de Banco de Dados deverá 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 parâmetro 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 as criou e quando todas as demais tarefas pararem referenciá-las. 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 que uma tabela temporária 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 está definido em qual tabela a consulta será resolvida. 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 as 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 de chamada. 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
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 dá suporte a 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.
É recomendável o uso de variáveis de tabela em vez de tabelas temporárias. As tabelas temporárias são úteis quando é necessário criar índices explicitamente nelas ou quando os valores da tabela devem ficar visíveis para vários procedimentos armazenados ou funções. Em geral, as variáveis de tabela contribuem para um processamento de consulta mais eficiente. Para obter mais informações, consulte tabela (Transact-SQL).
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 deverão 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á dar suporte a uma ordenação binária. Para obter mais informações, consulte Tipos CLR definidos pelo usuário.
Restrições UNIQUE
Se não for especificado CLUSTERED ou NONCLUSTERED 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 exclusiva for definida em uma coluna de tipo de dados CLR definido pelo usuário, a implementação do tipo deverá dar suporte a uma ordenação binária ou baseada 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.
As restrições FOREIGN KEY sã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 auto-referê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 poderão 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 referenciam a 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 referenciar somente colunas em restrições PRIMARY KEY ou UNIQUE na tabela ou em 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á dar suporte a uma ordenação binária. Para obter mais informações, consulte Tipos CLR definidos pelo usuário.
A coluna do tipo varchar(max) poderá integrar uma restrição FOREIGN KEY somente se a chave primária que ela referencia também estiver definida como tipo varchar(max).
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 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. As 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 índice criado para uma restrição não pode ser descartado com o uso de DROP INDEX; a restrição deve ser descartada com ALTER TABLE. Um índice criado para uma restrição e usado por ela pode ser reconstruído com o uso de DBCC DBREINDEX.
Os nomes de restrições devem seguir as regras para identificadores, com exceção de que o nome não pode começar com um sinal numérico (#). Se 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. Para obter mais informações, consulte Escalonamento de bloqueios (Mecanismo de Banco de Dados). Quando ALLOW_ROW_LOCKS = OFF e ALLOW_PAGE_LOCK = OFF, somente um bloqueio em nível de tabela é permitido ao acessar o índice. Para obter mais informações sobre como configurar a granularidade de bloqueio de um índice, consulte Personalizando bloqueio de um í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 dessa coluna. NULL não é zero nem em branco: NULL significa que nenhuma entrada foi feita ou foi fornecido um NULL explícito e, em geral, indica 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 escassas 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, ela 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, sua 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 definido 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 definidos 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 Criando tabelas e índices compactados.
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 será 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 será necessária.
Exemplos
A. Usando restrições PRIMARY KEY
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 (permitindo que o sistema forneça o nome da restrição) do banco de dados de exemplo AdventureWorks.
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 precisa 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 AdventureWorks, a tabela SpecialOfferProduct contém uma PRIMARY KEY de várias colunas. O exemplo a seguir mostra como fazer referência a essa chave em 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 AdventureWorks poderá conter uma tabela de pesquisa listando os diversos cargos que os funcionários podem ocupar na empresa. Na coluna que descreve cada cargo, uma cadeia de caracteres padrão poderá 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 AdventureWorks. Observe que, para executar o exemplo, 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 xml tipada para uma coleção de esquemas XML
O exemplo a seguir cria uma tabela com uma coluna xml que é tipada 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 AdventureWorks;
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 para que ela possa ser referenciada com a 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 FILESTREAMPhoto. 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 T1
(c1 int, c2 nvarchar(200) )
WITH (DATA_COMPRESSION = ROW);
Para obter exemplos de compactação de dados adicionais, consulte Criando tabelas e índices compactados.
O. Criando uma tabela que tem colunas escassas e um conjunto de colunas
Os exemplos a seguir mostram como criar uma tabela com uma coluna esparsa e uma tabela com duas colunas esparsas e um conjunto de colunas. Os exemplos usam a sintaxe básica. Para obter exemplos mais complexos, consulte Usando colunas esparsas e Usando conjuntos de colunas.
Para criar uma tabela com uma coluna esparsa, execute o seguinte código.
CREATE TABLE T1
(c1 int PRIMARY KEY,
C2 varchar(50) SPARSE NULL ) ;
Para criar uma tabela com duas colunas esparsas e um conjunto de colunas nomeado CSet, execute o seguinte código.
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