Melhorias no SQL Server e no Banco de Dados SQL do Azure no tratamento de alguns tipos de dados e operações incomuns

Artigo
12/14/2024

Este artigo apresenta como as estruturas persistentes no banco de dados do SQL Server podem ser validadas como parte do nível de compatibilidade de atualização e como as estruturas afetadas podem ser recriadas após a atualização do nível de compatibilidade.

Versão original do produto: SQL Server 2017, SQL Server 2016
Número original do KB: 4010261

O mecanismo de banco de dados no Microsoft SQL Server 2016 e no Banco de Dados SQL do Azure inclui melhorias nas conversões de tipo de dados e várias outras operações. A maioria desses aprimoramentos oferece maior precisão quando você trabalha com tipos de ponto flutuante e também com tipos clássicos de data e hora.

Todos esses aprimoramentos estão disponíveis quando você usa um nível de compatibilidade de banco de dados de pelo menos 130. Isso significa que, para algumas expressões (principalmente incomuns), você pode ver resultados diferentes para alguns valores de entrada depois de atualizar o banco de dados para o nível de compatibilidade 130 ou uma configuração superior. Esses resultados podem ser refletidos em:

estruturas persistentes no banco de dados
incluídos que estão sujeitos a CHECK restrições
colunas computadas persistente
Índices que fazem referência a colunas computadas
índices filtrados e exibições indexadas.

Se você tiver um banco de dados criado em uma versão anterior do SQL Server, recomendamos que você faça uma validação adicional depois de atualizar para o SQL Server 2016 ou posterior e antes de alterar o nível de compatibilidade do banco de dados.

Se você achar que qualquer uma das estruturas persistentes em seu banco de dados é afetada por essas alterações, recomendamos que você recrie as estruturas afetadas depois de atualizar o nível de compatibilidade do banco de dados. Ao fazer isso, você se beneficiará dessas melhorias no SQL Server 2016 ou posterior.

Este artigo descreve como as estruturas persistentes em seu banco de dados podem ser validadas como parte da atualização para o nível de compatibilidade 130 ou uma configuração superior e como todas as estruturas afetadas podem ser recriadas depois que você altera o nível de compatibilidade.

Etapas de validação durante uma atualização para o nível de compatibilidade do banco de dados

A partir do SQL Server 2016, o SQL Server e o Banco de Dados SQL do Azure incluem melhorias na precisão das seguintes operações:

Conversões de tipo de dados incomuns. Isso inclui o seguinte:
- Float/inteiro de/para datetime/smalldatetime
- Real/float de/para numérico/dinheiro/smallmoney
- Flutuar para real
Alguns casos de DATEPART/DATEDIFF e DEGREES
CONVERT que usa um NULL estilo

Para usar essas melhorias na avaliação de expressão em seu aplicativo, altere o nível de compatibilidade de seus bancos de dados para 130 (para SQL Server 2016) ou 140 (para SQL Server 2017 e Banco de Dados SQL do Azure). Para obter mais informações sobre todas as alterações e alguns exemplos que mostram as alterações, consulte a seção Apêndice A .

As seguintes estruturas no banco de dados podem persistir os resultados de uma expressão:

Dados da tabela sujeitos a CHECK restrições
Colunas computadas persistentes
Índices que usam colunas computadas na chave ou nas colunas incluídas
Índices filtrados
Exibições indexadas

Considere o cenário a seguir.

Você tem um banco de dados que foi criado por uma versão anterior do SQL Server ou que já foi criado no SQL Server 2016 ou em uma versão posterior, mas em um nível de compatibilidade 120 ou anterior.
Você usa todas as expressões cuja precisão foi aprimorada como parte da definição de estruturas persistentes em seu banco de dados.

Nesse cenário, você pode ter estruturas persistentes que são afetadas pelas melhorias na precisão implementadas usando o nível de compatibilidade 130 ou superior. Se esse for o caso, recomendamos que você valide as estruturas persistentes e reconstrua qualquer estrutura afetada.

Se você tiver estruturas afetadas e não as recompilar depois de alterar o nível de compatibilidade, poderá experimentar resultados de consulta ligeiramente diferentes. Os resultados dependem se um determinado índice, coluna computada ou exibição é usado e se os dados em uma tabela podem ser considerados uma violação de uma restrição.

Observação

Sinalizador de rastreamento 139 no SQL Server

O sinalizador de rastreamento global 139 é introduzido no SQL Server 2016 CU3 e no Service Pack (SP) 1 para forçar a semântica de conversão correta no escopo de comandos de verificação DBCC, como DBCC CHECKDB, DBCC CHECKTABLEe DBCC CHECKCONSTRAINTS quando você analisa a precisão aprimorada e a lógica de conversão introduzida com o nível de compatibilidade 130 em um banco de dados que tem um nível de compatibilidade anterior.

Aviso

O sinalizador de rastreamento 139 não deve ser habilitado continuamente em um ambiente de produção e deve ser usado com a única finalidade de executar as verificações de validação de banco de dados descritas neste artigo. Portanto, ele deve ser desabilitado usando dbcc traceoff (139, -1) na mesma sessão, após a conclusão das verificações de validação.

