Udostępnij za pośrednictwem

Użyj kolumn rozproszonych

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w usłudze Microsoft Fabric

Kolumny rozrzedzone to zwykłe kolumny, które mają zoptymalizowane przechowywanie dla wartości Null. Kolumny rozrzedne zmniejszają wymagania dotyczące miejsca dla wartości null, ale wiążą się z większym nakładem podczas pobierania wartości nie-NULL. Rozważ użycie kolumn rzadszych, gdy zapisane miejsce wynosi co najmniej od 20% a 40%. Kolumny rozrzedłe i zestawy kolumn są definiowane przy użyciu instrukcji CREATE TABLE lub ALTER TABLE.

Kolumny rozrzedzone mogą być używane z zestawami kolumn i z filtrowanymi indeksami.

  • Zestawy kolumn

    Instrukcje INSERT, UPDATE i DELETE mogą odwoływać się do rozrzednych kolumn według nazwy. Można jednak również wyświetlać i pracować ze wszystkimi rozrzednymi kolumnami tabeli, które są połączone w jedną kolumnę XML. Ta kolumna jest nazywana zestawem kolumn. Aby uzyskać więcej informacji na temat zestawów kolumn, zobacz Use Column Sets.

  • Przefiltrowane indeksy

    Ponieważ rozrzedzone kolumny mają wiele wierszy o wartości null, są one szczególnie odpowiednie dla filtrowanych indeksów. Filtrowany indeks w kolumnie skąpo zapełnionej może indeksować tylko wiersze, które zawierają wartości. Spowoduje to utworzenie mniejszego i bardziej wydajnego indeksu. Aby uzyskać więcej informacji, zobacz Tworzenie filtrowanych indeksów.

Kolumny rozrzedzone i filtrowane indeksy umożliwiają aplikacjom, takim jak usługi Windows SharePoint Services, efektywne przechowywanie i uzyskiwanie dostępu do dużej liczby właściwości zdefiniowanych przez użytkownika przy użyciu programu SQL Server.

Właściwości kolumn rozrzedzanych

Kolumny rozrzedłe mają następujące cechy:

  • Silnik bazy danych SQL Server używa słowa kluczowego SPARSE w definicji kolumny, aby zoptymalizować przechowywanie wartości w tej kolumnie. W związku z tym, gdy wartość kolumny ma wartość NULL dla dowolnego wiersza w tabeli, wartości nie wymagają magazynu.

  • Widoki wykazu dla tabeli zawierającej rozrzedłe kolumny są takie same jak w przypadku typowej tabeli. Widok wykazu sys.columns zawiera wiersz dla każdej kolumny w tabeli i zawiera zestaw kolumn, jeśli jest zdefiniowany.

  • Kolumny rozrzedne są właściwością warstwy magazynowania, a nie tabelą logiczną. W związku z tym instrukcja SELECT ... INTO nie kopiuje właściwości kolumny rozrzednej do nowej tabeli.

  • Funkcja COLUMNS_UPDATED zwraca wartość typu varbinary, aby wskazać wszystkie kolumny, które zostały zaktualizowane podczas operacji DML. Bity zwracane przez funkcję COLUMNS_UPDATED są następujące:

    • Gdy kolumna rozrzedzona jest jawnie aktualizowana, odpowiedni bit dla tej rozrzednionej kolumny jest ustawiony na 1, a bit dla zestawu kolumn jest ustawiony na 1.

    • Gdy zbiór kolumn jest jawnie aktualizowany, bit dla tego zbioru jest ustawiany na 1, a bity dla wszystkich rozrzedzonych kolumn w tej tabeli są ustawiane na 1.

    • W przypadku operacji wstawiania wszystkie bity są ustawione na 1.

    Aby uzyskać więcej informacji na temat zestawów kolumn, zobacz Use Column Sets.

Nie można określić następujących typów danych jako SPARSE:

geografia
geometria
obrazu
ntext

tekst
znacznik czasu
typów danych zdefiniowanych przez użytkownika

