Udostępnij za pośrednictwem

  • Artykuł

Przy użyciu kolumn Sparse

Kolumny rozrzedzone są zwykłe kolumny, które mają zoptymalizowane magazynu dla wartości null.Kolumny sparse zmniejszyć wymagania dotyczące miejsca dla wartości null jest związany z większe obciążenie pobrać wartości niepustych.Warto rozważyć sparse kolumn po co najmniej 20 do 40 procent miejsca zapisania.Sparse kolumn i zestawy kolumna są definiowane za pomocą Tworzenie tabeli lub ALTER TABLE instrukcji.

Sparse kolumn można używać z zestawami kolumna i przefiltrowane indeksów:

  • Zestawy kolumn

    INSERT, UPDATE i instrukcji DELETE można odwoływać się do kolumn sparse według nazwy.Można również wyświetlać i pracować z wszystkich sparse kolumna tabela, które są łączone w pojedynczą kolumna XML.W tej zestaw kolumn jest nazywana zestawem kolumna.Aby uzyskać więcej informacji na temat zestawów kolumna Zobacz Korzystanie z zestawów kolumny.

  • Filtrowane indeksów

    Ponieważ sparse kolumny mają wiele wierszy wartości null, są one szczególnie odpowiednie dla filtrowanych indeksów.Filtrowane indeksu na kolumna rozrzedzona można tylko wiersze, które zostały wypełnione wartości indeksu.Tworzy indeks mniejsze i bardziej wydajne.Aby uzyskać więcej informacji, zobacz Filtrowane wskazówek indeksu.

Sparse kolumny i przefiltrowane indeksy włączania aplikacji, takich jak Windows SharePoint Services, na efektywne przechowywanie i dostęp do dużej liczby właściwości zdefiniowane przez użytkownika za pomocą SQL Server.

Właściwości kolumny Sparse

Sparse kolumny mają następujące cechy:

  • Aparat baz danych programu SQL Server Używa SPARSE słowo kluczowe w definicji kolumna optymalizować magazyn wartości w tej kolumnie.Dlatego gdy wartość kolumna jest wartość NULL dla każdego wiersza w tabela, wartość wymaga nie magazynu.

  • Widoki wykazu dla tabela zawierającej kolumny sparse są takie same, jak w przypadku typowego tabela.sys.columns Wykazu widok zawiera wiersz dla każdej kolumna w tabela i zestaw kolumn , jeśli jest zdefiniowana.

  • Kolumny rozrzedzone są właściwość warstwy magazynu zamiast tabela logicznych.Dlatego SELECT...W instrukcja nie są kopiowane za pośrednictwem właściwość kolumna rozrzedzona do nowej tabela.

  • Funkcja COLUMNS_UPDATED zwraca varbinary wartość kolumny, które zostały zaktualizowane podczas DML akcja.Bity, które są zwracane przez funkcja COLUMNS_UPDATED są następujące:

    • Gdy kolumna rozrzedzona jest jawnie zaktualizowane odpowiadający mu bit w tym kolumna rozrzedzona jest ustawiona na 1, a bit w zestaw kolumn jest ustawiona na 1.

    • Gdy zestaw kolumn jest jawnie zaktualizowane bit dla zestaw kolumn jest ustawiona na 1, a bity wszystkie kolumny sparse w tej tabela są ustawione na 1.

    • Dla operacji wstawiania są wszystkie bity zestaw 1.

    Aby uzyskać informacje dotyczące zestawów kolumn, zobacz Korzystanie z zestawów kolumny.

Następujące typy danych nie może być określony jako SPARSE:

geography

text

geometry

timestamp

image

user-defined data types

ntext

Szacowane oszczędności miejsca przez typ danych

Kolumny sparse wymagają więcej miejsca dla wartości niepustych niż wymagane dla identycznych danych, które nie jest oznaczony jako SPARSE miejsce.W poniższych tabelach przedstawiono wykorzystanie miejsca dla każdego typu danych.Procent NULL kolumna wskazuje, jaki procent danych musi mieć wartość NULL dla oszczędności netto miejsca 40 procent.

