Opções ALTER DATABASE SET (Transact-SQL)

Este tópico contém a sintaxe de ALTER DATABASE relacionada à configuração das opções de banco de dados. Para obter informações sobre outra sintaxe de ALTER DATABASE, consulte ALTER DATABASE (Transact-SQL). O espelhamento, o Grupos de Disponibilidade AlwaysOn e os níveis de compatibilidade de banco de dados são opções SET, mas são descritas em tópicos separados por causa de sua extensão. Para obter mais informações, consulte espelhamento de banco de dados ALTER DATABASE (Transact-SQL), ALTER DATABASE SET HADR (Transact-SQL) e Nível de compatibilidade de ALTER DATABASE (Transact-SQL).

Observação

Muitas opções de definição de banco de dados podem ser configuradas para a sessão atual usando Instruções SET (Transact-SQL) e são configuradas com frequência por aplicativos quando eles são conectados. As opções definidas no nível de sessão substituem os valores de ALTER DATABASE SET . As opções de banco de dados descritas abaixo são os valores que podem ser definidos para as sessões que não fornecem explicitamente outros valores de definição de conjunto.

Convenções da sintaxe Transact-SQL

Sintaxe

ALTER DATABASE { database_name  | CURRENT }
SET 
{
    <optionspec> [ ,... n ] [ WITH <termination> ] 
}

<optionspec> ::= 
{
    <auto_option> 
  | <change_tracking_option> 
  | <containment_option> 
  | <cursor_option> 
  | <database_mirroring_option>
  | <date_correlation_optimization_option>
  | <db_encryption_option>
  | <db_state_option>
  | <db_update_option> 
  | <db_user_access_option> 
  | <external_access_option>
  | FILESTREAM ( <FILESTREAM_option> )
  | <HADR_options>
  | <parameterization_option>
  | <recovery_option> 
  | <target_recovery_time_option>
  | <service_broker_option>
  | <snapshot_option>
  | <sql_option> 
}

<auto_option> ::= 
{
    AUTO_CLOSE { ON | OFF } 
  | AUTO_CREATE_STATISTICS { ON | OFF } 
  | AUTO_SHRINK { ON | OFF } 
  | AUTO_UPDATE_STATISTICS { ON | OFF } 
  | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }
}

<change_tracking_option> ::=
{
  CHANGE_TRACKING 
   { 
       = OFF
     | = ON [ ( <change_tracking_option_list > [,... n] ) ] 
     | ( <change_tracking_option_list> [,... n ] )
   }
}

   <change_tracking_option_list> ::=
   {
       AUTO_CLEANUP = { ON | OFF } 
     | CHANGE_RETENTION = retention_period { DAYS | HOURS | MINUTES }
   }

<containment_option> ::= 
   CONTAINMENT = { NONE | PARTIAL }

<cursor_option> ::= 
{
    CURSOR_CLOSE_ON_COMMIT { ON | OFF } 
  | CURSOR_DEFAULT { LOCAL | GLOBAL } 
}

<database_mirroring_option>
  ALTER DATABASE Database Mirroring 

<date_correlation_optimization_option> ::=
    DATE_CORRELATION_OPTIMIZATION { ON | OFF }

<db_encryption_option> ::=
    ENCRYPTION { ON | OFF }

<db_state_option> ::=
    { ONLINE | OFFLINE | EMERGENCY }

<db_update_option> ::=
    { READ_ONLY | READ_WRITE }

<db_user_access_option> ::=
    { SINGLE_USER | RESTRICTED_USER | MULTI_USER }

<external_access_option> ::=
{
    DB_CHAINING { ON | OFF }
  | TRUSTWORTHY { ON | OFF }
  | DEFAULT_FULLTEXT_LANGUAGE = { <lcid> | <language name> | <language alias> }
  | DEFAULT_LANGUAGE = { <lcid> | <language name> | <language alias> }
  | NESTED_TRIGGERS = { OFF | ON }
  | TRANSFORM_NOISE_WORDS = { OFF | ON }
  | TWO_DIGIT_YEAR_CUTOFF = { 1753, ..., 2049, ..., 9999 }
}
<FILESTREAM_option> ::=
{
    NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }
  | DIRECTORY_NAME = <directory_name>
}
<HADR_options> ::=
  ALTER DATABASE SET HADR 

<parameterization_option> ::=
    PARAMETERIZATION { SIMPLE | FORCED }

<recovery_option> ::= 
{
    RECOVERY { FULL | BULK_LOGGED | SIMPLE } 
  | TORN_PAGE_DETECTION { ON | OFF }
  | PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
}

<target_recovery_time_option> ::=
    TARGET_RECOVERY_TIME = target_recovery_time { SECONDS | MINUTES }

<service_broker_option> ::=
{
    ENABLE_BROKER
  | DISABLE_BROKER
  | NEW_BROKER
  | ERROR_BROKER_CONVERSATIONS
  | HONOR_BROKER_PRIORITY { ON | OFF}
}

<snapshot_option> ::=
{
    ALLOW_SNAPSHOT_ISOLATION { ON | OFF }
  | READ_COMMITTED_SNAPSHOT {ON | OFF }
}
<sql_option> ::= 
{
    ANSI_NULL_DEFAULT { ON | OFF } 
  | ANSI_NULLS { ON | OFF } 
  | ANSI_PADDING { ON | OFF } 
  | ANSI_WARNINGS { ON | OFF } 
  | ARITHABORT { ON | OFF } 
  | COMPATIBILITY_LEVEL = { 90 | 100 | 110 }
  | CONCAT_NULL_YIELDS_NULL { ON | OFF } 
  | NUMERIC_ROUNDABORT { ON | OFF } 
  | QUOTED_IDENTIFIER { ON | OFF } 
  | RECURSIVE_TRIGGERS { ON | OFF } 
}

<termination>  ::= 
{
    ROLLBACK AFTER integer [ SECONDS ] 
  | ROLLBACK IMMEDIATE 
  | NO_WAIT
}

Argumentos

• database_name | CURRENT
  É o nome do banco de dados a ser modificado. CURRENT executa a ação no banco de dados atual. CORRENT não tem suporte em todas as opções em todos os contextos. Se houver falha em CURRENT, forneça o nome do banco de dados.

<auto_option> ::=

Controla opções automáticas.

• AUTO_CLOSE { ON | OFF }

  • ON
    O banco de dados é desligado corretamente e seus recursos são liberados depois da saída do último usuário.

    O banco de dados é reaberto automaticamente quando um usuário tenta usá-lo de novo. Por exemplo, quando é emitida uma instrução USE database_name. Se o banco de dados for desligado corretamente enquanto AUTO_CLOSE estiver definido como ON, ele não será reaberto até que um usuário tente usá-lo na próxima vez que o Mecanismo de Banco de Dados for reiniciado.

  • OFF
    O banco de dados permanecerá aberto depois da saída do último usuário.

  A opção AUTO_CLOSE é útil para bancos de dados desktop porque permite que os arquivos de banco de dados sejam gerenciados como arquivos comuns. Eles podem ser movidos, copiados para fazer backups ou mesmo enviados por email a outros usuários. O processo AUTO_CLOSE é assíncrono; abrir e fechar o banco de dados repetidamente não mais prejudica o desempenho.

  Observação
  A opção AUTO_CLOSE não está disponível em um banco de dados independente.

  O status dessa opção pode ser determinado por meio do exame da coluna is_auto_close_on na exibição do catálogo sys.databases ou da propriedade IsAutoClose da função DATABASEPROPERTYEX.

  Observação
  Quando AUTO_CLOSE for ON, algumas colunas da exibição do catálogo sys.databases e da função DATABASEPROPERTYEX retornarão NULL porque o banco de dados não está disponível para recuperar os dados. Para resolver isso, execute uma instrução USE para abrir o banco de dados.

  Observação
  O espelhamento do banco de dados requer AUTO_CLOSE OFF.

  Quando o banco de dados é definido como AUTOCLOSE = ON, uma operação que inicia o desligamento automático do banco de dados limpa o cache do plano da instância do SQL Server. A limpeza do cache de planos gera uma recompilação de todos os planos de execução subsequentes e pode provocar uma redução repentina e temporária do desempenho de consultas. No SQL Server 2005 Service Pack 2 e superior, para cada armazenamento em cache eliminado do cache de planos, o log de erros do SQL Server contém a seguinte mensagem informativa: "O SQL Server encontrou %d ocorrência(s) de liberação de armazenamento em cache '% s' (parte do cache de planos) devido à manutenção do banco de dados ou operações de reconfiguração". Essa mensagem é registrada a cada cinco minutos, contanto que o cache seja limpo dentro desse intervalo de tempo.

• AUTO_CREATE_STATISTICS { ON | OFF }

  • ON
    O otimizador de consulta cria estatísticas em colunas únicas em predicados de consulta, conforme necessário, para melhorar planos e desempenho de consulta. Estas estatísticas de coluna única são criadas quando o otimizador de consulta compila consultas. As estatísticas de coluna única só são criadas em colunas que ainda não são a primeira de um objeto de estatísticas existente.

    O padrão é ON. Nós recomendamos que você use a configuração padrão para a maioria dos bancos de dados.

  • OFF
    O otimizador de consulta não cria estatísticas em colunas únicas em predicados de consulta quando estiver compilando consultas. Definir essa opção como OFF pode acarretar planos de consulta de qualidade inferior e menor desempenho de consulta.

  O status dessa opção pode ser determinado por meio do exame da coluna is_auto_create_stats_on na exibição do catálogo sys.databases ou da propriedade IsAutoCreateStatistics da função DATABASEPROPERTYEX.

  Para obter mais informações, consulte a seção "Usando as opções de estatísticas em todo o banco de dados” em Estatísticas.

• AUTO_SHRINK {ON | OFF}

  • ON
    Os arquivos de banco de dados são candidatos à redução periódica.

    Arquivos de dados e arquivos de log podem ser reduzidos automaticamente. AUTO_SHRINK reduzirá o tamanho do log de transações somente se o banco de dados estiver definido como modelo de recuperação SIMPLE ou se foi feito backup do log. Quando definido como OFF, os arquivos de banco de dados não são reduzidos automaticamente durante as verificações periódicas de espaço não utilizado.

    A opção AUTO_SHRINK faz com que os arquivos sejam reduzidos quando mais que 25% do arquivo contém espaço não utilizado. O arquivo é reduzido de forma que 25% de seu tamanho seja de espaço não utilizado ou ele tenha o mesmo tamanho de quando foi criado, o que for maior.

    Não é possível reduzir um banco de dados somente leitura.

  • OFF
    Os arquivos de banco de dados não são reduzidos automaticamente durante as verificações periódicas de espaço não utilizado.

  O status dessa opção pode ser determinado por meio do exame da coluna is_auto_shrink_on na exibição do catálogo sys.databases ou da propriedade IsAutoShrink da função DATABASEPROPERTYEX.

  Observação
  A opção AUTO_SHRINK não está disponível em um banco de dados independente.

• AUTO_UPDATE_STATISTICS { ON | OFF }

  • ON
    Especifica que o otimizador de consultas atualiza estatísticas quando são usadas por uma consulta e quando podem estar desatualizadas. As estatísticas ficam desatualizadas depois que operações de inserção, atualização, exclusão ou mesclagem alteram a distribuição de dados na tabela ou na exibição indexada. O otimizador de consulta determina quando estatísticas podem estar desatualizadas contando o número de modificações de dados desde a última atualização das estatísticas e comparando o número de modificações a um limite. O limite se baseia no número de linhas na tabela ou na exibição indexada.

    O otimizador de consulta procura estatísticas desatualizadas antes de compilar uma consulta e antes de executar um plano de consulta em cache. Antes de compilar uma consulta, o otimizador usa as colunas, tabelas e exibições indexadas no predicado de consulta para determinar quais estatísticas podem estar desatualizadas. Antes de executar um plano de consulta em cache, o Mecanismo de Banco de Dados verifica se o plano de consulta faz referência a estatísticas atualizadas.

    A opção AUTO_UPDATE_STATISTICS se aplica a estatísticas criadas para índices, colunas únicas em predicados de consulta, além de estatísticas criadas usando a instrução CREATE STATISTICS. Essa opção também se aplica a estatísticas filtradas.

    O padrão é ON. Nós recomendamos que você use a configuração padrão para a maioria dos bancos de dados.

    Use a opção AUTO_UPDATE_STATISTICS_ASYNC para especificar se as estatísticas são atualizadas de forma síncrona ou assíncrona.

  • OFF
    Especifica que o otimizador de consulta não atualiza estatísticas quando elas são usadas por uma consulta e quando elas podem ficar desatualizadas. Definir essa opção como OFF pode acarretar planos de consulta de qualidade inferior e menor desempenho de consulta.

  O status dessa opção pode ser determinado por meio do exame da coluna is_auto_update_stats_on na exibição do catálogo sys.databases ou da propriedade IsAutoUpdateStatistics da função DATABASEPROPERTYEX.

  Para obter mais informações, consulte a seção "Usando as opções de estatísticas em todo o banco de dados” em Estatísticas.

• AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

  • ON
    Especifica que atualizações de estatísticas para a opção AUTO_UPDATE_STATISTICS são assíncronas. O otimizador de consulta não aguarda a conclusão das atualizações de estatísticas para compilar consultas.

    Definir essa opção como ON não tem nenhum efeito, a menos que AUTO_UPDATE_STATISTICS seja definida como ON.

    Por padrão, a opção AUTO_UPDATE_STATISTICS_ASYNC é definida como OFF e o otimizador de consulta atualiza estatísticas de forma síncrona.

  • OFF
    Especifica que atualizações de estatísticas para a opção AUTO_UPDATE_STATISTICS são síncronas. O otimizador de consulta aguarda a conclusão das atualizações de estatísticas para compilar consultas.

    Definir essa opção como OFF não tem nenhum efeito, a menos que AUTO_UPDATE_STATISTICS seja definida como ON.

  O status dessa opção pode ser determinado por meio do exame da coluna is_auto_update_stats_async_on na exibição do catálogo sys.databases.

  Para obter mais informações que descrevem quando usar atualizações de estatísticas síncronas ou assíncronas, consulte a seção que "Usando as opções de estatísticas em todo o banco de dados" em Estatísticas.

<change_tracking_option> ::=

Controla as opções de controle de alterações. É possível habilitar o controle de alterações, definir opções, alterar opções e desabilitar o controle de alterações. Para obter exemplos, consulte a seção Exemplos mais adiante neste tópico.

• ON
  Habilita o controle de alterações no banco de dados. Quando você habilita o controle de alterações, também pode definir as opções AUTO CLEANUP e CHANGE RETENTION.

• AUTO_CLEANUP = { ON | OFF }

  • ON
    As informações de controle de alterações são removidas automaticamente depois do período de retenção especificado.

  • OFF
    Os dados de controle de alterações não são removidos do banco de dados.

• CHANGE_RETENTION =retention_period { DAYS | HOURS | MINUTES }
  Especifica o período mínimo para manter as informações de controle de alterações no banco de dados. Os dados serão removidos somente quando o valor AUTO_CLEANUP for ON.

  retention_period é um inteiro que especifica o componente numérico do período de retenção.

  O período de retenção padrão é de 2 dias. O período de retenção mínimo é de 1 minuto.

• OFF
  Desabilita o controle de alterações no banco de dados. É necessário desabilitar o controle de alterações em todas as tabelas antes de poder desabilitá-lo no banco de dados.

<containment_option> ::=