Szacowane oszczędności miejsca według typu danych

Kolumny rozrzedane wymagają więcej miejsca do magazynowania dla wartości innych niż NULL niż miejsce wymagane dla identycznych danych, które nie są oznaczone jako SPARSE. W poniższych tabelach przedstawiono użycie miejsca dla każdego typu danych. Kolumna wartość procentowa wartości NULL wskazuje, jaki procent danych musi mieć wartość NULL dla oszczędności miejsca netto w wysokości 40 procent.

typy danych Fixed-Length

Typ danych Bajty niesparse Rozrzedzone bajty Wartość procentowa NULL
bit 0.125 5 98%
tinyint 1 5 86%
smallint 2 6 76%
int 4 8 64%
bigint 8 12 52%
rzeczywiste 4 8 64%
zmiennoprzecinkowa 8 12 52%
małe pieniądze 4 8 64%
pieniędzy 8 12 52%
smalldatetime 4 8 64%
data/godzina 8 12 52%
unikalny identyfikator 16 20 43%
daty 3 7 69%

Precyzja —Dependent-Length typy danych

Typ danych Bajty niesparowane Rzadkie bajty Wartość procentowa wartości NULL
datetime2(0) 6 10 57%
data/godzina2(7) 8 12 52%
czas (0) 3 7 69%
czas (7) 5 9 60%
datetimetoffset(0) 8 12 52%
datetimetoffset (7) 10 14 49%
dziesiętne/liczbowe (1,s) 5 9 60%
dziesiętne/liczbowe (38,s) 17 21 42%
vardecimal(p,s) Użyj typu dziesiętnego jako konserwatywnego oszacowania.

Dane - Typy DanychDependent-Length

Typ danych Bajty niespójne Bajty rozrzedzone Wartość procentowa wartości NULL
sql_variant Różni się w zależności od bazowego typu danych
varchar lub char 2* 4* 60%
nvarchar lub nchar 2* 4*+ 60%
varbinary lub binary 2* 4* 60%
xml 2* 4* 60%
hierarchyid 2* 4* 60%

*Długość jest równa średniej danych zawartych w typie, plus 2 lub 4 bajty.

In-Memory narzut wymagany w przypadku aktualizacji rozrzednych kolumn

Podczas projektowania tabel z rozrzedzonymi kolumnami należy pamiętać, że dla każdej kolumny rozrzedzonej, która nie jest pusta, wymagane jest dodatkowe 2 bajty przeciążenia, gdy wiersz jest aktualizowany. W wyniku tego dodatkowego wymagania dotyczącego pamięci aktualizacje mogą nieoczekiwanie zakończyć się niepowodzeniem z powodu błędu 576, gdy całkowity rozmiar wiersza, w tym obciążenie pamięcią, przekracza 8019, i nie można wypchnąć kolumn z wiersza.

Rozważmy przykład tabeli z 600 rozrzedczonymi kolumnami typu bigint. Jeśli istnieje 571 kolumn innych niż null, łączny rozmiar dysku wynosi 571 * 12 = 6852 bajty. Po dodaniu dodatkowego obciążenia wiersza i nagłówka rozrzedzonej kolumny, zwiększa się to do około 6895 bajtów. Strona nadal ma około 1124 bajtów dostępnych na dysku. Może to sprawiać wrażenie, że dodatkowe kolumny można pomyślnie zaktualizować. Jednak podczas aktualizacji w pamięci występuje dodatkowe obciążenie, które wynosi 2*(liczba kolumn rozrzednych innych niż null). W tym przykładzie, z uwzględnieniem dodatkowego obciążenia — 2 * 571 = 1142 bajtów — rozmiar wiersza na dysku wzrasta do około 8037 bajtów. Ten rozmiar przekracza maksymalny dozwolony rozmiar 8019 bajtów. Ponieważ wszystkie kolumny są typami danych o stałej długości, nie można ich wypchnąć z wiersza. W związku z tym aktualizacja kończy się niepowodzeniem z powodu błędu 576.

