Ulepszenia programu SQL Server i usługi Azure SQL Database w zakresie obsługi niektórych typów danych i nietypowych operacji

W tym artykule przedstawiono sposób weryfikacji utrwalonej struktury w bazie danych programu SQL Server w ramach poziomu zgodności uaktualnienia oraz sposobu odbudowy wszelkich struktur, których dotyczy problem po uaktualnieniu poziomu zgodności.

Oryginalna wersja produktu: SQL Server 2017, SQL Server 2016
Oryginalny numer KB: 4010261

Aparat bazy danych w programie Microsoft SQL Server 2016 i usłudze Azure SQL Database zawiera ulepszenia konwersji typów danych i kilka innych operacji. Większość tych ulepszeń oferuje zwiększoną precyzję podczas pracy z typami zmiennoprzecinkowych, a także z klasycznymi typami daty/godziny.

Te ulepszenia są dostępne w przypadku korzystania z poziomu zgodności bazy danych co najmniej 130. Oznacza to, że w przypadku niektórych (w większości nietypowych) wyrażeń po uaktualnieniu bazy danych do poziomu zgodności 130 lub wyższego ustawienia można zobaczyć różne wyniki dla niektórych wartości wejściowych. Te wyniki mogą zostać odzwierciedlone w następujących rezultatów:

• utrwalone struktury w bazie danych
• dołączone dane tabeli, które podlegają ograniczeniom CHECK
• utrwalone obliczone kolumny
• indeksy odwołujące się do obliczonych kolumn
• filtrowane indeksy i indeksowane widoki.

Jeśli masz bazę danych utworzoną we wcześniejszej wersji programu SQL Server, zalecamy przeprowadzenie dodatkowej weryfikacji po uaktualnieniu do programu SQL Server 2016 lub nowszego oraz przed zmianą poziomu zgodności bazy danych.

Jeśli w bazie danych wystąpią jakiekolwiek utrwalone struktury, zalecamy ponowne skompilowanie struktur, których dotyczy problem po uaktualnieniu poziomu zgodności bazy danych. W ten sposób skorzystasz z tych ulepszeń w programie SQL Server 2016 lub nowszym.

W tym artykule opisano, jak utrwalone struktury w bazie danych można zweryfikować w ramach uaktualnienia do poziomu zgodności 130 lub wyższego oraz jak można odtworzyć wszystkie struktury, których dotyczy problem po zmianie poziomu zgodności.

Kroki weryfikacji podczas uaktualniania do poziomu zgodności bazy danych

Począwszy od programu SQL Server 2016, zarówno program SQL Server, jak i usługa Azure SQL Database obejmują ulepszenia dokładności następujących operacji:

• Nietypowe konwersje typów danych. Należą do nich następujące elementy:
  • Liczba zmiennoprzecinkowa/całkowita do/z daty/godziny/smalldatetime
  • Real/float do/z liczbowych/pieniędzy/smallmoney
  • Zmiennoprzecinkowy do rzeczywistego
• Niektóre przypadki i DATEPART/DATEDIFFDEGREES
• CONVERT który używa NULL stylu

Aby użyć tych ulepszeń do oceny wyrażeń w aplikacji, zmień poziom zgodności baz danych na 130 (dla programu SQL Server 2016) lub 140 (dla programów SQL Server 2017 i Azure SQL Database). Aby uzyskać więcej informacji na temat wszystkich zmian i niektórych przykładów, które pokazują zmiany, zobacz sekcję Dodatek A .

Następujące struktury w bazie danych mogą utrwalać wyniki wyrażenia:

• Dane tabeli, CHECK których dotyczą ograniczenia
• Utrwalone obliczone kolumny
• Indeksy korzystające z kolumn obliczeniowych w kluczu lub dołączonych kolumnach
• Przefiltrowane indeksy
• Widoki indeksowane

Rozważmy następujący scenariusz:

• Masz bazę danych utworzoną przez wcześniejszą wersję programu SQL Server lub utworzoną już w programie SQL Server 2016 lub nowszej wersji, ale na poziomie zgodności 120 lub starszym.

• Używasz dowolnych wyrażeń, których precyzja została ulepszona w ramach definicji utrwałych struktur w bazie danych.