Typy danych o stałej długości

Typ danych

Nonsparse bajty

Bajty sparse

Procent wartości NULL

bit

0.125

4.125

98%

tinyint

1

5

86%

smallint

2

6

76%

int

4

8

64%

bigint

8

12

52%

real

4

8

64%

float

8

12

52%

smallmoney

4

8

64%

money

8

12

52%

smalldatetime

4

8

64%

datetime

8

12

52%

uniqueidentifier

16

20

43%

date

3

7

69%

Typy danych Precision Dependent–Length

Typ danych

Nonsparse bajty

Bajty sparse

Procent wartości NULL

datetime2(0)

6

10

57%

datetime2(7)

8

12

52%

time(0)

3

7

69%

time(7)

5

9

60%

datetimetoffset(0)

8

12

52%

datetimetoffset (7)

10

14

49%

decimal/numeric(1,s)

5

9

60%

decimal/numeric(38,s)

17

21

42%

vardecimal(p,s)

Użyj decimal typu jako konserwatywnego szacowania.

Typy danych Dependent–Length danych

Typ danych

Nonsparse bajty

Bajty sparse

Procent wartości NULL

sql_variant

Zależy od typu danych podstawowych

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 typu plus 2 lub 4 bajty.

Ograniczenia dla kolumn Sparse

Sparse kolumna może być dowolny SQL Server Typ danych i zachowują się jak inne kolumna z następującymi ograniczeniami:

  • A kolumna rozrzedzona musi być nullable i nie może mieć właściwości ROWGUIDCOL lub tożsamości.

  • A kolumna rozrzedzona nie może być następujących typów danych: text, ntext, image, timestamp, user-defined data type, geometry, or geography; lub mieć atrybut FILESTREAM.

  • A kolumna rozrzedzona nie może mieć wartość domyślną.

  • A kolumna rozrzedzona nie można powiązać do reguły.

  • Chociaż kolumny obliczanej mogą zawierać kolumna rozrzedzona, kolumną obliczaną, nie można oznaczyć jako SPARSE.

  • A kolumna rozrzedzona nie może być częścią indeks klastrowany lub unikatowy indeks klucz podstawowego.Jednakże trwałe i nonpersisted kolumny obliczane, zdefiniowane w kolumnach sparse może być częścią klucz klastrowanego.

  • A kolumna rozrzedzona nie można użyć jako klucz partycji indeks klastrowany lub sterty.Jednak kolumna rozrzedzona może być używany jako klucz partycji indeks nieklastrowany.

  • A kolumna rozrzedzona nie może być częścią typ tabela zdefiniowanej przez użytkownika, które są używane w tabela, zmiennych i parametrów oródwierszową.

  • Sparse kolumn są niezgodne z kompresji danych.Dlatego sparse kolumny nie można dodać do skompresowanego tabel ani można skompresować wszystkie tabele zawierające sparse kolumn.

  • Zmienianie kolumna z odstępem nonsparse lub nonsparse do rozrzedzenia wymaga zmiany formatu kolumna. Aparat baz danych programu SQL Server Używa do wykonania tej zmiany następującą procedurę:

    1. Dodaje nową kolumna do tabela w nowy rozmiar i format.

    2. Dla każdego wiersza w tabela, aktualizacje i wartości przechowywanych w starym kolumna do nowej kolumna.

    3. Stare kolumna powoduje usunięcie schematu tabela.

    4. Przebudowuje odzyskania miejsca zajmowanego przez stary kolumna w tabela.

    Ostrzeżenie

    Krok 2 może się nie powieść, gdy rozmiar danych w wierszu przekracza maksymalny dopuszczalny rozmiar wiersza.Rozmiar ten obejmuje rozmiaru danych przechowywanych w starym kolumna i zaktualizowane dane przechowywane w nowej kolumnie.Limit ten jest 8060 bajtów dla tabel, które nie zawierają żadnych kolumn rozrzedzony lub 8018 bajtów dla tabel zawierających sparse kolumn.Ten błąd może wystąpić, nawet jeśli wszystkie kolumny uprawnionych została przesunięta poza wiersz.Aby uzyskać więcej informacji, zobacz Przepełnienie wiersz danych powyżej 8 KB.

  • Gdy zmienisz nonsparse kolumny do kolumna rozrzedzona sparse kolumna zajmie więcej miejsca dla wartości niepustych.Jeśli wiersz jest zbliżony do wiersza maksymalny limit rozmiaru, operacja może się nie powieść.