Ograniczenia dotyczące używania rozrzednych kolumn

Kolumny rozrzedłe mogą być dowolnym typem danych programu SQL Server i zachowywać się jak każda inna kolumna z następującymi ograniczeniami:

  • Kolumna rozrzedzona musi być podatna na wartość null i nie może mieć właściwości ROWGUIDCOL lub IDENTITY. Nie można stosować rzadkiej kolumny danych typu: text, ntext, image, timestamp, typu danych zdefiniowanego przez użytkownika, geometrylub geography, ani posiadać atrybut FILESTREAM.

  • Kolumna rozrzedła nie może mieć wartości domyślnej.

  • Nie można powiązać kolumny rozrzedzonej z regułą.

  • Chociaż obliczona kolumna może zawierać kolumnę rozrzedzoną, obliczona kolumna nie może być oznaczona jako SPARSE.

  • Maskę danych można zdefiniować w rozrzedzonej kolumnie, ale nie na rozrzedzonej kolumnie, która jest częścią zestawu kolumn.

  • Kolumna rozproszona nie może być częścią indeksu klastrowanego ani unikatowego indeksu klucza podstawowego. Jednak zarówno trwałe, jak i nietrwałe obliczone kolumny zdefiniowane na kolumnach rozrzedzonych mogą być częścią klucza klastrowanego.

  • Nie można użyć rzadkiej kolumny jako klucza partycji klastrowanego indeksu lub grupy. Można jednak użyć kolumny rozrzedzonej jako klucza partycji indeksu nieklastrowanego.

  • Kolumna rzadka nie może być częścią typu tabeli zdefiniowanego przez użytkownika, które są używane w zmiennych tabeli i parametrach o wartości tabelarycznej.

  • Kolumny rozrzedłe są niezgodne z kompresją danych. W związku z tym nie można dodać rozrzedzone kolumny do skompresowanych tabel ani nie można skompresować żadnych tabel zawierających kolumny rozrzedzone.

  • Zmiana kolumny z rzadkiej na gęstą lub z gęstej na rzadką wymaga zmiany formatu przechowywania kolumny. Aparat bazy danych programu SQL Server używa następującej procedury w celu wykonania tej zmiany:

    1. Dodaje nową kolumnę do tabeli w nowym rozmiarze i formacie przechowywania.

    2. Dla każdego wiersza w tabeli aktualizuje i kopiuje wartość przechowywaną w starej kolumnie do nowej kolumny.

    3. Usuwa starą kolumnę ze schematu tabeli.

    4. Ponownie kompiluje tabelę (jeśli nie ma indeksu klastrowanego) lub ponownie kompiluje indeks klastrowany w celu odzyskania miejsca używanego przez starą kolumnę.

    Notatka

    Krok 2 może zakończyć się niepowodzeniem, gdy rozmiar danych w wierszu przekracza maksymalny dozwolony rozmiar wiersza. Ten rozmiar obejmuje rozmiar danych przechowywanych w starej kolumnie oraz zaktualizowane dane przechowywane w nowej kolumnie. Ten limit wynosi 8060 bajtów dla tabel, które nie zawierają żadnych rozrzednych kolumn ani 8018 bajtów dla tabel zawierających rozrzedzoną kolumnę. Ten błąd może wystąpić nawet wtedy, gdy wszystkie kwalifikujące się kolumny zostały przeniesione poza wiersz.

  • W przypadku zmiany kolumny bez rozrzedzenia na kolumnę rozrzedzoną, kolumna rozrzedzona będzie zużywać więcej miejsca na wartości inne niż null. Gdy wiersz zbliża się do maksymalnego limitu rozmiaru wiersza, operacja może zakończyć się niepowodzeniem.

Technologie programu SQL Server, które obsługują kolumny rozrzedłe