Há suporte para o sinalizador de rastreamento 139 a partir do SQL Server 2016 CU3 e do SQL Server 2016 SP1.

Para atualizar o nível de compatibilidade, siga estas etapas:

Execute a validação para identificar quaisquer estruturas persistentes afetadas:
1. Habilite o sinalizador de rastreamento 139 executando DBCC TRACEON(139, -1).
2. Executar DBCC CHECKDB/TABLE e CHECKCONSTRAINTS comandos.
3. Desative o sinalizador de rastreamento 139 executando DBCC TRACEOFF(139, -1).
Altere o nível de compatibilidade do banco de dados para 130 (para SQL Server 2016) ou 140 (para SQL Server 2017 e Banco de Dados SQL do Azure).
Reconstrua todas as estruturas identificadas na etapa 1.

Observação

Sinalizadores de rastreamento no Banco de Dados SQL do Azure Não há suporte para a configuração de sinalizadores de rastreamento no Banco de Dados SQL do Azure. Portanto, você deve alterar o nível de compatibilidade antes de executar a validação:

Atualize o nível de compatibilidade do banco de dados para 140.
Valide para identificar quaisquer estruturas persistentes afetadas.
Reconstrua as estruturas identificadas na etapa 2.

O Apêndice A contém uma lista detalhada de todas as melhorias de precisão e fornece um exemplo para cada uma.
O Apêndice B contém um processo passo a passo detalhado para fazer a validação e reconstruir quaisquer estruturas afetadas.
O Apêndice C e o Apêndice D contêm scripts para ajudar a identificar objetos potencialmente afetados no banco de dados. Portanto, você pode definir o escopo de suas validações e gerar scripts correspondentes para executar as verificações. Para determinar mais facilmente se alguma estrutura persistente em seus bancos de dados é afetada pelas melhorias de precisão no nível de compatibilidade 130, execute o script no Apêndice D para gerar as verificações de validação corretas e, em seguida, execute esse script para fazer a validação.

Apêndice A: Alterações no nível de compatibilidade 130

Este apêndice fornece listas detalhadas dos aprimoramentos na avaliação de expressão no nível de compatibilidade 130. Cada alteração inclui uma consulta de exemplo associada. As consultas podem ser usadas para mostrar as diferenças entre a execução em um banco de dados que usa um nível de compatibilidade anterior a 130 em comparação com um banco de dados que usa o nível de compatibilidade 130.

As tabelas a seguir listam conversões de tipo de dados e operações adicionais.

Conversões de tipo de dados

De	Para	Alterar	Exemplo de consulta	Resultado para o nível < de compatibilidade 130	Resultado para o nível de compatibilidade = 130
`float`, `real`, `numeric`, `decimal`, `money` ou `smallmoney`	`datetime` ou `smalldatetime`	Aumente a precisão do arredondamento. Anteriormente, o dia e a hora eram convertidos separadamente e os resultados eram truncados antes de serem combinados.	`DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT)`	1.19999996141975	1,2
`datetime`	`bigint, int, or smallint`	Uma data e hora negativa cuja parte da hora é exatamente meio dia ou em um tique de meio dia é arredondada incorretamente (o resultado está errado em 1).	`DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT)`	0	-1
`datetime` ou `smalldatetime`	`float, real, numeric, money, or smallmoney`	Precisão aprimorada para os últimos 8 bits de precisão em alguns casos.	`DECLARE @p0 DATETIME = '1899-12-31 23:58:00.470' DECLARE @f FLOAT = CONVERT(FLOAT, @p0) SELECT @f, CAST(@f AS VARBINARY(8))`	-0.00138344907407406, 0xBF56AA9B21D85800	-0,00138344907407407, 0xBF56AA9B21D8583B
`float`	`real`	As verificações de limites são menos rígidas.	`SELECT CAST (3.40282347000E+038 AS REAL)`	Estouro aritmético	3.402823E+38
`numeric`, `money` e `smallmoney`	`float`	Quando a escala de entrada é zero, há uma imprecisão de arredondamento quando você combina as quatro partes do numérico.	`DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f)`	0x4720000000000000	0x4720000000000001
`numeric`, `money` e `smallmoney`	`float`	Quando a escala de entrada é diferente de zero, há uma imprecisão de arredondamento quando você divide por 10^escala.	`DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8))`	0x41678C29C06522C4	0x41678C29C06522C3
`real` ou `float`	numeric	Precisão de arredondamento aprimorada em alguns casos.	`DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1))`	0,2	0,1
`real` ou `float`	numeric	Precisão aprimorada ao arredondar para mais de 16 dígitos em alguns casos.	`DECLARE @v decimal(38, 18) = 1E-18 SELECT @v`	0.000000000000000000	0.000000000000000001
`real` ou `float`	`money` ou `smallmoney`	Precisão aprimorada ao converter números grandes em alguns casos.	`DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money)`	562949953421312.2048	562949953421312.25
`(n)(var)char`	`numeric`	Uma entrada de mais de 39 caracteres não aciona mais necessariamente um estouro aritmético.	`DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1))`	Estouro aritmético	1,1
`(n)(var)char`	`bit`	Suporta espaços e sinais à esquerda.	`DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit)`	Falha na conversão ao converter o `nvarchar` valor '1' em bit de tipo de dados.	1
`datetime`	`time` ou `datetime2`	Precisão aprimorada ao converter para tipos de data/hora com maior precisão. Lembre-se de que os valores de data e hora são armazenados como tiques que representam 1/300 de segundo. Os tipos time e datetime2 mais recentes armazenam um número discreto de dígitos, em que o número de dígitos corresponde à precisão.	`DECLARE @value datetime = '1900-01-01 00:00:00.003' SELECT CAST(@value AS time(7))`	00:00:00.0030000	00:00:00.0033333
`time` ou `datetime2`	`datetime`	Arredondamento aprimorado em alguns casos.	`DECLARE @value time(4) = '00:00:00.0045' SELECT CAST(@value AS datetime)`	1900-01-01 00:00:00.007	1900-01-01 00:00:00.003

