Создание псевдонима типа данных в Microsoft SQL Server на T-SQL

SQL Server от компании Microsoft позволяет создавать псевдоним типа данных, а также полноценный пользовательский тип данных, это можно сделать с помощью конструкции CREATE TYPE языка T-SQL, а как это реализуется, я сейчас покажу на примерах.

Пользовательский тип данных создается с помощью класса сборки в среде Microsoft.NET Framework CLR и так как мы с Вами еще не рассматривали возможность MS SQL Server работы с CLR, рассматривать создание пользовательского типа данных мы пока не будем.

А вот псевдоним типа данных можно создать и без использования CLR, так как он основывается на собственном системном типе данных. Поэтому сегодня мы будем разбирать возможность создания псевдонима типа, а также определяемого пользователем табличного типа данных, который иногда может быть достаточно полезным.

Примечание! Все примеры ниже будут рассмотрены на версии Microsoft SQL Server 2012 Express.

Псевдоним типа данных в Microsoft SQL Server

Псевдоним типа данных – это тип данных, который основывается на уже существующем системном типе. Псевдонимы типов данных можно использовать в тех случаях, когда нам необходимо, и мы должны быть в этом уверены, чтобы в нескольких таблицах определенные столбцы имели одинаковые тип, длину данных, и возможность иметь значение NULL.

Также их можно использовать для упрощения написания и понимания объектов базы данных, т.е. если Вы в базе данных для определенных целей достаточно часто используете один и тот же тип данных, Вы можете создать его псевдоним с понятным названием, которое будет лучше характеризовать назначение данного типа данных.

Для создания псевдонима типа данных необходимо придумать его название, определить системный тип данных, на котором будет основываться псевдоним, а также указать может или не может создаваемый псевдоним принимать значение NULL, если не указать, то будет применяться значение по умолчанию, которое разрешает использование NULL.

Для создания и удаления псевдонима типа данных можно использовать как графические инструменты Management Studio (SSMS), так и язык T-SQL, т.е. конструкцию CREATE TYPE для создания и DROP TYPE для удаления.

Если псевдоним типа данных создать в пользовательской базе данных, то он будет присутствовать только в ней, но если создать псевдоним в базе данных model (шаблон для создания всех пользовательских баз), он будет присутствовать во всех новых пользовательских базах данных.

Итак, давайте для примера создадим псевдоним типа данных, при этом я покажу возможность его создания как с помощью графических инструментов SSMS, так и с помощью CREATE TYPE языка T-SQL. Допустим, название нашего тестового псевдонима будет MyTestType, основан он будет на системном типе VARCHAR с длиной 100, и он будет иметь возможность принимать значение NULL.

Примечание! Для примера создавать псевдоним типа данных я буду в базе данных Test, которую я предварительно создал. О том, как создавать базы данных мы говорили в материале «Как создать базу данных в MS SQL Server».

Создание псевдонима типа данных с помощью Management Studio

Для того чтобы создать псевдоним типа данных с помощью графических инструментов Management Studio, необходимо в обозревателе объектов открыть базу данных, в которой планируется создать псевдоним типа данных и найти пункт «Определяемые пользователем типы данных», полный путь – «База данных->Test-> Программирование -> Типы -> Определяемые пользователем типы данных».

Затем щелкнуть правой кнопкой мыши по данному пункту и выбрать «Создать определяемый пользователем тип данных».

В итоге откроется окно для создания псевдонима типа данных, где мы указываем схему, имя псевдонима, тип данных, на основе которого будет создан псевдоним, его длину, а также отмечаем галочку «Разрешить значения NULL», т.е. этот псевдоним может принимать значения NULL, жмем «OK».

В обозревателе объектов в пункте «Типы» отобразится созданный нами псевдоним типа данных с названием MyTestType.

Заметка! Обзор Azure Data Studio. Что это за инструмент и для чего он нужен.

Создание псевдонима типа данных с помощью CREATE TYPE

Для создания псевдонима типа данных в T-SQL используется конструкция CREATE TYPE. Для того чтобы создать псевдоним, открываем окно редактирования запроса («Создать запрос») и пишем следующую инструкцию:

 
   CREATE TYPE dbo.MyTestType FROM VARCHAR (100) NULL
   GO

Данной инструкцией можно заменить все действия, которые мы делали чуть выше, когда для создания псевдонима типа использовали графические инструменты Management Studio, т.е. эти способы абсолютно идентичны, они создают одинаковые псевдонимы.

