CREATE TYPE (Transact-SQL)

Статья
12/11/2023

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

Создает в текущей базе данных псевдоним типа данных или определяемый пользователем тип в SQL Server или базе данных SQL Azure. Реализация типа данных псевдонима основана на ядро СУБД собственном системном типе. Определяемый пользователем тип реализуется с помощью класса сборки в общеязыковой среде выполнения (CLR) Microsoft .NET Framework. Чтобы привязать определяемый пользователем тип к его реализации, сборка СРЕДЫ CLR, содержащая реализацию типа, сначала должна быть зарегистрирована в ядро СУБД с помощью CREATE ASSE МБ LY.

Возможность выполнения CLR-кода в SQL Server отключена по умолчанию. Можно создавать, изменять и удалять объекты базы данных, ссылающиеся на модули управляемого кода. Однако эти ссылки не выполняются в SQL Server, если параметр clr не включен с помощью sp_configure.

Примечание.

В этом разделе рассматривается интеграция среды CLR .NET Framework с SQL Server. Интеграция СРЕДЫ CLR не применяется к База данных SQL Azure.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис определяемого пользователем типа данных:

CREATE TYPE [ schema_name. ] type_name
{
      FROM base_type
      [ ( precision [ , scale ] ) ]
      [ NULL | NOT NULL ]
    | EXTERNAL NAME assembly_name [ .class_name ]
    | AS TABLE ( { <column_definition> | <computed_column_definition> [ , ...n ]
      [ <table_constraint> ] [ , ...n ]
      [ <table_index> ] [ , ...n ] } )
} [ ; ]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ]
    [ NULL | NOT NULL ]
    [
        DEFAULT constant_expression ]
      | [ IDENTITY [ ( seed , increment ) ]
    ]
    [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ]

<data type> ::=
[ type_schema_name . ] type_name
    [ ( precision [ , scale ] | max |
                [ { CONTENT | DOCUMENT } ] xml_schema_collection ) ]

<column_constraint> ::=
{ { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [
            WITH ( <index_option> [ , ...n ] )
        ]
  | CHECK ( logical_expression )
}

<computed_column_definition> ::=
column_name AS computed_column_expression
[ PERSISTED [ NOT NULL ] ]
[
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
        [
            WITH ( <index_option> [ , ...n ] )
        ]
    | CHECK ( logical_expression )
]

<table_constraint> ::=
{
    { PRIMARY KEY | UNIQUE }
        [ CLUSTERED | NONCLUSTERED ]
    ( column [ ASC | DESC ] [ , ...n ] )
        [
    WITH ( <index_option> [ , ...n ] )
        ]
    | CHECK ( logical_expression )
}

<index_option> ::=
{
    IGNORE_DUP_KEY = { ON | OFF }
}

< table_index > ::=
  INDEX index_name
     [ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ , ...n ] )

Синтаксис оптимизированных для памяти типов таблиц, определяемых пользователем:

CREATE TYPE [ schema_name. ] type_name
AS TABLE ( { <column_definition> [ , ...n ] }
    | [ <table_constraint> ] [ , ...n ]
    | [ <table_index> ] [ , ...n ] )
    [ WITH ( <table_option> [ , ...n ] ) ]
 [ ; ]

<column_definition> ::=
column_name <data_type>
    [ COLLATE collation_name ] [ NULL | NOT NULL ]
      [ IDENTITY [ (1 , 1) ]
    ]
    [ <column_constraint> [ , ...n ] ] [ <column_index> ]

<data type> ::=
 [ type_schema_name . ] type_name [ ( precision [ , scale ] ) ]

<column_constraint> ::=
{ PRIMARY KEY { NONCLUSTERED HASH WITH ( BUCKET_COUNT = bucket_count )
                | NONCLUSTERED }
}

< table_constraint > ::=
{ PRIMARY KEY { NONCLUSTERED HASH (column [ , ...n ] )
                   WITH ( BUCKET_COUNT = bucket_count )
               | NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
           }
}

<column_index> ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count ) 
      | NONCLUSTERED ( column [ ASC | DESC ] [ , ...n ] )
}

< table_index > ::=
  INDEX index_name
{ [ NONCLUSTERED ] HASH (column [ , ...n ] ) WITH ( BUCKET_COUNT = bucket_count )
    | [ NONCLUSTERED ] ( column [ ASC | DESC ] [ , ...n ] )
}

<table_option> ::=
{
    [ MEMORY_OPTIMIZED = { ON | OFF } ]
}

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

schema_name

Имя схемы, к которой принадлежит псевдоним типа данных или определяемого пользователем типа.

type_name

Имя типа данных псевдонима или определяемого пользователем типа. Имена типов должны соответствовать требованиям к именам идентификаторов.

base_type

Предоставленный ядро СУБД тип данных, на котором основан псевдоним. Аргумент base_type имеет тип sysname, не имеет значения по умолчанию и может принимать одно из следующих значений:

bigint, int, smallint и tinyint
binary(n), varbinary(n), varbinary(max)
bit
char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n) и varchar(max)
date, datetime, datetime2, datetimeoffset, smalldatetime и time
decimal и numeric
float и real
Изображение
деньги и небольшие деньги
sql_variant
text и ntext
uniqueidentifier

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

precision

Для десятичного или числового значения точность представляет собой неотрицательное целое число, указывающее максимальное общее число десятичных цифр, которые можно сохранить, как слева, так и справа от десятичной точки. Дополнительные сведения см. в разделе decimal и numeric (Transact-SQL).

scale

Для десятичного или числового значения масштабирование — это неотрицательное целое число, указывающее максимальное число десятичных цифр, которое можно сохранить справа от десятичной точки, и оно должно быть меньше или равно точности. Дополнительные сведения см. в разделе decimal и numeric (Transact-SQL).

NULL | NOT NULL

Указывает, может ли данный тип иметь значение NULL. Если тип не указан, по умолчанию используется вариант NULL.

assembly_name

Применяется к: SQL Server

Указывает на сборку SQL Server, которая ссылается на реализацию определяемого пользователем типа в общеязыковой среде выполнения. assembly_name должен соответствовать существующей сборке в SQL Server в текущей базе данных.

Примечание.

EXTERNAL_NAME недоступно в автономной базе данных.

[ . class_name ]

Применяется к: SQL Server

Задает класс сборки, реализующей определяемый пользователем тип. Аргумент class_name должен быть допустимым идентификатором и существовать как класс в сборке с видимостью сборки. Аргумент class_name учитывает регистр символов независимо от параметров сортировки, установленных в библиотеке. Его значение должно точно соответствовать имени класса в соответствующей сборке. Именем класса может быть заключенное в квадратные скобки ([]) имя с указанием пространства имен, если в языке программирования, на котором записан класс, используется концепция пространств имен, как, например, в языке C#. Если class_name не указан, SQL Server предполагает, что он совпадает с type_name.

<column_definition>

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

<типа данных>

Определяет тип данных в столбце для определяемого пользователем табличного типа. Дополнительные сведения о типах данных см. в разделе Типы данных (Transact-SQL). Дополнительные сведения о таблицах см. в разделе CREATE TABLE (Transact-SQL).

<column_constraint>

Определяет ограничения столбца для определяемого пользователем табличного типа. Поддерживаемые ограничения включают PRIMARY KEY, UNIQUEи CHECK. Дополнительные сведения о таблицах см. в разделе CREATE TABLE (Transact-SQL).

<computed_column_definition>

Определяет выражение вычисляемого столбца в качестве столбца в определяемом пользователем табличном типе. Дополнительные сведения о таблицах см. в разделе CREATE TABLE (Transact-SQL).

<table_constraint>

Определяет ограничение таблицы на основе определяемого пользователем табличного типа. Поддерживаемые ограничения включают PRIMARY KEY, UNIQUEи CHECK.

<index_option>

Определяет реакцию на ошибку, возникшую из-за дублирования значений ключа при вставке нескольких строк в уникальный кластеризованный или уникальный некластеризованный индекс. Дополнительные сведения о параметрах индекса см. в разделе CREATE INDEX (Transact-SQL).

