CREATE TYPE (Transact-SQL)

Articolo
11/19/2024

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL in Microsoft Fabric

Crea un tipo di dati alias o un tipo definito dall'utente nel database corrente in SQL Server o in Database SQL di Azure. L'implementazione di un tipo di dati alias è basata su un tipo di sistema nativo motore di database. I tipi definiti dall'utente vengono invece implementati tramite una classe di un assembly CLR (Common Language Runtime) di Microsoft .NET Framework. Per associare un tipo definito dall'utente alla relativa implementazione, l'assembly CLR che contiene l'implementazione del tipo deve prima essere registrato nel motore di database tramite CREATE ASSEMBLY.

Per impostazione predefinita, l'esecuzione di codice CLR è disattivata in SQL Server. È possibile creare, modificare ed eliminare oggetti di database che fanno riferimento a moduli di codice gestito. Tuttavia, questi riferimenti non vengono eseguiti in SQL Server a meno che l'opzione clr enabled non sia abilitata tramite sp_configure.

Nota

In questo articolo viene illustrata l'integrazione di CLR di .NET Framework in SQL Server. L'integrazione con CLR non si applica a database SQL di Azure o al database SQL in Microsoft Fabric, in cui i tipi CLR (.NET) non sono supportati.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

Sintassi del tipo di dati definito dall'utente:

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 ] )
     [INCLUDE (column, ...n)]

Sintassi dei tipi di tabella ottimizzati per la memoria definita dall'utente:

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 } ]
}

Argomenti

schema_name

Nome dello schema a cui appartiene il tipo di dati alias o il tipo definito dall'utente.

type_name

Nome del tipo di dati alias o del tipo definito dall'utente. I nomi dei tipi devono essere conformi alle regole per gli identificatori.

base_type

Tipo di dati fornito motore di database su cui si basa il tipo di dati alias. base_type è di tipo sysname e non prevede alcun valore predefinito. I possibili valori sono i seguenti:

bigint, int, smallint e tinyint
binary(n), varbinary(n)e varbinary(max)
bit
char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n)e varchar(max)
date, datetime, datetime2, datetimeoffset, smalldatetime e time
decimal e numeric
float e real
Immagine
money e smallmoney.
sql_variant
text e ntext
uniqueidentifier

Per l'argomento base_type è anche possibile specificare qualsiasi sinonimo di tipo di dati che esegue il mapping a uno di questi tipi di dati di sistema.

precision

Per decimal o numeric, precisione è un numero intero non negativo che indica il numero totale massimo di cifre decimali che è possibile archiviare, sia a sinistra che a destra del separatore decimale. Per altre informazioni, vedere decimal e numeric (Transact-SQL).

scale

Per decimal o numeric, la scala è un numero intero non negativo che indica il numero massimo di cifre decimali che possono essere archiviate a destra del separatore decimale e deve essere minore o uguale alla precisione. Per altre informazioni, vedere decimal e numeric (Transact-SQL).

NULL | NOT NULL

Specifica se il tipo supporta la memorizzazione di valori Null. Se non specificato, NULL è l'impostazione predefinita.

assembly_name

Si applica a: SQL Server

Specifica l'assembly di SQL Server che fa riferimento all'implementazione del tipo definito dall'utente in CLR. assembly_name deve corrispondere a un assembly esistente in SQL Server nel database corrente.

Nota

EXTERNAL_NAME non è disponibile in un database indipendente.

[ . class_name ]

Si applica a: SQL Server

Specifica la classe nell'assembly che implementa il tipo definito dall'utente. class_name deve essere un identificatore valido e deve esistere come classe nell'assembly con visibilità dell'assembly. class_name supporta la distinzione tra maiuscole e minuscole, indipendentemente dalle regole di confronto del database e deve corrispondere esattamente al nome di classe nell'assembly corrispondente. Il nome di classe può essere un nome completo con lo spazio dei nomi e racchiuso tra parentesi quadre ([ ]) se il linguaggio di programmazione usato per scrivere la classe usa il concetto degli spazi dei nomi, come nel caso, ad esempio, di C#. Se class_name non è specificato, SQL Server presuppone che sia uguale a type_name.

<column_definition>

Definisce le colonne per un tipo di tabella definito dall'utente.

<tipo di dati>

Definisce il tipo di dati in una colonna con un tipo di tabella definito dall'utente. Per altre informazioni sui tipi di dati, vedere Tipi di dati (Transact-SQL). Per altre informazioni sulle tabelle , vedere CREATE TABLE (Transact-SQL).

<column_constraint>

Definisce i vincoli di colonna per un tipo di tabella definito dall'utente. I vincoli supportati includono PRIMARY KEY, UNIQUEe CHECK. Per altre informazioni sulle tabelle , vedere CREATE TABLE (Transact-SQL).

<computed_column_definition>