Operação

Operação	Alterar	Exemplo de consulta	Resultado para o nível <de compatibilidade 130	Resultado para o nível de compatibilidade 130
Use a `RADIANS` função interna ou `DEGREES` que usa o tipo de dados numérico.	`DEGREES` divide por pi/180, onde anteriormente multiplicava por 180/pi. Semelhante para `RADIANS`.	`DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1)`	57.295779513082323000	57.295779513082322865
Adição ou subtração numérica quando a escala de um operando é maior que a escala do resultado.	O arredondamento sempre ocorre após a adição ou subtração, enquanto anteriormente às vezes podia ocorrer antes.	`DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2`	8.8	8,9
`CONVERT` com `NULL` estilo.	`CONVERT` com `NULL` estilo sempre retorna `NULL` quando o tipo de destino é numérico.	`SELECT CONVERT (SMALLINT, '0', NULL);`	0	`NULL`
`DATEPART` que usa a opção microssegundos ou nanossegundos, com o tipo de dados datetime.	O valor não é mais truncado no nível de milissegundos antes de ser convertido em micro ou nanossegundos.	`DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt);`	3000	3333
`DATEDIFF` que usa a opção microssegundos ou nanossegundos, com o tipo de dados datetime.	O valor não é mais truncado no nível de milissegundos antes de ser convertido em micro ou nanossegundos.	`DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME = '1900-01-01 00:00:00.007' SELECT DATEDIFF(MICROSECOND, @d1, @d2)`	3000	3333
Comparação entre valores datetime e datetime2 com valores diferentes de zero para milissegundos.	O valor datetime não é mais truncado no nível de milissegundos quando você executa uma comparação com um valor datetime2. Isso significa que certos valores que antes eram iguais não são mais iguais.	`DECLARE @d1 DATETIME = '1900-01-01 00:00:00.003' DECLARE @d2 DATETIME2(3) = @d1 SELECT CAST(@d1 AS datetime2(7)), @d2SELECT CASE WHEN (@d1=@d2) THEN 'equal' ELSE 'unequal' END`	1900-01-01 00:00:00.0030000, 1900-01-01 00:00:00.003 igual	1900-01-01 00:00:00.0033333, 1900-01-01 00:00:00.003 desigual
`ROUND` que usa o `float` tipo de dados.	Os resultados do arredondamento diferem.	`SELECT ROUND(CAST (-0.4175 AS FLOAT), 3)`	-0.418	-0.417

Apêndice B: Etapas para verificar e atualizar estruturas persistentes

Recomendamos que você determine se o banco de dados tem estruturas persistentes afetadas pelas alterações no nível de compatibilidade 130 e que recrie todas as estruturas afetadas.

Isso se aplica somente a estruturas persistentes que foram criadas no banco de dados em uma versão mais antiga do SQL Server ou usando um nível de compatibilidade inferior a 130. As estruturas persistentes que são potencialmente afetadas incluem o seguinte:

Dados da tabela sujeitos a CHECK restrições
Colunas computadas persistentes
Índices que usam colunas computadas na chave ou nas colunas incluídas
Índices filtrados
Exibições indexadas

Nessa situação, execute o procedimento a seguir.

Etapa 1: Verificar o nível de compatibilidade do banco de dados

Verifique o nível de compatibilidade do banco de dados usando o procedimento documentado em Exibir ou alterar o nível de compatibilidade de um banco de dados.
Se o nível de compatibilidade do banco de dados for inferior a 130, recomendamos que você execute a validação descrita na Etapa 2 antes de aumentar o nível de compatibilidade para 130.

Etapa 2: Identificar estruturas persistentes afetadas

Determine se o banco de dados contém estruturas persistentes afetadas pela precisão aprimorada e pela lógica de conversão no nível de compatibilidade 130 de uma das seguintes maneiras:

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, que valida todas as estruturas do banco de dados.
DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, que valida as estruturas relacionadas a uma única tabela.

A opção WITH EXTENDED_LOGICAL_CHECKS é necessária para garantir que os valores persistentes sejam comparados com os valores calculados e para sinalizar casos em que há uma diferença. Como essas verificações são extensas, o tempo de execução das instruções que usam essa opção é maior do que a execução DBCC de DBCC instruções sem a opção. Portanto, a recomendação para bancos de dados grandes é usar DBCC CHECKTABLE para identificar tabelas individuais.

DBCC CHECKCONSTRAINTS pode ser usado para validar CHECK restrições. Essa instrução pode ser usada no banco de dados ou no nível da tabela.

