Miglioramenti di SQL Server e database SQL di Azure nella gestione di alcuni tipi di dati e operazioni non comuni

Questo articolo illustra come le strutture persistenti nel database di SQL Server possono essere convalidate come parte del livello di compatibilità dell'aggiornamento e come le strutture interessate possono essere ricompilate dopo l'aggiornamento del livello di compatibilità.

Versione originale del prodotto: SQL Server 2017, SQL Server 2016
Numero KB originale: 4010261

Il motore di database in Microsoft SQL Server 2016 e database SQL di Azure include miglioramenti nelle conversioni dei tipi di dati e in diverse altre operazioni. La maggior parte di questi miglioramenti offre una maggiore precisione quando si lavora con tipi a virgola mobile e anche con i tipi datetime classici.

Questi miglioramenti sono tutti disponibili quando si usa un livello di compatibilità del database di almeno 130. Ciò significa che per alcune espressioni (per lo più non comuni) è possibile visualizzare risultati diversi per alcuni valori di input dopo l'aggiornamento del database al livello di compatibilità 130 o superiore. Questi risultati possono essere riportati in:

• strutture persistenti nel database
• dati di tabella inclusi soggetti a CHECK vincoli
• colonne calcolate persistenti
• indici che fanno riferimento a colonne calcolate
• indici filtrati e viste indicizzate.

Se si dispone di un database creato in una versione precedente di SQL Server, è consigliabile eseguire una convalida aggiuntiva dopo l'aggiornamento a SQL Server 2016 o versione successiva e prima di modificare il livello di compatibilità del database.

Se nel database sono presenti strutture persistenti interessate da queste modifiche, è consigliabile ricompilare le strutture interessate dopo l'aggiornamento del livello di compatibilità del database. In questo modo, è possibile trarre vantaggio da questi miglioramenti in SQL Server 2016 o versione successiva.

Questo articolo descrive come le strutture persistenti nel database possono essere convalidate come parte dell'aggiornamento al livello di compatibilità 130 o superiore e come le strutture interessate possono essere ricompilate dopo aver modificato il livello di compatibilità.

Passaggi di convalida durante un aggiornamento al livello di compatibilità del database

A partire da SQL Server 2016, sia SQL Server che database SQL di Azure includono miglioramenti alla precisione delle operazioni seguenti:

• Conversioni non comuni dei tipi di dati. Di seguito sono riportati i seguenti:
  • Float/integer to/from datetime/smalldatetime
  • Real/float to/from numeric/money/smallmoney
  • Float to real
• Alcuni casi di DATEPART/DATEDIFF e DEGREES
• CONVERT che usa uno NULL stile

Per usare questi miglioramenti alla valutazione delle espressioni nell'applicazione, modificare il livello di compatibilità dei database impostando 130 (per SQL Server 2016) o 140 (per SQL Server 2017 e database SQL di Azure). Per altre informazioni su tutte le modifiche e alcuni esempi che mostrano le modifiche, vedere la sezione Appendice A .

Le strutture seguenti nel database possono rendere persistenti i risultati di un'espressione:

• Tabella dati soggetti a CHECK vincoli
• Colonne calcolate persistenti
• Indici che usano colonne calcolate nelle colonne chiave o incluse
• Indici filtrati
• Viste indicizzate

Prendi in considerazione lo scenario seguente:

• Si dispone di un database creato da una versione precedente di SQL Server o già creato in SQL Server 2016 o versione successiva, ma a un livello di compatibilità 120 o precedente.

• Si usano espressioni la cui precisione è stata migliorata come parte della definizione di strutture persistenti nel database.

In questo scenario, potrebbero essere presenti strutture persistenti interessate dai miglioramenti apportati alla precisione implementati usando il livello di compatibilità 130 o superiore. In questo caso, è consigliabile convalidare le strutture persistenti e ricompilare qualsiasi struttura interessata.

Se le strutture interessate non vengono ricompilata dopo la modifica del livello di compatibilità, è possibile che si verifichino risultati di query leggermente diversi. I risultati dipendono dal fatto che venga usato un particolare indice, colonna calcolata o vista e se i dati in una tabella possano essere considerati una violazione di un vincolo.

Il flag di traccia 139 è supportato a partire da SQL Server 2016 CU3 e SQL Server 2016 SP1.

Per aggiornare il livello di compatibilità, seguire questa procedura:

1. Eseguire la convalida per identificare le strutture persistenti interessate:
   1. Abilitare il flag di traccia 139 eseguendo DBCC TRACEON(139, -1).
   2. Eseguire DBCC CHECKDB/TABLE i comandi e CHECKCONSTRAINTS .
   3. Disabilitare il flag di traccia 139 eseguendo DBCC TRACEOFF(139, -1).
2. Impostare il livello di compatibilità del database su 130 (per SQL Server 2016) o 140 (per SQL Server 2017 e database SQL di Azure).
3. Ricompilare le strutture identificate nel passaggio 1.

• Appendice A contiene un elenco dettagliato di tutti i miglioramenti della precisione e fornisce un esempio per ognuno di essi.

• L'Appendice B contiene un processo dettagliato dettagliato per eseguire la convalida e ricompilare le strutture interessate.

• L'Appendice C e l'Appendice D contengono script per individuare gli oggetti potenzialmente interessati nel database. Pertanto, è possibile definire l'ambito delle convalide e generare script corrispondenti per eseguire i controlli. Per determinare più facilmente se le strutture persistenti nei database sono interessate dai miglioramenti della precisione nel livello di compatibilità 130, eseguire lo script nell'Appendice D per generare i controlli di convalida corretti e quindi eseguire questo script per eseguire la convalida.

Appendice A: Modifiche al livello di compatibilità 130

Questa appendice fornisce elenchi dettagliati dei miglioramenti apportati alla valutazione delle espressioni nel livello di compatibilità 130. Ogni modifica include una query di esempio associata. Le query possono essere usate per mostrare le differenze tra l'esecuzione in un database che usa un livello di compatibilità pre-130 rispetto a un database che usa il livello di compatibilità 130.

Nelle tabelle seguenti sono elencate le conversioni dei tipi di dati e le operazioni aggiuntive.

Conversioni dei tipi di dati

Da	Per	Modifica	Query di esempio	Risultato per il livello < di compatibilità 130	Risultato per il livello di compatibilità = 130
float, real, numeric, decimal, money o smallmoney	datetime o smalldatetime	Aumentare la precisione di arrotondamento. In precedenza, giorno e ora sono stati convertiti separatamente e i risultati sono stati troncati prima di combinarli.	DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT)	1.19999996141975	1.2
datetime	bigint, int, or smallint	Un valore datetime negativo la cui parte dell'ora è esattamente una mezza giornata o in un segno di spunta di mezza giornata viene arrotondata in modo errato (il risultato è disattivato di 1).	DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT)	0	-1
datetime o smalldatetime	float, real, numeric, money, or smallmoney	Maggiore precisione per gli ultimi 8 bit di precisione in alcuni casi.	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	I controlli limite sono meno rigidi.	SELECT CAST (3.40282347000E+038 AS REAL)	Overflow aritmetico	3.402823E+38
numeric, money e smallmoney	float	Quando la scala di input è zero, si verifica un'imprecisione di arrotondamento quando si combinano le quattro parti di numerico.	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 la scala di input è diversa da zero, si verifica un'imprecisione di arrotondamento quando si divide per 10^scala.	DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8))	0x41678C29C06522C4	0x41678C29C06522C3
real o float	numeric	Maggiore precisione di arrotondamento in alcuni casi.	DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1))	0,2	0,1
real o float	numeric	Maggiore precisione quando si arrotonda a più di 16 cifre in alcuni casi.	DECLARE @v decimal(38, 18) = 1E-18 SELECT @v	0.000000000000000000	0.000000000000000001
real o float	money o smallmoney	Maggiore accuratezza quando si convertono numeri di grandi dimensioni in alcuni casi.	DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money)	562949953421312.2048	562949953421312.25
(n)(var)char	numeric	Un input di più di 39 caratteri non attiva più necessariamente un overflow aritmetico.	DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1))	Overflow aritmetico	1.1
(n)(var)char	bit	Supporta spazi iniziali e segni.	DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit)	Conversione non riuscita durante la conversione del nvarchar valore '1' in bit del tipo di dati.	1
datetime	time o datetime2	Maggiore precisione quando si esegue la conversione in tipi di data/ora con maggiore precisione. Tenere presente che i valori datetime vengono archiviati come tick che rappresentano 1/300 di secondo. I tipi time e datetime2 più recenti archiviano un numero discreto di cifre, in cui il numero di cifre corrisponde alla precisione.	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 o datetime2	datetime	Miglioramento dell'arrotondamento in alcuni casi.	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

Operazione

Operazione	Modifica	Query di esempio	Risultato per il livello <di compatibilità 130	Risultato per il livello di compatibilità 130
Usare la RADIANS funzione predefinita o DEGREES che usa il tipo di dati numerico.	DEGREES divide per pi/180, dove in precedenza moltiplicato per 180/pi. Simile a .RADIANS	DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1)	57.295779513082323000	57.295779513082322865
Addizione numerica o sottrazione quando la scala di un operando è maggiore della scala del risultato.	L'arrotondamento si verifica sempre dopo l'addizione o la sottrazione, mentre in precedenza poteva verificarsi prima.	DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2	8.8	8.9
CONVERT con NULL stile.	CONVERT con NULL style restituisce NULL sempre quando il tipo di destinazione è numerico.	SELECT CONVERT (SMALLINT, '0', NULL);	0	NULL
DATEPART che usa l'opzione microsecondi o nanosecondi, con il tipo di dati datetime.	Il valore non viene più troncato a livello di millisecondo prima della conversione in microsecondi.	DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt);	3000	3333
DATEDIFF che usa l'opzione microsecondi o nanosecondi, con il tipo di dati datetime.	Il valore non viene più troncato a livello di millisecondo prima della conversione in microsecondi.	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
Confronto tra valori datetime e datetime2 con valori diversi da zero per millisecondi.	Il valore Datetime non viene più troncato a livello di millisecondo quando si esegue un confronto con un valore datetime2. Ciò significa che determinati valori confrontati in precedenza non sono più uguali.	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 uguale a	1900-01-01 00:00:00.0033333, 1900-01-01 00:00:00.003
ROUND funzione che usa il float tipo di dati.	I risultati dell'arrotondamento differiscono.	SELECT ROUND(CAST (-0.4175 AS FLOAT), 3)	-0.418	-0.417

Appendice B: Passaggi per verificare e aggiornare strutture persistenti

È consigliabile determinare se il database dispone di strutture persistenti interessate dalle modifiche apportate al livello di compatibilità 130 e di ricompilare le strutture interessate.

Questo vale solo per le strutture persistenti create nel database in una versione precedente di SQL Server o usando un livello di compatibilità inferiore a 130. Le strutture persistenti potenzialmente interessate includono quanto segue:

• Tabella dati soggetti a CHECK vincoli
• Colonne calcolate persistenti
• Indici che usano colonne calcolate nelle colonne chiave o incluse
• Indici filtrati
• Viste indicizzate

In questo caso, eseguire la procedura seguente.

Passaggio 1: Verificare il livello di compatibilità del database

1. Controllare il livello di compatibilità del database usando la procedura documentata in Visualizzare o modificare il livello di compatibilità di un database.
2. Se il livello di compatibilità del database è inferiore a 130, è consigliabile eseguire la convalida descritta nel passaggio 2 prima di aumentare il livello di compatibilità a 130.

