CREATE TYPE (Transact-SQL)

Artikel
12/15/2023

Berlaku untuk:SQL Server Azure SQL Database Azure SQL Managed Instance

Membuat jenis data alias atau jenis yang ditentukan pengguna dalam database saat ini di SQL Server atau Azure SQL Database. Implementasi jenis data alias didasarkan pada jenis sistem asli Mesin Database. Jenis yang ditentukan pengguna diimplementasikan melalui kelas rakitan dalam runtime bahasa umum (CLR) Microsoft .NET Framework. Untuk mengikat jenis yang ditentukan pengguna ke implementasinya, rakitan CLR yang berisi implementasi jenis harus terlebih dahulu didaftarkan di Mesin Database dengan menggunakan CREATE ASSEMBLY.

Kemampuan untuk menjalankan kode CLR nonaktif secara default di SQL Server. Anda dapat membuat, mengubah, dan menghilangkan objek database yang mereferensikan modul kode terkelola. Namun, referensi ini tidak dijalankan di SQL Server kecuali Opsi yang diaktifkan clr diaktifkan dengan menggunakan sp_configure.

Catatan

Integrasi .NET Framework CLR ke SQL Server dibahas dalam topik ini. Integrasi CLR tidak berlaku untuk Azure SQL Database.

Konvensi sintaks transact-SQL

Sintaks

Sintaks jenis data yang ditentukan pengguna:

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

Sintaks jenis tabel memori yang dioptimalkan yang ditentukan pengguna:

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

Catatan

Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.

Argumen

schema_name

Nama skema tempat tipe data alias atau jenis yang ditentukan pengguna berada.

type_name

Nama jenis data alias atau jenis yang ditentukan pengguna. Nama jenis harus mematuhi aturan untuk pengidentifikasi.

base_type

Mesin Database menyediakan jenis data yang didasarkan pada jenis data alias. base_type adalah sysname, tanpa default, dan bisa menjadi salah satu nilai berikut:

bigint, int, smallint, dan tinyint
binary(n), varbinary(n), dan varbinary(max)
bit
char(n), nchar(n), nvarchar(n), nvarchar(max), varchar(n), dan varchar(max)
tanggal, tanggalwaktu, datetime2, datetimeoffset, smalldatetime, dan waktu
desimal dan numerik
float dan real
gambar
uang dan smallmoney
aql_variant
teks dan ntext
pengidentifikasi unik

base_type juga dapat berupa sinonim jenis data apa pun yang memetakan ke salah satu jenis data sistem ini.

Presisi

Untuk desimal atau numerik, presisi adalah bilangan bulat non-negatif yang menunjukkan jumlah total maksimum digit desimal yang dapat disimpan, baik di sebelah kiri maupun di sebelah kanan titik desimal. Untuk informasi selengkapnya, lihat desimal dan numerik (Transact-SQL).

Skala

Untuk desimal atau numerik, skala adalah bilangan bulat non-negatif yang menunjukkan jumlah maksimum digit desimal yang dapat disimpan di sebelah kanan titik desimal, dan harus kurang dari atau sama dengan presisi. Untuk informasi selengkapnya, lihat desimal dan numerik (Transact-SQL).

NULL | BUKAN NULL

Menentukan apakah jenis dapat menampung nilai null. Jika tidak ditentukan, NULL adalah default.

assembly_name

Berlaku untuk: SQL Server

Menentukan rakitan SQL Server yang mereferensikan implementasi jenis yang ditentukan pengguna dalam runtime bahasa umum. assembly_name harus cocok dengan rakitan yang ada di SQL Server dalam database saat ini.

Catatan

EXTERNAL_NAME tidak tersedia dalam database mandiri.

[ . class_name ]

Berlaku untuk: SQL Server

Menentukan kelas dalam rakitan yang mengimplementasikan jenis yang ditentukan pengguna. class_name harus menjadi pengidentifikasi yang valid dan harus ada sebagai kelas dalam rakitan dengan visibilitas perakitan. class_name peka huruf besar/kecil, terlepas dari kolase database, dan harus sama persis dengan nama kelas di rakitan yang sesuai. Nama kelas dapat berupa nama yang memenuhi syarat namespace yang diapit dalam kurung siku ([ ]) jika bahasa pemrograman yang digunakan untuk menulis kelas menggunakan konsep namespace layanan, seperti C#. Jika class_name tidak ditentukan, SQL Server menganggapnya sama dengan type_name.

<column_definition>

Menentukan kolom untuk jenis tabel yang ditentukan pengguna.

<jenis data>

Menentukan jenis data dalam kolom untuk jenis tabel yang ditentukan pengguna. Untuk informasi selengkapnya tentang jenis data, lihat Jenis Data (Transact-SQL). Untuk informasi selengkapnya tentang tabel, lihat CREATE TABLE (Transact-SQL).

<column_constraint>

Menentukan batasan kolom untuk jenis tabel yang ditentukan pengguna. Batasan yang didukung meliputi PRIMARY KEY, UNIQUE, dan CHECK. Untuk informasi selengkapnya tentang tabel, lihat CREATE TABLE (Transact-SQL).