W tym scenariuszu mogą istnieć utrwalone struktury, które mają wpływ na ulepszenia dokładności implementowane przy użyciu poziomu zgodności 130 lub wyższego. Jeśli tak jest, zalecamy zweryfikowanie utrwalonej struktury i ponowne skompilowanie dowolnej struktury, której dotyczy problem.

Jeśli masz wpływ na struktury i nie kompilujesz ich po zmianie poziomu zgodności, wyniki zapytania mogą być nieco inne. Wyniki zależą od tego, czy jest używany określony indeks, obliczona kolumna lub widok, oraz czy dane w tabeli mogą być uznawane za naruszenie ograniczenia.

Flaga śledzenia 139 jest obsługiwana od wersji SQL Server 2016 CU3 i SQL Server 2016 SP1.

Aby podwyższyć poziom zgodności, wykonaj następujące kroki:

1. Wykonaj walidację, aby zidentyfikować wszystkie utrwalone struktury, których dotyczy problem:
   1. Włącz flagę śledzenia 139, uruchamiając polecenie DBCC TRACEON(139, -1).
   2. Uruchom DBCC CHECKDB/TABLE polecenia i CHECKCONSTRAINTS .
   3. Wyłącz flagę śledzenia 139, uruchamiając polecenie DBCC TRACEOFF(139, -1).
2. Zmień poziom zgodności bazy danych na 130 (dla programu SQL Server 2016) lub 140 (dla programów SQL Server 2017 i Azure SQL Database).
3. Skompiluj wszystkie struktury zidentyfikowane w kroku 1.

• Dodatek A zawiera szczegółową listę wszystkich ulepszeń dokładności i zawiera przykład dla każdego z nich.

• Dodatek B zawiera szczegółowy proces weryfikacji i ponowne kompilowania wszelkich struktur, których dotyczy problem.

• Dodatek C i dodatek D zawierają skrypty ułatwiające wskazanie potencjalnie dotkniętych obiektów w bazie danych. W związku z tym można określić zakres weryfikacji i wygenerować odpowiednie skrypty w celu uruchomienia testów. Aby najłatwiej określić, czy wszystkie utrwalone struktury w bazach danych mają wpływ na ulepszenia dokładności na poziomie zgodności 130, uruchom skrypt w dodatku D , aby wygenerować prawidłowe kontrole poprawności, a następnie uruchom ten skrypt, aby przeprowadzić walidację.

Dodatek A: Zmiany na poziomie zgodności 130

Ten dodatek zawiera szczegółowe listy ulepszeń oceny wyrażeń na poziomie zgodności 130. Każda zmiana zawiera skojarzone przykładowe zapytanie. Zapytania mogą służyć do pokazywania różnic między wykonywaniem w bazie danych, która korzysta z poziomu zgodności sprzed 130 w porównaniu z bazą danych korzystającą z poziomu zgodności 130.

W poniższych tabelach wymieniono konwersje typów danych i dodatkowe operacje.

Konwersje typów danych

Operacja

Dodatek B: Kroki weryfikowania i aktualizowania utrwałych struktur

Zalecamy określenie, czy baza danych ma jakiekolwiek utrwalone struktury, które mają wpływ na zmiany poziomu zgodności 130, i że ponownie skompilujesz wszystkie struktury, których dotyczy problem.

Dotyczy to tylko trwałych struktur utworzonych w bazie danych w starszej wersji programu SQL Server lub przy użyciu poziomu zgodności niższego niż 130. Utrwalone struktury, których potencjalnie dotyczy problem, obejmują następujące elementy:

• Dane tabeli, CHECK których dotyczą ograniczenia
• Utrwalone obliczone kolumny
• Indeksy korzystające z kolumn obliczeniowych w kluczu lub dołączonych kolumnach
• Przefiltrowane indeksy
• Widoki indeksowane

W takiej sytuacji uruchom następującą procedurę.

Krok 1. Weryfikowanie poziomu zgodności bazy danych

1. Sprawdź poziom zgodności bazy danych, korzystając z procedury opisanej w temacie Wyświetl lub zmień poziom zgodności bazy danych.
2. Jeśli poziom zgodności bazy danych jest niższy niż 130, zalecamy przeprowadzenie weryfikacji opisanej w kroku 2 przed zwiększeniem poziomu zgodności do 130.