Passaggio 2: Identificare le strutture persistenti interessate

Determinare se il database contiene strutture persistenti interessate dalla logica di precisione e conversione migliorata nel livello di compatibilità 130 in uno dei modi seguenti:

• DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, che convalida tutte le strutture nel database.
• DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, che convalida le strutture correlate a una singola tabella.

L'opzione WITH EXTENDED_LOGICAL_CHECKS è necessaria per assicurarsi che i valori persistenti vengano confrontati con i valori calcolati e per contrassegnare i casi in cui esiste una differenza. Poiché questi controlli sono estesi, il runtime di DBCC istruzioni che usano questa opzione è più lungo delle istruzioni in esecuzione DBCC senza l'opzione . Pertanto, la raccomandazione per i database di grandi dimensioni consiste nell'usare DBCC CHECKTABLE per individuare singole tabelle.

DBCC CHECKCONSTRAINTS può essere usato per convalidare CHECK i vincoli. Questa istruzione può essere utilizzata a livello di database o di tabella.

DBCC CHECK Le istruzioni devono essere sempre eseguite durante una finestra di manutenzione, a causa del potenziale impatto dei controlli sul carico di lavoro online.

Convalida a livello di database

La convalida a livello di database è adatta per database di piccole dimensioni e con dimensioni moderate. Usare la convalida a livello di tabella per database di grandi dimensioni.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS viene utilizzato per convalidare tutte le strutture persistenti nel database.

DBCC CHECKCONSTRAINTS viene usato per convalidare tutti i CHECK vincoli nel database.

DBCC CHECKCONSTRAINTS viene usato per convalidare l'integrità dei vincoli. Usare lo script seguente per convalidare il database:

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

L'uso del flag di traccia garantisce che i controlli vengano eseguiti usando la logica di precisione e conversione migliorata nel livello di compatibilità 130, forzando la semantica di conversione corretta anche quando il database ha un livello di compatibilità inferiore.

Se l'istruzione CHECKCONSTRAINTS è terminata e non restituisce un set di risultati, non è necessaria alcuna azione aggiuntiva.

Se l'istruzione restituisce un set di risultati, ogni riga nei risultati indica una violazione di un vincolo e include anche i valori che violano il vincolo.

• Salvare i nomi delle tabelle e dei vincoli, insieme ai valori che hanno causato la violazione (la WHERE colonna nel set di risultati).

Nell'esempio seguente viene illustrata una tabella con un CHECK vincolo e una singola riga che soddisfa il vincolo con livelli di compatibilità inferiori, ma che viola il vincolo con il livello di compatibilità 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
DBCC TRACEON(139, -1)
GO
DBCC CHECKCONSTRAINTS
GO
DBCC TRACEOFF(139, -1)
GO

Il CHECKCONSTRAINT comando restituisce i risultati seguenti.