<computed_column_definition>

Menentukan ekspresi kolom komputasi sebagai kolom dalam jenis tabel yang ditentukan pengguna. Untuk informasi selengkapnya tentang tabel, lihat CREATE TABLE (Transact-SQL).

<table_constraint>

Menentukan batasan tabel pada jenis tabel yang ditentukan pengguna. Batasan yang didukung meliputi PRIMARY KEY, UNIQUE, dan CHECK.

<index_option>

Menentukan respons kesalahan untuk menduplikasi nilai kunci dalam operasi penyisipan beberapa baris pada indeks berkluster atau unik yang unik. Untuk informasi selengkapnya tentang opsi indeks, lihat CREATE INDEX (Transact-SQL).

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

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance.

Menentukan untuk membuat indeks pada tabel. Ini bisa menjadi indeks berkluster, atau indeks non-kluster. Indeks berisi kolom yang tercantum, dan mengurutkan data dalam urutan naik atau turun.

INDEX

Anda harus menentukan indeks kolom dan tabel sebagai bagian CREATE TABLE dari pernyataan. CREATE INDEX dan DROP INDEX tidak didukung untuk tabel yang dioptimalkan memori.

MEMORY_OPTIMIZED

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, Azure SQL Database, dan Azure SQL Managed Instance. Azure SQL Managed Instance tidak mendukung tabel memori yang dioptimalkan di tingkat Tujuan Umum.

Menunjukkan apakah jenis tabel dioptimalkan untuk memori. Opsi ini nonaktif secara default; tabel (jenis) bukan tabel memori yang dioptimalkan (jenis). Jenis tabel memori yang dioptimalkan adalah tabel pengguna yang dioptimalkan memori, yang skemanya dipertahankan pada disk yang mirip dengan tabel pengguna lain.

BUCKET_COUNT

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Database, dan Azure SQL Managed Instance.

Menunjukkan jumlah wadah yang harus dibuat dalam indeks hash. Nilai maksimum untuk BUCKET_COUNT dalam indeks hash adalah 1.073.741.824. Untuk informasi selengkapnya tentang jumlah wadah, lihat Indeks pada Tabel yang Dioptimalkan Memori. bucket_count adalah argumen yang diperlukan.

HASH

Berlaku untuk: SQL Server 2014 (12.x) dan versi yang lebih baru, Azure SQL Database, Azure SQL Database, dan Azure SQL Managed Instance.

Menunjukkan bahwa HASH indeks dibuat. Indeks hash hanya didukung pada tabel memori yang dioptimalkan.

Keterangan

Kelas rakitan yang dirujuk dalam assembly_name, bersama dengan metodenya, harus memenuhi semua persyaratan untuk menerapkan jenis yang ditentukan pengguna di SQL Server. Untuk informasi selengkapnya tentang persyaratan ini, lihat Jenis yang Ditentukan Pengguna CLR.

Pertimbangan tambahan meliputi hal-hal berikut:

Kelas dapat berisi metode yang kelebihan beban, tetapi metode ini hanya dapat dipanggil dari dalam kode terkelola, bukan dari Transact-SQL.
Setiap anggota statis harus dinyatakan sebagai const atau readonly jika assembly_name adalah SAFE atau EXTERNAL_ACCESS.

Dalam database, hanya ada satu jenis yang ditentukan pengguna yang terdaftar terhadap jenis tertentu yang telah diunggah di SQL Server dari CLR. Jika jenis yang ditentukan pengguna dibuat pada jenis CLR yang sudah ada jenis yang ditentukan pengguna dalam database, CREATE TYPE gagal dengan kesalahan. Pembatasan ini diperlukan untuk menghindari ambiguitas selama resolusi Jenis SQL jika jenis CLR dapat dipetakan ke lebih dari satu jenis yang ditentukan pengguna.

Jika ada metode mutator dalam jenis tidak mengembalikan kekosongan, CREATE TYPE pernyataan tidak dijalankan.

Untuk mengubah jenis yang ditentukan pengguna, Anda harus menghilangkan jenis dengan menggunakan DROP TYPE pernyataan lalu membuatnya kembali.

Tidak seperti jenis yang ditentukan pengguna yang dibuat dengan menggunakan sp_addtype, peran database publik tidak secara otomatis diberikan REFERENCES izin pada jenis yang dibuat dengan menggunakan CREATE TYPE. Izin ini harus diberikan secara terpisah.

Dalam jenis tabel yang ditentukan pengguna, jenis terstruktur yang ditentukan pengguna yang digunakan dalam jenis> data column_name<adalah bagian dari cakupan skema database tempat tipe tabel ditentukan. Untuk mengakses jenis terstruktur yang ditentukan pengguna dalam cakupan yang berbeda dalam database, gunakan nama dua bagian.

Dalam jenis tabel yang ditentukan pengguna, kunci utama pada kolom komputasi harus PERSISTED dan NOT NULL.

Jenis tabel yang dioptimalkan memori

