Posts tagged typy danych

Intro

Typy danych użytkownika tworzymy zazwyczaj z myślą, by ewentualne zmiany długości, precyzji czy skali przeprowadzać w sposób prostszy, bo scentralizowany. Niestety, Microsoft nie daje nam w SQL Server polecenia ALTER TYPE, ale i tak używamy własnych typów, a w razie potrzeby jakiejkolwiek zmiany przeprowadzamy operację opisaną tutaj: [EN] Call for voting – ALTER TYPE in SQL Server. W tej notce pokażę, jak można skryptować proste typy danych stworzone przez użytkowników w bazie danych. Nie będę opisywał skryptowania typów tabelarycznych i typów stworzonych w CLR.

Skryptowanie typów prostych

Zadanie: zeskryptować wszystkie proste typy danych użytkownika w bazie danych.

Dla każdego typu trzeba stworzyć kod sprawdzający, czy typ nie istnieje (trzeba zajrzeć do widoku sys.types) oraz wygenerować kod polecenia CREATE TYPE o składni jak poniżej (na podstawie Books Online):

CREATE TYPE [ schema_name. ] type_name
FROM base_type   [ ( precision [ , scale ] ) ] [ NULL | NOT NULL ];

Rozwiązanie:

USE AdventureWorks2008R2;
SET NOCOUNT ON;
SELECT
  'IF NOT EXISTS (
  SELECT * FROM sys.types
  WHERE [schema_id] = SCHEMA_ID(' +
  QUOTENAME(SCHEMA_NAME([schema_id]), '''') + ') AND name = N' +
  QUOTENAME(name, '''') + '
)
  CREATE TYPE ' + QUOTENAME(SCHEMA_NAME([schema_id])) + '.' + QUOTENAME(name) + '
  FROM ' + TYPE_NAME(system_type_id) +
  CASE
    WHEN system_type_id IN (
      165, --varbinary
      167, --varchar
      173, --binary
      175, --char
      231, --nvarchar
      239  --nchar
    )
      THEN '(' +
        CONVERT(
          varchar(4),
          CASE
            WHEN max_length = -1
              THEN 'max'
            ELSE max_length
          END
        ) + ')'
    WHEN system_type_id IN (
      41, --time
      42, --datetime2
      43  --datetimeoffset
    )
      THEN '(' + CONVERT(char(1), scale) + ')'
    WHEN system_type_id IN (
      106, --decimal
      108  --numeric
    )
      THEN '(' +
        CONVERT(varchar(2), precision) + ',' +
        CONVERT(varchar(2), scale) + ')'
    WHEN system_type_id = 62 --float
      THEN '(' + CONVERT(varchar(2), precision) + ')'
    ELSE ''
  END + ' ' +
  CASE
    WHEN is_nullable = 0
      THEN 'NOT NULL'
    ELSE 'NULL'
  END + ';
GO'
FROM sys.types
WHERE is_user_defined = 1 --typ użytkownika
AND is_table_type = 0     --nie typ tabelaryczny
AND is_assembly_type = 0; --nie typ CLR
GO

Fragment wyniku:

Uwagi: w Management Studio ustaw Results to Text, sugeruję też zwiększenie maksymalnej długości tekstu w jednej kolumnie zwracanej w wynikach zapytań (w menu głównym kliknij Tools – Options – Query Results – SQL Server – Results to Text i w polu Maximum number of characters displayed in each column wpisz 8192, a następnie uruchom ponownie Management Studio).

Komentarz do kodu:

• metadane typów zwraca widok sys.types (ale metadane typów tabelarycznych są też w sys.table_types),
• typy też mają dwuczłonowe nazwy (znajdują się w schematach) – stąd użycie funkcji SCHEMA_NAME,
• funkcja QUOTENAME jak zwykle służy mi do otaczania identyfikatorów nawiasami kwadratowymi i apostrofami,
• do uzyskania nazwy typu systemowego użytego na potrzeby danego typu użytkownika wykorzystałem funkcję TYPE_NAME wywołaną na identyfikatorze typu systemowego trzymanym w kolumnie system_type_id w widoku sys.types,
• przy niektórych typach systemowych niezbędne jest podanie maksymalnej długości (typy binarne i tekstowe),  dokładności (data i czas), precyzji (float) albo precyzji i skali (decimal/numeric),
• dzięki kolumnie is_nullable z widoku sys.types mogłem określić, czy typ pozwala domyślnie na przechowywanie wartości NULL, czy nie,
• w klauzuli WHERE odfiltrowałem typy systemowe, typy tabelaryczne i typy CLR.

Słowo podsumowania

Skryptowanie typów prostych jest stosunkowo łatwe. W zasadzie wszystkie niezbędne metadane są do odczytania z jednego widoku systemowego (sys.types). A przy okazji można się choćby oswoić z niekoniecznie oczywistym faktem – typy też są trzymane w schematach. Skryptowanie pozostałych typów użytkownika (tabelarycznych i CLR) jest już nieco trudniejsze. Ale to temat na być może kolejny wpis na moim blogu :-)

[EDYCJA] Dziękuję Krzysiowi Stachyrze, który wypatrzył w tym wpisie czeski błąd – oczywiście SQL Server nie daje polecenia ALTER TYPE. ALTER TABLE na szczęście mamy :-) [/EDYCJA]