Tabella	Vincolo	Dove
[dbo]. [table1]	[chk1]	[c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'

Questo risultato indica che il vincolo [chk1] viene violato per la combinazione di valori di colonna in 'Where'.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS convalida tutte le strutture persistenti nel database. Questa è l'opzione più comoda perché una singola istruzione convalida tutte le strutture nel database. Tuttavia, questa opzione non è adatta per database di grandi dimensioni a causa del runtime previsto dell'istruzione.

Usare lo script seguente per convalidare l'intero database:

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

L'uso del flag di traccia garantisce che i controlli vengano eseguiti usando la logica di precisione e conversione migliorata nel livello di compatibilità 130, forzando la semantica di conversione corretta anche quando il database ha un livello di compatibilità inferiore.

Se l'istruzione CHECKDB viene completata correttamente, non è necessaria alcuna azione aggiuntiva.

Se l'istruzione è stata completata con errori, seguire questa procedura:

1. Salvare i risultati dall'esecuzione dell'istruzione DBCC , disponibile nel riquadro dei messaggi in SQL Server Management Studio (SSMS) in un file.
2. Verificare che uno degli errori segnalati sia correlato a strutture persistenti

Tabella 1: Strutture persistenti e messaggi di errore corrispondenti per incoerenze

Tipo di struttura interessato	Messaggi di errore osservati	Prendere nota di
Colonne calcolate persistenti	Msg 2537, Errore di tabella di livello 16: ID <oggetto object_id> , ID <indice index_id> , . Il controllo del record (colonna calcolata valida) non è riuscito. I valori sono .	ID <oggetto object_id> e ID <indice index_id>
Indici che fanno riferimento a colonne calcolate nella chiave o nelle colonne incluse Indici filtrati	Errore di tabella msg 8951: tabella '<table_name>' (ID <object_id>). La riga di dati non dispone di una riga di indice corrispondente nell'indice '<index_name>' (ID <index_id>) e/o msg 8952 errore di tabella: tabella '<table_name>' (ID <table_name>). La riga di indice nell'indice '' (ID <index_id>) non corrisponde ad alcuna riga di dati. Inoltre, potrebbero essere presenti errori secondari 8955 e/o 8956. Contiene informazioni dettagliate sulle righe esatte interessate. Questi potrebbero essere ignorati per questo esercizio.	ID <oggetto object_id> e ID <indice index_id>
Viste indicizzate	Msg 8908 La vista indicizzata '<view_name>' (ID <oggetto object_id>) non contiene tutte le righe che produce la definizione della vista. E/o Msg 8907 La vista indicizzata '<view_name>' (ID <oggetto object_id>) contiene righe non prodotte dalla definizione della vista.	ID <oggetto object_id>

Dopo aver completato la convalida a livello di database, passare al passaggio 3.

Convalida a livello di oggetto

Per i database di dimensioni maggiori, è utile convalidare strutture e vincoli in una tabella o una vista alla volta per ridurre le dimensioni delle finestre di manutenzione o limitare i controlli logici estesi solo a oggetti potenzialmente interessati.

Usare le query nella sezione Appendice C per identificare le tabelle potenzialmente interessate. Lo script nella sezione Appendice D può essere usato per generare CHECKTABLE e CHECKCONSTRAINTS vincoli in base alle query elencate nella sezione Appendice C .

DBCC CHECKCONSTRAINTS

Per convalidare i vincoli correlati a una singola tabella o vista, usare lo script seguente:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

L'uso del flag di traccia garantisce che i controlli vengano eseguiti usando la logica di precisione e conversione migliorata nel livello di compatibilità 130, forzando la semantica migliorata anche quando il database ha un livello di compatibilità inferiore.

Se l'istruzione CHECKCONSTRAINTS è terminata e non restituisce un set di risultati, non è necessaria alcuna azione aggiuntiva.

Se l'istruzione restituisce un set di risultati, ogni riga nei risultati indica una violazione di un vincolo e fornisce anche i valori che violano il vincolo.

Salvare i nomi delle tabelle e dei vincoli, insieme ai valori che hanno causato la violazione (la WHERE colonna nel set di risultati).

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

Per convalidare le strutture persistenti correlate a una singola tabella o vista, usare lo script seguente:

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 l'istruzione CHECKTABLE viene completata correttamente, non è necessaria alcuna azione aggiuntiva.

Se l'istruzione è stata completata con errori, seguire questa procedura:

1. Salvare i risultati dall'esecuzione dell'istruzione DBCC , disponibile nel riquadro dei messaggi in SSMS, in un file.
2. Verificare che uno degli errori segnalati sia correlato alle strutture persistenti elencate nella tabella 1.
3. Dopo aver completato la convalida a livello di tabella, passare al passaggio 3.

Passaggio 3: Eseguire l'aggiornamento al livello di compatibilità 130

Se il livello di compatibilità del database è già 130, è possibile ignorare questo passaggio.

Il livello di compatibilità del database può essere modificato in 130 usando lo script seguente:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Passaggio 4: Aggiornare le strutture persistenti

Se non sono state trovate incoerenze durante la convalida eseguita nel passaggio 2, l'aggiornamento viene completato e può ignorare questo passaggio. Se sono state trovate incoerenze nel passaggio 2, sono necessarie azioni aggiuntive per rimuovere le incoerenze dal database. Le azioni necessarie dipendono dal tipo di struttura interessata.

Eseguire il backup del database (o dei database)

È consigliabile eseguire un backup completo del database prima di eseguire una qualsiasi delle azioni descritte nella sezione seguente. Se si usa database SQL di Azure, non è necessario eseguire manualmente un backup. È sempre possibile usare la funzionalità di ripristino temporizzato per tornare indietro nel tempo nel caso in cui qualsiasi problema si verifichi con uno degli aggiornamenti.

Vincoli CHECK

La correzione delle CHECK violazioni dei vincoli richiede la modifica dei dati nella tabella o del CHECK vincolo stesso.

Dal nome del vincolo (ottenuto nel passaggio 2), è possibile ottenere la definizione del vincolo come indicato di seguito:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Per esaminare le righe della tabella interessate, è possibile usare le informazioni Where restituite in precedenza dall'istruzione DBCC CHECKCONSTRAINTS :

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

È necessario aggiornare le righe interessate o modificare la definizione del vincolo per assicurarsi che il vincolo non venga violato.

Aggiornamento dei dati della tabella

Non esiste una regola rigida che indica come aggiornare i dati. In genere, per ogni istruzione Where diversa restituita da DBCC CHECKCONSTRAINTS, si eseguirà l'istruzione di aggiornamento seguente:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Si consideri la tabella di esempio seguente con un vincolo e una riga che viola il vincolo nel livello di compatibilità 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

In questo esempio il vincolo è semplice. La colonna c4 deve essere uguale a un'espressione che include c2 e c3. Per aggiornare la tabella, assegnare questo valore 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

Si noti che la WHERE clausola utilizzata nell'istruzione update corrisponde alle informazioni Where restituite da DBCC CHECKCONSTRAINTS.

Aggiornamento del vincolo CHECK

Per modificare un CHECK vincolo, è necessario eliminarlo e ricrearlo. È consigliabile eseguire entrambe le operazioni nella stessa transazione, nel caso in cui si verifichino problemi con la definizione di vincolo aggiornata. È possibile usare il codice Transact-SQL seguente:

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

Colonne calcolate persistenti

Il modo più semplice per aggiornare le colonne calcolate persistenti consiste nell'aggiornare una delle colonne a cui fa riferimento la colonna calcolata. Il nuovo valore per la colonna può essere uguale al valore precedente, in modo che l'operazione non modifichi i dati utente.

Seguire questi passaggi per ogni object_id relazione alle incoerenze nelle colonne calcolate annotate nel passaggio 2.

1. Identificare le colonne calcolate:

   • Eseguire la query seguente per recuperare il nome della tabella e i nomi delle colonne calcolate persistenti per gli elementi annotati 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
     

2. Identificare le colonne a cui si fa riferimento:

• Eseguire la query seguente per identificare le colonne a cui fa riferimento la colonna calcolata. Prendere nota di uno dei nomi di colonna a cui si fa riferimento:

  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
  

1. Eseguire un'istruzione UPDATE che include una delle colonne di riferimento per attivare un aggiornamento della colonna calcolata:

   • L'istruzione seguente attiverà un aggiornamento della colonna a cui fa riferimento la colonna calcolata e attiverà anche un aggiornamento della colonna calcolata.

     UPDATE [schema_name].[table_name]
     SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)
     

   • L'espressione ISNULL nell'istruzione viene creata in modo che il valore della colonna originale non venga modificato, assicurandosi comunque che la colonna calcolata venga aggiornata usando la logica di valutazione delle espressioni del livello di compatibilità del database 130.

   • Tenere presente che, per le tabelle molto grandi, potrebbe non essere necessario aggiornare tutte le righe in una singola transazione. In questo caso, è possibile eseguire l'aggiornamento in batch aggiungendo una WHERE clausola all'istruzione update che identifica un intervallo di righe, ad esempio in base alla chiave primaria.