Достаточно много SQL инструкций языка T-SQL мы рассмотрели в «Справочнике T-SQL для начинающих», поэтому кому интересно можете посмотреть.

Определяемый пользователем табличный тип данных в Microsoft SQL Server

В SQL сервере есть специальный тип данных Table, который используется для хранения результирующего набора данных. Другими словами мы можем объявить переменную, которая может хранить табличные данные, структура которых определяется в момент объявления табличной переменной. Определяемый пользователем табличный тип данных основан на системном табличном типе данных.

Возможность создавать табличные типы данных нужна нам, для того чтобы упростить создание табличных переменных с часто используемой структурой, а также для создания параметров, которые возвращают табличные значения, например, чтобы отправлять несколько строк данных в хранимую процедуру или функцию, не создавая тем самым большое количество входящих параметров или временных таблиц с параметрами. В процедурах или функциях возвращающий табличное значение параметр должен быть объявлен как READONLY, т.е. только для чтения

Для создания определяемого пользователем табличного типа данных также как и для создания псевдонима типа используется конструкция CREATE TYPE. Для примера давайте создадим табличный тип данных, который будет содержать два столбца. Для этого в редакторе запросов среды Management Studio пишем следующий код:

 
   CREATE TYPE dbo.MyTestTableType AS TABLE (
        ProductName VARCHAR (100) NULL,
        Price MONEY NULL
    )
   GO

В обозревателе объектов в пункте «Типы ->Определяемые пользователем табличные типы» отобразится созданный нами табличный тип данных.

Пример использования псевдонима типа данных и определяемого пользователем табличного типа

Для того чтобы проверить работу псевдонима типа данных и определяемого пользователем табличного типа давайте создадим процедуру, которая будет принимать два параметра. Первый параметр с типом MyTestType (т.е. VARCHAR(100)), а второй MyTestTableType (т.е. табличный тип). Возвращать она будет просто данные из входящих параметров.

Код создания процедуры:

 
   CREATE PROCEDURE dbo.MyTestProcedure  @TextComment MyTestType, 
                                         @TmpTable MyTestTableType READONLY
    AS
   BEGIN
        SELECT TMP.*, @TextComment AS TextComment 
        FROM  @TmpTable TMP
   END

Теперь давайте напишем SQL инструкцию, в которой мы создадим две переменные на основе наших псевдонимов, затем присвоим им значения, и выполним только что созданную процедуру, передав ей переменные в качестве параметров.

 
   DECLARE @TextComment MyTestType,
                @TestTable MyTestTableType

   SET @TextComment = 'Номер заказа 25.'

   INSERT INTO @TestTable (ProductName, Price)
        VALUES ('Принтер', 100)
   INSERT INTO @TestTable (ProductName, Price)
        VALUES ('Монитор', 200)

   EXEC dbo.MyTestProcedure @TextComment, @TestTable

Как видим, процедура работает, т.е. возвращает данные из входящих параметров. Также хотелось бы отметить, что для внесений изменений в псевдоним типа или определяемый пользователем табличный тип нужно удалить эти типы и создать заново.

Удаление псевдонима типа данных

Удалить псевдоним типа или определяемый пользователем табличный тип можно как с помощью графических инструментов среды Management Studio, так и с помощью языка T-SQL (DROP TYPE). Удаление возможно, только в том случае, если в базе данных отсутствуют таблицы, содержащие столбцы с псевдонимом типа, а также ссылающиеся на псевдоним типа или определяемый пользователем табличный тип вычисляемые столбцы, функции, процедуры или триггеры.

Давайте попробуем удалить псевдоним типа данных и определяемый пользователем табличный тип, когда в базе есть процедура, которая использует параметры на их основе.

 
   DROP TYPE dbo.MyTestType
   DROP TYPE dbo.MyTestTableType

У нас возникла ошибка, поэтому перед удалением псевдонима типа данных и определяемого пользователем табличного типа необходимо удалить все ссылающиеся объекты, в нашем случае это процедура MyTestProcedure.

Пробуем еще раз с удалением процедуры.

 
   DROP PROCEDURE dbo.MyTestProcedure
   DROP TYPE dbo.MyTestType
   DROP TYPE dbo.MyTestTableType

В этот раз все прошло успешно.

Заметка! Если Вас интересует SQL и T-SQL, рекомендую посмотреть мои видеокурсы по T-SQL, с помощью которых Вы «с нуля» научитесь работать с SQL и программировать с использованием языка T-SQL в Microsoft SQL Server.

На этом у меня все, надеюсь, материал был Вам полезен. Пока!