SQL Server und Azure SQL Datenbankverbesserungen bei der Behandlung einiger Datentypen und ungewöhnlicher Vorgänge

Artikel
06/20/2023

In diesem Artikel wird beschrieben, wie persistente Strukturen in Ihrer SQL Server Datenbank als Teil des Upgradekompatibilitätsgrads überprüft werden können und wie alle betroffenen Strukturen nach dem Upgrade des Kompatibilitätsgrads neu erstellt werden können.

Ursprüngliche Produktversion: SQL Server 2017, SQL Server 2016
Ursprüngliche KB-Nummer: 4010261

Die Datenbank-Engine in Microsoft SQL Server 2016 und Azure SQL Database enthält Verbesserungen bei Datentypkonvertierungen und mehreren anderen Vorgängen. Die meisten dieser Verbesserungen bieten eine höhere Genauigkeit, wenn Sie mit Gleitkommatypen und auch mit klassischen datetime-Typen arbeiten.

Diese Verbesserungen sind alle verfügbar, wenn Sie einen Datenbank-Kompatibilitätsgrad von mindestens 130 verwenden. Dies bedeutet, dass bei einigen (meist ungewöhnlichen) Ausdrücken unterschiedliche Ergebnisse für einige Eingabewerte angezeigt werden können, nachdem Sie die Datenbank auf den Kompatibilitätsgrad 130 oder eine höhere Einstellung aktualisiert haben. Diese Ergebnisse können sich in folgenden Ergebnissen widerspiegeln:

Persistente Strukturen in der Datenbank
eingeschlossene Tabellendaten, die Einschränkungen unterliegen CHECK
Persistente berechnete Spalten
Indizes, die auf berechnete Spalten verweisen
gefilterte Indizes und indizierte Sichten.

Wenn Sie über eine Datenbank verfügen, die in einer früheren Version von SQL Server erstellt wurde, empfiehlt es sich, nach dem Upgrade auf SQL Server 2016 oder höher und vor dem Ändern des Datenbank-Kompatibilitätsgrads eine zusätzliche Überprüfung durchzuführen.

Wenn Sie feststellen, dass eine der persistenten Strukturen in Ihrer Datenbank von diesen Änderungen betroffen ist, empfehlen wir Ihnen, die betroffenen Strukturen nach dem Upgrade des Datenbank-Kompatibilitätsgrads neu zu erstellen. Auf diese Weise profitieren Sie von diesen Verbesserungen in SQL Server 2016 oder höher.

In diesem Artikel wird beschrieben, wie persistente Strukturen in Ihrer Datenbank im Rahmen des Upgrades auf kompatibilitätsgrad 130 oder höher überprüft werden können und wie alle betroffenen Strukturen neu erstellt werden können, nachdem Sie den Kompatibilitätsgrad geändert haben.

Überprüfungsschritte während eines Upgrades auf den Datenbank-Kompatibilitätsgrad

Ab SQL Server 2016 bieten sowohl SQL Server als auch Azure SQL Database Verbesserungen an der Genauigkeit der folgenden Vorgänge:

Ungewöhnliche Datentypkonvertierungen. Hierzu gehören:
- Float/integer to/from datetime/smalldatetime
- Real/float to/from numeric/money/smallmoney
- Float to real (Float to real)
Einige Fälle von DATEPART/DATEDIFF und DEGREES
CONVERT die eine NULL Formatvorlage verwendet

Um diese Verbesserungen für die Ausdrucksauswertung in Ihrer Anwendung zu verwenden, ändern Sie den Kompatibilitätsgrad Ihrer Datenbanken in 130 (für SQL Server 2016) oder 140 (für SQL Server 2017 und Azure SQL Datenbank). Weitere Informationen zu allen Änderungen und einige Beispiele für die Änderungen finden Sie im Abschnitt Anhang A .

Die folgenden Strukturen in der Datenbank können die Ergebnisse eines Ausdrucks beibehalten:

Tabellendaten, die Einschränkungen unterliegen CHECK
Persistente berechnete Spalten
Indizes, die berechnete Spalten im Schlüssel oder eingeschlossene Spalten verwenden
Gefilterte Indizes
Indizierte Sichten

Stellen Sie sich folgendes Szenario vor:

Sie verfügen über eine Datenbank, die von einer früheren Version von SQL Server erstellt wurde oder die bereits in SQL Server 2016 oder einer höheren Version erstellt wurde, jedoch mit einem Kompatibilitätsgrad 120 oder einem früheren Level.
Sie verwenden alle Ausdrücke, deren Genauigkeit verbessert wurde, als Teil der Definition persistenter Strukturen in Ihrer Datenbank.

In diesem Szenario verfügen Sie möglicherweise über persistente Strukturen, die von den Verbesserungen der Genauigkeit betroffen sind, die mithilfe des Kompatibilitätsgrads 130 oder höher implementiert werden. In diesem Fall wird empfohlen, die persistenten Strukturen zu überprüfen und alle betroffenen Strukturen neu zu erstellen.