2. Identificare gli indici che fanno riferimento alla colonna calcolata.

   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
   

Questa query identifica gli indici che fanno riferimento alla colonna calcolata persistente. È necessario ricompilare qualsiasi indice di questo tipo. A tale scopo, seguire la procedura descritta nella sezione seguente.

Indici, indici filtrati e viste indicizzate

Le incoerenze negli indici corrispondono agli errori 8951 e 8952 (per le tabelle) o 8907 e 8908 (per le viste) nell'output del DBCC CHECK passaggio 2.

Per ripristinare queste incoerenze, eseguire DBCC CHECKTABLE con REPAIR_REBUILD. In questo modo verranno ripristinate le strutture degli indici senza perdita di dati. Tuttavia, il database deve essere in modalità utente singolo e pertanto non è disponibile per altri utenti durante la riparazione.

È anche possibile ricompilare manualmente gli indici interessati. Questa opzione deve essere usata se il carico di lavoro non può essere portato offline, perché la ricompilazione dell'indice può essere eseguita come operazione ONLINE (nelle edizioni supportate di SQL Server).

Ricompilazione degli indici

Se l'impostazione del database in modalità utente singolo non è un'opzione, è possibile ricompilare singolarmente gli indici usando ALTER INDEX REBUILD, per ogni indice identificato nel passaggio 2.

Usare la query seguente per ottenere i nomi di tabella e di indice per un determinato 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

Usare l'istruzione seguente per ricompilare l'indice:

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

L'esempio seguente mostra la ricompilazione di un indice filtrato:

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 si dispone di piani di manutenzione regolari, è consigliabile includere questa ricompilazione dell'indice come parte della manutenzione pianificata.

Ripristinare usando DBCC

Per ogni (object_id) correlato a un indice con incoerenze annotate nel passaggio 2, eseguire lo script seguente per eseguire il ripristino. Questo script imposta il database in modalità utente singolo per l'operazione di ripristino. Nel peggiore dei casi, il ripristino esegue una ricompilazione completa dell'indice.

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

Appendice C: Query per identificare le tabelle candidate

Gli script seguenti identificano le tabelle candidate che è possibile convalidare usando DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, in base all'esistenza di strutture persistenti e vincoli che usano i tipi di dati interessati dai miglioramenti apportati al livello di compatibilità 130.

Il set di query seguente elenca i dettagli sulle tabelle e sulle strutture potenzialmente interessate che richiedono una convalida aggiuntiva.

Viste indicizzate

La query seguente restituisce tutte le viste indicizzate che fanno riferimento a colonne usando i tipi di dati interessati o usando una delle funzioni predefinite interessate:

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%')

Colonne calcolate persistenti

La query seguente restituisce tutte le tabelle con colonne calcolate che fanno riferimento ad altre colonne usando i tipi di dati interessati o usando una delle funzioni predefinite interessate, in cui la colonna viene mantenuta o a cui viene fatto riferimento da un indice.

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)

)

Indici filtrati

La query seguente restituisce tutte le tabelle con indici filtrati che fanno riferimento a colonne nella condizione di filtro con tipi di dati interessati:

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

)

Controlla vincoli

La query seguente elenca tutte le tabelle con vincoli CHECK che fanno riferimento ai tipi di dati interessati o alle funzioni predefinite:

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%')

Appendice D: Script per creare istruzioni CHECK*

Lo script seguente combina le query dell'appendice precedente e semplifica i risultati presentando un elenco di tabelle e viste sotto forma di CHECKCONSTRAINTS istruzioni 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