Controla opções de contenção de banco de dados.

• CONTAINMENT = { NONE | PARTIAL}

  • NONE
    O banco de dados não é um banco de dados independente.

  • PARTIAL
    O banco de dados é um banco de dados independente. Haverá falha na configuração da contenção do banco de dados como parcial, se o banco de dados tiver replicação, Change Data Capture ou controle de alterações habilitados. A verificação de erros é interrompida depois de uma falha. Para obter mais informações sobre bancos de dados independentes, consulte Bancos de dados contidos.

<cursor_option> ::=

Controla opções de cursor.

• CURSOR_CLOSE_ON_COMMIT { ON | OFF }

  • ON
    Qualquer cursor é aberto quando uma transação é confirmada ou quando uma transação revertida é fechada.

  • OFF
    Os cursores permanecem abertos quando uma transação é confirmada; uma transação revertida fechará todos os cursores, exceto aqueles definidos como INSENSITIVE ou STATIC.

  As configurações no nível de conexão que são definidas com o uso da instrução SET substituem a configuração de banco de dados padrão por CURSOR_CLOSE_ON_COMMIT. Por padrão, clientes ODBC e OLE DB emitem uma configuração CURSOR_CLOSE_ON_COMMIT de instrução SET no nível de conexão como OFF para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET CURSOR_CLOSE_ON_COMMIT (Transact-SQL).

  O status dessa opção pode ser determinado por meio do exame da coluna is_cursor_close_on_commit_on na exibição do catálogo sys.databases ou da propriedade IsCloseCursorsOnCommitEnabled da função DATABASEPROPERTYEX.

• CURSOR_DEFAULT { LOCAL | GLOBAL }
  Controla se o escopo do cursor usa LOCAL ou GLOBAL.

  • LOCAL
    Quando LOCAL for especificado e um cursor não for definido como GLOBAL ao ser criado, o escopo do cursor será local para o lote, o procedimento armazenado ou o gatilho nos quais o cursor foi criado. O nome de cursor é válido somente dentro desse escopo. O cursor pode ser referenciado por meio de variáveis de cursor local no lote, no procedimento armazenado ou no gatilho, ou em um parâmetro OUTPUT do procedimento armazenado. O cursor será implicitamente desalocado quando o lote, o procedimento armazenado ou o gatilho for encerrado, a menos que seja repassado como um parâmetro OUTPUT. Se for repassado em um parâmetro OUTPUT, o cursor será desalocado quando a última variável que o referencia for desalocada ou sair do escopo.

  • GLOBAL
    Quando GLOBAL for especificado e um cursor não for definido como LOCAL ao ser criado, o escopo do cursor será global para a conexão. O nome do cursor pode ser referenciado em qualquer procedimento armazenado ou lote executado pela conexão.

  O cursor é implicitamente desalocado somente na desconexão. Para obter mais informações, consulte DECLARE CURSOR (Transact-SQL).

  O status dessa opção pode ser determinado por meio do exame da coluna is_local_cursor_default na exibição do catálogo sys.databases ou da propriedade IsLocalCursorsDefault da função DATABASEPROPERTYEX.

<database_mirroring>

Para obter descrições de argumentos, consulte espelhamento de banco de dados ALTER DATABASE (Transact-SQL).

<date_correlation_optimization_option> ::=

Controla a opção date_correlation_optimization.

• DATE_CORRELATION_OPTIMIZATION { ON | OFF }

  • ON
    O SQL Server mantém as estatísticas de correlação entre duas tabelas do banco de dados que estiverem vinculadas por uma restrição FOREIGN KEY e tiverem colunas datetime.

  • OFF
    Estatísticas de correlação não são mantidas.

  Para que seja possível definir DATE_CORRELATION_OPTIMIZATION como ON, não deve haver nenhuma conexão ativa com o banco de dados exceto aquela que está executando a instrução ALTER DATABASE. Depois, há suporte a várias conexões.

  A configuração atual dessa opção pode ser determinada por meio do exame da coluna is_date_correlation_on na exibição do catálogo sys.databases.

<db_encryption_option> ::=

Controla o estado de criptografia do banco de dados.

• ENCRYPTION {ON | OFF}
  Define o banco de dados a ser criptografado (ON) ou não criptografado (OFF). Para obter mais informações sobre a criptografia de banco de dados, consulte Criptografia de Dados Transparente (TDE).

Quando a criptografia estiver habilitada no nível de banco de dados, todos os grupos de arquivos serão criptografados. Qualquer novo grupo de arquivos herdará a propriedade criptografada. Se algum grupo de arquivos do banco de dados for definido como READ ONLY, haverá falha na operação de criptografia de banco de dados.

É possível ver o estado da criptografia do banco de dados usando a exibição de gerenciamento dinâmico sys.dm_database_encryption_keys.

<db_state_option> ::=

Controla o estado do banco de dados.

• OFFLINE
  O banco de dados é fechado, desligado corretamente e marcado como offline. Não é possível modificar o banco de dados enquanto ele estiver offline.

• ONLINE
  O banco de dados está aberto e disponível para uso.

• EMERGENCY
  O banco de dados está marcado como READ_ONLY, o log está desabilitado e o acesso é limitado aos membros da função de servidor fixa sysadmin. EMERGENCY é usado principalmente para solução de problemas. Por exemplo, um banco de dados marcado como suspeito devido a um arquivo de log corrompido pode ser definido com o estado EMERGENCY. Isso permite habilitar o acesso somente leitura do administrador do sistema ao banco de dados. Apenas membros da função de servidor fixa sysadmin podem definir um banco de dados com o estado EMERGENCY.

Observação
Permissões: a permissão ALTER DATABASE para o banco de dados de assunto é necessária para alterar um banco de dados para o estado offline ou emergência. A permissão ALTER ANY DATABASE no nível do servidor é necessária para mover um banco de dados de offline para online.

O status dessa opção pode ser determinado por meio do exame das colunas state e state_desc na exibição de catálogo sys.databases ou na propriedade Status da função DATABASEPROPERTYEX. Para obter mais informações, consulte Estados de banco de dados.

Um banco de dados marcado como RESTORING não pode ser definido como OFFLINE, ONLINE ou EMERGENCY. Um banco de dados pode estar no estado RESTORING durante uma operação de restauração ativa ou quando uma operação de restauração de um banco de dados ou arquivo de log falhar devido a um arquivo de backup corrompido.

<db_update_option> ::=

Controla se atualizações são permitidas no banco de dados.

• READ_ONLY
  Os usuários podem ler dados do banco de dados, mas não modificá-los.

  Observação
  Para melhorar o desempenho da consulta, atualize as estatísticas antes de configurar um banco de dados como READ_ONLY. Se forem necessárias estatísticas adicionais depois de um banco de dados ser definido como READ_ONLY, o Mecanismo de Banco de Dados criará estatísticas no tempdb. Para obter mais informações sobre estatísticas para um banco de dados somente leitura, consulte Estatísticas.

• READ_WRITE
  O banco de dados está disponível para operações de leitura e gravação.

Para alterar esse estado, é necessário ter acesso exclusivo ao banco de dados. Para obter mais informações, consulte a cláusula SINGLE_USER.

<db_user_access_option> ::=

Controla o acesso de usuários ao banco de dados.