DBCC CHECK As instruções sempre devem ser executadas durante uma janela de manutenção, devido ao impacto potencial das verificações na carga de trabalho online.

Validação no nível do banco de dados

A validação no nível do banco de dados é adequada para bancos de dados pequenos e de tamanho moderado. Use a validação em nível de tabela para bancos de dados grandes.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS é usado para validar todas as estruturas persistentes no banco de dados.

DBCC CHECKCONSTRAINTS é usado para validar todas as CHECK restrições no banco de dados.

DBCC CHECKCONSTRAINTS é usado para validar a integridade das restrições. Use o seguinte script para validar o banco de dados:

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

O uso do sinalizador de rastreamento garante que as verificações sejam executadas usando a precisão aprimorada e a lógica de conversão que está no nível de compatibilidade 130, forçando a semântica de conversão correta mesmo quando o banco de dados tem um nível de compatibilidade mais baixo.

Se a CHECKCONSTRAINTS instrução for concluída e não retornar um conjunto de resultados, nenhuma ação adicional será necessária.

Se a instrução retornar um conjunto de resultados, cada linha nos resultados indicará uma violação de uma restrição e também incluirá os valores que violam a restrição.

Salve os nomes das tabelas e restrições, juntamente com os valores que causaram a violação (a WHERE coluna no conjunto de resultados).

O exemplo a seguir mostra uma tabela com uma CHECK restrição e uma única linha que atende à restrição em níveis de compatibilidade mais baixos, mas que viola a restrição em

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
    c2 datetime,
    c3 datetime,
    c4 int,
    CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2,c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(
    convert(datetime, '1900-01-01 00:00:00.997'),
    convert(datetime, '1900-01-01 00:00:01'), 3
)
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

O CHECKCONSTRAINT comando retorna os seguintes resultados.

Tabela	Constraint	Onde
[DBO]. [Tabela 1]	[chk1]	[c2] = '1900-01-01 00:00:00.997' E [c3] = '1900-01-01 00:00:01.000' E [c4] = '3'

Esse resultado indica que a restrição [chk1] é violada para a combinação de valores de coluna no 'Where'.

DBCC CHECKDB COM EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS valida todas as estruturas persistentes no banco de dados. Essa é a opção mais conveniente porque uma única instrução valida todas as estruturas no banco de dados. No entanto, essa opção não é adequada para bancos de dados grandes devido ao tempo de execução esperado da instrução.

Use o seguinte script para validar todo o banco de dados:

USE [database_name]
GO
DBCC TRACEON(139, -1)
GO
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS
GO
DBCC TRACEOFF(139, -1)
GO

Se a CHECKDB instrução for concluída com êxito, nenhuma ação adicional será necessária.

Se a instrução for concluída com erros, siga estas etapas:

Salve os resultados da execução da DBCC instrução, encontrados no painel de mensagens do SSMS (SQL Server Management Studio), em um arquivo.
Verifique se algum dos erros relatados está relacionado a estruturas persistentes

Tabela 1: Estruturas persistentes e mensagens de erro correspondentes para inconsistências

Tipo de estrutura afetado	Mensagens de erro observadas	Tome nota de
Colunas computadas persistentes	Msg 2537, Nível 16 Erro de tabela: ID <do objeto object_id> , ID <do índice index_id> , . A verificação de registro (coluna computada válida) falhou. Os valores são .	ID <do objeto object_id> e ID <do índice index_id>
Índices que fazem referência a colunas computadas na chave ou em colunas incluídas Índices filtrados	Msg 8951 Erro de tabela: tabela '<table_name>' (ID <object_id>). A linha de dados não tem uma linha de índice correspondente no índice '<index_name>' (ID <index_id>) E/ou Msg 8952 Erro de tabela: tabela '<table_name>' (ID <table_name>). A linha de índice no índice '' (ID <index_id>) não corresponde a nenhuma linha de dados. Além disso, pode haver erros secundários 8955 e/ou 8956. Isso contém detalhes sobre as linhas exatas afetadas. Estes podem ser desconsiderados para este exercício.	ID <do objeto object_id> e ID <do índice index_id>
Exibições indexadas	Msg 8908 A exibição indexada '<view_name>' (ID <de objeto object_id>) não contém todas as linhas que a definição de exibição produz. E/ou Msg 8907 A exibição indexada '<view_name>' (ID <de objeto object_id>) contém linhas que não foram produzidas pela definição de exibição.	ID <do objeto object_id>

Depois de concluir a validação no nível do banco de dados, vá para a Etapa 3.

Validação no nível do objeto

Para bancos de dados maiores, é útil validar estruturas e restrições em uma tabela ou uma exibição por vez para reduzir o tamanho das janelas de manutenção ou limitar as verificações lógicas estendidas apenas a objetos potencialmente afetados.

Use as consultas na seção Apêndice C para identificar tabelas potencialmente afetadas. O script na seção Apêndice D pode ser usado para gerar CHECKTABLE restrições e CHECKCONSTRAINTS com base nas consultas listadas na seção Apêndice C .

DBCC CHECKCONSTRAINTS

Para validar as restrições relacionadas a uma única tabela ou exibição, use o seguinte script:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