W tej sekcji opisano, jak rozrzedłe kolumny są obsługiwane w następujących technologiach programu SQL Server:

  • Replikacja transakcyjna

    Replikacja transakcyjna obsługuje rozrzedłe kolumny, ale nie obsługuje zestawów kolumn, których można używać z rozrzednymi kolumnami. Aby uzyskać więcej informacji na temat zestawów kolumn, zobacz Use Column Sets.

    Replikacja atrybutu SPARSE jest określana przez opcję schematu określoną przy użyciu sp_addarticle lub przy użyciu okna dialogowego właściwości artykułu w programie SQL Server Management Studio. Starsze wersje programu SQL Server nie obsługują rozrzednych kolumn. Jeśli musisz replikować dane do starszej wersji, określ, że atrybut SPARSE nie powinien być replikowany.

    W przypadku opublikowanych tabel nie można dodać żadnych nowych rozrzednych kolumn do tabeli ani zmienić właściwości rozrzednicy istniejącej kolumny. Jeśli taka operacja jest wymagana, usuń i ponownie utwórz publikację.

  • Replikacja scalająca

    Replikacja scalania nie obsługuje rozrzednych kolumn ani zestawów kolumn.

  • Śledzenie zmian

    Śledzenie zmian obsługuje rozrzedłe kolumny i zestawy kolumn. Po zaktualizowaniu zestawu kolumn w tabeli śledzenie zmian traktuje to jako aktualizację całego wiersza. Nie podano szczegółowego śledzenia zmian, aby uzyskać dokładny zestaw rozrzedzonych kolumn, które są aktualizowane w ramach operacji aktualizacji zestawu kolumn. Jeśli rozrzednione kolumny są jawnie aktualizowane za pomocą instrukcji DML, śledzenie zmian na nich będzie działać zwykle i może zidentyfikować dokładny zestaw zmienionych kolumn.

  • Przechwytywanie zmian danych

    Przechwytywanie zmian danych obsługuje rozrzedłe kolumny, ale nie obsługuje zestawów kolumnowych.

  • Właściwość rzadka kolumny nie zostaje zachowana podczas kopiowania tabeli.

Przykłady

W tym przykładzie tabela dokumentów zawiera wspólny zestaw zawierający kolumny DocID i Title. Grupa Produkcyjna chce mieć kolumnę ProductionSpecification i ProductionLocation dla wszystkich dokumentów produkcyjnych. Grupa Marketing chce kolumny MarketingSurveyGroup dla dokumentów marketingowych. Kod w tym przykładzie tworzy tabelę, która używa rozrzednych kolumn, wstawia dwa wiersze do tabeli, a następnie wybiera dane z tabeli.

Notatka

Ta tabela zawiera tylko pięć kolumn, aby ułatwić wyświetlanie i odczytywanie. Deklarowanie kolumn rozrzedzonych jako dopuszczających wartość null jest opcjonalne, jeśli ustawiono opcję ANSI_NULL_DFLT_ON. Gdy SET ANSI_DEFAULTS jest WŁĄCZONE, SET ANSI_NULL_DFLT_ON jest włączony. ANSI_DEFAULTS jest domyślnie włączona dla większości dostawców połączeń. Aby uzyskać więcej informacji, zobacz SET ANSI_DEFAULTS.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStore  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL ) ;  
GO  
  
INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO

Wybranie wszystkich kolumn z tabeli zwraca zwykły zestaw wyników.

SELECT * FROM DocumentStore ;

Oto zestaw wyników.

DocID Title ProductionSpecification ProductionLocation MarketingSurveyGroup

1 Tire Spec 1 AXZZ217 27 NULL

2 Survey 2142 NULL NULL Men 25-35

Ponieważ dział produkcji nie jest zainteresowany danymi marketingu, chce użyć listy kolumn, która zwraca tylko interesujące kolumny, jak pokazano w poniższym zapytaniu.

SELECT DocID, Title, ProductionSpecification, ProductionLocation   
FROM DocumentStore   
WHERE ProductionSpecification IS NOT NULL ;

Oto zestaw wyników.

DocID Title ProductionSpecification ProductionLocation

1 Tire Spec 1 AXZZ217 27

Zobacz też