Mam problem z wydajnością przy bardzo dużych przydziałach pamięci obsługujących tę tabelę z kilkoma NVARCHAR(4000)kolumnami. Chodzi o to, że te kolumny nigdy nie są większe niż NVARCHAR(260).

Za pomocą

ALTER TABLE [table] ALTER COLUMN [col] NVARCHAR(260) NULL

powoduje, że SQL Server przepisuje całą tabelę (i używa 2x rozmiaru tabeli w przestrzeni dziennika), która jest miliardami wierszy, tylko aby niczego nie zmieniać, nie jest opcją. Zwiększenie szerokości kolumny nie ma tego problemu, ale zmniejsza się.

Próbowałem utworzyć ograniczenie CHECK (DATALENGTH([col]) <= 520)lub CHECK (LEN([col]) <= 260)SQL Server nadal decyduje się na ponowne zapisanie całej tabeli.

Czy jest jakiś sposób na zmianę typu danych kolumny jako operacji tylko na metadanych? Bez kosztów przepisywania całego stołu? Używam SQL Server 2017 (14.0.2027.2 i 14.0.3192.2).

Oto przykładowa tabela DDL do odtworzenia:

CREATE TABLE [table](
    id INT IDENTITY(1,1) NOT NULL,
    [col] NVARCHAR(4000) NULL,
    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

A następnie uruchom ALTER.

Nie znam sposobu na bezpośrednie osiągnięcie tego, czego tu szukasz. Zauważ, że optymalizator zapytań nie jest obecnie wystarczająco inteligentny, aby uwzględnić ograniczenia w obliczeniach przyznania pamięci, więc ograniczenie i tak by nie pomogło. Kilka metod pozwalających uniknąć przepisywania danych tabeli:

1. CAST kolumnę jako NVARCHAR (260) we wszystkich kodach, które z niej korzystają. Optymalizator kwerendy obliczy przydział pamięci przy użyciu rzutowanego typu danych zamiast surowego.
2. Zmień nazwę tabeli i utwórz widok, który wykonuje rzutowanie. Osiąga to to samo co opcja 1, ale może ograniczać ilość kodu, który należy zaktualizować.
3. Utwórz nietrwałą kolumnę obliczeniową z odpowiednim typem danych i poproś o wybranie wszystkich zapytań z tej kolumny zamiast z oryginalnej kolumny.
4. Zmień nazwę istniejącej kolumny i dodaj kolumnę obliczeniową z oryginalną nazwą. Następnie dostosuj wszystkie zapytania, wprowadzając aktualizacje lub wstawki do oryginalnej kolumny, aby zamiast tego użyć nowej nazwy kolumny.

Czy jest jakiś sposób na zmianę typu danych kolumny jako operacji tylko na metadanych?

Nie sądzę, tak właśnie działa ten produkt. Istnieje kilka naprawdę dobrych obejść tego ograniczenia zaproponowanego w odpowiedzi Joe .

... powoduje, że SQL Server przepisuje całą tabelę (i używa 2x rozmiaru tabeli w obszarze dziennika)

Odpowiem na dwie części tego oświadczenia osobno.

Przepisywanie tabeli

Jak wspomniałem wcześniej, tak naprawdę nie da się tego uniknąć. Wydaje się, że taka jest sytuacja, nawet jeśli nie ma to pełnego sensu z naszej perspektywy jako klientów.

Spojrzenie na DBCC PAGEprzed i po zmianie kolumny z 4000 na 260 pokazuje, że wszystkie dane są powielone na stronie danych (moja tabela testowa miała 'A'260 razy w rzędzie):

W tym momencie na stronie znajdują się dwie kopie dokładnie takich samych danych. „Stara” kolumna jest zasadniczo usuwana (identyfikator jest zmieniany z id = 2 na id = 67108865), a „nowa” wersja kolumny jest aktualizowana, aby wskazywała nowe przesunięcie danych na stronie:

Używanie 2x rozmiaru tabeli w obszarze dziennika

Dodanie WITH (ONLINE = ON)na końcu ALTERinstrukcji zmniejsza aktywność rejestrowania o około połowę , więc jest to jedno ulepszenie, które można wprowadzić, aby zmniejszyć ilość potrzebnych zapisów na dysku / dysku.

Użyłem tej uprzęży testowej, aby ją wypróbować:

USE [master];
GO
DROP DATABASE IF EXISTS [248749];
GO
CREATE DATABASE [248749] 
ON PRIMARY 
(
    NAME = N'248749', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\248749.mdf', 
    SIZE = 2048000KB, 
    FILEGROWTH = 65536KB
)
LOG ON 
(
    NAME = N'248749_log', 
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\DATA\248749_log.ldf', 
    SIZE = 2048000KB, 
    FILEGROWTH = 65536KB
);
GO
USE [248749];
GO

CREATE TABLE dbo.[table]
(
    id int IDENTITY(1,1) NOT NULL,
    [col] nvarchar (4000) NULL,

    CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED (id ASC)
);

INSERT INTO dbo.[table]
SELECT TOP (1000000)
    REPLICATE(N'A', 260)
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2
    CROSS JOIN master.dbo.spt_values v3;
GO

Sprawdziłem sys.dm_io_virtual_file_stats(DB_ID(N'248749'), DEFAULT)przed i po uruchomieniu ALTERinstrukcji, a oto różnice:

Domyślnie (offline) ALTER

• Plik danych zapisuje / zapisuje bajty: 34 809/2 193,801,216
• Zapis pliku dziennika / zapisane bajty: 40 953/1 484 910 080

online ALTER

• Zapis / zapis danych w pliku danych: 36 874/1 693 745 152 (spadek o 22,8%)
• Zapis pliku dziennika / zapisane bajty: 24.680 / 866,166,272 (spadek o 41%)

Jak widać, nastąpił niewielki spadek zapisów pliku danych i znaczny spadek zapisów pliku dziennika.

Wiele razy byłem w podobnej sytuacji.

Kroki :

Dodaj nową kolumnę o pożądanej szerokości

Użyj kursora, z kilkoma tysiącami iteracji (może dziesięć lub dwadzieścia tysięcy) na zatwierdzenie, aby skopiować dane ze starej kolumny do nowej kolumny

Upuść starą kolumnę

Zmień nazwę nowej kolumny na nazwę starej kolumny

Tada!

Istnieje alternatywa zależna od dostępnego miejsca w bazie danych.

1. Utwórz dokładną kopię tabeli (np. new_table), Z wyjątkiem kolumny, w której będziesz skracał z NVARCHAR(4000)do NVARCHAR(260):

   CREATE TABLE [new_table](
       id INT IDENTITY(1,1) NOT NULL,
       [col] NVARCHAR(260) NULL,
       CONSTRAINT [PK_test_new] PRIMARY KEY CLUSTERED (id ASC)
   );
   

2. W oknie konserwacji skopiuj dane z tabeli „zepsutej” ( table) do tabeli „naprawionej” ( new_table) za pomocą prostego INSERT ... INTO ... SELECT ....:

   SET IDENTITY_INSERT [new_table] ON
   GO
   INSERT id, col INTO [new_table] SELECT id, col from [table]
   GO
   SET IDENTITY_INSERT [new_table] OFF
   GO
   

3. Zmień nazwę tabeli „zepsuty” tablena coś innego:

   EXEC sp_rename 'table', 'old_table';  

4. Zmień nazwę tabeli „stałej” new_tablena table:

   EXEC sp_rename 'new_table', 'table';  

5. Jeśli wszystko jest w porządku, upuść tabelę o zmienionej nazwie: „zepsuta”:

    DROP TABLE [old_table]
    GO
   

Proszę bardzo.

Odpowiadając na twoje pytania

Czy jest jakiś sposób na zmianę typu danych kolumny jako operacji tylko na metadanych?

Nie. Obecnie nie jest możliwe

Bez kosztów przepisywania całego stołu?

Nie.
( Zobacz moje rozwiązanie i inne. )