O uso do sinalizador de rastreamento garante que as verificações sejam executadas usando a precisão aprimorada e a lógica de conversão que está no nível de compatibilidade 130, forçando a semântica aprimorada mesmo quando o banco de dados tem um nível de compatibilidade mais baixo.

Se a CHECKCONSTRAINTS instrução for concluída e não retornar um conjunto de resultados, nenhuma ação adicional será necessária.

Se a instrução retornar um conjunto de resultados, cada linha nos resultados indicará uma violação de uma restrição e também fornecerá os valores que violam a restrição.

Salve os nomes das tabelas e restrições, juntamente com os valores que causaram a violação (a WHERE coluna no conjunto de resultados).

TABELA DE VERIFICAÇÃO DBCC COM EXTENDED_LOGICAL_CHECKS

Para validar as estruturas persistentes relacionadas a uma única tabela ou exibição, use o seguinte script:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

Se a CHECKTABLE instrução for concluída com êxito, nenhuma ação adicional será necessária.

Se a instrução for concluída com erros, siga estas etapas:

Salve os resultados da execução da instrução, encontrados no painel de DBCC mensagens do SSMS, em um arquivo.
Verifique se algum dos erros relatados está relacionado a estruturas persistentes, conforme listado na Tabela 1.
Depois de concluir a validação no nível da tabela, vá para a Etapa 3.

Etapa 3: atualizar para o nível de compatibilidade 130

Se o nível de compatibilidade do banco de dados já for 130, você poderá ignorar esta etapa.

O nível de compatibilidade do banco de dados pode ser alterado para 130 usando o seguinte script:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Observação

Como há alterações no otimizador de consulta no nível de compatibilidade 130, recomendamos que você habilite o repositório de consultas antes de alterar o nível de compatibilidade. Para obter mais informações, consulte a seção Manter a estabilidade de desempenho durante a atualização para o SQL Server mais recente em Cenários de uso do Repositório de Consultas.

Etapa 4: Atualizar estruturas persistentes

Se nenhuma inconsistência foi encontrada durante a validação executada na Etapa 2, você concluiu a atualização e pode ignorar esta etapa. Se forem encontradas inconsistências na Etapa 2, ações adicionais serão necessárias para remover as inconsistências do banco de dados. As ações necessárias dependem do tipo de estrutura afetada.

Importante

Execute as ações de reparo nesta etapa somente depois que o nível de compatibilidade do banco de dados for alterado para 130.

Fazer backup do banco de dados (ou bancos de dados)

Recomendamos que você faça um backup completo do banco de dados antes de executar qualquer uma das ações descritas na seção a seguir. Se você usar o Banco de Dados SQL do Azure, não precisará fazer um backup por conta própria; Você sempre pode usar a funcionalidade de restauração pontual para voltar no tempo caso algo dê errado com qualquer uma das atualizações.

Restrições CHECK

A correção de CHECK violações de restrição requer a modificação dos dados na tabela ou da CHECK própria restrição.

A partir do nome da restrição (obtido na Etapa 2), você pode obter a definição de restrição da seguinte maneira:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Para inspecionar as linhas da tabela afetadas, você pode usar as informações Where que foram retornadas DBCC CHECKCONSTRAINTS anteriormente pela instrução:

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

Você precisa atualizar as linhas afetadas ou alterar a definição de restrição para garantir que a restrição não seja violada.

Atualizando dados da tabela

Não existe uma regra rígida que indique como os dados devem ser atualizados. Geralmente, para cada instrução Where diferente retornada por DBCC CHECKCONSTRAINTS, você executará a seguinte instrução update:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Considere a tabela de exemplo a seguir com uma restrição e uma linha que viola a restrição no nível de compatibilidade 130:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table1
(
c2 datetime,
c3 datetime,
c4 int,
CONSTRAINT chk1 CHECK (c4= (DATEDIFF (ms, c2, c3)))
)
GO
INSERT dbo.table1 (c2, c3, c4) VALUES
(convert(datetime, '1900-01-01 00:00:00.997'),
convert(datetime, '1900-01-01 00:00:01'), 3)
GO

Neste exemplo, a restrição é direta. Column c4 deve ser igual a uma expressão envolvendo c2 e c3. Para atualizar a tabela, atribua este valor a c4:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130
GO
UPDATE dbo.table1 SET c4 = datediff (ms, c2,c3)
WHERE [c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'
GO

Observe que a WHERE cláusula usada na instrução de atualização corresponde às informações Where retornadas por DBCC CHECKCONSTRAINTS.

Atualizando a restrição CHECK

Para alterar uma CHECK restrição, você precisa descartá-la e recriá-la. Recomendamos fazer as duas coisas na mesma transação, caso haja algum problema com a definição de restrição atualizada. Você pode usar o seguinte Transact-SQL:

BEGIN TRANSACTION

ALTER TABLE [schema_name].[table_name]

DROP CONSTRAINT [constraint_name]

ALTER TABLE [schema_name].[table_name]

ADD CONSTRAINT [constraint_name]

CHECK (new_constraint_definition)

COMMIT

GO

The following example updates the constraint chk1 in dbo.table1:

BEGIN TRANSACTION

ALTER TABLE dbo.table1

DROP CONSTRAINT chk1

ALTER TABLE dbo.table1

ADD CONSTRAINT chk1

CHECK (c4 <= DATEDIFF (ms, c2, c3))

COMMIT

GO

Colunas computadas persistentes

A maneira mais fácil de atualizar colunas computadas persistentes é atualizar uma das colunas referenciadas pela coluna computada. O novo valor da coluna pode ser o mesmo que o valor antigo, de modo que a operação não altere nenhum dado do usuário.

Siga estas etapas para todas as object_id inconsistências relacionadas a colunas computadas que você anotou na Etapa 2.

Identifique colunas computadas:

Execute a seguinte consulta para recuperar o nome da tabela e os nomes das colunas computadas persistentes para o observado object_id:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) AS 'table',
QUOTENAME(c1.name) AS 'persisted computed column',
c1.column_id AS 'computed_column_id' ,
definition AS 'computed_column_definition'
FROM sys.tables t
JOIN sys.computed_columns c1 ON t.object_id=c1.object_id
AND c1.is_persisted=1
JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.object_id=object_id