• SINGLE_USER
  Especifica que somente um usuário por vez pode acessar o banco de dados. Se SINGLE_USER for especificado e houver outros usuários conectados ao banco de dados, a instrução ALTER DATABASE será bloqueada até que todos os usuários se desconectem do banco de dados especificado. Para substituir esse comportamento, consulte a cláusula WITH <termination>.

  O banco de dados permanecerá no modo SINGLE_USER, mesmo que o usuário que definiu a opção faça logoff. Nesse momento, um usuário diferente, mas somente um, poderá se conectar ao banco de dados.

  Antes de definir o banco de dados como SINGLE_USER, verifique se a opção AUTO_UPDATE_STATISTICS_ASYNC está definida como OFF. Quando definida como ON, o thread em segundo plano usado para a atualização de estatísticas estabelece uma conexão com o banco de dados e não é possível acessar o banco de dados em modo de usuário único. Para exibir o status dessa opção, consulte a coluna is_auto_update_stats_async_on na exibição do catálogo sys.databases. Se a opção estiver definida como ON, execute as tarefas a seguir:

  1. Defina AUTO_UPDATE_STATISTICS_ASYNC como OFF.

  2. Verifique se há estatísticas assíncronas ativas, examinando a exibição de gerenciamento dinâmico sys.dm_exec_background_job_queue.

  Se houver tarefas ativas, permita que as tarefas sejam concluídas ou as encerre manualmente usando KILL STATS JOB.

• RESTRICTED_USER
  RESTRICTED_USER permite que apenas membros da função de banco de dados fixa db_owner e das funções de servidor fixas dbcreator e sysadmin conectem-se ao banco de dados, mas não limita o seu número. Todas as conexões com o banco de dados são desconectadas no período especificado pela cláusula de término da instrução ALTER DATABASE. Depois que o banco de dados fizer a transição para o estado RESTRICTED_USER, as tentativas de conexão realizadas por usuários não qualificados serão recusadas.

• MULTI_USER
  Todos os usuários com permissões apropriadas para se conectar ao banco de dados são permitidos.

O status dessa opção pode ser determinado por meio do exame da coluna user_access na exibição do catálogo sys.databases ou da propriedade UserAccess da função DATABASEPROPERTYEX.

<external_access_option> ::=

Controla se o banco de dados pode ser acessado por recursos externos, como objetos de outro banco de dados.

• DB_CHAINING { ON | OFF }

  • ON
    O banco de dados pode ser a origem ou o destino de um encadeamento de propriedades de bancos de dados.

  • OFF
    O banco de dados não pode participar do encadeamento de propriedades de bancos de dados.

  Importante
  A instância do SQL Server reconhecerá essa configuração quando a opção do servidor cross db ownership chaining for 0 (OFF). Quando cross db ownership chaining for 1 (ON), todos os bancos de dados de usuário poderão participar de cadeias de propriedades de bancos de dados, independentemente do valor dessa opção. Essa opção é definida por meio de sp_configure.

  Para definir essa opção, a permissão CONTROL SERVER no banco de dados é necessária.

  A opção DB_CHAINING não pode ser definida nesses bancos de dados do sistema: master, model e tempdb.

  O status desta opção pode ser determinado por meio do exame da coluna is_db_chaining_on na exibição do catálogo sys.databases.

• TRUSTWORTHY { ON | OFF }

  • ON
    Os módulos de banco de dados (por exemplo, funções definidas pelo usuário ou procedimentos armazenados) que usam um contexto de representação poderão acessar recursos fora do banco de dados.

  • OFF
    Os módulos de banco de dados em um contexto de representação não poderão acessar recursos fora do banco de dados.

  TRUSTWORTHY será definido como OFF sempre que o banco de dados for anexado.

  Por padrão, todos os bancos de dados do sistema, exceto o banco de dados msdb, têm TRUSTWORTHY definido como OFF. O valor não pode ser alterado para os bancos de dados model e tempdb. É recomendável nunca definir a opção TRUSTWORTHY como ON para o banco de dados master.

  Para definir essa opção, a permissão CONTROL SERVER no banco de dados é necessária.

  O status dessa opção pode ser determinado por meio do exame da coluna is_trustworthy_on na exibição do catálogo sys.databases.

• DEFAULT_FULLTEXT_LANGUAGE
  Especifica o valor de idioma padrão para colunas indexadas de texto completo.

  Importante
  Essa opção será permitida apenas quando CONTAINMENT estiver definido como PARTIAL. Se CONTAINMENT estiver definido como NONE, ocorrerão erros.

• DEFAULT_LANGUAGE
  Especifica o idioma padrão de todos os logons recém-criados. O idioma pode ser especificado com o fornecimento da lcid (id local), do nome do idioma ou do alias do idioma. Para obter uma lista de nomes e alias de idiomas aceitáveis, consulte sys.syslanguages (Transact-SQL).

  Importante
  Essa opção será permitida apenas quando CONTAINMENT estiver definido como PARTIAL. Se CONTAINMENT estiver definido como NONE, ocorrerão erros.

• NESTED_TRIGGERS
  Especifica se um gatilho AFTER pode ser colocado em cascata, ou seja, executar uma ação que inicia outro gatilho que inicia outro gatilho e assim por diante.

  Importante
  Essa opção será permitida apenas quando CONTAINMENT estiver definido como PARTIAL. Se CONTAINMENT estiver definido como NONE, ocorrerão erros.

• TRANSFORM_NOISE_WORDS
  Usado para suprimir uma mensagem de erro se palavras de ruído ou palavras irrelevantes provocarem falha em uma operação Booliana em uma consulta de texto completo.

  Importante
  Essa opção será permitida apenas quando CONTAINMENT estiver definido como PARTIAL. Se CONTAINMENT estiver definido como NONE, ocorrerão erros.

• TWO_DIGIT_YEAR_CUTOFF
  Especifica um inteiro de 1753 a 9999 que representa o ano de corte para interpretar anos de dois dígitos e de quatro dígitos.

  Importante
  Essa opção será permitida apenas quando CONTAINMENT estiver definido como PARTIAL. Se CONTAINMENT estiver definido como NONE, ocorrerão erros.

<FILESTREAM_option> ::=

Controla as configurações de FileTables.

• NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL }

  • OFF
    O acesso não transacional a dados de FileTable está habilitado.

  • READ_ONLY
    Os dados FILESTREAM em FileTables neste banco de dados podem ser lidos por processos não transacionais.

  • FULL
    O acesso não transacional completo a dados FILESTREAM em FileTables está habilitado.

• DIRECTORY_NAME = <directory_name>
  Um nome de diretório compatível com o Windows. Esse nome deve ser exclusivo entre todos os nomes de de diretório no nível do banco de dados na instância do SQL Server. A comparação de exclusividade não diferencia maiúsculas de minúsculas, independentemente das configurações de agrupamento. Essa opção deve ser definida antes da criação de um FileTable neste banco de dados.

<parameterization_option> ::=

Controla a opção de parametrização.

• PARAMETERIZATION { SIMPLE | FORCED }

  • SIMPLE
    As consultas são parametrizadas com base no comportamento padrão do banco de dados.

  • FORCED
    O SQL Server parametriza todas as consultas feitas no banco de dados.

  A configuração atual desta opção pode ser determinada por meio do exame da coluna is_parameterization_forced na exibição do catálogo sys.databases.

<recovery_option> ::=

Controla as opções de recuperação de banco de dados e a verificação de erros de E/S de disco.

• FULL
  Fornece recuperação completa depois de falha de mídia usando backups do log de transação. Se um arquivo de dados for danificado, a recuperação de mídia poderá recuperar todas as transações confirmadas. Para obter mais informações, consulte Modelos de recuperação (SQL Server).

• BULK_LOGGED
  Fornece uma recuperação após uma falha de mídia, combinando o melhor desempenho e a quantidade mínima de uso do espaço de log para determinadas operações em larga escala ou em massa. Para obter informações sobre quais operações podem ser registradas minimamente em log, consulte O log de transações (SQL Server). No modelo de recuperação BULK_LOGGED, o registro para essas operações é mínimo. Para obter mais informações, consulte Modelos de recuperação (SQL Server).

