Förbättringar av SQL Server och Azure SQL Database i hanteringen av vissa datatyper och ovanliga åtgärder

Den här artikeln beskriver hur beständiga strukturer i SQL Server-databasen kan verifieras som en del av uppgraderingskompatibilitetsnivån och hur eventuella berörda strukturer kan återskapas när du har uppgraderat kompatibilitetsnivån.

Ursprunglig produktversion: SQL Server 2017, SQL Server 2016
Ursprungligt KB-nummer: 4010261

Databasmotorn i Microsoft SQL Server 2016 och Azure SQL Database innehåller förbättringar i datatypkonverteringar och flera andra åtgärder. De flesta av dessa förbättringar ger ökad precision när du arbetar med flyttalstyper och även med klassiska datetime-typer.

Dessa förbättringar är alla tillgängliga när du använder en databaskompatibilitetsnivå på minst 130. Det innebär att för vissa (mestadels ovanliga) uttryck kan du se olika resultat för vissa indatavärden när du har uppgraderat databasen till kompatibilitetsnivå 130 eller en högre inställning. Dessa resultat kan återspeglas i:

• beständiga strukturer i databasen
• inkluderade tabelldata som omfattas av CHECK begränsningar
• beständiga beräknade kolumner
• index som refererar till beräknade kolumner
• filtrerade index och indexerade vyer.

Om du har en databas som har skapats i en tidigare version av SQL Server rekommenderar vi att du utför ytterligare verifiering när du har uppgraderat till SQL Server 2016 eller senare och innan du ändrar databasens kompatibilitetsnivå.

Om du upptäcker att någon av de beständiga strukturerna i databasen påverkas av dessa ändringar rekommenderar vi att du återskapar berörda strukturer när du har uppgraderat databasens kompatibilitetsnivå. Genom att göra detta kan du dra nytta av dessa förbättringar i SQL Server 2016 eller senare.

Den här artikeln beskriver hur beständiga strukturer i databasen kan verifieras som en del av uppgraderingen till kompatibilitetsnivå 130 eller en högre inställning och hur eventuella berörda strukturer kan återskapas när du har ändrat kompatibilitetsnivån.

Verifieringssteg under en uppgradering till databaskompatibilitetsnivå

Från och med SQL Server 2016 innehåller både SQL Server och Azure SQL Database förbättringar av precisionen i följande åtgärder:

• Ovanliga datatypskonverteringar. Dessa inkluderar följande:
  • Flyttal/heltal till/från datetime/smalldatetime
  • Real/float to/from numerisk/money/smallmoney
  • Flytta till verkliga
• Vissa fall av DATEPART/DATEDIFF och DEGREES
• CONVERT som använder ett NULL format

Om du vill använda dessa förbättringar av uttrycksutvärderingen i ditt program ändrar du kompatibilitetsnivån för dina databaser till 130 (för SQL Server 2016) eller 140 (för SQL Server 2017 och Azure SQL Database). Mer information om alla ändringar och några exempel som visar ändringarna finns i avsnittet Bilaga A .

Följande strukturer i databasen kan bevara resultatet av ett uttryck:

• Tabelldata som omfattas av CHECK begränsningar
• Beständiga beräknade kolumner
• Index som använder beräknade kolumner i nyckeln eller inkluderade kolumner
• Filtrerade index
• Indexerade vyer

Föreställ dig följande scenario:

• Du har en databas som har skapats av en tidigare version av SQL Server, eller som redan har skapats i SQL Server 2016 eller en senare version men på kompatibilitetsnivå 120 eller tidigare.

• Du använder alla uttryck vars precision har förbättrats som en del av definitionen av beständiga strukturer i databasen.

I det här scenariot kan du ha bevarade strukturer som påverkas av de förbättringar av precisionen som implementeras med hjälp av kompatibilitetsnivå 130 eller senare. Om så är fallet rekommenderar vi att du verifierar de beständiga strukturerna och återskapar alla strukturer som påverkas.

Om du har påverkat strukturer och inte återskapar dem när du har ändrat kompatibilitetsnivån kan det uppstå något olika frågeresultat. Resultatet beror på om ett visst index, en beräknad kolumn eller en vy används och om data i en tabell kan betraktas som ett brott mot en begränsning.

Spårningsflagga 139 stöds från och med SQL Server 2016 CU3 och SQL Server 2016 SP1.

Följ dessa steg för att uppgradera kompatibilitetsnivån:

1. Utför validering för att identifiera eventuella berörda bevarade strukturer:
   1. Aktivera spårningsflagga 139 genom att köra DBCC TRACEON(139, -1).
   2. Kör DBCC CHECKDB/TABLE och CHECKCONSTRAINTS kommandon.
   3. Inaktivera spårningsflagga 139 genom att köra DBCC TRACEOFF(139, -1).
2. Ändra databaskompatibilitetsnivån till 130 (för SQL Server 2016) eller 140 (för SQL Server 2017 och Azure SQL Database).
3. Återskapa alla strukturer som du identifierade i steg 1.

• Bilaga A innehåller en detaljerad lista över alla precisionsförbättringar och ger ett exempel för var och en.

• Bilaga B innehåller en detaljerad steg-för-steg-process för att utföra validering och återskapa eventuella berörda strukturer.

• Bilaga C och bilaga D innehåller skript som hjälper dig att hitta potentiellt påverkade objekt i databasen. Därför kan du begränsa valideringarna och generera motsvarande skript för att köra kontrollerna. För att enkelt avgöra om några bevarade strukturer i dina databaser påverkas av precisionsförbättringarna på kompatibilitetsnivå 130 kör du skriptet i bilaga D för att generera rätt valideringskontroller och kör sedan det här skriptet för att utföra validering.

Bilaga A: Ändringar i kompatibilitetsnivå 130

Den här bilagan innehåller detaljerade listor över förbättringar av uttrycksutvärderingen på kompatibilitetsnivå 130. Varje ändring innehåller en associerad exempelfråga. Frågorna kan användas för att visa skillnaderna mellan körning i en databas som använder en kompatibilitetsnivå före 130 jämfört med en databas som använder kompatibilitetsnivå 130.

I följande tabeller visas datatypskonverteringar och ytterligare åtgärder.

Datatypkonverteringar

Åtgärd

Bilaga B: Steg för att verifiera och uppdatera beständiga strukturer

Vi rekommenderar att du avgör om databasen har några bevarade strukturer som påverkas av ändringarna på kompatibilitetsnivå 130 och att du återskapar eventuella berörda strukturer.

Detta gäller endast för beständiga strukturer som har skapats i databasen på en äldre version av SQL Server eller med hjälp av en kompatibilitetsnivå som är lägre än 130. Bland de beständiga strukturer som potentiellt påverkas finns följande:

• Tabelldata som omfattas av CHECK begränsningar
• Beständiga beräknade kolumner
• Index som använder beräknade kolumner i nyckeln eller inkluderade kolumner
• Filtrerade index
• Indexerade vyer

Kör följande procedur i den här situationen.

Steg 1: Verifiera databasens kompatibilitetsnivå

1. Kontrollera databasens kompatibilitetsnivå med hjälp av proceduren som beskrivs i Visa eller ändra kompatibilitetsnivån för en databas.
2. Om databaskompatibilitetsnivån är lägre än 130 rekommenderar vi att du utför verifieringen som beskrivs i steg 2 innan du ökar kompatibilitetsnivån till 130.

Steg 2: Identifiera berörda beständiga strukturer

Avgör om databasen innehåller några bevarade strukturer som påverkas av den förbättrade precisions- och konverteringslogik som finns på kompatibilitetsnivå 130 på något av följande sätt:

• DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, som verifierar alla strukturer i databasen.
• DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, som verifierar de strukturer som är relaterade till en enda tabell.

Alternativet WITH EXTENDED_LOGICAL_CHECKS krävs för att se till att de bevarade värdena jämförs med beräknade värden och flagga fall där det finns en skillnad. Eftersom dessa kontroller är omfattande är körningen av DBCC instruktioner som använder det här alternativet längre än körningssatser DBCC utan alternativet. Därför är rekommendationen för stora databaser att använda DBCC CHECKTABLE för att hitta enskilda tabeller.

DBCC CHECKCONSTRAINTS kan användas för att verifiera CHECK begränsningar. Den här instruktionen kan användas antingen på databas- eller tabellnivå.

DBCC CHECK -instruktioner bör alltid köras under en underhållsperiod på grund av den potentiella effekten av kontrollerna på onlinearbetsbelastningen.

Verifiering på databasnivå