Identifique as colunas referenciadas:

Execute a consulta a seguir para identificar colunas referenciadas pela coluna computada. Anote um dos nomes de coluna referenciados:

SELECT QUOTENAME(s.name) + N'.' + QUOTENAME(o.name) AS 'referencing object',
o.type_desc AS 'object type', referenced_minor_id AS 'referenced_column_id', c.name AS 'referenced_column_name'
FROM sys.sql_expression_dependencies sed
JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id
JOIN sys.objects o ON sed.referencing_id=o.object_id
JOIN sys.schemas s ON o.schema_id=s.schema_id
JOIN sys.columns c ON o.object_id=c.object_id AND sed.referenced_minor_id=c.column_id
WHERE referencing_class=1 AND referenced_class=1 AND referencing_id=object_id AND referencing_minor_id=computed_column_id

Execute uma UPDATE instrução envolvendo uma das colunas referenciadas para disparar uma atualização da coluna computada:
- A instrução a seguir disparará uma atualização da coluna referenciada pela coluna computada e também disparará uma atualização da coluna computada.
```
UPDATE [schema_name].[table_name]
SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)
```
- A ISNULL expressão na instrução é criada de forma que o valor da coluna original não seja alterado, ao mesmo tempo em que garante que a coluna computada seja atualizada usando a lógica de avaliação de expressão de nível de compatibilidade 130 do banco de dados.
- Lembre-se de que, para tabelas muito grandes, talvez você não queira atualizar todas as linhas em uma única transação. Nesse caso, você pode executar a atualização em lotes adicionando uma WHERE cláusula à instrução update que identifica um intervalo de linhas; com base na chave primária, por exemplo.

Identifique os índices que fazem referência à coluna computada.

SELECT i.name AS [index name]
FROM sys.index_columns ic JOIN sys.indexes i ON ic.object_id=i.object_id AND ic.index_id=i.index_id
WHERE i.object_id=object_id AND ic.column_id=computed_column_id

Essa consulta identifica todos os índices que fazem referência à coluna computada persistente. Qualquer índice desse tipo deve ser reconstruído. Para fazer isso, siga as etapas na seção a seguir.

Índices, índices filtrados e exibições indexadas

As inconsistências nos índices correspondem aos erros 8951 e 8952 (para tabelas) ou 8907 e 8908 (para exibições) na DBCC CHECK saída da Etapa 2.

Para corrigir essas inconsistências, execute DBCC CHECKTABLE com REPAIR_REBUILD. Isso reparará as estruturas de índices sem perda de dados. No entanto, o banco de dados deve estar no modo de usuário único e, portanto, não está disponível para outros usuários enquanto o reparo está ocorrendo.

Você também pode recriar manualmente os índices afetados. Essa opção deverá ser usada se a carga de trabalho não puder ser colocada offline, pois a recompilação do índice pode ser executada como uma operação ONLINE (em edições com suporte do SQL Server).

Recriar índices

Se definir o banco de dados no modo de usuário único não for uma opção, você poderá recompilar índices individualmente usando ALTER INDEX REBUILD, para cada índice identificado na Etapa 2.

Use a consulta a seguir para obter os nomes de tabela e índice para um determinado object_id e index_id.

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'table', i.name AS 'index_name'

FROM sys.objects o JOIN sys.indexes i ON o.object_id=i.object_id

WHERE o.object_id = object_id AND i.index_id = index_id

Use a seguinte instrução para recompilar o índice:

ALTER INDEX index_name ON [schema_name].[table_name] REBUILD WITH (ONLINE=ON)

Observação

Se você estiver usando as edições Standard, Web ou Express, não há suporte para a compilação de índice online. Portanto, a opção WITH (ONLINE=ON) deve ser removida da ALTER INDEX instrução.

O exemplo a seguir mostra a recompilação de um índice filtrado:

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=120
GO
CREATE TABLE dbo.table2
(
    c2 datetime,
    c3 float
)
GO
INSERT dbo.table2 (c2,c3) VALUES ('1899-12-31 23:58:00.470', -0.00138344907407406)
GO
CREATE INDEX ix_1 ON dbo.table2(c2)
WHERE (c2=-0.00138344907407406)
GO
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130GOALTER INDEX ix_1 ON [dbo].[table2] REBUILD WITH (ONLINE=ON)
GO