• SIMPLE
  Uma estratégia simples de backup que usa um espaço de log mínimo é fornecida. O espaço de log poderá ser reutilizado automaticamente quando não for mais necessário à recuperação de falha de servidor. Para obter mais informações, consulte Modelos de recuperação (SQL Server).

  Importante
  O modelo de recuperação simples é mais fácil de gerenciar que os outros dois modelos, mas às custas de uma exposição maior à perda de dados se um arquivo de dados for danificado. Todas as alterações desde o backup mais recente do banco de dados ou diferencial serão perdidas e terão que ser reinseridas manualmente.

O modelo de recuperação padrão é determinado pelo modelo de recuperação do banco de dados model. Para obter mais informações sobre como selecionar o modelo de recuperação apropriado, consulte Modelos de recuperação (SQL Server).

O status dessa opção pode ser determinado com o exame da colunas recovery_model e recovery_model_desc na exibição do catálogo sys.databases ou na propriedade Recovery da função DATABASEPROPERTYEX.

• TORN_PAGE_DETECTION { ON | OFF }

  • ON
    Páginas incompletas podem ser detectadas pelo Mecanismo de Banco de Dados.

  • OFF
    Páginas incompletas não podem ser detectadas pelo Mecanismo de Banco de Dados.

  Importante
  A estrutura de sintaxe TORN_PAGE_DETECTION ON | OFF será removida em uma versão futura do SQL Server. Evite usar essa estrutura de sintaxe em novos trabalhos de desenvolvimentos e planeje modificar os aplicativos que a utilizam atualmente. Use a opção PAGE_VERIFY em seu lugar.

• PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
  Descobre páginas de banco de dados danificadas por erros de caminho de E/S do disco. Os erros de caminho de E/S do disco podem ser a causa de problemas de corrupção de banco de dados e, em geral, são causados por quedas de energia ou falhas no disco que ocorrem no momento da gravação da página no disco.

  • CHECKSUM
    Calcula uma soma de verificação com base no conteúdo da página inteira e armazena o valor no cabeçalho da página quando a página é gravada em disco. Quando a página é lida pelo disco, a soma de verificação é recalculada e comparada ao valor da soma de verificação armazenado no cabeçalho da página. Se os valores não forem correspondentes, a mensagem de erro 824 (indicando uma falha na soma de verificação) será informada no log de erros do SQL Server e no log de eventos do Windows. Uma falha de soma de verificação indica um problema no caminho de E/S. Para determinar a causa principal, é necessária uma inspeção de hardware, drivers de firmware, BIOS, drivers de filtro (como software antivírus) e outros componentes de caminho de E/S.

  • TORN_PAGE_DETECTION
    Salva um padrão específico de 2 bits para cada setor de 512 bytes na página de banco de dados de 8 quilobytes (KB) e o armazena no cabeçalho da página do banco de dados quando a página é gravada em disco. Quando a página for lida pelo disco, os bits desativados armazenados no cabeçalho da página serão comparados às informações do setor da página real. Valores não correspondentes indicam que apenas parte da página foi gravada em disco. Nessa situação, a mensagem de erro 824 (indicando um erro de página interrompida) é informada no log de erros do SQL Server e no log de eventos do Windows. Páginas interrompidas serão detectadas normalmente através da recuperação de banco de dados se realmente for uma gravação incompleta de uma página. Entretanto, outras falhas de caminho de E/S podem gerar uma página interrompida a qualquer momento.

  • NONE
    Gravações de páginas de banco de dados não gerarão um valor de CHECKSUM ou de TORN_PAGE_DETECTION. O SQL Server não verificará uma soma de verificação ou uma página interrompida durante uma leitura, mesmo que um valor de CHECKSUM ou de TORN_PAGE_DETECTION esteja presente no cabeçalho da página.

  Considere os seguintes pontos importantes ao usar a opção PAGE_VERIFY:

  • O padrão é CHECKSUM.

  • Quando um usuário ou banco de dados do sistema é atualizado para o SQL Server 2005 ou posterior, o valor de PAGE_VERIFY (NONE ou TORN_PAGE_DETECTION) é retido. Recomendamos o uso de CHECKSUM.

    Observação

    Em versões anteriores do SQL Server, a opção de banco de dados PAGE_VERIFY é definida como NONE para o banco de dados tempdb e não pode ser modificada. No SQL Server 2008 e em versões posteriores, o valor padrão do banco de dados tempdb é CHECKSUM para novas instalações do SQL Server. Ao atualizar uma instalação do SQL Server, o valor padrão permanece como NONE. A opção pode ser modificada. É recomendável usar CHECKSUM para o banco de dados tempdb.

  • TORN_PAGE_DETECTION pode usar menos recursos, mas fornece um subconjunto mínimo da proteção CHECKSUM.

  • PAGE_VERIFY pode ser definido sem que o banco de dados fique offline, seja bloqueado ou a simultaneidade de usuário seja impedida nele.

  • CHECKSUM é mutuamente exclusivo com TORN_PAGE_DETECTION. As duas opções não podem ser habilitadas ao mesmo tempo.

  Quando a falha em uma página interrompida ou soma de verificação é detectada, é possível recuperá-las restaurando os dados ou recriando o índice se a falha estiver limitada apenas a páginas de índice. Se você encontrar uma falha de soma de verificação, para determinar o tipo de página de banco de dados ou páginas afetadas, execute DBCC CHECKDB. Para obter mais informações sobre opções de restauração, consulte Argumentos de RESTORE (Transact-SQL). Embora a restauração de dados resolva o problema de corrupção de dados, sua causa, por exemplo, falha do hardware de disco, deve ser diagnosticada e corrigida assim que possível para evitar a repetição dos erros.

  O SQL Server repetirá mais quatro vezes qualquer leitura que falhe com uma soma de verificação, página interrompida ou outro erro de E/S. Se a leitura tiver êxito em qualquer uma das novas tentativas, uma mensagem será gravada no log de erros e o comando que disparou a leitura continuará. Se as novas tentativas falharem, o comando falhará com a mensagem de erro 824.

  Para obter mais informações sobre soma de verificação, página interrompida, nova tentativa de leitura, mensagens de erro 823 e 824 e outros recursos de auditoria de E/S do SQL Server, consulte este site da Microsoft.

  A configuração atual dessa opção pode ser determinada por meio de um exame da coluna page_verify_option na exibição do catálogo sys.databases ou da propriedade IsTornPageDetectionEnabled da função DATABASEPROPERTYEX.

<target_recovery_time_option> ::=

Especifica a frequência de pontos de verificação indiretos por banco de dados. O padrão é 0, o que indica que o banco de dados usará pontos de verificação automáticos cuja frequência depende da configuração do intervalo de recuperação da instância de servidor.

• TARGET_RECOVERY_TIME **=**target_recovery_time { SECONDS | MINUTES }

  • target_recovery_time
    Especifica o salto máximo no tempo para recuperar o banco de dados especificado no caso de uma falha.

  • SECONDS
    Indica que target_recovery_time é expresso como o número de segundos.

  • MINUTES
    Indica que target_recovery_time é expresso como o número de minutos.

Para obter mais informações sobre pontos de verificação indiretos, consulte Pontos de verificação de banco de dados (SQL Server).

<service_broker_option> ::=

Controla as seguintes opções do Service Broker: habilita ou desabilita a entrega de mensagens, define um novo identificador do Service Broker ou define as prioridades de conversa como ON ou OFF.

• ENABLE_BROKER
  Especifica que o Service Broker está habilitado para o banco de dados especificado. A entrega das mensagens é iniciada e o sinalizador is_broker_enabled é definido como true na exibição do catálogo sys.databases. O banco de dados retém o identificador do Service Broker existente. O Service Broker não pode ser habilitado quando o banco de dados é a entidade de segurança em uma configuração de espelhamento de banco de dados.

  Observação

  ENABLE_BROKER requer um bloqueio de banco de dados exclusivo. Se outras sessões bloquearam recursos no banco de dados, ENABLE_BROKER esperará até que as outras sessões liberem os bloqueios. Para habilitar o Service Broker em um banco de dados de usuário, verifique se nenhuma outra sessão está usando o banco de dados antes de executar a instrução ALTER DATABASE SET ENABLE_BROKER, por exemplo, colocando o banco de dados no modo de usuário único. Para habilitar o Service Broker no banco de dados msdb, primeiro interrompa o SQL Server Agent de forma que o Service Broker possa obter o bloqueio necessário.