SQL ServerTechnologie, które obsługują Sparse kolumn

W tej sekcji opisano sposób sparse kolumn są obsługiwane w następującej SQL Server technologii:

  • replikacja transakcyjnej

    replikacja transakcyjnej obsługuje sparse kolumnas, ale nie obsługuje kolumna zestawy, które mogą być używane z odstępem kolumnas.Aby uzyskać więcej informacji na temat zestawów kolumna Zobacz Korzystanie z zestawów kolumny.

    Replikacja SPARSE atrybut jest określana przez opcję schematu jest określona przy użyciu sp_addarticle lub za pomocą Właściwości artykułu okno dialogowe w SQL Server Management Studio.We wcześniejszych wersjach SQL Server nie obsługują sparse kolumn.Zreplikować dane do wcześniejszej wersja, określić SPARSE atrybut nie powinien replikowany.

    Dla tabel, które są publikowane nie można dodać dowolny sparse kolumna do tabela lub zmienić sparse właściwość istniejącej kolumna.Jeśli działanie takie jest wymagane, upuść ponownie i publikacja.

  • Scalania replikacja

    Replikacja scalania nie obsługuje sparse kolumna lub zestawy kolumn.

  • Śledzenie zmian

    Obsługa rozrzedzania śledzenia zmian kolumnas i kolumna ustawia.Gdy zestaw kolumn jest aktualizowany do tabela śledzenia zmian traktuje to jako aktualizacja całego wiersza.Nie szczegółowe śledzenie zmian jest pod warunkiem uzyskania dokładnie zestaw sparse kolumn, które są aktualizowane przez zestaw kolumn operacji aktualizacji.Jeśli kolumny rozrzedzone są aktualizowane jawnie poprzez DML instrukcja, śledzenie ich zmian będzie działać normalnie i można określić dokładny zestaw kolumn zmienione.

  • Zmienianie przechwytywanie danych

    Zmiana przechwytywania danych obsługuje sparse kolumn, ale nie obsługuje zestawów kolumna.

  • Właściwość sparse kolumna nie jest zachowywane podczas kopiowania tabela.

Przykłady

W tym przykładzie tabela dokument zawiera zestaw wspólnych zawierającej kolumny DocID i Title.Grupy produkcji chce ProductionSpecification i ProductionLocation kolumna dla wszystkich dokumentów produkcji.Zespół marketingu chce MarketingSurveyGroup kolumna dla dokumentów handlowych.Kod w tym przykładzie tworzy tabela sparse kolumn, wstawia dwa wiersze do tabela, a następnie wybiera dane z tabela.

Ostrzeżenie

Ta tabela ma tylko pięć kolumn, aby ułatwić wyświetlanie i przeczytać.Deklarowanie sparse kolumny za nullable jest opcjonalna, jeśli opcja ANSI_NULL_DFLT_ON jest zestaw.

USE AdventureWorks2008R2;
GO

CREATE TABLE dbo.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 dbo.DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);
GO

INSERT dbo.DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35');
GO

Zaznaczenie wszystkich kolumn z tabela zwraca zestaw wyników zwykłych.

SELECT DocID, Title, ProductionSpecification, ProductionLocation, MarketingSurveyGroup
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 zainteresowana danych marketingowych, chcą użyć listy kolumna, która zwraca tylko kolumna odsetek, jak pokazano w następującej kwerendzie.

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