Udostępnij za pośrednictwem

Korzystanie z zestawów kolumn

  • Artykuł

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

Tabele korzystające z kolumn rzadkich mogą wyznaczyć zestaw kolumn do zwrócenia wszystkich rzadkich kolumn w tabeli. Zestaw kolumn to nietypowa reprezentacja XML, która łączy wszystkie rozrzedzone kolumny tabeli w dane wyjściowe ze strukturą. Zestaw kolumn jest podobny do kolumny obliczeniowej w tym, że zestaw kolumn nie jest fizycznie przechowywany w tabeli. Zestaw kolumn różni się od kolumny obliczeniowej w tym, że zestaw kolumn jest bezpośrednio aktualizowalny.

Należy rozważyć użycie zestawów kolumn, gdy liczba kolumn w tabeli jest duża, a działanie na nich osobno jest kłopotliwe. Aplikacje mogą zobaczyć pewną poprawę wydajności podczas wybierania i wstawiania danych przy użyciu zestawów kolumn w tabelach, które mają wiele kolumn. Jednak wydajność zestawów kolumn można zmniejszyć, gdy wiele indeksów jest zdefiniowanych w kolumnach w tabeli. Wynika to z tego, że ilość pamięci wymaganej do wykonania planu wykonania wzrasta.

Aby zdefiniować zestaw kolumn, użyj słów kluczowych *<column_set_name>* FOR ALL_SPARSE_COLUMNS w instrukcjach CREATE TABLE CREATE TABLE lub ALTER TABLE instrukcji.

Wskazówki dotyczące używania zestawów kolumn

W przypadku korzystania z zestawów kolumn należy wziąć pod uwagę następujące wskazówki:

  • Kolumny rzadkie i zestaw kolumn można dodać w ramach tej samej instrukcji.

  • Nie można dodać zestawu kolumn do tabeli, jeśli ta tabela zawiera już rozrzedzona kolumny.

  • Nie można zmienić ani zmienić nazwy kolumny w zestawie kolumn. Aby zmienić zestaw kolumn, należy usunąć i ponownie utworzyć rozrzedzone kolumny i zestaw kolumn. Kolumny ze słowem kluczowym SPARSE można dodawać i odrzucać z tabeli.

  • Zestaw kolumn można dodać do tabeli, która nie zawiera żadnych rozrzedzonych kolumn. Jeśli rzadkie kolumny zostaną dodane później do tabeli, zostaną one wyświetlone w zestawie kolumn.

  • Tylko jeden zestaw kolumn jest dozwolony dla każdej tabeli.

  • Zestaw kolumn jest opcjonalny i nie jest wymagany, aby używać rozrzedzonych kolumn.

  • Nie można zdefiniować ograniczeń ani wartości domyślnych w zestawie kolumn.

  • Obliczone kolumny nie mogą zawierać kolumn zestawu kolumn.

  • Zapytania rozproszone nie są obsługiwane w tabelach zawierających zestawy kolumn.

  • Replikacja nie obsługuje zestawów kolumn.

  • Przechwytywanie zmian danych nie obsługuje zestawów kolumn.

  • Zestaw kolumn nie może być częścią żadnego rodzaju indeksu. Obejmuje to indeksy XML, indeksy pełnotekstowe i indeksowane widoki. Nie można dodać zestawu kolumn jako dołączonej kolumny w żadnym indeksie.

  • Nie można użyć zestawu kolumn w wyrażeniu filtru filtrowanego indeksu ani przefiltrowanych statystyk.

  • Gdy widok zawiera zestaw kolumn, zestaw kolumn jest wyświetlany w widoku jako kolumna XML.

  • Nie można uwzględnić zestawu kolumn w definicji indeksowanego widoku.

  • Widoki partycjonowane, które zawierają tabele zawierające zestawy kolumn, można aktualizować, gdy widok partycjonowany określa rozrzedzone kolumny według nazwy. Widok partycjonowany nie jest aktualizowalny, gdy odwołuje się do zestawu kolumn.

  • Powiadomienia dotyczące zapytań odwołujących się do zestawów kolumn nie są dozwolone.

  • Dane XML mają limit rozmiaru 2 GB. Jeśli połączone dane wszystkich kolumn rozrzednych innych niż NULL w wierszu przekraczają ten limit, zapytanie lub operacja DML spowoduje wystąpienie błędu.

  • Aby uzyskać informacje o danych zwracanych przez funkcję COLUMNS_UPDATED, zobacz Use Sparse Columns.

Wskazówki dotyczące wybierania danych z zestawu kolumn

Rozważ następujące wskazówki dotyczące wybierania danych z zestawu kolumn:

  • Koncepcyjnie zestaw kolumn jest typem aktualizowalnej, obliczonej kolumny XML, która agreguje zestaw podstawowych kolumn relacyjnych w pojedynczą reprezentację XML. Zestaw kolumn obsługuje tylko właściwość ALL_SPARSE_COLUMNS. Ta właściwość służy do agregowania wszystkich wartości innych niż NULL ze wszystkich rozrzedowanych kolumn dla określonego wiersza.

  • W edytorze tabel programu SQL Server Management Studio zestawy kolumn są wyświetlane jako edytowalne pole XML. Zdefiniuj zestawy kolumn w formacie:

    <column_name_1>value1</column_name_1><column_name_2>value2</column_name_2>...

    Przykłady wartości zestawu kolumn są następujące:

    <sparseProp1>10</sparseProp1><sparseProp3>20</sparseProp3>  

    <DocTitle>Bicycle Parts List</DocTitle><Region>West</Region>

  • Kolumny rozrzedzone, które zawierają wartości null, są pomijane z reprezentacji XML dla zestawu kolumn.

Ostrzeżenie

Dodanie zestawu kolumn zmienia zachowanie zapytań SELECT *. Zapytanie ustawi kolumnę jako kolumnę XML i nie zwróci pojedynczych kolumn rozrzedzonych. Projektanci schematów i deweloperzy oprogramowania muszą uważać, aby nie przerywać istniejących aplikacji. Poszczególne rozrzedzone kolumny mogą być nadal odpytywane według nazwy w instrukcji SELECT.

Wstawianie lub modyfikowanie danych w zestawie kolumn

Manipulowanie danymi w rzadkiej kolumnie może odbywać się przy użyciu nazwy poszczególnych kolumn lub odwołując się do nazwy zestawu kolumn i określając wartości zestawu kolumn przy użyciu formatu XML zestawu kolumn. Kolumny rzadkie mogą występować w dowolnej kolejności w kolumnie XML.

Gdy wartości kolumn o rozrzedzonych danych są wstawiane lub aktualizowane przy użyciu zestawu kolumn XML, wartości wstawione do bazowych kolumn o rozrzedzonych danych są niejawnie konwertowane z typu danych xml. W przypadku większości typów danych liczbowych, w tym bigint, int, smallint, tinyint, bit, floati real, pusta wartość w XML dla kolumny jest konwertowana na pusty ciąg. Spowoduje to wstawienie zera do kolumny, jak pokazano w poniższym przykładzie. Jednak podstawienie wartości 0 nie ma zastosowania do liczbowych i typów danych dziesiętnych, te wartości muszą zostać określone lub spowodują błąd konwersji.

CREATE TABLE t (i int SPARSE, cs xml column_set FOR ALL_SPARSE_COLUMNS);  
GO  
INSERT t(cs) VALUES ('<i/>');  
GO  
SELECT i FROM t;  
GO

W tym przykładzie nie określono żadnej wartości dla kolumny i, ale wartość 0 została wstawiona.

Używanie typu danych sql_variant

Typ daty sql_variant może przechowywać wiele różnych typów danych, takich jak int, chari data . Kolumna zbioru danych wyjściowych zawiera informacje o typie danych, takie jak skala, precyzja i ustawienia regionalne, które są skojarzone z wartością sql_variant jako atrybuty w generowanej kolumnie XML. Jeśli spróbujesz podać te atrybuty w niestandardowej instrukcji XML jako dane wejściowe operacji wstawiania lub aktualizacji w zestawie kolumn, niektóre z tych atrybutów są wymagane, a niektóre z nich mają przypisaną wartość domyślną. W poniższej tabeli wymieniono typy danych i wartości domyślne generowane przez serwer, gdy nie podano wartości.

Typ danych localeID* sqlOpcjePorównywania wersja sortowania SQL SqlSortId Maksymalna długość Precyzja Skala
char, varchar, binarne -1 "Wartość domyślna" 0 0 8000 Nie dotyczy** Nie dotyczy
nvarchar -1 "Wartość domyślna" 0 0 4000 Nie dotyczy Nie dotyczy
dziesiętny, zmiennoprzecinkowy, rzeczywisty Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy 18 0
integer, bigint, tinyint, smallint Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy
data/godzina2 Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy 7
przesunięcie daty/godziny Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy 7
data/godzina, data, smalldatetime Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy
pieniądze, małe pieniądze Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy
czasu Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy Nie dotyczy 7

* localeID -1 oznacza domyślne ustawienia regionalne. Angielskie ustawienia regionalne to 1033.

** Nie dotyczy = żadne wartości nie są danymi wyjściowymi tych atrybutów podczas operacji wybierania w zestawie kolumn. Generuje błąd, gdy wartość jest określona dla tego atrybutu przez obiekt wywołujący w reprezentacji XML podanej dla kolumny ustawionej w operacji wstawiania lub aktualizacji.

Bezpieczeństwo

Model zabezpieczeń zestawu kolumn działa podobnie do modelu zabezpieczeń, który istnieje między tabelami i kolumnami. Zestawy kolumn można wizualizować jako mini-tabelę, a operacja wybierania przypomina operację SELECT * dla tej mini-tabeli. Jednak relacja między zestawem kolumn a kolumnami rozrzedzonymi to relacja grupująca, a nie wyłącznie kontenerowa. Model zabezpieczeń sprawdza zabezpieczenia na zestawie kolumn i honoruje operacje DENY w podstawowych rzadkich kolumnach. Dodatkowe cechy modelu zabezpieczeń są następujące:

  • Uprawnienia zabezpieczeń można przyznać i odwołać z kolumny zestawu kolumn, podobnie jak każda inna kolumna w tabeli.

  • Polecenie GRANT lub REVOKE dla uprawnień SELECT, INSERT, UPDATE, DELETE i REFERENCES na zbiorze kolumn nie jest propagowane do podrzędnych kolumn członkowskich tego zestawu. Dotyczy tylko użycia kolumny zestawu kolumn. Uprawnienie DENY w zestawie kolumn jest propagowane do podstawowych rozrzedzonych kolumn tabeli.

  • Wykonanie instrukcji SELECT, INSERT, UPDATE i DELETE w kolumnie zestawu kolumn wymaga, aby użytkownik miał odpowiednie uprawnienia do kolumny zestawu kolumn, a także odpowiednie uprawnienie do wszystkich kolumn rozrzedzona w tabeli. Ponieważ zestaw kolumn reprezentuje wszystkie rzadkie kolumny w tabeli, musisz mieć uprawnienia do wszystkich rzadkich kolumn, a obejmuje to rzadkie kolumny, których być może nie zmieniasz.

  • Wykonanie instrukcji REVOKE na kolumnie rozrzedzonej lub zestawie kolumn powoduje przywrócenie domyślnych zabezpieczeń obiektu nadrzędnego.

Przykłady

W poniższych przykładach tabela dokumentów zawiera wspólny zestaw kolumn DocID i Title. Grupa Produkcyjna chce mieć kolumnę ProductionSpecification i ProductionLocation dla wszystkich dokumentów produkcyjnych. Grupa Marketing chce kolumnę MarketingSurveyGroup dla dokumentów marketingowych.

A. Tworzenie tabeli z zestawem kolumn

Poniższy przykład tworzy tabelę, która używa rozrzedzonych kolumn i zawiera kolumnowy zestaw SpecialPurposeColumns. Przykład 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.

USE AdventureWorks2022;  
GO  
  
CREATE TABLE DocumentStoreWithColumnSet  
    (DocID int PRIMARY KEY,  
     Title varchar(200) NOT NULL,  
     ProductionSpecification varchar(20) SPARSE NULL,  
     ProductionLocation smallint SPARSE NULL,  
     MarketingSurveyGroup varchar(20) SPARSE NULL,  
     MarketingProgramID int SPARSE NULL,  
     SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);  
GO

B. Wstawianie danych do tabeli przy użyciu nazw rozrzedzonych kolumn

W poniższych przykładach wstawimy dwa wiersze do tabeli utworzonej w przykładzie A. Przykłady używają nazw rozproszonych kolumn i nie odwołują się do zestawu kolumn.

INSERT DocumentStoreWithColumnSet (DocID, Title, ProductionSpecification, ProductionLocation)  
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27);  
GO  
  
INSERT DocumentStoreWithColumnSet (DocID, Title, MarketingSurveyGroup)  
VALUES (2, 'Survey 2142', 'Men 25 - 35');  
GO

C. Wstawianie danych do tabeli przy użyciu nazwy zestawu kolumn

Poniższy przykład wstawia trzeci wiersz do tabeli utworzonej w przykładzie A. Tym razem nazwy rozrzednych kolumn nie są używane. Zamiast tego jest używana nazwa zestawu kolumn, a wstawienie zawiera wartości dwóch z czterech rozrzedzonych kolumn w formacie XML.

INSERT DocumentStoreWithColumnSet (DocID, Title, SpecialPurposeColumns)  
VALUES (3, 'Tire Spec 2', '<ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>');  
GO

D. Obserwuj wyniki zestawu kolumn, gdy jest używana funkcja SELECT *

Poniższy przykład wybiera wszystkie kolumny z tabeli zawierającej zestaw kolumn. Zwraca kolumnę XML, która łączy wartości kolumn rozproszonych. Nie zwraca poszczególnych kolumn rozrzednych.

SELECT DocID, Title, SpecialPurposeColumns FROM DocumentStoreWithColumnSet ;

Oto zestaw wyników.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
2      Survey 2142  <MarketingSurveyGroup>Men 25 - 35</MarketingSurveyGroup>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

E. Obserwuj wyniki wybierania kolumny ustawionej według nazwy

Ponieważ dział produkcji nie jest zainteresowany danymi marketingu, w tym przykładzie dodano klauzulę WHERE w celu ograniczenia danych wyjściowych. W przykładzie użyto nazwy zestawu kolumn.

SELECT DocID, Title, SpecialPurposeColumns  
FROM DocumentStoreWithColumnSet  
WHERE ProductionSpecification IS NOT NULL ;

Oto zestaw wyników.

DocID  Title        SpecialPurposeColumns  
1      Tire Spec 1  <ProductionSpecification>AXZZ217</ProductionSpecification><ProductionLocation>27</ProductionLocation>  
3      Tire Spec 2  <ProductionSpecification>AXW9R411</ProductionSpecification><ProductionLocation>38</ProductionLocation>

F. Obserwowanie wyników wybierania rozrzednych kolumn według nazwy

Jeśli tabela zawiera zestaw kolumn, nadal można wykonywać zapytania względem tabeli przy użyciu poszczególnych nazw kolumn, jak pokazano w poniższym przykładzie.

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

Oto zestaw wyników.

DocID  Title        ProductionSpecification ProductionLocation`  
1      Tire Spec 1  AXZZ217                 27`  
3      Tire Spec 2  AXW9R411                38`

G. Aktualizowanie tabeli przy użyciu zestawu kolumn

W poniższym przykładzie trzeci rekord zostanie zaktualizowany o nowe wartości dla obu rozrzednych kolumn, które są używane przez ten wiersz.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification><ProductionLocation>38</ProductionLocation>'  
WHERE DocID = 3 ;  
GO

Ważny

Instrukcja UPDATE, która używa zestawu kolumn, aktualizuje wszystkie rozrzedzone kolumny w tabeli. Kolumny rzadkich, do których się nie odwołuje, są aktualizowane do NULL.

Poniższy przykład aktualizuje trzeci rekord, ale określa tylko wartość jednej z dwóch wypełnionych kolumn. Druga kolumna ProductionLocation nie jest uwzględniona w instrukcji UPDATE i jest aktualizowana do NULL.

UPDATE DocumentStoreWithColumnSet  
SET SpecialPurposeColumns = '<ProductionSpecification>ZZ285W</ProductionSpecification>'  
WHERE DocID = 3 ;  
GO

Następne kroki