• DISABLE_BROKER
  Especifica que o Service Broker está desabilitado para o banco de dados especificado. A entrega das mensagens é interrompida e o sinalizador is_broker_enabled é definido como false na exibição do catálogo sys.databases. O banco de dados retém o identificador do Service Broker existente.

• NEW_BROKER
  Especifica que o banco de dados deve receber um novo identificador do Broker. Como o banco de dados é considerado como um novo Service Broker, todas as conversas existentes nele são imediatamente removidas sem produzir mensagens de caixa de diálogo de término. Qualquer rota que referencia o antigo identificador do Service Broker deve ser recriada novamente com o novo identificador.

• ERROR_BROKER_CONVERSATIONS
  Especifica que a entrega de mensagem do Service Broker está habilitada. Isso preserva o identificador existente do Service Broker para o banco de dados. Service Broker termina todas as conversas no banco de dados com um erro. Isso permite que os aplicativos executem a limpeza regular das conversas existentes.

• HONOR_BROKER_PRIORITY {ON | OFF}

  • ON
    As operações de envio levam em conta os níveis de prioridade atribuídos às conversas. As mensagens de conversas com altos níveis de prioridade são enviadas antes das mensagens de conversas com baixos níveis de prioridade atribuídos.

  • OFF
    As operações de envio são executadas como se todas as conversas tivessem o nível de prioridade padrão.

  As alterações na opção HONOR_BROKER_PRIORITY entram em vigor imediatamente para os novos diálogos ou diálogos que não tenham mensagens esperando para serem enviadas. As caixas de diálogo com mensagens esperando para serem enviadas quando ALTER DATABASE for executado não assumirão a nova configuração até que algumas das mensagens da caixa de diálogo sejam enviadas. O tempo necessário para que todas as caixas de diálogo comecem a usar a nova configuração pode variar consideravelmente.

  A configuração atual dessa propriedade é relatada na coluna is_broker_priority_honored na exibição do catálogo sys.databases.

<snapshot_option> ::=

Determina o nível de isolamento da transação.

• ALLOW_SNAPSHOT_ISOLATION { ON | OFF }

  • ON
    Habilita a opção de Instantâneo no nível do banco de dados. Quando habilitada, as instruções DML começam a gerar versões de linha mesmo quando nenhuma transação usar Isolamento de Instantâneo. Quando essa opção está habilitada, as transações podem especificar o nível de isolamento da transação SNAPSHOT. Ao executar uma transação no nível de isolamento SNAPSHOT, todas as instruções consultam um instantâneo de dados, se houver um no início da instrução. Se uma transação que executa no nível de isolamento SNAPSHOT acessar dados em vários bancos de dados, ALLOW_SNAPSHOT_ISOLATION deverá ser definido como ON em todos os bancos de dados ou cada instrução na transação deverá usar dicas de bloqueio em qualquer referência em uma cláusula FROM para uma tabela em um banco de dados onde ALLOW_SNAPSHOT_ISOLATION seja OFF.

  • OFF
    Desativa a opção de Instantâneo no nível do banco de dados. As transações não podem especificar o nível de isolamento da transação SNAPSHOT.

  Ao definir ALLOW_SNAPSHOT_ISOLATION para um novo estado (de ON para OFF, ou de OFF, para ON), ALTER DATABASE não retorna o controle para o chamador até que todas as transações existentes no banco de dados sejam confirmadas. Se o banco de dados já estiver no estado especificado na instrução ALTER DATABASE, o controle será retornado ao chamador imediatamente. Se a instrução ALTER DATABASE não for retornada rapidamente, use sys.dm_tran_active_snapshot_database_transactions para determinar se há transações de longa duração. Se a instrução ALTER DATABASE for cancelada, o banco de dados permanecerá no estado que estava quando ALTER DATABASE foi iniciada. A exibição do catálogo sys.databases indica o estado de transações de isolamento de instantâneo no banco de dados. Se snapshot_isolation_state_desc = IN_TRANSITION_TO_ON, ALTER DATABASE ALLOW_SNAPSHOT_ISOLATION OFF fará uma pausa de seis segundos e tentará novamente executar a operação.

  Não é possível alterar o estado de ALLOW_SNAPSHOT_ISOLATION se o banco de dados for OFFLINE.

  Se você definir ALLOW_SNAPSHOT_ISOLATION em um banco de dados READ_ONLY, a configuração será retida se o banco de dados for definido mais tarde como READ_WRITE.

  É possível alterar as configurações ALLOW_SNAPSHOT_ISOLATION para os bancos de dados master, model, msdb e tempdb. Se você alterar a configuração para tempdb, ela será retida sempre que a instância do Mecanismo de Banco de Dados for interrompida e reiniciada. Se você alterar a configuração para modelo, ela se tornará o padrão para qualquer novo banco de dados que for criado, exceto o tempdb.

  Por padrão, a opção é ON para os bancos de dados master e msdb.

  A configuração atual dessa opção pode ser determinada por meio do exame da coluna snapshot_isolation_state na exibição do catálogo sys.databases.

• READ_COMMITTED_SNAPSHOT { ON | OFF }

  • ON
    Habilita a opção de Instantâneo de Leitura Confirmada no nível do banco de dados. Quando habilitada, as instruções DML começam a gerar versões de linha mesmo quando nenhuma transação usar Isolamento de Instantâneo. Quando essa opção está habilitada, as transações que especificam o nível de isolamento de leitura confirmada usam o controle de versão de linha, em vez de bloqueio. Quando uma transação é executada no nível de isolamento de leitura confirmada, todas as instruções consultam um instantâneo de dados, se houver um no início da instrução.

  • OFF
    Desabilita a opção de Instantâneo de Leitura Confirmada no nível do banco de dados. As transações que especificam o nível de isolamento READ COMMITTED usam bloqueio.

  Para definir READ_COMMITTED_SNAPSHOT como ON ou OFF, não deve haver nenhuma conexão ativa com o banco de dados exceto para a que está executando o comando ALTER DATABASE. Entretanto, o banco de dados não precisa estar no modo de usuário único. Não é possível alterar o estado dessa opção quando o banco de dados for OFFLINE.

  Se você definir READ_COMMITTED_SNAPSHOT em um banco de dados READ_ONLY, a configuração será retida quando o banco de dados for definido mais tarde como READ_WRITE.

  READ_COMMITTED_SNAPSHOT não pode ser ativado para os bancos de dados do sistema master, tempdb ou msdb. Se você alterar a configuração para model, ela se tornará o padrão para qualquer novo banco de dados criado, exceto para tempdb.

  A configuração atual dessa opção pode ser determinada por meio do exame da coluna is_read_committed_snapshot_on na exibição do catálogo sys.databases.

<sql_option> ::=

Controla as opções de conformidade ANSI no nível de banco de dados.

• ANSI_NULL_DEFAULT { ON | OFF }
  Determina o valor padrão, NULL ou NOT NULL, de uma coluna ou tipo de dados CLR definido pelo usuário para os quais a nulidade não é definida explicitamente nas instruções CREATE TABLE ou ALTER TABLE. As colunas definidas com restrições seguem as regras de restrição apesar dessa configuração.

  • ON
    O valor padrão é NULL.

  • OFF
    O valor padrão é NOT NULL.

  As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_NULL_DEFAULT. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_NULL_DEFAULT de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_NULL_DFLT_ON (Transact-SQL).

  Para compatibilidade ANSI, definir a opção de banco de dados ANSI_NULL_DEFAULT como ON altera o banco de dados padrão para NULL.

  O status dessa opção pode ser determinado por meio do exame da coluna is_ansi_null_default_on na exibição do catálogo sys.databases ou da propriedade IsAnsiNullDefault da função DATABASEPROPERTYEX.