Krok 2. Identyfikowanie utrwałych struktur, których dotyczy problem

Ustal, czy baza danych zawiera jakiekolwiek utrwalone struktury, które mają wpływ na ulepszoną logikę dokładności i konwersji na poziomie zgodności 130 w jeden z następujących sposobów:

• DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS, który weryfikuje wszystkie struktury w bazie danych.
• DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKS, który weryfikuje struktury powiązane z pojedynczą tabelą.

Opcja WITH EXTENDED_LOGICAL_CHECKS jest wymagana, aby upewnić się, że utrwalone wartości są porównywane z obliczonymi wartościami oraz do flagowania przypadków, w których występuje różnica. Ponieważ te testy są obszerne, środowisko uruchomieniowe instrukcji korzystających DBCC z tej opcji jest dłuższe niż uruchamianie DBCC instrukcji bez opcji. W związku z tym zaleceniem dla dużych baz danych jest użycie DBCC CHECKTABLE w celu zidentyfikowania poszczególnych tabel.

DBCC CHECKCONSTRAINTS może służyć do sprawdzania CHECK poprawności ograniczeń. Tej instrukcji można użyć na poziomie bazy danych lub tabeli.

DBCC CHECK instrukcje powinny być zawsze uruchamiane w oknie obsługi ze względu na potencjalny wpływ kontroli obciążenia online.

Walidacja na poziomie bazy danych

Walidacja na poziomie bazy danych jest odpowiednia dla małych i umiarkowanie rozmiarów baz danych. Użyj walidacji na poziomie tabeli dla dużych baz danych.

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS służy do sprawdzania poprawności wszystkich utrwalonej struktury w bazie danych.

DBCC CHECKCONSTRAINTS służy do sprawdzania poprawności wszystkich CHECK ograniczeń w bazie danych.

DBCC CHECKCONSTRAINTS służy do sprawdzania integralności ograniczeń. Użyj następującego skryptu, aby zweryfikować bazę danych:

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

Użycie flagi śledzenia zapewnia, że kontrole są wykonywane przy użyciu ulepszonej precyzji i logiki konwersji, która jest na poziomie zgodności 130, wymuszając poprawną semantyka konwersji nawet wtedy, gdy baza danych ma niższy poziom zgodności.

Jeśli instrukcja jest zakończona CHECKCONSTRAINTS i nie zwraca zestawu wyników, nie jest wymagana żadna dodatkowa akcja.

Jeśli instrukcja zwraca zestaw wyników, każdy wiersz w wynikach wskazuje naruszenie ograniczenia, a także zawiera wartości naruszające ograniczenie.

• Zapisz nazwy tabel i ograniczeń wraz z wartościami, które spowodowały naruszenie (kolumna WHERE w zestawie wyników).

W poniższym przykładzie przedstawiono tabelę z ograniczeniem CHECK i pojedynczy wiersz spełniający ograniczenia na niższych poziomach zgodności, ale narusza to ograniczenie na poziomie zgodności 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

Polecenie CHECKCONSTRAINT zwraca następujące wyniki.

Ten wynik wskazuje, że ograniczenie [chk1] jest naruszone dla kombinacji wartości kolumn w kolumnie "Where".

DBCC CHECKDB Z EXTENDED_LOGICAL_CHECKS

DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS weryfikuje wszystkie utrwalone struktury w bazie danych. Jest to najbardziej wygodna opcja, ponieważ pojedyncza instrukcja weryfikuje wszystkie struktury w bazie danych. Jednak ta opcja nie jest odpowiednia dla dużych baz danych ze względu na oczekiwany czas wykonywania instrukcji.

Użyj następującego skryptu, aby zweryfikować całą bazę danych:

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

Użycie flagi śledzenia zapewnia, że kontrole są wykonywane przy użyciu ulepszonej precyzji i logiki konwersji, która jest na poziomie zgodności 130, wymuszając poprawną semantyka konwersji nawet wtedy, gdy baza danych ma niższy poziom zgodności.

Jeśli instrukcja została ukończona CHECKDB pomyślnie, nie jest wymagana żadna dodatkowa akcja.

Jeśli instrukcja zostanie zakończona z błędami, wykonaj następujące kroki:

1. Zapisz wyniki wykonania instrukcji znajdującej się w okienku DBCC komunikatów w programie SQL Server Management Studio (SSMS) do pliku.
2. Sprawdź, czy jakiekolwiek zgłoszone błędy są związane z utrwalone struktury

Tabela 1. Utrwalone struktury i odpowiednie komunikaty o błędach w przypadku niespójności

Po zakończeniu weryfikacji na poziomie bazy danych przejdź do kroku 3.

Walidacja na poziomie obiektu

W przypadku większych baz danych warto zweryfikować struktury i ograniczenia w jednej tabeli lub jednym widoku jednocześnie, aby zmniejszyć rozmiar okien obsługi lub ograniczyć rozszerzone kontrole logiczne tylko do potencjalnie dotkniętych obiektów.

Użyj zapytań w sekcji Dodatku C , aby zidentyfikować potencjalnie dotknięte tabele. Skrypt w sekcji Dodatku D może służyć do generowania CHECKTABLE i CHECKCONSTRAINTS ograniczeń na podstawie zapytań wymienionych w sekcji Dodatku C .

OGRANICZENIA SPRAWDZANIA DBCC

Aby zweryfikować ograniczenia związane z pojedynczą tabelą lub widokiem, użyj następującego skryptu:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKCONSTRAINTS()

GO

DBCC TRACEOFF(139, -1)

GO

Użycie flagi śledzenia zapewnia, że kontrole są wykonywane przy użyciu ulepszonej precyzji i logiki konwersji, która jest na poziomie zgodności 130, wymuszając ulepszoną semantyka nawet wtedy, gdy baza danych ma niższy poziom zgodności.

Jeśli instrukcja jest zakończona CHECKCONSTRAINTS i nie zwraca zestawu wyników, nie jest wymagana żadna dodatkowa akcja.

Jeśli instrukcja zwraca zestaw wyników, każdy wiersz w wynikach wskazuje naruszenie ograniczenia, a także zawiera wartości naruszające ograniczenie.

Zapisz nazwy tabel i ograniczeń wraz z wartościami, które spowodowały naruszenie (kolumna WHERE w zestawie wyników).

TABELA KONTROLNA DBCC Z EXTENDED_LOGICAL_CHECKS

Aby zweryfikować utrwalone struktury powiązane z pojedynczą tabelą lub widokiem, użyj następującego skryptu:

USE [database_name]

GO

DBCC TRACEON(139, -1)

GO

DBCC CHECKTABLE() WITH EXTENDED_LOGICAL_CHECKS, NO_INFOMSGS, TABLERESULTS

GO

DBCC TRACEOFF(139, -1)

GO

Jeśli instrukcja została ukończona CHECKTABLE pomyślnie, nie jest wymagana żadna dodatkowa akcja.

Jeśli instrukcja zostanie zakończona z błędami, wykonaj następujące kroki:

1. Zapisz wyniki z wykonania instrukcji znajdującej się w okienku DBCC komunikatów w programie SSMS do pliku.
2. Sprawdź, czy którekolwiek z zgłoszonych błędów są związane z utrwalone struktury wymienione w tabeli 1.
3. Po zakończeniu weryfikacji na poziomie tabeli przejdź do kroku 3.

Krok 3. Uaktualnienie do poziomu zgodności 130

Jeśli poziom zgodności bazy danych wynosi już 130, możesz pominąć ten krok.

Poziom zgodności bazy danych można zmienić na 130, używając następującego skryptu:

USE [database_name]

GO

ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL=130

GO

Krok 4. Aktualizowanie utrwałych struktur

Jeśli nie znaleziono niespójności podczas walidacji wykonanej w kroku 2, wykonasz uaktualnienie i możesz pominąć ten krok. Jeśli w kroku 2 znaleziono niespójności, wymagane są dodatkowe akcje w celu usunięcia niespójności z bazy danych. Wymagane akcje zależą od rodzaju struktury, której dotyczy problem.

Tworzenie kopii zapasowej bazy danych (lub baz danych)

Zalecamy wykonanie pełnej kopii zapasowej bazy danych przed wykonaniem akcji opisanych w poniższej sekcji. Jeśli używasz usługi Azure SQL Database, nie musisz samodzielnie tworzyć kopii zapasowej; Zawsze można użyć funkcji przywracania do punktu w czasie, aby wrócić w czasie, jeśli coś pójdzie nie tak z dowolną aktualizacją.