Wenn Sie betroffene Strukturen haben und diese nach dem Ändern des Kompatibilitätsgrads nicht neu erstellen, können sich die Abfrageergebnisse leicht unterscheiden. Die Ergebnisse hängen davon ab, ob ein bestimmter Index, eine berechnete Spalte oder sicht verwendet wird und ob Daten in einer Tabelle als Verstoß gegen eine Einschränkung angesehen werden können.

Hinweis

Ablaufverfolgungsflag 139 in SQL Server

Das globale Ablaufverfolgungsflag 139 wird in SQL Server 2016 CU3 und Service Pack (SP) 1 eingeführt, um eine korrekte Konvertierungssemantik im Bereich von DBCC-Überprüfungsbefehlen wie DBCC CHECKDB, DBCC CHECKTABLEund zu erzwingen, wenn DBCC CHECKCONSTRAINTS Sie die verbesserte Genauigkeit und Konvertierungslogik analysieren, die mit Kompatibilitätsgrad 130 für eine Datenbank mit einem früheren Kompatibilitätsgrad eingeführt wurde.

Warnung

Das Ablaufverfolgungsflag 139 ist nicht für die kontinuierliche Aktivierung in einer Produktionsumgebung vorgesehen und sollte ausschließlich für die Durchführung der in diesem Artikel beschriebenen Überprüfungen der Datenbank verwendet werden. Daher sollte sie mithilfe von dbcc traceoff (139, -1) in derselben Sitzung deaktiviert werden, nachdem die Überprüfungen abgeschlossen wurden.

Das Ablaufverfolgungsflag 139 wird ab SQL Server 2016 CU3 und SQL Server 2016 SP1 unterstützt.

Führen Sie die folgenden Schritte aus, um den Kompatibilitätsgrad zu aktualisieren:

Führen Sie eine Überprüfung durch, um alle betroffenen persistenten Strukturen zu identifizieren:
1. Aktivieren Sie das Ablaufverfolgungsflag 139, indem Sie ausführen DBCC TRACEON(139, -1).
2. Führen Sie DBCC CHECKDB/TABLE die Befehle und CHECKCONSTRAINTS aus.
3. Deaktivieren Sie das Ablaufverfolgungsflag 139, indem Sie ausführen DBCC TRACEOFF(139, -1).
Ändern Sie den Datenbank-Kompatibilitätsgrad in 130 (für SQL Server 2016) oder 140 (für SQL Server 2017 und Azure SQL Datenbank).
Erstellen Sie alle Strukturen neu, die Sie in Schritt 1 identifiziert haben.

Hinweis

Ablaufverfolgungsflags in Azure SQL Datenbankeinstellungs-Ablaufverfolgungsflags werden in Azure SQL-Datenbank nicht unterstützt. Daher müssen Sie den Kompatibilitätsgrad ändern, bevor Sie die Überprüfung durchführen:

Aktualisieren Sie den Datenbank-Kompatibilitätsgrad auf 140.
Überprüfen Sie, um alle betroffenen persistenten Strukturen zu identifizieren.
Erstellen Sie die Strukturen neu, die Sie in Schritt 2 identifiziert haben.

Anhang A enthält eine detaillierte Liste aller Genauigkeitsverbesserungen und enthält jeweils ein Beispiel.
Anhang B enthält einen detaillierten Schritt-für-Schritt-Prozess zum Durchführen der Überprüfung und zum Neuerstellen betroffener Strukturen.
Anhang C und Anhang D enthalten Skripts zum Ermitteln potenziell betroffener Objekte in der Datenbank. Daher können Sie Ihre Überprüfungen festlegen und entsprechende Skripts generieren, um die Überprüfungen auszuführen. Um am einfachsten festzustellen, ob persistente Strukturen in Ihren Datenbanken von den Genauigkeitsverbesserungen des Kompatibilitätsgrads 130 betroffen sind, führen Sie das Skript in Anhang D aus, um die richtigen Validierungsprüfungen zu generieren, und führen Sie dann dieses Skript aus, um die Überprüfung durchzuführen.

Anhang A: Änderungen am Kompatibilitätsgrad 130

Dieser Anhang enthält detaillierte Listen der Verbesserungen der Ausdrucksauswertung im Kompatibilitätsgrad 130. Jede Änderung enthält eine zugeordnete Beispielabfrage. Die Abfragen können verwendet werden, um die Unterschiede zwischen der Ausführung in einer Datenbank anzuzeigen, die einen Kompatibilitätsgrad vor 130 verwendet, im Vergleich zu einer Datenbank, die den Kompatibilitätsgrad 130 verwendet.

In den folgenden Tabellen sind Datentypkonvertierungen und zusätzliche Vorgänge aufgeführt.

Datentypkonvertierungen

Von	An	Ändern	Beispielabfrage	Ergebnis für Kompatibilitätsgrad < 130	Ergebnis für Kompatibilitätsgrad = 130
`float`, `real`, `numeric`, `decimal`, `money`oder `smallmoney`	`datetime` oder `smalldatetime`	Erhöhen Sie die Rundungsgenauigkeit. Zuvor wurden Tag und Uhrzeit separat konvertiert, und die Ergebnisse wurden abgeschnitten, bevor Sie sie kombiniert haben.	`DECLARE @f FLOAT = 1.2 DECLARE @d DATETIME = @f SELECT CAST(@d AS FLOAT)`	1.19999996141975	1.2
`datetime`	`bigint, int, or smallint`	Ein negativer datetime-Wert, dessen Zeitteil genau ein halber Tag oder in einem Tick eines halben Tages ist, wird falsch gerundet (das Ergebnis ist um 1 deaktiviert).	`DECLARE @h DATETIME = -0.5 SELECT @h, CAST(@h AS INT)`	0	-1
`datetime` oder `smalldatetime`	`float, real, numeric, money, or smallmoney`	In einigen Fällen wurde die Genauigkeit der letzten 8 Bits verbessert.	`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.00138344907407407407, 0xBF56AA9B21D8583B
`float`	`real`	Begrenzungsprüfungen sind weniger streng.	`SELECT CAST (3.40282347000E+038 AS REAL)`	Arithmetischer Überlauf	3.402823E+38
`numeric`, `money` und `smallmoney`	`float`	Wenn die Eingabeskala 0 (null) ist, gibt es eine rundende Ungenauigkeit, wenn Sie die vier Teile der numerischen Zahl kombinieren.	`DECLARE @n NUMERIC(38, 0)= 41538374868278625639929991208632320 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CONVERT(BINARY(8), @f)`	0x4720000000000000	0x4720000000000001
`numeric`, `money` und `smallmoney`	`float`	Wenn die Eingabeskala ungleich 0 (null) ist, gibt es eine rundende Ungenauigkeit, wenn Sie durch 10^-Skala dividieren.	`DECLARE @n NUMERIC(18, 10) = 12345678.0123456781 DECLARE @f FLOAT = CAST(@n AS FLOAT) SELECT CAST(@f AS BINARY(8))`	0x41678C29C06522C4	0x41678C29C06522C3
`real` oder `float`	Numerischen	In einigen Fällen wurde die Rundungsgenauigkeit verbessert.	`DECLARE @f float = 0.14999999999999999 SELECT CAST(@f AS numeric(1, 1))`	0.2	0,1
`real` oder `float`	Numerischen	Die Genauigkeit wurde verbessert, wenn Sie in einigen Fällen auf mehr als 16 Ziffern runden.	`DECLARE @v decimal(38, 18) = 1E-18 SELECT @v`	0.000000000000000000	0.000000000000000001
`real` oder `float`	`money` oder `smallmoney`	In einigen Fällen wurde die Genauigkeit beim Konvertieren großer Zahlen verbessert.	`DECLARE @f float = 2SET @f = POWER(@f, 49) + POWER(@f, -2) SELECT CAST(@f AS money)`	562949953421312.2048	562949953421312.25
`(n)(var)char`	`numeric`	Eine Eingabe von mehr als 39 Zeichen löst nicht mehr unbedingt einen arithmetischen Überlauf aus.	`DECLARE @value nchar(100) = '1.11111111111111111111111111111111111111' SELECT CAST(@value AS decimal(2,1))`	Arithmetischer Überlauf	1.1
`(n)(var)char`	`bit`	Unterstützt führende Leerzeichen und Zeichen.	`DECLARE @value nvarchar(100) = '1' SELECT CAST(@value AS bit)`	Fehler bei der Konvertierung des `nvarchar` Werts "1" in das Datentypbit.	1
`datetime`	`time` oder `datetime2`	Verbesserte Genauigkeit beim Konvertieren in Datums-/Uhrzeittypen mit höherer Genauigkeit. Beachten Sie, dass datetime-Werte als Ticks gespeichert werden, die 1/300 einer Sekunde darstellen. Die neueren Time- und datetime2-Typen speichern eine diskrete Anzahl von Ziffern, wobei die Anzahl der Ziffern mit der Genauigkeit übereinstimmt.	`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` oder `datetime2`	`datetime`	In einigen Fällen wurde die Rundung verbessert.	`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

Vorgang

Vorgang	Ändern	Beispielabfrage	Ergebnis für Kompatibilitätsgrad <130	Ergebnis für Kompatibilitätsgrad 130
Verwenden Sie die `RADIANS` integrierte Funktion oder `DEGREES` , die den numerischen Datentyp verwendet.	`DEGREES` dividiert durch pi/180, wobei es zuvor mit 180/Pi multipliziert wurde. Ähnlich für `RADIANS`.	`DECLARE @arg1 numeric = 1 SELECT DEGREES(@arg1)`	57.295779513082323000	57.295779513082322865
Numerische Addition oder Subtraktion, wenn die Skala eines Operanden größer als die Skala des Ergebnisses ist.	Die Rundung erfolgt immer nach dem Additions- oder Subtraktionsvorgang, während es zuvor manchmal früher vorkommen konnte.	`DECLARE @p1 numeric(38, 2) = -1.15 DECLARE @p2 numeric(38, 1) = 10 SELECT @p1 + @p2`	8.8	8.9
`CONVERT` mit `NULL` Stil.	`CONVERT` mit `NULL` style wird immer zurückgegeben `NULL` , wenn der Zieltyp numerisch ist.	`SELECT CONVERT (SMALLINT, '0', NULL);`	0	`NULL`
`DATEPART` , die die Option Mikrosekunden oder Nanosekunden mit dem datetime-Datentyp verwendet.	Der Wert wird vor der Konvertierung in Mikro- oder Nanosekunden nicht mehr auf Millisekundenebene abgeschnitten.	`DECLARE @dt DATETIME = '01-01-1900 00:00:00.003'; SELECT DATEPART(MICROSECOND, @dt);`	3000	3333
`DATEDIFF` , die die Option Mikrosekunden oder Nanosekunden mit dem datetime-Datentyp verwendet.	Der Wert wird vor der Konvertierung in Mikro- oder Nanosekunden nicht mehr auf Millisekundenebene abgeschnitten.	`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
Vergleich zwischen datetime- und datetime2-Werten mit Werten ungleich null für Millisekunden.	Der Datetime-Wert wird nicht mehr auf Millisekundenebene abgeschnitten, wenn Sie einen Vergleich mit einem datetime2-Wert ausführen. Dies bedeutet, dass bestimmte Werte, die zuvor verglichen wurden, nicht mehr gleich sind.	`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 gleich	1900-01-01 00:00:00.0033333, 1900-01-01 00:00:00.003 ungleich
`ROUND` -Funktion, die den `float` Datentyp verwendet.	Die Rundungsergebnisse unterscheiden sich.	`SELECT ROUND(CAST (-0.4175 AS FLOAT), 3)`	-0.418	-0.417

Anhang B: Schritte zum Überprüfen und Aktualisieren persistenter Strukturen

Es wird empfohlen, festzustellen, ob die Datenbank über persistente Strukturen verfügt, die von den Änderungen am Kompatibilitätsgrad 130 betroffen sind, und alle betroffenen Strukturen neu zu erstellen.

Dies gilt nur für persistente Strukturen, die in der Datenbank mit einer älteren Version von SQL Server oder unter Verwendung eines Kompatibilitätsgrads unter 130 erstellt wurden. Zu den persistenten Strukturen, die möglicherweise betroffen sind, gehören die folgenden:

Tabellendaten, die Einschränkungen unterliegen CHECK
Persistente berechnete Spalten
Indizes, die berechnete Spalten im Schlüssel oder eingeschlossene Spalten verwenden
Gefilterte Indizes
Indizierte Sichten

Führen Sie in diesem Fall das folgende Verfahren aus.

Schritt 1: Überprüfen des Datenbank-Kompatibilitätsgrads

Überprüfen Sie den Kompatibilitätsgrad Ihrer Datenbank mithilfe des unter Anzeigen oder Ändern des Kompatibilitätsgrads einer Datenbank dokumentierten Prozedur.
Wenn der Datenbank-Kompatibilitätsgrad niedriger als 130 ist, empfehlen wir Ihnen, die in Schritt 2 beschriebene Überprüfung durchzuführen, bevor Sie den Kompatibilitätsgrad auf 130 erhöhen.

Schritt 2: Identifizieren betroffener persistenter Strukturen

Ermitteln Sie, ob die Datenbank persistente Strukturen enthält, die von der verbesserten Genauigkeit und Konvertierungslogik im Kompatibilitätsgrad 130 betroffen sind, auf eine der folgenden Arten:

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, der alle Strukturen in der Datenbank überprüft.
DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, der die Strukturen im Zusammenhang mit einer einzelnen Tabelle überprüft.

Die Option WITH EXTENDED_LOGICAL_CHECKS ist erforderlich, um sicherzustellen, dass die persistenten Werte mit berechneten Werten verglichen werden, und um Fälle zu kennzeichnen, in denen es einen Unterschied gibt. Da diese Überprüfungen umfangreich sind, ist die Laufzeit von DBCC Anweisungen, die diese Option verwenden, länger als die Ausführung DBCC von Anweisungen ohne die Option. Daher empfiehlt es sich, für große Datenbanken die Verwendung einzelner Tabellen zu verwenden DBCC CHECKTABLE .

DBCC CHECKCONSTRAINTS kann verwendet werden, um Einschränkungen zu überprüfen CHECK . Diese Anweisung kann entweder auf Datenbank- oder Tabellenebene verwendet werden.