Validering på databasnivå är lämplig för små och måttligt stora databaser. Använd validering på tabellnivå för stora databaser.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS används för att verifiera alla bevarade strukturer i databasen.

DBCC CHECKCONSTRAINTS används för att verifiera alla CHECK begränsningar i databasen.

DBCC CHECKCONSTRAINTS används för att verifiera begränsningarnas integritet. Använd följande skript för att verifiera databasen:

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

Användningen av spårningsflaggan ser till att kontrollerna utförs med hjälp av den förbättrade precisions- och konverteringslogik som är på kompatibilitetsnivå 130, vilket tvingar fram rätt konverteringssemantik även när databasen har en lägre kompatibilitetsnivå.

Om -instruktionen CHECKCONSTRAINTS är klar och inte returnerar en resultatuppsättning behövs ingen ytterligare åtgärd.

Om -instruktionen returnerar en resultatuppsättning anger varje rad i resultatet ett brott mot en begränsning, och den innehåller även de värden som bryter mot villkoret.

• Spara namnen på tabellerna och begränsningarna tillsammans med de värden som orsakade den överträdta ( WHERE kolumnen i resultatuppsättningen).

I följande exempel visas en tabell med en CHECK begränsning och en enskild rad som uppfyller villkoret under lägre kompatibilitetsnivåer, men som bryter mot villkoret under kompatibilitetsnivå 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

Kommandot CHECKCONSTRAINT returnerar följande resultat.

Det här resultatet anger att villkoret [chk1] överträds för kombinationen av kolumnvärden i "Var".

DBCC CHECKDB MED EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS verifierar alla bevarade strukturer i databasen. Det här är det mest praktiska alternativet eftersom en enda instruktion validerar alla strukturer i databasen. Det här alternativet är dock inte lämpligt för stora databaser på grund av den förväntade körningstiden för -instruktionen.

Använd följande skript för att verifiera hela databasen:

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

Användningen av spårningsflaggan ser till att kontrollerna utförs med hjälp av den förbättrade precisions- och konverteringslogik som är på kompatibilitetsnivå 130, vilket tvingar fram rätt konverteringssemantik även när databasen har en lägre kompatibilitetsnivå.

Om instruktionen CHECKDB har slutförts krävs ingen ytterligare åtgärd.

Om instruktionen har slutförts med fel följer du dessa steg:

1. Spara resultatet från körningen av -instruktionen DBCC som finns i meddelandefönstret i SQL Server Management Studio (SSMS) till en fil.
2. Kontrollera att något av de rapporterade felen är relaterade till beständiga strukturer

Tabell 1: Beständiga strukturer och motsvarande felmeddelanden för inkonsekvenser

När du har slutfört verifiering på databasnivå går du till Steg 3.

Validering på objektnivå

För större databaser är det bra att verifiera strukturer och begränsningar i en tabell eller en vy i taget för att minska storleken på underhållsperioder eller begränsa de utökade logiska kontrollerna till potentiellt påverkade objekt.

Använd frågorna i avsnittet Bilaga C för att identifiera potentiellt påverkade tabeller. Skriptet i avsnittet Bilaga D kan användas för att generera CHECKTABLE och CHECKCONSTRAINTS begränsningar baserat på de frågor som anges i avsnittet Bilaga C .

DBCC CHECKCONSTRAINTS

Använd följande skript för att verifiera de begränsningar som är relaterade till en enskild tabell eller vy:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

Användningen av spårningsflaggan ser till att kontrollerna utförs med hjälp av den förbättrade precisions- och konverteringslogik som finns på kompatibilitetsnivå 130, vilket tvingar fram förbättrade semantik även när databasen har en lägre kompatibilitetsnivå.

Om -instruktionen CHECKCONSTRAINTS är klar och inte returnerar en resultatuppsättning behövs ingen ytterligare åtgärd.

Om -instruktionen returnerar en resultatuppsättning anger varje rad i resultatet ett brott mot en begränsning och anger även de värden som bryter mot villkoret.

Spara namnen på tabellerna och begränsningarna tillsammans med de värden som orsakade den överträdta ( WHERE kolumnen i resultatuppsättningen).

DBCC CHECKTABLE MED EXTENDED_LOGICAL_CHECKS

Använd följande skript för att verifiera de beständiga strukturer som är relaterade till en enskild tabell eller vy:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