• ANSI_NULLS { ON | OFF }

  • ON
    Todas as comparações com um valor nulo são avaliadas como UNKNOWN.

  • OFF
    As comparações de valores não UNICODE com um valor nulo são avaliadas como TRUE se ambos os valores forem NULL.

  Importante
  Em uma versão futura do SQL Server, ANSI_NULLS sempre estará ON e quaisquer aplicativos que definam explicitamente a opção como OFF produzirão um erro. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que o utilizam atualmente.

  As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_NULLS. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_NULLS de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_NULLS (Transact-SQL).

  SET ANSI_NULLS também deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

  O status dessa opção pode ser determinado por meio do exame da coluna is_ansi_nulls_on na exibição do catálogo sys.databases ou da propriedade IsAnsiNullsEnabled da função DATABASEPROPERTYEX.

• ANSI_PADDING { ON | OFF }

  • ON
    As cadeias de caracteres são preenchidas com o mesmo comprimento antes da conversão ou inserção em um tipo de dados varchar ou nvarchar.

    Espaços em branco à direita nos valores de caracteres inseridos nas colunas varchar ou nvarchar e os zeros à direita nos valores binários inseridos nas colunas varbinary não serão cortados. Os valores não são preenchidos com o tamanho da coluna.

  • OFF
    Espaços em branco à direita para varchar ou nvarchar e zeros para varbinary são cortados.

  Quando OFF é especificado, essa configuração afeta apenas a definição de novas colunas.

  Importante

  Em uma versão futura do SQL Server, ANSI_PADDING sempre será ON e quaisquer aplicativos que definam explicitamente a opção como OFF produzirão um erro. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que o utilizam atualmente. É recomendável sempre definir ANSI_PADDING como ON. ANSI_PADDING deve ser ON ao criar ou manipular índices em colunas computadas ou exibições indexadas.

  As colunas char(n) e binary(n) que permitem nulos são preenchidas até o comprimento da coluna quando ANSI_PADDING está definido como ON, mas brancos à direita e zeros são cortados quando ANSI_PADDING está definido como OFF. As colunas char(n) e binary(n) que não permitem nulos sempre são preenchidas até o comprimento da coluna.

  As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_PADDING. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_PADDING da instrução SET no nível da conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_PADDING (Transact-SQL).

  Importante

  O status dessa opção pode ser determinado por meio do exame da coluna is_ansi_padding_on na exibição do catálogo sys.databases ou da propriedade IsAnsiPaddingEnabled da função DATABASEPROPERTYEX.

• ANSI_WARNINGS { ON | OFF }

  • ON
    Erros ou avisos são emitidos quando condições como “divisão por zero” ou “valores nulos” aparecerem em funções de agregação.

  • OFF
    Nenhum aviso é emitido e os valores nulos são retornados quando condições como “dividir por zero” ocorrem.

  SET ANSI_WARNINGS também deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

  As configurações no nível de conexão que são definidas usando uma instrução SET substituem a configuração no nível de banco de dados padrão para ANSI_WARNINGS. Por padrão, clientes ODBC e OLE DB emitem uma configuração ANSI_WARNINGS de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET ANSI_WARNINGS (Transact-SQL).

  O status dessa opção pode ser determinado por meio do exame da coluna is_ansi_warnings_on na exibição do catálogo sys.databases ou da propriedade IsAnsiWarningsEnabled da função DATABASEPROPERTYEX.

• ARITHABORT { ON | OFF }

  • ON
    Uma consulta é encerrada quando ocorre um estouro ou erro de divisão por zero durante a execução da consulta.

  • OFF
    Uma mensagem de aviso é exibida quando um desses erros ocorre, mas a consulta, o lote ou a transação continuam a ser processados como se nenhum erro tivesse ocorrido.

  SET ARITHABORT também deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

  O status dessa opção pode ser determinado por meio do exame da coluna is_arithabort_on na exibição do catálogo sys.databases ou da propriedade IsArithmeticAbortEnabled da função DATABASEPROPERTYEX.

• COMPATIBILITY_LEVEL { 90 | 100 | 110 }
  Para obter mais informações, consulte Nível de compatibilidade de ALTER DATABASE (Transact-SQL).

• CONCAT_NULL_YIELDS_NULL { ON | OFF }

  • ON
    O resultado de uma operação de concatenação será NULL quando qualquer operando for NULL. Por exemplo, concatenar a cadeia de caracteres "This is" e NULL gera o valor NULL, em vez do valor "This is".

  • OFF
    O valor nulo é tratado como uma cadeia de caracteres vazia.

  CONCAT_NULL_YIELDS_NULL deve ser definido como ON ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

  Importante
  Em uma versão futura do SQL Server, CONCAT_NULL_YIELDS_NULL sempre estará ON e quaisquer aplicativos que definam explicitamente a opção como OFF produzirão um erro. Evite usar esse recurso em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que o utilizam atualmente.

  As configurações no nível de conexão que são definidas usando uma instrução SET substituem a configuração no nível de banco de dados padrão para CONCAT_NULL_YIELDS_NULL. Por padrão, clientes ODBC e OLE DB emitem uma configuração CONCAT_NULL_YIELDS_NULL de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET CONCAT_NULL_YIELDS_NULL (Transact-SQL).

  O status dessa opção pode ser determinado por meio do exame da coluna is_concat_null_yields_null_on na exibição do catálogo sys.databases ou da propriedade IsNullConcat da função DATABASEPROPERTYEX.

• QUOTED_IDENTIFIER { ON | OFF }

  • ON
    As aspas duplas podem ser utilizadas para delimitar identificadores.

    Todas as cadeias de caracteres delimitadas por aspas duplas são interpretadas como identificadores de objeto. Os identificadores entre aspas não precisam seguir as regras Transact-SQL para identificadores. Eles podem ser palavras-chave e incluir caracteres geralmente não permitidos nos identificadores Transact-SQL. Se o sinal de aspas simples (') fizer parte da cadeia de caracteres literal, ele poderá ser representado por aspas duplas (").

  • OFF
    Os identificadores não podem estar entre aspas e devem seguir todas as regras de identificadores Transact-SQL. Literais podem ser delimitados por aspas simples ou duplas.

  O SQL Server também permite que os identificadores sejam delimitados por colchetes ([ ]). Os identificadores entre colchetes podem sempre ser usados, seja qual for a configuração de QUOTED_IDENTIFIER. Para obter mais informações, consulte Identificadores de banco de dados.

  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.

  As configurações no nível de conexão que são definidas com o uso de uma instrução SET substituem a configuração no nível de banco de dados padrão para QUOTED_IDENTIFIER. Por padrão, clientes ODBC e OLE DB emitem uma configuração QUOTED_IDENTIFIER de instrução SET no nível de conexão como ON para a sessão ao se conectar a uma instância do SQL Server. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

  O status dessa opção pode ser determinado por meio do exame da coluna is_quoted_identifier_on na exibição do catálogo sys.databases ou da propriedade IsQuotedIdentifiersEnabled da função DATABASEPROPERTYEX.

• NUMERIC_ROUNDABORT { ON | OFF }

  • ON
    Um erro é gerado quando ocorre perda de precisão em uma expressão.

  • OFF
    As perdas de precisão não geram mensagens de erro e o resultado é arredondado de acordo com a precisão da coluna ou variável que armazena o resultado.

  NUMERIC_ROUNDABORT deve ser definido como OFF ao criar ou fazer alterações em índices em colunas computadas ou exibições indexadas.

  O status dessa opção pode ser determinado por meio do exame da coluna is_numeric_roundabort_on na exibição do catálogo sys.databases ou da propriedade IsNumericRoundAbortEnabled da função DATABASEPROPERTYEX.

• RECURSIVE_TRIGGERS { ON | OFF }

  • ON
    O disparo recursivo de gatilhos AFTER é permitido.

  • OFF
    Apenas o disparo recursivo direto de gatilhos AFTER não é permitido. Para desabilitar também a recursão indireta de gatilhos AFTER, defina a opção do servidor de gatilhos aninhados como 0 usando sp_configure.

  Observação
  Somente a recursão direta será evitada quando RECURSIVE_TRIGGERS estiver definido como OFF. Para desabilitar a recursão indireta, é necessário definir também a opção de servidor nested triggers como 0.

  O status dessa opção pode ser determinado por meio do exame da coluna is_recursive_triggers_on na exibição do catálogo sys.databases ou da propriedade IsRecursiveTriggersEnabled da função DATABASEPROPERTYEX.

WITH <termination> ::=

Especifica quando reverter transações incompletas quando há transição do banco de dados de um estado para outro. Se a cláusula de término for omitida, a instrução ALTER DATABASE aguardará indefinidamente se houver algum bloqueio no banco de dados. Somente uma cláusula de término pode ser especificada e ela sucede as cláusulas SET.

Observação
Nem todas as opções de banco de dados usam a cláusula WITH <termination>. Para obter mais informações, consulte a tabela em Opções de configuração na seção Comentários deste tópico.

• ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
  Especifica se a reversão deve ser feita após o número de segundos especificado ou imediatamente.

• NO_WAIT
  Especifica que, se a alteração de estado do banco de dados ou da opção solicitada não puder ser concluída imediatamente sem esperar que as transações sejam confirmadas ou revertidas por si mesmas, a solicitação falhará.

Comentários

Opções de configuração

Para recuperar as configurações atuais das opções de banco de dados, use a exibição do catálogo sys.databases ou DATABASEPROPERTYEX.

Depois de definir uma opção de banco de dados, a modificação entra em vigor imediatamente.

Para alterar os valores padrão de qualquer uma das opções para todos os bancos de dados recém-criados, altere a opção de banco de dados apropriada no banco de dados model.

Nem todas as opções de banco de dados usam a cláusula WITH <termination> ou podem ser especificadas em combinação com outras opções. A tabela a seguir lista essas opções e seu status de opção e término.

Categoria de opções	Pode ser especificado com outras opções	Pode usar a cláusula WITH <termination>
<db_state_option>	Sim	Sim
<db_user_access_option>	Sim	Sim
<db_update_option>	Sim	Sim
<external_access_option>	Sim	Não
<cursor_option>	Sim	Não
<auto_option>	Sim	Não
<sql_option>	Sim	Não
<recovery_option>	Sim	Não
<target_recovery_time_option>	Não	Sim
<database_mirroring_option>	Não	Não
ALLOW_SNAPSHOT_ISOLATION	Não	Não
READ_COMMITTED_SNAPSHOT	Não	Sim
<service_broker_option>	Sim	Não
DATE_CORRELATION_OPTIMIZATION	Sim	Sim
<parameterization_option>	Sim	Sim
<change_tracking_option>	Sim	Sim
<db_encryption>	Sim	Não

O plano de cache da instância do SQL Server é limpo com a definição de uma das seguintes opções:

OFFLINE	READ_WRITE
ONLINE	MODIFY FILEGROUP DEFAULT
MODIFY_NAME	MODIFY FILEGROUP READ_WRITE
COLLATE	MODIFY FILEGROUP READ_ONLY
READ_ONLY

O cache de procedimento também é liberado nos seguintes cenários.

• Um banco de dados tem a opção de banco de dados AUTO_CLOSE definida como ON. Quando nenhuma conexão de usuário fizer referência ou usar o banco de dados, a tarefa de banco de dados tentará fechar e encerrar o banco de dados automaticamente.

• Execute diversas consultas em um banco de dados que tem opções padrão. O banco de dados é removido.

• Um instantâneo de banco de dados para um banco de dados de origem é removido.

• Você recria com sucesso o log de transação para um banco de dados.

• Você restaura um backup de banco de dados.

• Você desanexa um banco de dados.

A limpeza do cache de planos gera uma recompilação de todos os planos de execução subsequentes e pode provocar uma redução repentina e temporária do desempenho de consultas. Para cada armazenamento em cache limpo no cache de planos, o log de erros do SQL Server contém a seguinte mensagem informativa: "O SQL Server encontrou %d ocorrência(s) de liberação de armazenamento em cache '% s' (parte do cache de planos) devido à manutenção do banco de dados ou operações de reconfiguração". Essa mensagem é registrada a cada cinco minutos, contanto que o cache seja liberado dentro desse intervalo de tempo.

Exemplos

A.Configurando opções em um banco de dados

O exemplo a seguir define o modelo de recuperação e as opções de verificação de página de dados para o banco de dados de exemplo AdventureWorks2012 .

USE master;
GO
ALTER DATABASE AdventureWorks2012 
SET RECOVERY FULL, PAGE_VERIFY CHECKSUM;
GO

B.Configurando o banco de dados como READ_ONLY

Alterar o estado de um banco de dados ou grupo de arquivos para READ_ONLY ou READ_WRITE requer acesso exclusivo ao banco de dados. O exemplo a seguir define o banco de dados como o modo SINGLE_USER para obter acesso exclusivo. Em seguida, o exemplo define o estado do banco de dados AdventureWorks2012 como READ_ONLY e retorna o acesso ao banco de dados para todos os usuários.

Observação
Este exemplo usa a opção de término WITH ROLLBACK IMMEDIATE na primeira instrução ALTER DATABASE. Todas as transações incompletas serão revertidas e qualquer outra conexão com o banco de dados AdventureWorks2012 será desconectada imediatamente.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

C.Habilitando o isolamento de instantâneo em um banco de dados

O exemplo a seguir habilita a opção de estrutura de isolamento de instantâneo para o banco de dados AdventureWorks2012 .

USE AdventureWorks2012;
GO
-- Check the state of the snapshot_isolation_framework
-- in the database.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO
USE master;
GO
ALTER DATABASE AdventureWorks2012
    SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Check again.
SELECT name, snapshot_isolation_state,
     snapshot_isolation_state_desc AS description
FROM sys.databases
WHERE name = N'AdventureWorks2012';
GO

O conjunto de resultados mostra que a estrutura de isolamento de instantâneo está habilitada.

name                 snapshot_isolation_state  description

-------------------- ------------------------  ----------

AdventureWorks2012 1                         ON

D.Habilitando, modificando e desabilitando o controle de alterações

O exemplo a seguir habilita o controle de alterações no banco de dados AdventureWorks2012 e define o período de retenção para 4 dias.

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON
(AUTO_CLEANUP = ON, CHANGE_RETENTION = 2 DAYS);

O exemplo a seguir mostra como alterar o período de retenção para 3 dias.

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING (CHANGE_RETENTION = 3 DAYS);

O exemplo a seguir mostra como desabilitar o controle de alterações no banco de dados AdventureWorks2012 .

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = OFF;

Consulte também

Referência

Nível de compatibilidade de ALTER DATABASE (Transact-SQL)

espelhamento de banco de dados ALTER DATABASE (Transact-SQL)

ALTER DATABASE SET HADR (Transact-SQL)

CREATE DATABASE (Transact-SQL)

DATABASEPROPERTYEX (Transact-SQL)

DROP DATABASE (Transact-SQL)

SET TRANSACTION ISOLATION LEVEL (Transact-SQL)

sp_configure (Transact-SQL)

sys.databases (Transact-SQL)

sys.data_spaces (Transact-SQL)

Conceitos

Estatísticas

Habilitar e desabilitar o controle de alterações (SQL Server)