DBCC CHECK -Anweisungen sollten aufgrund der potenziellen Auswirkungen der Überprüfungen auf die Onlineworkload immer während eines Wartungsfensters ausgeführt werden.

Validierung auf Datenbankebene

Die Validierung auf Datenbankebene eignet sich für kleine und mittlere Datenbanken. Verwenden Sie die Überprüfung auf Tabellenebene für große Datenbanken.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS wird verwendet, um alle persistenten Strukturen in der Datenbank zu überprüfen.

DBCC CHECKCONSTRAINTS wird verwendet, um alle CHECK Einschränkungen in der Datenbank zu überprüfen.

DBCC CHECKCONSTRAINTS wird verwendet, um die Integrität von Einschränkungen zu überprüfen. Verwenden Sie das folgende Skript, um die Datenbank zu überprüfen:

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

Die Verwendung des Ablaufverfolgungsflags stellt sicher, dass die Überprüfungen mithilfe der verbesserten Genauigkeit und Konvertierungslogik im Kompatibilitätsgrad 130 durchgeführt werden, sodass die richtige Konvertierungssemantik auch dann erzwungen wird, wenn die Datenbank einen niedrigeren Kompatibilitätsgrad aufweist.

Wenn die CHECKCONSTRAINTS Anweisung abgeschlossen ist und kein Resultset zurückgibt, ist keine zusätzliche Aktion erforderlich.

Wenn die Anweisung ein Resultset zurückgibt, weist jede Zeile in den Ergebnissen auf einen Verstoß gegen eine Einschränkung hin und enthält auch die Werte, die gegen die Einschränkung verstoßen.

Speichern Sie die Namen der Tabellen und Einschränkungen zusammen mit den Werten, die den verletzten Fehler verursacht haben (die WHERE Spalte im Resultset).

Das folgende Beispiel zeigt eine Tabelle mit einer CHECK Einschränkung und eine einzelne Zeile, die die Einschränkung unter niedrigeren Kompatibilitätsgraden erfüllt, aber die Einschränkung unter Kompatibilitätsgrad 130 verletzt.

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

Der CHECKCONSTRAINT Befehl gibt die folgenden Ergebnisse zurück.

Tabelle	Einschränkung	Dabei gilt:
[dbo]. [Tabelle1]	[chk1]	[c2] = '1900-01-01 00:00:00.997' AND [c3] = '1900-01-01 00:00:01.000' AND [c4] = '3'

Dieses Ergebnis gibt an, dass die Einschränkung [chk1] für die Kombination von Spaltenwerten im "Where" verletzt wird.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS überprüft alle persistenten Strukturen in der Datenbank. Dies ist die bequemste Option, da eine einzelne Anweisung alle Strukturen in der Datenbank überprüft. Diese Option eignet sich jedoch aufgrund der erwarteten Laufzeit der Anweisung nicht für große Datenbanken.

Verwenden Sie das folgende Skript, um die gesamte Datenbank zu überprüfen:

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

Wenn die CHECKDB Anweisung erfolgreich abgeschlossen wurde, ist keine zusätzliche Aktion erforderlich.

Wenn die Anweisung mit Fehlern abgeschlossen wurde, führen Sie die folgenden Schritte aus:

Speichern Sie die Ergebnisse der Ausführung der DBCC Anweisung im Nachrichtenbereich in SQL Server Management Studio (SSMS) in einer Datei.
Überprüfen Sie, ob alle gemeldeten Fehler im Zusammenhang mit persistenten Strukturen stehen.

Tabelle 1: Persistente Strukturen und entsprechende Fehlermeldungen für Inkonsistenzen

Betroffener Strukturtyp	Beobachtete Fehlermeldungen	Notieren Sie sich
Persistente berechnete Spalten	Msg 2537, Ebene 16 Tabellenfehler: Objekt-ID <object_id> , Index-ID <index_id> , . Fehler bei der Datensatzüberprüfung (gültige berechnete Spalte). Die Werte sind .	objekt-ID <object_id> und Index-ID <index_id>
Indizes, die auf berechnete Spalten im Schlüssel oder eingeschlossene Spalten verweisen Gefilterte Indizes	Msg 8951 Tabellenfehler: Tabelle "<table_name>" (ID <object_id>). Datenzeile enthält keine übereinstimmende Indexzeile im Index "<index_name>" (ID <index_id>) Und/oder Msg 8952 Tabellenfehler: Tabelle "<table_name>" (ID <table_name>). Die Indexzeile im Index "" (ID <index_id>) stimmt mit keiner Datenzeile überein. Darüber hinaus können sekundäre Fehler 8955 und/oder 8956 vorliegen. Dies enthält Details zu den genauen betroffenen Zeilen. Diese können bei dieser Übung ignoriert werden.	objekt-ID <object_id> und Index-ID <index_id>
Indizierte Sichten	Msg 8908 Die indizierte Sicht "<view_name>" (Objekt-ID <object_id>) enthält nicht alle Zeilen, die die Sichtdefinition erzeugt. And/oder Msg 8907 Die indizierte Sicht "<view_name>" (Objekt-ID <object_id>) enthält Zeilen, die nicht von der Ansichtsdefinition erstellt wurden.	objekt-ID <object_id>