Definisce un'espressione di colonna calcolata come una colonna in un tipo di tabella definito dall'utente. Per altre informazioni sulle tabelle , vedere CREATE TABLE (Transact-SQL).

<table_constraint>

Definisce un vincolo di tabella per un tipo di tabella definito dall'utente. I vincoli supportati includono PRIMARY KEY, UNIQUEe CHECK.

<index_option>

Specifica il tipo di risposta in caso di errori di valori di chiave duplicati in un'operazione di inserimento di più righe su un indice cluster o non cluster univoco. Per altre informazioni sulle opzioni per gli indici, vedere CREATE INDEX (Transact-SQL).

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

Si applica a: SQL Server 2014 (12.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure.

Specifica che deve essere creato un indice per la tabella. Può trattarsi di un indice cluster o un indice non cluster. L'indice contiene le colonne elencate e ordina i dati in ordine crescente o decrescente.

INDEX

È necessario specificare gli indici di colonna e tabella come parte dell'istruzione CREATE TABLE . CREATE INDEX e DROP INDEX non sono supportati per le tabelle ottimizzate per la memoria.

MEMORY_OPTIMIZED

Si applica a: SQL Server 2014 (12.x) e versioni successive, database SQL di Azure e Istanza gestita di SQL di Azure. Istanza gestita di SQL di Azure non supporta tabelle ottimizzate per la memoria nel livello Utilizzo generico.

Indica se il tipo di tabella è con ottimizzazione per la memoria. Questa opzione è disattivata per impostazione predefinita; la tabella (tipo) non è una tabella ottimizzata per la memoria (tipo). I tipi di tabella ottimizzata per la memoria sono tabelle utente ottimizzate per la memoria, il cui schema è persistente su disco in modo analogo ad altre tabelle utente.

BUCKET_COUNT

Si applica a: SQL Server 2014 (12.x) e versioni successive, database SQL di Azure, database SQL di Azure e Istanza gestita di SQL di Azure.

Indica il numero di bucket che deve essere creato nell'indice hash. Il valore massimo per BUCKET_COUNT negli indici hash è 1.073.741.824. Per altre informazioni sui conteggi dei bucket, vedere Indici nelle tabelle ottimizzate per la memoria. bucket_count è un argomento obbligatorio.

HASH

Si applica a: SQL Server 2014 (12.x) e versioni successive, database SQL di Azure, database SQL di Azure e Istanza gestita di SQL di Azure.

Indica che viene creato un HASH indice. Gli indici hash sono supportati solo nelle tabelle con ottimizzazione per la memoria.

Osservazioni:

La classe dell'assembly cui si fa riferimento in assembly_name e i relativi metodi devono soddisfare tutti i requisiti per l'implementazione di un tipo definito dall'utente in SQL Server. Per altre informazioni su tali requisiti, vedere Tipi CLR definiti dall'utente.

Ulteriori considerazioni:

La classe può contenere metodi di overload, ma questi metodi possono essere chiamati solo dall'interno del codice gestito, non da Transact-SQL.
Tutti i membri statici devono essere dichiarati come const o readonly se assembly_name è SAFE o EXTERNAL_ACCESS.

Nell'ambito di un database può esistere un solo tipo definito dall'utente registrato per qualsiasi tipo specificato caricato in SQL Server da CLR. Se viene creato un tipo definito dall'utente in un tipo CLR per il quale esiste già un tipo definito dall'utente nel database, CREATE TYPE viene generato un errore. Questa restrizione risulta necessaria per evitare ambiguità durante la risoluzione dei tipi SQL, nel caso su un tipo CLR possa essere eseguito il mapping a più di un tipo definito dall'utente.

Se un metodo mutatore nel tipo non restituisce void, l'istruzione CREATE TYPE non viene eseguita.

Per modificare un tipo definito dall'utente, è necessario eliminare il tipo usando un'istruzione DROP TYPE e quindi ricrearlo.

A differenza dei tipi definiti dall'utente creati tramite sp_addtype, al ruolo del database pubblico non viene concessa REFERENCES automaticamente l'autorizzazione per i tipi creati tramite CREATE TYPE. È necessario concedere l'autorizzazione separatamente.

Nei tipi di tabella definiti dall'utente, i tipi strutturati definiti dall'utente usati in column_name<tipo di dati> fanno parte dell'ambito dello schema del database in cui è definito il tipo di tabella. Per accedere ai tipi strutturati definiti dall'utente in un ambito diverso all'interno del database, usare nomi in due parti.

Nei tipi di tabella definiti dall'utente, la chiave primaria nelle colonne calcolate deve essere PERSISTED e NOT NULL.

Nel database SQL di Infrastruttura è possibile creare tipi definiti dall'utente, ma non eseguire il mirroring in Fabric OneLake e le colonne di tipi definiti dall'utente vengono ignorate nel mirroring.

Tipi di tabelle ottimizzate per la memoria

A partire da SQL Server 2014 (12.x), l'elaborazione dei dati in un tipo di tabella può essere eseguita nella memoria primaria e non su disco. Per altre informazioni, vedere Panoramica oltp in memoria e scenari di utilizzo. Per esempi di codice che illustrano come creare tipi di tabelle ottimizzate per la memoria, vedere Creazione di una tabella con ottimizzazione per la memoria e di una stored procedure compilata in modo nativo.

Autorizzazioni

È richiesta CREATE TYPE l'autorizzazione nel database corrente e ALTER l'autorizzazione per schema_name. Se schema_name non viene specificato, vengono applicate le regole di risoluzione dei nomi predefinite per determinare lo schema per l'utente corrente. Se viene specificato assembly_name , un utente deve possedere l'assembly o disporre REFERENCES dell'autorizzazione.

Se una o più colonne nell'istruzione CREATE TABLE sono definite con un tipo definito dall'utente, è necessaria l'autorizzazione REFERENCES per il tipo definito dall'utente.

Un utente che crea una tabella con una colonna che usa un tipo definito dall'utente richiede l'autorizzazione REFERENCES per il tipo definito dall'utente. Se questa tabella deve essere creata in tempdb, è necessario concedere l'autorizzazione REFERENCES in modo esplicito ogni volta che la tabella viene creata oppure questo tipo di dati e REFERENCES l'autorizzazione devono essere aggiunti al model database. Ad esempio:

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

Se questa operazione viene eseguita, questo tipo di dati e REFERENCES l'autorizzazione saranno disponibili in tempdb modo permanente. In caso contrario, il tipo di dati definito dall'utente e le autorizzazioni verranno rimossi al riavvio di SQL Server. Per altre informazioni, vedere CREATE TABLE.

Se non si vuole che ogni nuovo database erediti la definizione e le autorizzazioni per questo tipo di dati definito dall'utente dal modello, è possibile usare una stored procedure di avvio per creare e assegnare le autorizzazioni appropriate solo nel tempdb database. Ad esempio:

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

In alternativa, anziché usare tabelle temporanee, è consigliabile usare le variabili di tabella quando è necessario fare riferimento ai tipi di dati definiti dall'utente per le esigenze di archiviazione temporanea. Per fare riferimento alle variabili di tabella ai tipi di dati definiti dall'utente, non è necessario concedere in modo esplicito le autorizzazioni per il tipo di dati definito dall'utente.

Esempi

R. Creare un tipo di alias basato sul tipo di dati varchar

Nell'esempio seguente viene creato un tipo alias basato sul tipo di dati di sistema varchar.

CREATE TYPE SSN
FROM VARCHAR(11) NOT NULL;

B. Creare un tipo definito dall'utente

Si applica a: SQL Server

Nell'esempio seguente viene creato un tipo Utf8String che fa riferimento alla classe utf8string nell'assembly utf8string. Prima di creare il tipo, l'assembly utf8string viene registrato nel database locale. Sostituire la parte binaria dell'istruzione CREATE ASSEMBLY con una descrizione valida.

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

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

C. Creare un tipo di tabella definito dall'utente

Nell'esempio seguente viene creato un tipo di tabella definito dall'utente con due colonne. Per altre informazioni su come creare e usare parametri con valori di tabella, vedere Usare parametri con valori di tabella (motore di database).

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

D. Creare un tipo di tabella definito dall'utente con chiave primaria e indice

Nell'esempio seguente viene creato un tipo di tabella definito dall'utente che contiene tre colonne, una delle quali (Name) è la chiave primaria e un'altra (Price) ha un indice non cluster. Per altre informazioni su come creare e usare parametri con valori di tabella, vedere Usare parametri con valori di tabella (motore di database).

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

Condividi tramite

CREATE TYPE (Transact-SQL)

Sintassi

Argomenti

schema_name

type_name

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<tipo di dati>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

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

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Osservazioni:

Tipi di tabelle ottimizzate per la memoria

Autorizzazioni

Esempi

R. Creare un tipo di alias basato sul tipo di dati varchar

B. Creare un tipo definito dall'utente

C. Creare un tipo di tabella definito dall'utente

D. Creare un tipo di tabella definito dall'utente con chiave primaria e indice

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

CREATE TYPE (Transact-SQL)

Sintassi

Argomenti

schema_name

type_name

base_type

precision

scale

NULL | NOT NULL

assembly_name

[ . class_name ]

<column_definition>

<tipo di dati>

<column_constraint>

<computed_column_definition>

<table_constraint>

<index_option>

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

INDEX

MEMORY_OPTIMIZED

BUCKET_COUNT

HASH

Osservazioni:

Tipi di tabelle ottimizzate per la memoria

Autorizzazioni

Esempi

R. Creare un tipo di alias basato sul tipo di dati varchar

B. Creare un tipo definito dall'utente

C. Creare un tipo di tabella definito dall'utente

D. Creare un tipo di tabella definito dall'utente con chiave primaria e indice

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive