Spis treści:

Kategoria:SQL Server


Typ sql_variant w SQL Server

Alternatywa dla nvarchar

Zdarza się, że w jakimś miejscu trzeba przechowywać dane różnych typów. Nie jest to dobra praktyka, bo uniemożliwia stosowanie wielu mechanizmów SQL Server, w tym tak ważnego jak kontrola typów. Jeżeli istnieje taka mozliwość, lepiej zastosować konkretny typ. Jeżeli możliwości nie ma, można zastosować typ, który przyjmie wszystko. Do przechowywania takich niezidentyfikowanych wartości stosuje się typ, do którego można skonwertować największą liczbę innych typów. Najczęściej rozważa się czterech kandydatów:

  • nvarchar(X) - pole tekstowe. Zaletą jest czytelna postać wartości. Wadą jest zależny od ustawień regionalnych i językowych format wartości (np. data).
  • varbinary(X) - pole binarne. Zaletą jest zwięzły, binarny format zapisu, wartości zajmują mało miejsca. Wadą jest mała czytelność. Każde pole jest typowo komputerową reprezentacją wartości.
  • xml - pole typu xml. Zaletą jest uniwersalność, czytelność, łatwość przetwarzania, umożliwia przechowywanie złożonych struktur. Wadą jest rozmiar.
  • sql_variant - pole ogólne. Charakteryzuje się bardzo prostą obsługą, wbudowaną w SQL Server. Jako jedyne pozwala uzyskać informacje o rzeczywistych typach przechowywanych w kolumnie. Wadą jest rozmiar pola ograniczony do 8 kB i brak możliwości przechowywania niektórych typówsql_variant nie pozwoli na przechowywanie następujących typów: varchar(max), varbinary(max), nvarchar(max), xml, text, ntext, image, rowversion (timestamp), sql_variant, geography, hierarchyid, geometry, datetimeoffset, typy definiowane przez użytkownika..

Dziś zajmę się typem sql_variant jako najprzyjemniejszym w obsłudze, ale także jako typem mało znanym.

Przeanalizujmy zatem typ sql_variant w szczegółach.

Definiowanie kolumny typu sql_variant

Nagłówek dodałem bardziej w celach porządkowych niż w celu wyjaśnienia jakiegoś specyficznego sposobu deklaracji. Popatrzmy na poniższy przykład:

CREATE TABLE Variant
(
  ID int IDENTITY PRIMARY KEY,
  Name nvarchar(20) NOT NULL,
  CustomObject sql_variant NOT NULL
)

Widać wyraźnie, że typ sql_variant definiuje się tak samo jak każdy inny typ.

Wstawianie i pobieranie rekordów z typem sql_variant

Wstawianie rekordów również nie powinno zaskakiwać. Wspomniałem wcześniej, że obsługa typu sql_variant jest wbudowana w SQL Server - nic więc dziwnego, że wstawianie wygląda tak:

INSERT INTO Variant VALUES (N'Obwód''20mm');
INSERT INTO Variant VALUES (N'Dostępny', 1);
INSERT INTO Variant VALUES (N'Data aktywności'GETDATE());
INSERT INTO Variant VALUES (N'Współczynnik', 37.42)

Pobieranie jest równie łatwe:

SELECTFROM Variant

W wyniku zapytania otrzymamy następujący rezultat:

IDNameCustomObject
1Obwód20mm
2Dostępny1
3Data aktywności2013-10-25 18:30:54.340
4Współczynnik37.42

Oprócz bezsprzecznie przydatnej właściwości typu sql_variant czyli przechowywania różnych innych typów w jednej kolumnie, nie przedstawiłem żadnych innych specjalnych właściwości sql_variant. Czas więc to zmienić.

Pobieranie informacji o rzeczywistym typie danych

W tabeli Variant, a konkretnie w kolumnie CustomObject znajdują się różne typy. Przetwarzanie takich danych wymaga czasem pobrania nie tyle wartości, co nazwy rzeczywistego typu umieszczonego w rekordzie. Na szczęscie SQL Server ma do tego odpowiednie funkcje:

SELECT *, SQL_VARIANT_PROPERTY(CustomObject, 'BaseType') [Type] FROM Variant

Funkcja SQL_VARIANT_PROPERTY przyjmuje w postaci pierwszego argumentu kolumnę typu sql_variant, natomiast w drugim jedną z kilku predefiniowanych wartości. Popatrzmy na rozszerzony zestaw:

SELECT Name,
SQL_VARIANT_PROPERTY(CustomObject, 'BaseType') [Type],
SQL_VARIANT_PROPERTY(CustomObject, 'Precision') [Precision],
SQL_VARIANT_PROPERTY(CustomObject, 'Scale') Scale,
SQL_VARIANT_PROPERTY(CustomObject, 'TotalBytes') TotalBytes,
SQL_VARIANT_PROPERTY(CustomObject, 'Collation') [Collation],
SQL_VARIANT_PROPERTY(CustomObject, 'MaxLength') [MaxLength]
FROM Variant