Nachdem Sie die Überprüfung auf Datenbankebene abgeschlossen haben, fahren Sie mit Schritt 3 fort.

Überprüfung auf Objektebene

Bei größeren Datenbanken ist es hilfreich, Strukturen und Einschränkungen für jeweils eine Tabelle oder Sicht zu überprüfen, um die Größe von Wartungsfenstern zu reduzieren oder die erweiterten logischen Überprüfungen nur auf potenziell betroffene Objekte zu beschränken.

Verwenden Sie die Abfragen im Abschnitt "Anhang C ", um potenziell betroffene Tabellen zu identifizieren. Das Skript im Abschnitt "Anhang D" kann verwendet werden, um Einschränkungen und CHECKCONSTRAINTS basierend auf den abfragen zu generierenCHECKTABLE, die im Abschnitt "Anhang C" aufgeführt sind.

DBCC CHECKCONSTRAINTS

Verwenden Sie das folgende Skript, um die Einschränkungen im Zusammenhang mit einer einzelnen Tabelle oder Sicht zu überprüfen:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

Die Verwendung des Ablaufverfolgungsflags stellt sicher, dass die Überprüfungen mithilfe der verbesserten Genauigkeit und Konvertierungslogik im Kompatibilitätsgrad 130 durchgeführt werden, sodass die verbesserte Semantik auch dann erzwungen wird, wenn die Datenbank einen niedrigeren Kompatibilitätsgrad aufweist.

Wenn die CHECKCONSTRAINTS Anweisung abgeschlossen ist und kein Resultset zurückgibt, ist keine zusätzliche Aktion erforderlich.

Wenn die Anweisung ein Resultset zurückgibt, weist jede Zeile in den Ergebnissen auf einen Verstoß gegen eine Einschränkung hin und stellt auch die Werte bereit, die die Einschränkung verletzen.

Speichern Sie die Namen der Tabellen und Einschränkungen zusammen mit den Werten, die den verletzten Fehler verursacht haben (die WHERE Spalte im Resultset).

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

Verwenden Sie das folgende Skript, um die persistenten Strukturen im Zusammenhang mit einer einzelnen Tabelle oder Sicht zu überprüfen:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

Wenn die CHECKTABLE Anweisung erfolgreich abgeschlossen wurde, ist keine zusätzliche Aktion erforderlich.

Wenn die Anweisung mit Fehlern abgeschlossen wurde, führen Sie die folgenden Schritte aus:

Speichern Sie die Ergebnisse der Ausführung der DBCC Anweisung im Nachrichtenbereich in SSMS in einer Datei.
Überprüfen Sie, ob alle gemeldeten Fehler im Zusammenhang mit persistenten Strukturen stehen, wie in Tabelle 1 aufgeführt.
Fahren Sie nach Abschluss der Überprüfung auf Tabellenebene mit Schritt 3 fort.

Schritt 3: Upgrade auf Kompatibilitätsgrad 130

Wenn der Kompatibilitätsgrad der Datenbank bereits 130 beträgt, können Sie diesen Schritt überspringen.

Der Kompatibilitätsgrad der Datenbank kann mithilfe des folgenden Skripts in 130 geändert werden:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Hinweis

Da es Änderungen am Abfrageoptimierer unter Kompatibilitätsgrad 130 gibt, empfiehlt es sich, den Abfragespeicher zu aktivieren, bevor Sie den Kompatibilitätsgrad ändern. Weitere Informationen finden Sie im Abschnitt Beibehalten der Leistungsstabilität während des Upgrades auf neuere SQL Server in Abfragespeicher Verwendungsszenarien.

Schritt 4: Aktualisieren persistenter Strukturen

Wenn während der überprüfung in Schritt 2 keine Inkonsistenzen gefunden wurden, sind Sie mit dem Upgrade fertig und können diesen Schritt überspringen. Wenn in Schritt 2 Inkonsistenzen gefunden wurden, sind zusätzliche Aktionen erforderlich, um die Inkonsistenzen aus der Datenbank zu entfernen. Welche Aktionen erforderlich sind, hängt von der Art der betroffenen Struktur ab.

Wichtig

Führen Sie die Reparaturaktionen in diesem Schritt erst aus, nachdem der Datenbank-Kompatibilitätsgrad in 130 geändert wurde.

Sichern Ihrer Datenbank (oder Datenbanken)