Om instruktionen CHECKTABLE har slutförts krävs ingen ytterligare åtgärd.

Om instruktionen har slutförts med fel följer du dessa steg:

1. Spara resultatet från körningen av -instruktionen DBCC , som finns i meddelandefönstret i SSMS, till en fil.
2. Kontrollera att något av de rapporterade felen är relaterade till beständiga strukturer enligt tabell 1.
3. När du har slutfört valideringen på tabellnivå går du vidare till Steg 3.

Steg 3: Uppgradera till kompatibilitetsnivå 130

Om databasens kompatibilitetsnivå redan är 130 kan du hoppa över det här steget.

Databasens kompatibilitetsnivå kan ändras till 130 med hjälp av följande skript:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Steg 4: Uppdatera beständiga strukturer

Om inga inkonsekvenser hittades under valideringen som utfördes i steg 2 är du klar med uppgraderingen och kan hoppa över det här steget. Om inkonsekvenser hittades i steg 2 krävs ytterligare åtgärder för att ta bort inkonsekvenserna från databasen. Vilka åtgärder som krävs beror på vilken typ av struktur som påverkas.

Säkerhetskopiera databasen (eller databaserna)

Vi rekommenderar att du utför en fullständig databassäkerhetskopia innan du utför någon av de åtgärder som beskrivs i följande avsnitt. Om du använder Azure SQL Database behöver du inte ta en säkerhetskopia själv. Du kan alltid använda funktionen för återställning till tidpunkt för att gå tillbaka i tiden om något skulle gå fel med någon av uppdateringarna.

CHECK-begränsningar

För att CHECK korrigera begränsningsöverträdelser krävs ändringar av antingen data i tabellen eller själva villkoret CHECK .

Från namnet på villkoret (hämtas i steg 2) kan du hämta villkorsdefinitionen på följande sätt:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Om du vill granska de tabellrader som påverkas kan du använda den Where-information som tidigare returnerades av -instruktionen DBCC CHECKCONSTRAINTS :

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

Du måste antingen uppdatera de berörda raderna eller ändra villkorsdefinitionen för att se till att villkoret inte överträds.

Uppdatera tabelldata

Det finns ingen hård regel som anger hur data ska uppdateras. För varje enskild Where-instruktion som returneras av DBCC CHECKCONSTRAINTSkör du i allmänhet följande uppdateringsinstruktur:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Tänk på följande exempeltabell med en begränsning och en rad som bryter mot villkoret i kompatibilitetsnivå 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

I det här exemplet är villkoret enkelt. Kolumnen c4 måste vara lika med ett uttryck som involverar c2 och c3. Om du vill uppdatera tabellen tilldelar du det här värdet till 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

Observera att WHERE satsen som används i uppdateringsinstruktionen motsvarar den Där information som returneras av DBCC CHECKCONSTRAINTS.

Uppdatera CHECK-villkor

Om du vill ändra en CHECK begränsning måste du släppa och återskapa den. Vi rekommenderar att du gör båda i samma transaktion, ifall det skulle uppstå problem med den uppdaterade begränsningsdefinitionen. Du kan använda följande Transact-SQL:

BEGIN TRANSACTION

ALTER TABLE [schema_name].[table_name]

DROP CONSTRAINT [constraint_name]

ALTER TABLE [schema_name].[table_name]

ADD CONSTRAINT [constraint_name]

CHECK (new_constraint_definition)

COMMIT

GO

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

BEGIN TRANSACTION

ALTER TABLE dbo.table1

DROP CONSTRAINT chk1

ALTER TABLE dbo.table1

ADD CONSTRAINT chk1

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

COMMIT

GO

Beständiga beräknade kolumner

Det enklaste sättet att uppdatera beständiga beräknade kolumner är att uppdatera en av de kolumner som refereras av den beräknade kolumnen. Det nya värdet för kolumnen kan vara samma som det gamla värdet, så att åtgärden inte ändrar några användardata.

Följ dessa steg för alla object_id relaterade till inkonsekvenser i beräknade kolumner som du antecknade i steg 2.

1. Identifiera beräknade kolumner:

   • Kör följande fråga för att hämta tabellnamnet och namnen på beständiga beräknade kolumner för antecknade 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. Identifiera refererade kolumner:

• Kör följande fråga för att identifiera kolumner som refereras till av den beräknade kolumnen. Anteckna något av de refererade kolumnnamnen:

  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. Kör en UPDATE instruktion som involverar en av de refererade kolumnerna för att utlösa en uppdatering av den beräknade kolumnen:

   • Följande instruktion utlöser en uppdatering av kolumnen som refereras av den beräknade kolumnen och utlöser även en uppdatering av den beräknade kolumnen.

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

   • Uttrycket ISNULL i -instruktionen skapas på ett sådant sätt att värdet för den ursprungliga kolumnen inte ändras, samtidigt som du ser till att den beräknade kolumnen uppdateras med hjälp av utvärderingslogik för DB-kompatibilitetsnivå 130-uttryck.

   • Tänk på att för mycket stora tabeller kanske du inte vill uppdatera alla rader i en enda transaktion. I sådana fall kan du köra uppdateringen i batchar genom att lägga till en WHERE sats i uppdateringsinstruktionen som identifierar ett radintervall, till exempel baserat på primärnyckeln.

2. Identifiera index som refererar till den beräknade kolumnen.

   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
   

Den här frågan identifierar alla index som refererar till den beständiga beräknade kolumnen. Ett sådant index måste återskapas. Följ stegen i följande avsnitt för att göra detta.

Index, filtrerade index och indexerade vyer

Inkonsekvenser i index motsvarar felen 8951 och 8952 (för tabeller) eller 8907 och 8908 (för vyer) i DBCC CHECK utdata från steg 2.

Om du vill reparera dessa inkonsekvenser kör du DBCC CHECKTABLE med REPAIR_REBUILD. Detta reparerar indexstrukturerna utan dataförlust. Databasen måste dock vara i enanvändarläge och är därför inte tillgänglig för andra användare när reparationen pågår.

Du kan också återskapa berörda index manuellt. Det här alternativet bör användas om arbetsbelastningen inte kan tas offline eftersom återskapande av index kan utföras som en ONLINE-åtgärd (i versioner av SQL Server som stöds).

Återskapa index

Om det inte är ett alternativ att ställa in databasen i enanvändarläge kan du återskapa index individuellt med hjälp ALTER INDEX REBUILDav för varje index som identifieras i steg 2.

Använd följande fråga för att hämta tabell- och indexnamnen för en viss object_id och 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

Använd följande instruktion för att återskapa indexet:

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

I följande exempel visas återskapande av ett filtrerat 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

Om du har regelbundna underhållsplaner rekommenderar vi att du inkluderar det här indexet som en del av ditt schemalagda underhåll.

Reparera med hjälp av DBCC

För varje (object_id) som är relaterad till ett index med inkonsekvenser som du antecknade i steg 2 kör du följande skript för att utföra reparationen. Det här skriptet anger databasen i enanvändarläge för reparationsåtgärden. I värsta fall utför reparationen en fullständig indexåterbyggnad.

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

Bilaga C: Frågor för att identifiera kandidattabeller

Följande skript identifierar kandidattabeller som du kanske vill verifiera med hjälp DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKSav , baserat på förekomsten av bevarade strukturer och begränsningar som använder datatyper som påverkas av förbättringarna på kompatibilitetsnivå 130.

Följande uppsättning frågor innehåller information om tabellerna och potentiellt påverkade strukturer som kräver ytterligare validering.

Indexerade vyer

Följande fråga returnerar alla indexerade vyer som refererar till kolumner med hjälp av berörda datatyper eller med hjälp av någon av de berörda inbyggda funktionerna:

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

Beständiga beräknade kolumner

Följande fråga returnerar alla tabeller med beräknade kolumner som refererar till andra kolumner med hjälp av berörda datatyper, eller med hjälp av någon av de berörda inbyggda funktionerna, där antingen kolumnen sparas eller refereras från ett 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)

)

Filtrerade index

Följande fråga returnerar alla tabeller med filtrerade index som refererar till kolumner i filtervillkoret som har påverkat datatyper:

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

)

Kontrollera begränsningar

Följande fråga visar alla tabeller med kontrollbegränsningar som refererar till berörda datatyper eller inbyggda funktioner:

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

Bilaga D: Skript för att skapa CHECK*-instruktioner

Följande skript kombinerar frågorna från föregående bilaga och förenklar resultatet genom att presentera en lista med tabeller och vyer i form av CHECKCONSTRAINTS och CHECKTABLE -instruktioner.

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