SPRAWDZANIE ograniczeń

Poprawianie CHECK naruszeń ograniczeń wymaga modyfikacji danych w tabeli lub samego CHECK ograniczenia.

Z nazwy ograniczenia (uzyskanego w kroku 2) można uzyskać definicję ograniczenia w następujący sposób:

SELECT definition FROM sys.check_constraints

WHERE object_id= OBJECT_ID(N'constraint_name')

Aby sprawdzić wiersze tabeli, których dotyczy problem, możesz użyć informacji Where, które zostały wcześniej zwrócone przez instrukcję DBCC CHECKCONSTRAINTS :

SELECT *

FROM [schema_name].[table_name]

WHERE Where_clause

Musisz zaktualizować wiersze, których dotyczy problem, lub zmienić definicję ograniczenia, aby upewnić się, że ograniczenie nie zostało naruszone.

Aktualizowanie danych tabeli

Nie ma twardej reguły określającej sposób aktualizowania danych. Ogólnie rzecz biorąc, dla każdej innej instrukcji Where zwróconej przez DBCC CHECKCONSTRAINTS, uruchomisz następującą instrukcję aktualizacji:

UPDATE [schema_name].[table_name] SET new_column_values

WHERE Where_clause

Rozważmy następującą przykładową tabelę z ograniczeniem i wierszem, który narusza ograniczenie na poziomie zgodności 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

W tym przykładzie ograniczenie jest proste. Kolumna c4 musi być równa wyrażeniu z elementami c2 i c3. Aby zaktualizować tabelę, przypisz tę wartość do 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

Zwróć uwagę, że klauzula użyta WHERE w instrukcji update odpowiada informacjom Where zwróconym przez DBCC CHECKCONSTRAINTSelement .

Aktualizowanie ograniczenia CHECK

Aby zmienić CHECK ograniczenie, należy go usunąć i ponownie utworzyć. Zalecamy wykonanie obu tych czynności w tej samej transakcji, na wypadek, gdy występują jakiekolwiek problemy ze zaktualizowaną definicją ograniczenia. Możesz użyć następującego języka 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

Utrwalone obliczone kolumny

Najprostszym sposobem aktualizacji utrwalone obliczone kolumny jest zaktualizowanie jednej z kolumn, do których odwołuje się obliczona kolumna. Nowa wartość kolumny może być taka sama jak stara wartość, tak aby operacja nie zmieniała żadnych danych użytkownika.

Wykonaj następujące kroki dla każdego object_id powiązanego z niespójnościami w obliczonych kolumnach zanotowanych w kroku 2.

1. Identyfikowanie obliczonych kolumn:

   • Uruchom następujące zapytanie, aby pobrać nazwę tabeli i nazwy utrwanych kolumn obliczeniowych dla zanotowanego object_idelementu :

     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. Identyfikowanie przywoływanych kolumn:

• Uruchom następujące zapytanie, aby zidentyfikować kolumny, do których odwołuje się obliczona kolumna. Zanotuj jedną z przywołynych nazw kolumn:

  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. Uruchom instrukcję UPDATE obejmującą jedną z przywoływanych kolumn, aby wyzwolić aktualizację obliczonej kolumny:

   • Poniższa instrukcja spowoduje wyzwolenie aktualizacji kolumny, do których odwołuje się obliczona kolumna, a także wyzwoli aktualizację obliczonej kolumny.

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

   • Wyrażenie ISNULL w instrukcji jest spreparowane w taki sposób, aby wartość oryginalnej kolumny nie została zmieniona, jednocześnie upewniając się, że obliczona kolumna jest aktualizowana przy użyciu logiki obliczania na poziomie zgodności bazy danych 130.

   • Należy pamiętać, że w przypadku bardzo dużych tabel może nie być konieczne zaktualizowanie wszystkich wierszy w jednej transakcji. W takim przypadku można uruchomić aktualizację w partiach, dodając klauzulę WHERE do instrukcji update, która identyfikuje zakres wierszy, na przykład na podstawie klucza podstawowego.

2. Zidentyfikuj indeksy odwołujące się do obliczonej kolumny.

   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
   