Es wird empfohlen, dass Sie eine vollständige Datenbanksicherung durchführen, bevor Sie eine der Im folgenden Abschnitt beschriebenen Aktionen ausführen. Wenn Sie Azure SQL-Datenbank verwenden, müssen Sie keine Sicherung selbst erstellen. Sie können jederzeit die Point-in-Time-Wiederherstellungsfunktion verwenden, um in der Zeit zurückzugehen, falls mit einem der Updates etwas schief geht.

CHECK-Einschränkungen

Zum Korrigieren von CHECK Einschränkungsverletzungen müssen entweder die Daten in der Tabelle oder die CHECK Einschränkung selbst geändert werden.

Aus dem Namen der Einschränkung (in Schritt 2 abgerufen) können Sie die Einschränkungsdefinition wie folgt abrufen:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Um die betroffenen Tabellenzeilen zu untersuchen, können Sie die Where-Informationen verwenden, die zuvor von der DBCC CHECKCONSTRAINTS -Anweisung zurückgegeben wurden:

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

Sie müssen entweder die betroffenen Zeilen aktualisieren oder die Einschränkungsdefinition ändern, um sicherzustellen, dass die Einschränkung nicht verletzt wird.

Aktualisieren von Tabellendaten

Es gibt keine feste Regel, die besagt, wie die Daten aktualisiert werden sollen. Im Allgemeinen führen Sie für jede von zurückgegebene DBCC CHECKCONSTRAINTSWhere-Anweisung die folgende Update-Anweisung aus:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Betrachten Sie die folgende Beispieltabelle mit einer Einschränkung und einer Zeile, die gegen die Einschränkung im Kompatibilitätsgrad 130 verstößt:

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 diesem Beispiel ist die Einschränkung einfach. Die Spalte c4 muss gleich einem Ausdruck sein, der und c3umfasstc2. Um die Tabelle zu aktualisieren, weisen Sie diesen Wert zu 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

Beachten Sie, dass die WHERE in der update-Anweisung verwendete Klausel den von DBCC CHECKCONSTRAINTSzurückgegebenen Where-Informationen entspricht.

Aktualisieren der CHECK-Einschränkung

Um eine CHECK Einschränkung zu ändern, müssen Sie sie löschen und neu erstellen. Es wird empfohlen, beides in derselben Transaktion auszuführen, nur für den Fall, dass Probleme mit der aktualisierten Einschränkungsdefinition auftreten. Sie können die folgende Transact-SQL-Instanz verwenden:

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

Persistente berechnete Spalten

Die einfachste Möglichkeit zum Aktualisieren persistenter berechneter Spalten besteht darin, eine der Spalten zu aktualisieren, auf die von der berechneten Spalte verwiesen wird. Der neue Wert für die Spalte kann mit dem alten Wert identisch sein, sodass der Vorgang keine Benutzerdaten ändert.

Führen Sie diese Schritte für alle object_id Inkonsistenzen in berechneten Spalten aus, die Sie in Schritt 2 notiert haben.

Identifizieren von berechneten Spalten:

Führen Sie die folgende Abfrage aus, um den Tabellennamen und die Namen der persistenten berechneten Spalten für die notierte object_idabzurufen:

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

Identifizieren von Spalten, auf die verwiesen wird:

Führen Sie die folgende Abfrage aus, um Spalten zu identifizieren, auf die von der berechneten Spalte verwiesen wird. Notieren Sie sich einen der Spaltennamen, auf die verwiesen wird:

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

Führen Sie eine UPDATE Anweisung mit einer der Spalten aus, auf die verwiesen wird, um eine Aktualisierung der berechneten Spalte auszulösen:
- Die folgende Anweisung löst eine Aktualisierung der Spalte aus, auf die von der berechneten Spalte verwiesen wird, und löst außerdem eine Aktualisierung der berechneten Spalte aus.
```
UPDATE [schema_name].[table_name]
SET referenced_column_name=ISNULL(referenced_column_name, referenced_column_name)
```
- Der ISNULL Ausdruck in der Anweisung ist so gestaltet, dass der Wert der ursprünglichen Spalte nicht geändert wird, während gleichzeitig sichergestellt wird, dass die berechnete Spalte mithilfe der Auswertungslogik für den Db-Kompatibilitätsgrad 130 aktualisiert wird.
- Beachten Sie, dass Sie bei sehr großen Tabellen möglicherweise nicht alle Zeilen in einer einzelnen Transaktion aktualisieren möchten. In einem solchen Fall können Sie das Update in Batches ausführen, indem Sie der update-Anweisung eine WHERE -Klausel hinzufügen, die einen Zeilenbereich identifiziert, z. B. basierend auf dem Primärschlüssel.

Identifizieren Sie Indizes, die auf die berechnete Spalte verweisen.

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

Diese Abfrage identifiziert alle Indizes, die auf die persistente berechnete Spalte verweisen. Ein solcher Index muss neu erstellt werden. Führen Sie dazu die Schritte im folgenden Abschnitt aus.

Indizes, gefilterte Indizes und indizierte Sichten

Inkonsistenzen in Indizes entsprechen den Fehlern 8951 und 8952 (für Tabellen) oder 8907 und 8908 (für Ansichten) in der DBCC CHECK Ausgabe von Schritt 2.

Führen Sie DBCC CHECKTABLE mit REPAIR_REBUILDaus, um diese Inkonsistenzen zu beheben. Dadurch werden die Indizes ohne Datenverlust repariert. Die Datenbank muss sich jedoch im Einzelbenutzermodus befinden und ist daher für andere Benutzer während der Reparatur nicht verfügbar.

Sie können betroffene Indizes auch manuell neu erstellen. Diese Option sollte verwendet werden, wenn die Workload nicht offline geschaltet werden kann, da die Indexneuerstellung als ONLINE-Vorgang (in unterstützten Editionen von SQL Server) ausgeführt werden kann.

Neuerstellen von Indizes

Wenn das Festlegen der Datenbank im Einzelbenutzermodus keine Option ist, können Sie Indizes einzeln mit neu ALTER INDEX REBUILDerstellen, indem Sie verwenden, für jeden in Schritt 2 identifizierten Index.

Verwenden Sie die folgende Abfrage, um die Tabellen- und Indexnamen für eine angegebene object_id und index_idabzurufen.

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

Verwenden Sie die folgende Anweisung, um den Index neu zu erstellen:

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

Hinweis

Wenn Sie Standard-, Web- oder Express-Editionen verwenden, wird das Erstellen von Onlineindizes nicht unterstützt. Daher muss die Option WITH (ONLINE=ON) aus der ALTER INDEX -Anweisung entfernt werden.

Das folgende Beispiel zeigt die Neuerstellung eines gefilterten Indexes:

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

Wenn Sie über regelmäßige Wartungspläne verfügen, empfehlen wir Ihnen, diese Indexneuerstellung als Teil Ihrer geplanten Wartung einzubeziehen.

Reparieren mithilfe von DBCC

Führen Sie für jede (object_id) im Zusammenhang mit einem Index mit Inkonsistenzen, die Sie in Schritt 2 notiert haben, das folgende Skript aus, um die Reparatur durchzuführen. Dieses Skript legt die Datenbank im Einzelbenutzermodus für den Reparaturvorgang fest. Im schlimmsten Fall führt die Reparatur eine vollständige Indexneuerstellung durch.

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

Anhang C: Abfragen zum Identifizieren von Kandidatentabellen

Die folgenden Skripts identifizieren Kandidatentabellen, die Sie möglicherweise mithilfe DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKSvon überprüfen möchten, basierend auf dem Vorhandensein von persistenten Strukturen und Einschränkungen, die Datentypen verwenden, die von den Verbesserungen des Kompatibilitätsgrads 130 betroffen sind.

Die folgenden Abfragen enthalten Details zu den Tabellen und potenziell betroffenen Strukturen, die eine zusätzliche Überprüfung erfordern.

Indizierte Sichten

Die folgende Abfrage gibt alle indizierten Sichten zurück, die auf Spalten verweisen, indem betroffene Datentypen oder eine der betroffenen integrierten Funktionen verwendet wird:

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

Persistente berechnete Spalten

Die folgende Abfrage gibt alle Tabellen mit berechneten Spalten zurück, die auf andere Spalten verweisen, indem betroffene Datentypen oder eine der betroffenen integrierten Funktionen verwendet wird, wobei entweder die Spalte beibehalten oder aus einem Index referenziert wird.

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)

)

Gefilterte Indizes

Die folgende Abfrage gibt alle Tabellen mit gefilterten Indizes zurück, die auf Spalten in der Filterbedingung verweisen, die betroffene Datentypen aufweisen:

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

)

Check-Einschränkungen

Die folgende Abfrage listet alle Tabellen mit Check-Einschränkungen auf, die auf betroffene Datentypen oder integrierte Funktionen verweisen:

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

Anhang D: Skript zum Erstellen von CHECK*-Anweisungen

Das folgende Skript kombiniert die Abfragen aus dem vorherigen Anhang und vereinfacht die Ergebnisse, indem eine Liste von Tabellen und Sichten in Form von CHECKCONSTRAINTS - und CHECKTABLE -Anweisungen dargestellt wird.

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

Freigeben über

SQL Server und Azure SQL Datenbankverbesserungen bei der Behandlung einiger Datentypen und ungewöhnlicher Vorgänge

Überprüfungsschritte während eines Upgrades auf den Datenbank-Kompatibilitätsgrad

Anhang A: Änderungen am Kompatibilitätsgrad 130

Anhang B: Schritte zum Überprüfen und Aktualisieren persistenter Strukturen

Anhang C: Abfragen zum Identifizieren von Kandidatentabellen

Anhang D: Skript zum Erstellen von CHECK*-Anweisungen

Feedback

Zusätzliche Ressourcen