Kiedy obliczane są kolumny?

29

Kiedy ustalane są wartości dla kolumn obliczanych?

Kiedy wartość jest pobierana?
Kiedy wartość zostanie zmieniona?
Innym razem?

Domyślam się, że jest to pytanie dla początkujących, ponieważ nie znajduję niczego w moich wyszukiwaniach.

sql-server computed-column

— Shelby115
źródło

19

Zależy to od sposobu zdefiniowania kolumny obliczanej. PERSISTEDKolumna obliczone zostaną obliczone i zapisane jako dane wewnątrz stołu. Jeśli nie zdefiniujesz kolumny jako PERSISTED, zostanie ona obliczona po uruchomieniu zapytania.

Proszę zobaczyć odpowiedź Aarona, aby uzyskać świetne wyjaśnienie i dowód.

Pinal Dave również szczegółowo to opisuje i pokazuje dowód przechowywania w swojej serii:

SERWER SQL - kolumna obliczeniowa - WYTRZYMAŁOŚĆ i pamięć masowa

— Artur D.
źródło

6

A jeśli będą się utrzymywać, ale plan zapytań używa indeksu, który nie obejmuje tej kolumny? Nie jestem pewien, czy możesz to sprawdzić, czy po prostu obliczy to w locie i nie mogę go obecnie przetestować.

— Martin Smith

1

@ Martin, masz rację, w moim teście SQL Server wybrał ponowne obliczenia zamiast wyszukiwania.

— Aaron Bertrand

34

Bardzo łatwo to udowodnić na własną rękę. Możemy utworzyć tabelę z kolumną obliczeniową, która korzysta ze skalarnej funkcji zdefiniowanej przez użytkownika, a następnie sprawdzać plany i statystyki funkcji przed aktualizacją i po jej wybraniu oraz po niej i sprawdzać, kiedy wykonanie zostanie zarejestrowane.

Powiedzmy, że mamy tę funkcję:

CREATE FUNCTION dbo.mask(@x varchar(32))
RETURNS varchar(32) WITH SCHEMABINDING
AS
BEGIN
  RETURN (SELECT 'XX' + SUBSTRING(@x, 3, LEN(@x)-4) + 'XXXX');
END
GO

I ta tabela:

CREATE TABLE dbo.Floobs
(
  FloobID int IDENTITY(1,1),
  Name varchar(32),
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)),
  CONSTRAINT pk_Floobs PRIMARY KEY(FloobID),
  CONSTRAINT ck_Name CHECK (LEN(Name)>=8)
);
GO

Sprawdźmy sys.dm_exec_function_stats(nowość w SQL Server 2016 i Azure SQL Database) przed i po wstawieniu, a następnie po wybraniu:

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

INSERT dbo.Floobs(Name) VALUES('FrankieC');

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

SELECT * FROM dbo.Floobs;

SELECT o.name, s.execution_count
FROM sys.dm_exec_function_stats AS s
INNER JOIN sys.objects AS o
ON o.[object_id] = s.[object_id]
WHERE s.database_id = DB_ID();

Nie widzę wywołania funkcji na wstawce, tylko na select.

Teraz upuść tabele i zrób to jeszcze raz, tym razem zmieniając kolumnę na PERSISTED:

DROP TABLE dbo.Floobs;
GO
DROP FUNCTION dbo.mask;
GO

...
  MaskedName AS CONVERT(varchar(32), dbo.mask(Name)) PERSISTED,
...

I widzę, że dzieje się wręcz przeciwnie: dostaję wykonanie zalogowane na wstawce, ale nie na zaznaczeniu.

Nie masz wystarczająco nowoczesnej wersji programu SQL Server sys.dm_exec_function_stats? Bez obaw, jest to również ujęte w planach wykonania .

W przypadku wersji nietrwałej widzimy funkcję, do której odwołuje się tylko w select:

Podczas gdy wersja utrwalona pokazuje tylko obliczenia wykonywane na wstawce:

Teraz Martin porusza świetny punkt w komentarzu : nie zawsze tak będzie. Utwórzmy indeks, który nie obejmuje utrwalonej kolumny obliczeniowej, i uruchommy kwerendę, która korzysta z tego indeksu, i zobaczmy, czy wyszukiwanie pobiera dane z istniejących utrwalonych danych, czy oblicza dane w czasie wykonywania (upuść i ponownie utwórz funkcję i stolik tutaj):

CREATE INDEX x ON dbo.Floobs(Name);
GO

INSERT dbo.Floobs(name) 
  SELECT LEFT(name, 32) 
  FROM sys.all_columns 
  WHERE LEN(name) >= 8;

Teraz uruchomimy kwerendę, która korzysta z indeksu (tak czy inaczej domyślnie używa indeksu w tym konkretnym przypadku, nawet bez klauzuli where):

SELECT * FROM dbo.Floobs WITH (INDEX(x))
  WHERE Name LIKE 'S%';

Widzę dodatkowe wykonania w statystykach funkcji, a plan nie leży:

Tak więc odpowiedź brzmi: TO ZALEŻY . W takim przypadku SQL Server uznał, że taniej będzie ponownie obliczyć wartości, niż przeprowadzić wyszukiwanie. To może się zmienić z powodu różnych czynników, więc nie polegaj na tym. Może się to zdarzyć w obu kierunkach, niezależnie od tego, czy używana jest funkcja zdefiniowana przez użytkownika; Użyłem go tylko tutaj, ponieważ znacznie ułatwiło to zilustrowanie.

— Aaron Bertrand
źródło

Bardzo doceniam, że nigdy nie kwestionowałem zachowania silnika podczas obliczania wyników.

— Arthur D

8

@ArthurD Jest to decyzja optymalizacyjna oparta (głównie) na szacunkowych kosztach każdej alternatywy, zobacz moją odpowiedź na inne pytanie tutaj.

— Paul White mówi GoFundMonica

-1

Odpowiedź na to pytanie naprawdę brzmi „to zależy”. Właśnie natknąłem się na przykład, w którym SQL Server używa indeksu w utrwalonej kolumnie obliczeniowej, ale nadal wykonuje tę funkcję, tak jakby wartości nigdy nie były utrwalane na początku. Może to mieć związek z typem danych kolumny ( nvarchar(37)) lub ewentualnie wielkością tabeli (około 7 milionów wierszy), ale SQL Server postanowił zignorować persistedsłowo kluczowe, jak się wydaje, w tym konkretnym przypadku.

W tym przypadku kluczem podstawowym w tabeli jest TransactionID, który jest również obliczoną i utrwaloną kolumną. Plan wykonania generuje skanowanie indeksu iw tabeli zawierającej tylko 7 milionów wierszy uruchomienie tego prostego zapytania zajmuje 2-3 minuty, ponieważ funkcja jest uruchamiana ponownie w każdym wierszu, a wartości nie wydają się być utrwalane w indeks.

— Corpulent Brony
źródło