Se você tiver planos de manutenção regulares, recomendamos que inclua essa recompilação de índice como parte de sua manutenção agendada.

Reparar usando DBCC

Para cada (object_id) relacionado a um índice com inconsistências que você anotou na Etapa 2, execute o script a seguir para executar o reparo. Esse script define o banco de dados no modo de usuário único para a operação de reparo. Na pior das hipóteses, o reparo executa uma recompilação completa do índice.

USE [database_name]

GO

ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKTABLE (object_id, REPAIR_REBUILD) WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

ALTER DATABASE CURRENT SET MULTI_USER

GO

Apêndice C: Consultas para identificar tabelas candidatas

Os scripts a seguir identificam tabelas candidatas que você pode querer validar usando DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, com base na existência de estruturas e restrições persistentes que usam tipos de dados afetados pelas melhorias no nível de compatibilidade 130.

O conjunto de consultas a seguir lista detalhes sobre as tabelas e estruturas potencialmente afetadas que exigem validação adicional.

Exibições indexadas

A consulta a seguir retorna todas as exibições indexadas que fazem referência a colunas usando tipos de dados afetados ou usando qualquer uma das funções internas afetadas:

SELECT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'view', QUOTENAME(i.name) AS 'index',QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'referenced table', QUOTENAME(c.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the view definition contains a float or datetime value, or a conversion to such value

s.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.objects o ON sed.referencing_id = o.object_id AND o.type=N'V'

JOIN sys.indexes i ON o.object_id=i.object_id

JOIN sys.sql_modules s ON s.object_id=o.object_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_idJOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (c.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR s.[definition] LIKE '%DATEDIFF%'

OR s.[definition] LIKE '%CONVERT%'

OR s.[definition] LIKE '%CAST%'

OR s.[definition] LIKE '%DATEPART%'

OR s.[definition] LIKE '%DEGREES%')

Colunas computadas persistentes

A consulta a seguir retorna todas as tabelas com colunas computadas que fazem referência a outras colunas usando tipos de dados afetados ou usando qualquer uma das funções internas afetadas, em que a coluna é persistente ou referenciada de um índice.

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with computed column',

QUOTENAME(c1.name) AS 'computed column', c1.is_persisted,QUOTENAME(c2.name) AS 'referenced column', t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is if the column definition contains a float or datetime value, or a conversion to such value

c1.definition

FROM sys.sql_expression_dependencies sed

JOIN sys.computed_columns c1 ON sed.referencing_id=c1.object_id AND sed.referencing_minor_id=c1.column_id

JOIN sys.columns c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id=c2.column_id

JOIN sys.types t ON c2.system_type_id=t.system_type_idWHERE referencing_class=1 AND referenced_class=1

AND (c2.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

) OR c1.[definition] LIKE '%DATEDIFF%'

OR c1.[definition] LIKE '%CONVERT%'

OR c1.[definition] LIKE '%DATEPART%'

OR c1.[definition] LIKE '%DEGREES%')

AND (

-- the column is persisted

c1.is_persisted=1

-- OR the column is included in an index

OR EXISTS (SELECT 1 FROM sys.index_columns ic WHERE ic.object_id=c1.object_id AND ic.column_id=c1.column_id)

)

Índices filtrados

A consulta a seguir retorna todas as tabelas com índices filtrados que fazem referência a colunas na condição de filtro que afetaram os tipos de dados:

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with filtered index',

QUOTENAME(i.name) AS 'referencing index',

QUOTENAME(c.name) AS 'referenced column',

t.name AS 'data type',

-- if the data type is numeric, integer, or money, the only cases that warrent additional checks

-- with DBCC is where the filter condition contains a float or datetime value

i.filter_definition AS 'filter condition'

FROM sys.sql_expression_dependencies sed

JOIN sys.indexes i ON sed.referencing_id=i.object_id AND sed.referencing_minor_id=i.index_id

JOIN sys.columns c ON sed.referenced_id=c.object_id AND sed.referenced_minor_id=c.column_id

JOIN sys.types t ON c.system_type_id=t.system_type_id

WHERE referencing_class=7 AND referenced_class=1 AND i.has_filter=1

AND c.system_type_id IN ( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint

)

Verificar restrições

A consulta a seguir lista todas as tabelas com restrições de verificação que fazem referência a tipos de dados afetados ou funções internas:

SELECT QUOTENAME(sed.referenced_schema_name) + N'.' +

QUOTENAME(sed.referenced_entity_name) AS 'candidate table with check constraint',

QUOTENAME(c.name) AS 'constraint_name', c.definition AS 'constraint_definition',

QUOTENAME(col.name) AS 'referenced column', t.name AS 'data type'

FROM sys.sql_expression_dependencies sed

JOIN sys.check_constraints c ON sed.referencing_id=c.object_id AND sed.referencing_class=1

JOIN sys.columns col ON sed.referenced_id=col.object_id AND sed.referenced_minor_id=col.column_id

JOIN sys.types t ON col.system_type_id=t.system_type_id