Dimulai di SQL Server 2014 (12.x), memproses data dalam jenis tabel dapat dilakukan dalam memori utama, dan bukan pada disk. Untuk informasi selengkapnya, lihat Gambaran umum dan skenario penggunaan OLTP Dalam Memori. Untuk sampel kode yang menunjukkan cara membuat jenis tabel yang dioptimalkan memori, lihat Membuat Tabel yang Dioptimalkan Memori dan Prosedur Tersimpan yang Dikompilasi Secara Asli.

Izin

CREATE TYPE Memerlukan izin dalam database saat ini dan ALTER izin pada schema_name. Jika schema_name tidak ditentukan, aturan resolusi nama default untuk menentukan skema untuk pengguna saat ini berlaku. Jika assembly_name ditentukan, pengguna harus memiliki rakitan atau memiliki REFERENCES izin di dalamnya.

Jika ada kolom dalam CREATE TABLE pernyataan yang didefinisikan sebagai jenis yang ditentukan pengguna, REFERENCES izin pada jenis yang ditentukan pengguna diperlukan.

Pengguna yang membuat tabel dengan kolom yang menggunakan jenis yang ditentukan pengguna memerlukan REFERENCES izin pada jenis yang ditentukan pengguna. Jika tabel ini harus dibuat di tempdb, maka REFERENCES izin perlu diberikan secara eksplisit setiap kali sebelum tabel dibuat, atau tipe data dan REFERENCES izin ini perlu ditambahkan ke model database. Misalnya:

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

Jika ini dilakukan, maka jenis data dan REFERENCES izin ini akan tersedia tempdb secara permanen. Jika tidak, jenis dan izin data yang ditentukan pengguna akan hilang saat SQL Server dimulai ulang. Untuk informasi selengkapnya, lihat CREATE TABLE.

Jika Anda tidak ingin setiap database baru mewarisi definisi dan izin untuk tipe data yang ditentukan pengguna ini dari model, Anda dapat menggunakan prosedur tersimpan startup untuk membuat dan menetapkan izin yang sesuai hanya dalam tempdb database. Misalnya:

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

Atau, alih-alih menggunakan tabel sementara, pertimbangkan untuk menggunakan variabel tabel saat Anda perlu mereferensikan jenis data yang ditentukan pengguna untuk kebutuhan penyimpanan sementara. Agar variabel tabel mereferensikan jenis data yang ditentukan pengguna, Anda tidak perlu secara eksplisit memberikan izin untuk jenis data yang ditentukan pengguna.

Contoh

J. Membuat jenis alias berdasarkan jenis data varchar

Contoh berikut membuat jenis alias berdasarkan jenis data yang disediakan varchar sistem.

CREATE TYPE SSN
FROM VARCHAR(11) NOT NULL;

B. Membuat jenis yang ditentukan pengguna

Berlaku untuk: SQL Server

Contoh berikut membuat jenis Utf8String yang mereferensikan kelas utf8string di rakitan utf8string. Sebelum membuat jenis, rakitan utf8string terdaftar di database lokal. Ganti bagian CREATE ASSEMBLY biner pernyataan dengan deskripsi yang valid.

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

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

C. Membuat jenis tabel yang ditentukan pengguna

Contoh berikut membuat jenis tabel yang ditentukan pengguna yang memiliki dua kolom. Untuk informasi selengkapnya tentang cara membuat dan menggunakan parameter bernilai tabel, lihat Menggunakan Parameter Bernilai Tabel (Mesin Database).

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

D. Membuat jenis tabel yang ditentukan pengguna dengan kunci dan indeks utama

Contoh berikut membuat jenis tabel yang ditentukan pengguna yang memiliki tiga kolom, salah satunya (Name) adalah kunci utama dan yang lain (Price) memiliki indeks non-kluster. Untuk informasi selengkapnya tentang cara membuat dan menggunakan parameter bernilai tabel, lihat Menggunakan Parameter Bernilai Tabel (Mesin 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

CREATE TYPE (Transact-SQL)

Sintaks

Argumen

schema_name

type_name

base_type

Presisi

Skala

NULL | BUKAN NULL

assembly_name

[ . class_name ]

<column_definition>

<jenis data>

<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

Keterangan

Jenis tabel yang dioptimalkan memori

Izin

Contoh

J. Membuat jenis alias berdasarkan jenis data varchar

B. Membuat jenis yang ditentukan pengguna

C. Membuat jenis tabel yang ditentukan pengguna

D. Membuat jenis tabel yang ditentukan pengguna dengan kunci dan indeks utama

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan:

CREATE TYPE (Transact-SQL)

Sintaks

Argumen

schema_name

type_name

base_type

Presisi

Skala

NULL | BUKAN NULL

assembly_name

[ . class_name ]

<column_definition>

<jenis data>

<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

Keterangan

Jenis tabel yang dioptimalkan memori

Izin

Contoh

J. Membuat jenis alias berdasarkan jenis data varchar

B. Membuat jenis yang ditentukan pengguna

C. Membuat jenis tabel yang ditentukan pengguna

D. Membuat jenis tabel yang ditentukan pengguna dengan kunci dan indeks utama

Konten terkait

Saran dan Komentar

Saran dan Komentar

Sumber Daya Tambahan: