Verbeteringen in SQL Server en Azure SQL Database bij het verwerken van bepaalde gegevenstypen en ongebruikelijke bewerkingen

In dit artikel wordt beschreven hoe persistente structuren in uw SQL Server-database kunnen worden gevalideerd als onderdeel van het compatibiliteitsniveau van de upgrade en hoe eventuele betrokken structuren opnieuw kunnen worden opgebouwd nadat u het compatibiliteitsniveau hebt bijgewerkt.

Oorspronkelijke productversie: SQL Server 2017, SQL Server 2016
Oorspronkelijk KB-nummer: 4010261

De database-engine in Microsoft SQL Server 2016 en Azure SQL Database bevat verbeteringen in gegevenstypeconversies en verschillende andere bewerkingen. De meeste van deze verbeteringen bieden meer precisie wanneer u met typen drijvende komma en ook met klassieke datum/tijd-typen werkt.

Deze verbeteringen zijn allemaal beschikbaar wanneer u een databasecompatibiliteitsniveau van ten minste 130 gebruikt. Dit betekent dat u voor sommige (meestal ongebruikelijke) expressies verschillende resultaten kunt zien voor sommige invoerwaarden nadat u de database hebt bijgewerkt naar compatibiliteitsniveau 130 of hoger. Deze resultaten kunnen worden weerspiegeld in:

• persistente structuren in de database
• opgenomen tabelgegevens die onderhevig zijn aan CHECK beperkingen
• persistente berekende kolommen
• indexen die verwijzen naar berekende kolommen
• gefilterde indexen en geïndexeerde weergaven.

Als u een database hebt die is gemaakt in een eerdere versie van SQL Server, raden we u aan extra validatie uit te voeren nadat u een upgrade hebt uitgevoerd naar SQL Server 2016 of hoger en voordat u het compatibiliteitsniveau van de database wijzigt.

Als u merkt dat een van de persistente structuren in uw database wordt beïnvloed door deze wijzigingen, raden we u aan de betrokken structuren opnieuw te bouwen nadat u het compatibiliteitsniveau van de database hebt bijgewerkt. Als u dit doet, profiteert u van deze verbeteringen in SQL Server 2016 of hoger.

In dit artikel wordt beschreven hoe persistente structuren in uw database kunnen worden gevalideerd als onderdeel van de upgrade naar compatibiliteitsniveau 130 of hoger en hoe betrokken structuren opnieuw kunnen worden opgebouwd nadat u het compatibiliteitsniveau hebt gewijzigd.

Validatiestappen tijdens een upgrade naar databasecompatibiliteitsniveau

Vanaf SQL Server 2016 bevatten zowel SQL Server als Azure SQL Database verbeteringen in de precisie van de volgende bewerkingen:

• Ongebruikelijke conversies van gegevenstypen. Deze omvatten het volgende:
  • Float/integer naar/from datetime/smalldatetime
  • Real/float to/from numeriek/money/smallmoney
  • Zweven naar echt
• Sommige gevallen van DATEPART/DATEDIFF en DEGREES
• CONVERT die gebruikmaakt van een NULL stijl

Als u deze verbeteringen wilt gebruiken voor de evaluatie van expressies in uw toepassing, wijzigt u het compatibiliteitsniveau van uw databases in 130 (voor SQL Server 2016) of 140 (voor SQL Server 2017 en Azure SQL Database). Zie de sectie Bijlage A voor meer informatie over alle wijzigingen en enkele voorbeelden die de wijzigingen weergeven.

De volgende structuren in de database kunnen de resultaten van een expressie behouden:

• Tabelgegevens die onderhevig zijn aan CHECK beperkingen
• Persistente berekende kolommen
• Indexen die gebruikmaken van berekende kolommen in de sleutel of opgenomen kolommen
• Gefilterde indexen
• Geïndexeerde weergaven

Bekijk het volgende scenario:

• U hebt een database die is gemaakt met een eerdere versie van SQL Server of die al is gemaakt in SQL Server 2016 of een latere versie, maar op compatibiliteitsniveau 120 of een lager niveau.

• U gebruikt expressies waarvan de precisie is verbeterd als onderdeel van de definitie van persistente structuren in uw database.

In dit scenario hebt u mogelijk permanente structuren die worden beïnvloed door de verbeteringen in precisie die worden geïmplementeerd met behulp van compatibiliteitsniveau 130 of hoger. Als dit het geval is, raden we u aan de persistente structuren te valideren en een structuur te herbouwen die wordt beïnvloed.

Als u structuren hebt beïnvloed en u deze niet opnieuw opbouwt nadat u het compatibiliteitsniveau hebt gewijzigd, kunnen er iets andere queryresultaten optreden. De resultaten zijn afhankelijk van of een bepaalde index, berekende kolom of weergave wordt gebruikt en of gegevens in een tabel kunnen worden beschouwd als een schending van een beperking.

Traceringsvlag 139 wordt ondersteund vanaf SQL Server 2016 CU3 en SQL Server 2016 SP1.

Voer de volgende stappen uit om het compatibiliteitsniveau te upgraden:

1. Voer de validatie uit om eventuele betrokken persistente structuren te identificeren:
   1. Schakel traceringsvlag 139 in door uit te voeren DBCC TRACEON(139, -1).
   2. Uitvoeren DBCC CHECKDB/TABLE en CHECKCONSTRAINTS opdrachten.
   3. Schakel traceringsvlag 139 uit door uit te voeren DBCC TRACEOFF(139, -1).
2. Wijzig het databasecompatibiliteitsniveau in 130 (voor SQL Server 2016) of 140 (voor SQL Server 2017 en Azure SQL Database).
3. Bouw alle structuren die u hebt geïdentificeerd in stap 1.

• Bijlage A bevat een gedetailleerde lijst met alle precisieverbeteringen en geeft een voorbeeld voor elk.

• Bijlage B bevat een gedetailleerd stapsgewijze procedure om validatie uit te voeren en eventuele betrokken structuren opnieuw te bouwen.

• Bijlage C en Bijlage D bevatten scripts om mogelijk beïnvloede objecten in de database vast te stellen. Daarom kunt u uw validaties instellen en bijbehorende scripts genereren om de controles uit te voeren. Als u het gemakkelijkst wilt bepalen of persistente structuren in uw databases worden beïnvloed door de precisieverbeteringen in compatibiliteitsniveau 130, voert u het script uit in bijlage D om de juiste validatiecontroles te genereren en voert u dit script uit om validatie uit te voeren.

Bijlage A: wijzigingen in compatibiliteitsniveau 130

Deze bijlage bevat gedetailleerde lijsten met de verbeteringen in de evaluatie van expressies in compatibiliteitsniveau 130. Elke wijziging bevat een bijbehorende voorbeeldquery. De query's kunnen worden gebruikt om de verschillen weer te geven tussen het uitvoeren in een database die gebruikmaakt van een compatibiliteitsniveau vóór 130 in vergelijking met een database die gebruikmaakt van compatibiliteitsniveau 130.

De volgende tabellen bevatten conversies van gegevenstypen en aanvullende bewerkingen.

Conversies van gegevenstypen

Bewerking

Bijlage B: Stappen voor het verifiëren en bijwerken van persistente structuren

U wordt aangeraden te bepalen of de database persistente structuren heeft die worden beïnvloed door de wijzigingen in compatibiliteitsniveau 130 en dat u eventuele betrokken structuren opnieuw bouwt.

Dit geldt alleen voor persistente structuren die zijn gemaakt in de database op een oudere versie van SQL Server of met behulp van een compatibiliteitsniveau dat lager is dan 130. De persistente structuren die mogelijk worden beïnvloed, zijn onder andere:

• Tabelgegevens die onderhevig zijn aan CHECK beperkingen
• Persistente berekende kolommen
• Indexen die gebruikmaken van berekende kolommen in de sleutel of opgenomen kolommen
• Gefilterde indexen
• Geïndexeerde weergaven

Voer in deze situatie de volgende procedure uit.

Stap 1: Compatibiliteitsniveau van database controleren

1. Controleer het compatibiliteitsniveau van uw database met behulp van de procedure die wordt beschreven in Weergeven of het compatibiliteitsniveau van een database wijzigen.
2. Als het compatibiliteitsniveau van de database lager is dan 130, raden we u aan de validatie uit te voeren die wordt beschreven in stap 2 voordat u het compatibiliteitsniveau verhoogt tot 130.

Stap 2: Betrokken persistente structuren identificeren

Bepaal of de database persistente structuren bevat die worden beïnvloed door de verbeterde precisie- en conversielogica in compatibiliteitsniveau 130 op een van de volgende manieren:

• DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, waarmee alle structuren in de database worden gevalideerd.
• DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, waarmee de structuren met betrekking tot één tabel worden gevalideerd.

De optie WITH EXTENDED_LOGICAL_CHECKS is vereist om ervoor te zorgen dat de persistente waarden worden vergeleken met berekende waarden en om gevallen te markeren waarin er een verschil is. Omdat deze controles uitgebreid zijn, is de runtime van DBCC instructies die deze optie gebruiken langer dan actieve DBCC instructies zonder de optie. Daarom is de aanbeveling voor grote databases te gebruiken DBCC CHECKTABLE om afzonderlijke tabellen vast te stellen.

DBCC CHECKCONSTRAINTS kan worden gebruikt om beperkingen te valideren CHECK . Deze instructie kan worden gebruikt op database- of tabelniveau.

DBCC CHECK instructies moeten altijd worden uitgevoerd tijdens een onderhoudsvenster, vanwege de mogelijke impact van de controles op de onlineworkload.

Validatie op databaseniveau

Validatie op databaseniveau is geschikt voor kleine en middelgrote databases. Gebruik validatie op tabelniveau voor grote databases.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS wordt gebruikt om alle persistente structuren in de database te valideren.

DBCC CHECKCONSTRAINTS wordt gebruikt om alle CHECK beperkingen in de database te valideren.

DBCC CHECKCONSTRAINTS wordt gebruikt om de integriteit van beperkingen te valideren. Gebruik het volgende script om de database te valideren:

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

Het gebruik van de traceringsvlag zorgt ervoor dat de controles worden uitgevoerd met behulp van de verbeterde precisie- en conversielogica in compatibiliteitsniveau 130, waardoor de juiste conversiesemantiek wordt afgedwongen, zelfs wanneer de database een lager compatibiliteitsniveau heeft.

Als de CHECKCONSTRAINTS instructie is voltooid en geen resultatenset retourneert, is er geen extra actie nodig.

Als de instructie een resultatenset retourneert, geeft elke regel in de resultaten een schending van een beperking aan en bevat deze ook de waarden die de beperking schenden.

• Sla de namen van de tabellen en beperkingen op, samen met de waarden die de schending hebben veroorzaakt (de WHERE kolom in de resultatenset).

In het volgende voorbeeld ziet u een tabel met een CHECK beperking en één rij die voldoet aan de beperking onder lagere compatibiliteitsniveaus, maar die de beperking onder compatibiliteitsniveau 130 schendt.

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

De CHECKCONSTRAINT opdracht retourneert de volgende resultaten.

Dit resultaat geeft aan dat de beperking [chk1] wordt geschonden voor de combinatie van kolomwaarden in 'Waar'.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS valideert alle persistente structuren in de database. Dit is de handigste optie omdat met één instructie alle structuren in de database worden gevalideerd. Deze optie is echter niet geschikt voor grote databases vanwege de verwachte runtime van de instructie.

Gebruik het volgende script om de hele database te valideren:

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

Het gebruik van de traceringsvlag zorgt ervoor dat de controles worden uitgevoerd met behulp van de verbeterde precisie- en conversielogica in compatibiliteitsniveau 130, waardoor de juiste conversiesemantiek wordt afgedwongen, zelfs wanneer de database een lager compatibiliteitsniveau heeft.

Als de CHECKDB instructie is voltooid, is er geen extra actie nodig.

Als de instructie is voltooid met fouten, voert u de volgende stappen uit:

1. Sla de resultaten van de uitvoering van de DBCC instructie op in het berichtenvenster in SQL Server Management Studio (SSMS) naar een bestand.
2. Controleer of een van de gerapporteerde fouten betrekking heeft op persistente structuren

Tabel 1: Persistente structuren en bijbehorende foutberichten voor inconsistenties

Nadat u de validatie op databaseniveau hebt voltooid, gaat u naar stap 3.

Validatie op objectniveau

Voor grotere databases is het handig om structuren en beperkingen voor één tabel of één weergave tegelijk te valideren om de grootte van onderhoudsvensters te verminderen of om de uitgebreide logische controles alleen te beperken tot mogelijk beïnvloede objecten.

Gebruik de query's in de sectie Bijlage C om mogelijk betrokken tabellen te identificeren. Het script in de sectie Bijlage D kan worden gebruikt voor het genereren CHECKTABLE en CHECKCONSTRAINTS de beperkingen op basis van de query's die worden vermeld in de sectie Bijlage C .

DBCC CHECKCONSTRAINTS

Gebruik het volgende script om de beperkingen met betrekking tot één tabel of weergave te valideren:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

Het gebruik van de traceringsvlag zorgt ervoor dat de controles worden uitgevoerd met behulp van de verbeterde precisie- en conversielogica die zich in compatibiliteitsniveau 130 bevindt, waardoor de verbeterde semantiek wordt afgedwongen, zelfs wanneer de database een lager compatibiliteitsniveau heeft.

Als de CHECKCONSTRAINTS instructie is voltooid en geen resultatenset retourneert, is er geen extra actie nodig.

Als de instructie wel een resultatenset retourneert, geeft elke regel in de resultaten een schending van een beperking aan en geeft ook de waarden op die de beperking schenden.

Sla de namen van de tabellen en beperkingen op, samen met de waarden die de schending hebben veroorzaakt (de WHERE kolom in de resultatenset).

DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS

Gebruik het volgende script om de persistente structuren te valideren die betrekking hebben op één tabel of weergave:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

Als de CHECKTABLE instructie is voltooid, is er geen extra actie nodig.

Als de instructie is voltooid met fouten, voert u de volgende stappen uit:

1. Sla de resultaten op van de uitvoering van de DBCC instructie, gevonden in het berichtenvenster in SSMS, in een bestand.
2. Controleer of een van de gerapporteerde fouten betrekking heeft op persistente structuren, zoals vermeld in tabel 1.
3. Nadat u de validatie op tabelniveau hebt voltooid, gaat u verder met stap 3.

Stap 3: Upgraden naar compatibiliteitsniveau 130

Als het compatibiliteitsniveau van de database al 130 is, kunt u deze stap overslaan.

Het compatibiliteitsniveau van de database kan worden gewijzigd in 130 met behulp van het volgende script:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Stap 4: Persistente structuren bijwerken

Als er geen inconsistenties zijn gevonden tijdens de validatie die in stap 2 is uitgevoerd, bent u klaar met de upgrade en kunt u deze stap overslaan. Als inconsistenties zijn gevonden in stap 2, zijn er aanvullende acties vereist om de inconsistenties uit de database te verwijderen. De vereiste acties zijn afhankelijk van het type structuur dat wordt beïnvloed.

Een back-up maken van uw database (of databases)

U wordt aangeraden een volledige databaseback-up te maken voordat u een van de acties uitvoert die in de volgende sectie worden beschreven. Als u Azure SQL Database gebruikt, hoeft u zelf geen back-up te maken; u kunt altijd de functionaliteit voor herstel naar een bepaald tijdstip gebruiken om terug te gaan in het geval er iets misgaat met een van de updates.

CHECK-beperkingen

Voor het corrigeren van schendingen van CHECK beperkingen is het wijzigen van de gegevens in de tabel of de CHECK beperking zelf vereist.

Met de naam van de beperking (verkregen in stap 2) kunt u de definitie van de beperking als volgt verkrijgen:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Als u de betrokken tabelrijen wilt controleren, kunt u de where-informatie gebruiken die eerder is geretourneerd door de DBCC CHECKCONSTRAINTS instructie:

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

U moet de betreffende rijen bijwerken of de definitie van de beperking wijzigen om ervoor te zorgen dat de beperking niet wordt geschonden.

Tabelgegevens bijwerken

Er is geen harde regel waarin wordt aangegeven hoe de gegevens moeten worden bijgewerkt. Over het algemeen voert u voor elke andere Where-instructie die wordt geretourneerd door DBCC CHECKCONSTRAINTSde volgende update-instructie uit:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Bekijk de volgende voorbeeldtabel met een beperking en een rij die de beperking in compatibiliteitsniveau 130 schendt:

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 dit voorbeeld is de beperking eenvoudig. De kolom c4 moet gelijk zijn aan een expressie die betrekking heeft op c2 en c3. Als u de tabel wilt bijwerken, wijst u deze waarde toe aan 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

U ziet dat de WHERE component die in de update-instructie wordt gebruikt, overeenkomt met de where-informatie die wordt geretourneerd door DBCC CHECKCONSTRAINTS.

Beperking CHECK bijwerken

Als u een CHECK beperking wilt wijzigen, moet u deze verwijderen en opnieuw maken. We raden u aan beide in dezelfde transactie uit te voeren, voor het geval er problemen zijn met de bijgewerkte beperkingsdefinitie. U kunt de volgende Transact-SQL gebruiken:

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 berekende kolommen

De eenvoudigste manier om permanente berekende kolommen bij te werken, is door een van de kolommen waarnaar wordt verwezen door de berekende kolom bij te werken. De nieuwe waarde voor de kolom kan hetzelfde zijn als de oude waarde, zodat de bewerking geen gebruikersgegevens wijzigt.

Volg deze stappen voor alle object_id gerelateerde inconsistenties in berekende kolommen die u in stap 2 hebt genoteerd.

1. Berekende kolommen identificeren:

   • Voer de volgende query uit om de tabelnaam en de namen van persistente berekende kolommen voor de genoteerde object_idop te halen:

     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. Kolommen waarnaar wordt verwezen identificeren:

• Voer de volgende query uit om kolommen te identificeren waarnaar wordt verwezen door de berekende kolom. Noteer een van de kolomnamen waarnaar wordt verwezen:

  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. Voer een UPDATE instructie uit met een van de kolommen waarnaar wordt verwezen om een update van de berekende kolom te activeren:

   • Met de volgende instructie wordt een update geactiveerd van de kolom waarnaar wordt verwezen door de berekende kolom en wordt ook een update van de berekende kolom geactiveerd.

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

   • De ISNULL expressie in de instructie wordt zodanig gemaakt dat de waarde van de oorspronkelijke kolom niet wordt gewijzigd, terwijl u er nog steeds voor zorgt dat de berekende kolom wordt bijgewerkt met de evaluatielogica van DB-compatibiliteitsniveau 130.

   • Houd er rekening mee dat u voor zeer grote tabellen mogelijk niet alle rijen in één transactie wilt bijwerken. In dat geval kunt u de update in batches uitvoeren door een WHERE component toe te voegen aan de update-instructie die een bereik van rijen identificeert, bijvoorbeeld op basis van de primaire sleutel.

2. Identificeer indexen die verwijzen naar de berekende kolom.

   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
   

Deze query identificeert alle indexen die verwijzen naar de persistente berekende kolom. Een dergelijke index moet opnieuw worden opgebouwd. Volg hiervoor de stappen in de volgende sectie.

Indexen, gefilterde indexen en geïndexeerde weergaven

Inconsistenties in indexen komen overeen met fouten 8951 en 8952 (voor tabellen) of 8907 en 8908 (voor weergaven) in de DBCC CHECK uitvoer van stap 2.

Als u deze inconsistenties wilt herstellen, voert DBCC CHECKTABLE u uit met REPAIR_REBUILD. Hiermee worden de indexenstructuren hersteld zonder gegevensverlies. De database moet zich echter in de modus voor één gebruiker bevinden en is daarom niet beschikbaar voor andere gebruikers tijdens het herstellen.

U kunt ook handmatig de betreffende indexen opnieuw opbouwen. Deze optie moet worden gebruikt als de workload niet offline kan worden gehaald, omdat het opnieuw opbouwen van de index kan worden uitgevoerd als een ONLINE-bewerking (in ondersteunde edities van SQL Server).

Indexen opnieuw samenstellen

Als het instellen van de database in de modus voor één gebruiker geen optie is, kunt u indexen afzonderlijk herbouwen met behulp van ALTER INDEX REBUILD, voor elke index die in stap 2 is geïdentificeerd.

Gebruik de volgende query om de tabel- en indexnamen voor een bepaalde object_id en 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

Gebruik de volgende instructie om de index opnieuw te bouwen:

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

In het volgende voorbeeld ziet u het opnieuw samenstellen van een gefilterde index:

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

Als u regelmatig onderhoudsplannen hebt, raden we u aan deze index opnieuw op te nemen als onderdeel van uw gepland onderhoud.

Herstellen met DBCC

Voor elke (object_id) met betrekking tot een index met inconsistenties die u in stap 2 hebt genoteerd, voert u het volgende script uit om het herstel uit te voeren. Met dit script wordt de database ingesteld in de modus voor één gebruiker voor de herstelbewerking. In het ergste geval voert de reparatie een volledige index opnieuw op.

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

Bijlage C: Query's om kandidaattabellen te identificeren

De volgende scripts identificeren kandidaattabellen die u mogelijk wilt valideren met behulp van DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, op basis van het bestaan van persistente structuren en beperkingen die gebruikmaken van gegevenstypen die worden beïnvloed door de verbeteringen in compatibiliteitsniveau 130.

De volgende set query's bevat details over de tabellen en mogelijk beïnvloede structuren waarvoor extra validatie is vereist.

Geïndexeerde weergaven

De volgende query retourneert alle geïndexeerde weergaven die verwijzen naar kolommen met behulp van betrokken gegevenstypen of met behulp van een van de betrokken ingebouwde functies:

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 berekende kolommen

De volgende query retourneert alle tabellen met berekende kolommen die verwijzen naar andere kolommen met behulp van betrokken gegevenstypen of met behulp van een van de betrokken ingebouwde functies, waarbij de kolom wordt behouden of waarnaar wordt verwezen vanuit een index.

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)

)

Gefilterde indexen

De volgende query retourneert alle tabellen met gefilterde indexen die verwijzen naar kolommen in de filtervoorwaarde die van invloed zijn op gegevenstypen:

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

)

Beperkingen controleren

De volgende query bevat alle tabellen met controlebeperkingen die verwijzen naar betrokken gegevenstypen of ingebouwde functies:

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

Bijlage D: Script voor het maken van CHECK*-instructies

Het volgende script combineert de query's uit de vorige bijlage en vereenvoudigt de resultaten door een lijst met tabellen en weergaven te presenteren in de vorm van CHECKCONSTRAINTS en CHECKTABLE instructies.

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