WHERE referencing_class=1 AND referenced_class=1 AND (col.system_type_id IN

( 59 --real

, 62 --float

, 58 --smalldatetime

, 61 --datetime

, 60 --money

, 122 --smallmoney

, 106 --decimal

, 108 --numeric

, 56 --int

, 48 --tinyint

, 52 -- smallint

, 41 --time

, 127 --bigint)

OR c.[definition] LIKE '%DATEDIFF%'

OR c.[definition] LIKE '%CONVERT%'

OR c.[definition] LIKE '%DATEPART%'

OR c.[definition] LIKE '%DEGREES%')

Apêndice D: Script para criar instruções CHECK*

O script a seguir combina as consultas do apêndice anterior e simplifica os resultados apresentando uma lista de tabelas e exibições na forma de CHECKCONSTRAINTS instruções e CHECKTABLE .

DECLARE @CRLF nvarchar(10) = CHAR(13) + CHAR(10);
DECLARE @sql nvarchar(max) = N'DBCC TRACEON(139,-1); ' + @CRLF ;

SELECT @sql += N'DBCC CHECKTABLE (N''' + object_for_checktable + N''') WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS; ' + @CRLF
FROM
(

--indexed views
SELECT DISTINCT QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
 INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id AND o.type = N'V'
 INNER JOIN sys.indexes AS i ON o.object_id = i.object_id
 INNER JOIN sys.sql_modules AS s ON s.object_id = o.object_id
 INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id
 INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id

WHERE referencing_class = 1 AND referenced_class=1 
 AND (c.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR s.[definition] LIKE N'%DATEDIFF%'
 OR s.[definition] LIKE N'%CONVERT%'
 OR s.[definition] LIKE N'%CAST%'
 OR s.[definition] LIKE N'%DATEPART%'
 OR s.[definition] LIKE N'%DEGREES%')

UNION

--persisted computed columns
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed
INNER JOIN sys.computed_columns AS c1 ON sed.referencing_id = c1.object_id AND sed.referencing_minor_id = c1.column_id
INNER JOIN sys.columns AS c2 ON sed.referenced_id=c2.object_id AND sed.referenced_minor_id = c2.column_id
INNER JOIN sys.types AS t ON c2.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 
 AND (c2.system_type_id IN
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c1.[definition] LIKE N'%DATEDIFF%'
 OR c1.[definition] LIKE N'%CONVERT%'
 OR c1.[definition] LIKE N'%DATEPART%'
 OR c1.[definition] LIKE N'%DEGREES%')
AND (
-- the column is persisted
c1.is_persisted = 1 
-- OR the column is included in an index
OR EXISTS (SELECT 1 FROM sys.index_columns AS ic 
WHERE ic.object_id = c1.object_id AND ic.column_id=c1.column_id)
)

UNION

--indexed views
SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checktable'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.indexes AS i ON sed.referencing_id = i.object_id AND sed.referencing_minor_id = i.index_id
INNER JOIN sys.columns AS c ON sed.referenced_id = c.object_id AND sed.referenced_minor_id = c.column_id 
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE referencing_class = 7 AND referenced_class = 1 AND i.has_filter = 1
AND c.system_type_id IN ( 
 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
)) AS a

SELECT @sql += N'DBCC CHECKCONSTRAINTS (N''' + object_for_checkconstraints + N'''); ' + @CRLF
FROM
(

SELECT DISTINCT QUOTENAME(sed.referenced_schema_name) + N'.' + QUOTENAME(sed.referenced_entity_name) AS 'object_for_checkconstraints'
FROM sys.sql_expression_dependencies AS sed 
INNER JOIN sys.check_constraints AS c ON sed.referencing_id = c.object_id AND sed.referencing_class = 1
INNER JOIN sys.columns AS col ON sed.referenced_id = col.object_id AND sed.referenced_minor_id = col.column_id
INNER JOIN sys.types AS t ON col.system_type_id = t.system_type_id
WHERE referencing_class = 1 AND referenced_class = 1 AND (col.system_type_id IN 
( 59 --real
 , 62 --float
 , 58 --smalldatetime
 , 61 --datetime
 , 60 --money
 , 122 --smallmoney
 , 106 --decimal
 , 108 --numeric
 , 56 --int
 , 48 --tinyint
 , 52 -- smallint
 , 41 --time
 , 127 --bigint
) OR c.[definition] LIKE N'%DATEDIFF%'
 OR c.[definition] LIKE N'%CONVERT%'
 OR c.[definition] LIKE N'%DATEPART%'
 OR c.[definition] LIKE N'%DEGREES%')
) a

SET @sql += N'DBCC TRACEOFF(139,-1);';

PRINT @sql;

--to run the script immediately, use the following command:
--EXECUTE sp_executesql @sql;
GO

Compartilhar via

Melhorias no SQL Server e no Banco de Dados SQL do Azure no tratamento de alguns tipos de dados e operações incomuns

Etapas de validação durante uma atualização para o nível de compatibilidade do banco de dados

Apêndice A: Alterações no nível de compatibilidade 130

Apêndice B: Etapas para verificar e atualizar estruturas persistentes

Apêndice C: Consultas para identificar tabelas candidatas

Apêndice D: Script para criar instruções CHECK*

Comentários

Recursos adicionais