To zapytanie identyfikuje wszystkie indeksy odwołujące się do utrwalonej kolumny obliczeniowej. Każdy taki indeks musi zostać ponownie skompilowany. W tym celu wykonaj kroki opisane w poniższej sekcji.

Indeksy, przefiltrowane indeksy i indeksowane widoki

Niespójności w indeksach odpowiadają błędom 8951 i 8952 (dla tabel) lub 8907 i 8908 (dla widoków) w DBCC CHECK danych wyjściowych z kroku 2.

Aby naprawić te niespójności, uruchom polecenie za pomocą polecenia DBCC CHECKTABLE REPAIR_REBUILD. Spowoduje to naprawienie struktur indeksów bez utraty danych. Jednak baza danych musi być w trybie pojedynczego użytkownika i dlatego jest niedostępna dla innych użytkowników podczas naprawy.

Można również ręcznie ponownie skompilować indeksy, których dotyczy problem. Ta opcja powinna być używana, jeśli nie można przełączyć obciążenia do trybu offline, ponieważ ponowne kompilowanie indeksu można wykonać jako operację online (w obsługiwanych wersjach programu SQL Server).

Odbudowywanie indeksów

Jeśli ustawienie bazy danych w trybie pojedynczego użytkownika nie jest opcją, można osobno ponownie skompilować indeksy przy użyciu polecenia , ALTER INDEX REBUILDdla każdego indeksu określonego w kroku 2.

Użyj następującego zapytania, aby uzyskać nazwy tabel i indeksów dla danego object_id elementu i 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

Użyj następującej instrukcji, aby ponownie skompilować indeks:

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

W poniższym przykładzie pokazano ponowne kompilowanie filtrowanego indeksu:

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

Jeśli masz regularne plany konserwacji, zalecamy uwzględnienie tego ponownego kompilowania indeksu w ramach zaplanowanej konserwacji.

Naprawianie za pomocą polecenia DBCC

Dla każdego (object_id) powiązanego z indeksem z niespójnościami zanotowanym w kroku 2 uruchom następujący skrypt, aby wykonać naprawę. Ten skrypt ustawia bazę danych w trybie pojedynczego użytkownika na potrzeby operacji naprawy. W najgorszym przypadku naprawa wykonuje pełną ponowną kompilację indeksu.

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

Dodatek C: Zapytania dotyczące identyfikowania tabel kandydatów

Poniższe skrypty identyfikują tabele kandydatów, które można zweryfikować przy użyciu klasy , DBCC CHECKTABLE WITH EXTENDED_LOGICAL_CHECKSna podstawie istnienia trwałych struktur i ograniczeń, które korzystają z typów danych, których dotyczą ulepszenia poziomu zgodności 130.

Poniższy zestaw zapytań zawiera szczegółowe informacje o tabelach i potencjalnie dotkniętych strukturach, które wymagają dodatkowej weryfikacji.

Widoki indeksowane

Następujące zapytanie zwraca wszystkie indeksowane widoki odwołujące się do kolumn przy użyciu typów danych, których dotyczy problem, lub używając dowolnej z funkcji wbudowanych, których dotyczy problem:

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

Utrwalone obliczone kolumny

Poniższe zapytanie zwraca wszystkie tabele z obliczonymi kolumnami odwołującymi się do innych kolumn przy użyciu typów danych, których dotyczy problem, lub przy użyciu dowolnej z funkcji wbudowanych, których dotyczy problem, gdzie kolumna jest utrwalana lub odwołuje się do indeksu.

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)

)

Przefiltrowane indeksy

Następujące zapytanie zwraca wszystkie tabele z filtrowanymi indeksami odwołujące się do kolumn w warunku filtru, które mają wpływ na typy danych:

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

)

Sprawdzanie ograniczeń

Poniższe zapytanie zawiera listę wszystkich tabel z ograniczeniami sprawdzania, które odwołują się do typów danych, których dotyczy problem, lub wbudowanych funkcji:

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

Dodatek D: Skrypt tworzenia instrukcji CHECK*

Poniższy skrypt łączy zapytania z poprzedniego dodatku i upraszcza wyniki, przedstawiając listę tabel i widoków w postaci instrukcji CHECKCONSTRAINTS i CHECKTABLE .

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

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

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

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

UNION

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

UNION

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

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

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

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

PRINT @sql;

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