Po wykonaniu zapytania otrzymamy taki oto rezultat:

NameTypePrecisionScaleTotalBytesCollationMaxLength
Obwódvarchar0012Polish_CI_AS8000
Dostępnyint1006NULL4
Data aktywnościdatetime23310NULL8
Współczynniknumeric429NULL5

Pokazany zestaw atrybutów powinien wystarczyć do zdecydowanej większości zastosowań. Jeżeli nie jest wystarczający (osobiście nigdy nie miałem potrzeby sięgnięcia po inne atrybuty), trzeba sięgnąć po alternatywne rozwiązanie - przechowywać wartości w postaci xml lub w jakimś własnym formacie.

Rozmiary kolumn sql_variant

Rozmiary pól podane w atrybutach mogą niektórych zaskakiwać, dlatego napiszę o nich parę słów. Kolumna TotalBytes przechowuje rozmiar całego typu - tego co jest w środku, włącznie z narzutem związanym z typem wariantowym. Kolumna MaxLength przechowuje maksymalny rozmiar typu przechowywanego w polu. Będzie to zatem 4 dla int, 10 dla varchar(10), 20 dla nvarchar(10). Wartość 8000 dla pierwszego rekordu powstała w wyniku niejawnej konwersji stałego łańcucha znaków na domniemany typ nvarchar(8000). Puryści mogą tę wartość rzutować na jakiś mniejszy typ.

Słowo należy się też fizycznemu rozmiarowi poszczególnych kolumn. Popatrzmy na poniższe zapytanie:

INSERT INTO Variant VALUES (N'Tekst'CAST('Tekst' as nvarchar(10)));

SELECT NameDATALENGTH(CustomObject) [DataLength]
FROM Variant

Specjalnie dorzuciłem jeszcze jeden rekord. Po wykonaniu powyższych instrukcji otrzymamy następujące dane:

NameDataLength
Obwód4
Dostępny4
Data aktywności8
Współczynnik5
Tekst10

Funkcja DATALENGTH na typie wariantowym działa tak, jakby działała na rzeczywistym typie przechowywanym przez pole wariantowe. Warto przypomnieć, że dla typów nvarchar(X) zwracana jest długość łańcucha w bajtach (dla nvarchar oraz nchar są to dwa bajty na znak.

Wewnętrzny narzut typów wariantowych

Wiadomo, że każda funkcja musi być w jakiś sposób zaimplementowana i gdzieś te dodatkowe informacje muszą być przechowywane. Aby dowiedzieć się, jaki jest wewnętrzny narzut typu sql_variant mozna posłużyć się następującym zapytaniem:

SELECT Name,
SQL_VARIANT_PROPERTY(CustomObject, 'BaseType') [BaseType],
CAST(SQL_VARIANT_PROPERTY(CustomObject, 'TotalBytes'AS int)-DATALENGTH(CustomObject) Internal
FROM Variant

Otrzymamy taki oto rezultat:

NameBaseTypeInternal
Obwódvarchar8
Dostępnyint2
Data aktywnościdatetime2
Współczynniknumeric4
Tekstnvarchar8

Trzecia kolumna, Internal, określa narzut powstający w wyniku przechowywania informacji o danym typie. Narzut można pobrać zapytaniem, ale można go również obliczyć za pomocą następujących reguł:

  • Każda wartość musi mieć informacje o typie - 2 bajty.
  • Typy numeric oraz decimal mają dodatkowo: 1 bajt na liczbę cyfr oraz 1 bajt na liczbę cyfr po przecinku.
  • Typ varbinary ma dodatkowe dwa bajty określające rozmiar.
  • Typy tekstowe mają dodatkowo: 2 bajty na rozmiar oraz 4 bajty na wartość COLLATION.

To chyba tyle na temat dodatkowych bajtów zajmowanych przez tytułowego bohatera. Narzut nie jest wielki, zwłaszcza w stosunku do prostoty i elastyczności posługiwania się typem sql_variant.

Podsumowanie

Typ sql_variant jest dość ciekawym narzędziem. Odpowiednio wykorzystany może dać dużo dobrego. Należy go jednak wykorzystywać tylko tam, gdzie jest to niezbędne. Tam, gdzie nie da się z góry określić typu, gdzie trudno zastosować relacyjne rozwiązanie. To alternatywa do tych rozwiązań, w których różne wartości przechowywane są w postaci tekstowej. Typ sql_variant może być częścią klucza głównego, może uczestniczyć w relacjach - jest zatem w pełni funkcjonalnym typem, który najzwyczajniej w świecie wypadałoby dobrze poznać.

Kategoria:SQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Ciekawy artykuł.
Czy można za pomocą EF wysłać swoje zapytanie?
Czy lepiej do tego użyć ADO.net i DataTable?