INDEX index_name [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

Область применения: SQL Server 2014 (12.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure.

Задает создание индекса для таблицы. Это может быть кластеризованный или некластеризованный индекс. Индекс содержит перечисленные столбцы и сортирует данные в порядке возрастания или убывания.

ИНДЕКС

Необходимо указать индексы столбцов и таблиц в рамках инструкции CREATE TABLE . CREATE INDEX и DROP INDEX не поддерживаются для таблиц, оптимизированных для памяти.

MEMORY_OPTIMIZED

Область применения: SQL Server 2014 (12.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure. Управляемый экземпляр SQL Azure не поддерживает оптимизированные для памяти таблицы на уровне общего назначения.

Указывает, является ли тип таблицы оптимизированным для памяти. Этот параметр отключен по умолчанию; Таблица (тип) не является оптимизированной для памяти таблицей (типом). Оптимизированные для памяти типы таблицы — это оптимизированные для памяти пользовательские таблицы, схемы которых, как и схемы других пользовательских таблиц, сохраняются на диске.

BUCKET_COUNT

Область применения: SQL Server 2014 (12.x) и более поздних версий, База данных SQL Azure, База данных SQL Azure и Управляемый экземпляр SQL Azure.

Отображает число контейнеров, которые необходимо создать в хэш-индексе. Максимальное значение для BUCKET_COUNT хэш-индексов — 1 073 741 824. Дополнительные сведения о количествах контейнеров см. в разделе "Индексы в таблицах, оптимизированных для памяти". bucket_count — это обязательный аргумент.

ХЭШ

Указывает, что HASH создается индекс. Хэш-индексы поддерживаются только в таблицах, оптимизированных для памяти.

Замечания

Класс сборки, ссылка на который содержится в assembly_name, и его методы должны удовлетворять всем требованиям, предъявляемым к реализации определяемых пользователем типов в SQL Server. Дополнительные сведения об этих требованиях см. в статье Определяемые пользователем типы CLR.

К числу дополнительных соображений относятся следующие:

Класс может содержать перегруженные методы, но эти методы можно вызывать только из управляемого кода, а не из Transact-SQL.
Любые статические элементы должны быть объявлены как const или readonly, если assembly_name или SAFEEXTERNAL_ACCESS.

В базе данных может существовать только один определяемый пользователем тип, зарегистрированный в качестве любого указанного типа, загруженного в SQL Server из CLR. Если определяемый пользователем тип создается в типе СРЕДЫ CLR, для которого определяемый пользователем тип уже существует в базе данных, CREATE TYPE завершается ошибкой. Это ограничение необходимо для того, чтобы избежать неоднозначности при разрешении типа SQL Type, если тип CLR может соответствовать более чем одному определяемому пользователем типу.

Если какой-либо метод мутатора в типе не возвращает void, CREATE TYPE инструкция не выполняется.

Чтобы изменить определяемый пользователем тип, необходимо удалить тип с помощью DROP TYPE инструкции, а затем повторно создать его.

В отличие от определяемых пользователем типов, созданных с помощью sp_addtype, роль общедоступной базы данных не предоставляется автоматически REFERENCES разрешение на типы, созданные с помощью CREATE TYPE. Это разрешение должно предоставляться отдельно.

В определяемых пользователем табличных типах определяемые пользователем структурированные типы, используемые в column_name<data type>, представляют собой часть области схемы базы данных, в которой определен тип таблицы. Чтобы получить доступ к определяемым пользователем структурированным типам в другой области базы данных, используйте двухкомпонентные имена.

В определяемых пользователем типах таблиц первичный ключ вычисляемых столбцов должен быть PERSISTED и NOT NULL.

Типы оптимизированных для памяти таблиц.

Начиная с версии SQL Server 2014 (12.x), обработка данных в типе таблицы может выполняться в основной памяти, но не на диске. Дополнительные сведения см . в обзоре и сценариях использования OLTP в памяти. Примеры кода по созданию типов оптимизированных для памяти таблиц см. в разделе Создание таблиц, оптимизированных для памяти, и хранимых процедур, скомпилированных в собственном коде.

Разрешения

Требуется CREATE TYPE разрешение в текущей базе данных и ALTER разрешение на schema_name. Если schema_name не задано, правила разрешения имен по умолчанию для определения схемы для текущего пользователя применяются. Если указана assembly_name , пользователь должен либо владеть сборкой, либо иметь REFERENCES разрешение на нее.

Если какие-либо столбцы в инструкции CREATE TABLE определены как принадлежащие к пользовательскому типу, необходимо иметь разрешение REFERENCES для этого типа.

Пользователь, создающий таблицу с столбцом, использующим определяемый пользователем тип, должен иметь REFERENCES разрешение на определяемый пользователем тип. Если эта таблица должна быть созданаtempdb, REFERENCES то необходимо явно предоставить разрешение перед созданием таблицы, либо этот тип данных и REFERENCES разрешение должны быть добавлены в model базу данных. Например:

CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GO
GRANT REFERENCES ON TYPE::dbo.udt_money TO public

Если это сделано, этот тип данных и REFERENCES разрешение будут доступны навсегда tempdb . В противном случае определяемый пользователем тип данных и разрешения нельзя будет использовать после перезапуска SQL Server. Дополнительные сведения см. в разделе CREATE TABLE.

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

USE master
GO
CREATE PROCEDURE setup_udt_in_tempdb
AS
EXEC ( 'USE tempdb;
CREATE TYPE dbo.udt_money FROM varchar(11) NOT NULL;
GRANT REFERENCES ON TYPE::dbo.udt_money TO public;')
GO
EXEC sp_procoption 'setup_udt_in_tempdb' , 'startup' , 'on'
GO

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

Примеры

А. Создание типа псевдонима на основе типа данных varchar

В следующем примере создается псевдоним на базе определенного в системе типа данных varchar.

CREATE TYPE SSN
FROM VARCHAR(11) NOT NULL;

B. Создание определяемого пользователем типа

Применяется к: SQL Server

В следующем примере создается тип Utf8String, который ссылается на класс utf8string в сборке utf8string. Перед тем как приступить к созданию этого типа, сборка utf8string регистрируется в локальной базе данных. Замените двоичную часть инструкции CREATE ASSEMBLY допустимым описанием.

CREATE ASSEMBLY utf8string
AUTHORIZATION [dbi]
FROM 0x4D... ;
GO

CREATE TYPE Utf8String
EXTERNAL NAME utf8string.[Microsoft.Samples.SqlServer.utf8string];
GO

C. Создание определяемого пользователем типа таблицы

В следующем примере создается определяемый пользователем табличный тип, который имеет два столбца. Дополнительные сведения о создании и использовании возвращающих табличное значение параметрах см. в разделе Использование параметров, возвращающих табличные значения (ядро СУБД).

CREATE TYPE LocationTableType AS TABLE (
    LocationName VARCHAR(50),
    CostRate INT
);
GO

D. Создание определяемого пользователем типа таблицы с первичным ключом и индексом

В приведенном ниже примере создается определяемый пользователем табличный тип с тремя столбцами, один из которых (Name) является первичным ключом, а другой (Price) имеет некластеризованный индекс. Дополнительные сведения о создании и использовании возвращающих табличное значение параметрах см. в разделе Использование параметров, возвращающих табличные значения (ядро СУБД).

CREATE TYPE InventoryItem AS TABLE (
    [Name] NVARCHAR(50) NOT NULL,
    SupplierId BIGINT NOT NULL,
    Price DECIMAL(18, 4) NULL,
    PRIMARY KEY (Name),
    INDEX IX_InventoryItem_Price(Price)
);
GO

CREATE TYPE (Transact-SQL)

Синтаксис

Аргументы

schema_name

type_name

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<типа данных>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

ИНДЕКС

MEMORY_OPTIMIZED

BUCKET_COUNT

ХЭШ

Замечания

Типы оптимизированных для памяти таблиц.

Разрешения

Примеры

А. Создание типа псевдонима на основе типа данных varchar

B. Создание определяемого пользователем типа

C. Создание определяемого пользователем типа таблицы

D. Создание определяемого пользователем типа таблицы с первичным ключом и индексом

Обратная связь

Обратная связь

Дополнительные ресурсы

CREATE TYPE (Transact-SQL)

Синтаксис

Аргументы

schema_name

type_name

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<типа данных>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

INDEX index_name [ CLUSTERED | NONCLUSTERED ] ( column_name [ ASC | DESC ] [ , ... n ] )

ИНДЕКС

MEMORY_OPTIMIZED

BUCKET_COUNT

ХЭШ

Замечания

Типы оптимизированных для памяти таблиц.

Разрешения

Примеры

А. Создание типа псевдонима на основе типа данных varchar

B. Создание определяемого пользователем типа

C. Создание определяемого пользователем типа таблицы

D. Создание определяемого пользователем типа таблицы с первичным ключом и индексом

Связанный контент

Обратная связь

Обратная связь

Дополнительные ресурсы