Kolasi basis data terkandung

Berlaku untuk: SQL ServerAzure SQL Managed Instance

Berbagai properti memengaruhi urutan sortir dan semantik kesetaraan data tekstual, termasuk sensitivitas huruf besar/kecil, sensitivitas aksen, dan bahasa dasar yang digunakan. Kualitas ini dinyatakan ke SQL Server melalui pilihan kolase untuk data. Untuk diskusi kolasi yang lebih mendalam, lihat Collation and Unicode support.

Kolase tidak hanya berlaku untuk data yang disimpan dalam tabel pengguna, tetapi untuk semua teks yang ditangani oleh SQL Server, termasuk metadata, objek sementara, nama variabel, dll. Penanganan ini berbeda dalam database yang terkandung dan tidak terkandung. Perubahan ini tidak memengaruhi banyak pengguna, tetapi membantu memberikan independensi dan keseragaman instans. Tetapi ini mungkin juga menimbulkan kebingungan dan masalah bagi sesi yang mengakses database tertutup dan tidak tertutup.

Perilaku pengurutan database yang terkonten berbeda secara halus dari perilaku dalam database yang tidak terkonten. Perilaku ini umumnya bermanfaat karena memberikan independensi antar-instans dan kesederhanaan. Beberapa pengguna mungkin mengalami masalah, khususnya ketika sesi mengakses database yang terkandung dan tidak terkandung.

Artikel ini mengklarifikasi konten perubahan, dan memeriksa area di mana perubahan dapat menyebabkan masalah.

Database yang tidak terkandung

Semua database memiliki kolase default (yang dapat diatur saat membuat atau mengubah database). Kolasi ini digunakan untuk semua metadata dalam database, dan bawaan untuk semua kolom string dalam database. Pengguna dapat memilih kolase yang berbeda untuk kolom tertentu dengan menggunakan COLLATE klausa.

Contoh 1

Misalnya, jika kita bekerja di Beijing, kita mungkin menggunakan kolasi Tionghoa:

ALTER DATABASE MyDB
    COLLATE Chinese_Simplified_Pinyin_100_CI_AS;

Sekarang jika kita membuat kolom, kolasi defaultnya adalah kolasi Cina ini, tetapi kita dapat memilih yang lain jika kita ingin.

CREATE TABLE MyTable
(
    mycolumn1 NVARCHAR,
    mycolumn2 NVARCHAR COLLATE Frisian_100_CS_AS
);
GO

SELECT name, collation_name
FROM sys.columns
WHERE name LIKE 'mycolumn%';
GO

Berikut adalah kumpulan hasilnya.

name            collation_name
--------------- ----------------------------------
mycolumn1       Chinese_Simplified_Pinyin_100_CI_AS
mycolumn2       Frisian_100_CS_AS

Ini tampak relatif sederhana, tetapi beberapa masalah muncul. Karena kolase untuk kolom bergantung pada database tempat tabel dibuat, masalah muncul dengan penggunaan tabel sementara yang disimpan di tempdb. Kolasi tempdb biasanya cocok dengan kolasi untuk instance, yang tidak harus sesuai dengan kolasi database.

Contoh 2

Misalnya, pertimbangkan database (Tionghoa) yang ditunjukkan sebelumnya, saat digunakan pada instans dengan kolaterasi Latin1_General :

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

Pada pandangan pertama, kedua tabel ini terlihat seperti memiliki skema yang sama, tetapi karena kolaborasi database berbeda, nilainya tidak kompatibel:

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Berikut adalah kumpulan hasilnya.

Msg 468, Tingkat 16, Status 9, Baris 2

Tidak dapat mengatasi konflik kolasi antara "Latin1_General_100_CI_AS_KS_WS_SC" dan "Chinese_Simplified_Pinyin_100_CI_AS" dalam operasi kesetaraan.

Kita dapat memperbaikinya dengan menyusun tabel sementara secara eksplisit. SQL Server mempermudah ini dengan menyediakan DATABASE_DEFAULT kata kunci untuk COLLATE klausa.

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX) COLLATE DATABASE_DEFAULT);
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Kueri ini sekarang berjalan tanpa kesalahan.

Kita juga dapat melihat perilaku yang bergantung pada kolasi pada variabel. Pertimbangkan fungsi berikut:

CREATE FUNCTION f (@x INT)
RETURNS INT
AS
BEGIN
    DECLARE @I AS INT = 1;
    DECLARE @İ AS INT = 2;
    RETURN @x * @i;
END

Ini adalah fungsi yang agak aneh. Dalam sebuah kolasi sensitif terhadap huruf besar/kecil, @i dalam klausa pengembalian tidak dapat mengikat ke @I atau @İ. Dalam kolasi Latin1_General yang tidak membedakan huruf besar/kecil, @i terikat ke @I, dan fungsi mengembalikan 1. Tetapi dalam kolatasi Turki yang tidak peka huruf besar/kecil, @i mengikat ke @İ, dan fungsi mengembalikan 2. Hal ini dapat menyebabkan masalah besar pada basis data yang berpindah antarinstans dengan kolasi yang berbeda.

Pangkalan data yang ada

Karena salah satu tujuan perancangan database mandiri adalah menjadikannya benar-benar mandiri, ketergantungan pada instans dan kolasi tempdb harus diputus. Untuk melakukan ini, database yang terkandung memperkenalkan konsep kolase katalog. Kolatasi katalog digunakan untuk metadata sistem dan objek sementara. Detail disediakan sebagai berikut.

Dalam database tertutup, pengurutan katalog adalah Latin1_General_100_CI_AS_WS_KS_SC. Kolase ini sama untuk semua database yang terkandung di semua instans SQL Server dan tidak dapat diubah.

Kolase database dipertahankan, tetapi hanya digunakan sebagai kolase default untuk data pengguna. Secara bawaan, kolasi database sama dengan kolasi database model, tetapi dapat diubah oleh pengguna melalui perintah CREATE atau ALTER DATABASE, seperti halnya database non-contained.

Kata kunci baru, CATALOG_DEFAULT, tersedia dalam COLLATE klausa. Ini digunakan sebagai pintasan ke kolase metadata saat ini dalam database yang terkandung dan tidak terkandung. Artinya, dalam database yang tidak memiliki pembatas, CATALOG_DEFAULT mengembalikan pengurutan database saat ini, karena metadata diurutkan berdasarkan pengurutan database. Dalam database mandiri, kedua nilai ini mungkin berbeda, karena pengguna dapat mengubah kolase database sehingga tidak cocok dengan kolase katalog.

Perilaku berbagai objek dalam database yang tidak terkandung dan terkandung dirangkum dalam tabel ini:

Dalam contoh tabel sementara yang dijelaskan sebelumnya, kita dapat melihat bahwa perilaku kolabasi ini menghilangkan kebutuhan akan klausul eksplisit COLLATE di sebagian besar penggunaan tabel sementara. Dalam database mandiri, kode ini sekarang berjalan tanpa kesalahan, bahkan jika database dan kolase instans berbeda:

CREATE TABLE T1 (T1_txt NVARCHAR (MAX));
GO

CREATE TABLE #T2 (T2_txt NVARCHAR (MAX));
GO

SELECT T1_txt, T2_txt
FROM T1
     INNER JOIN #T2
         ON T1.T1_txt = #T2.T2_txt;

Kueri ini berfungsi karena keduanya T1_txt dan T2_txt disusun dalam kolase database database yang terkandung.

Perpaduan antara konteks yang terkandung dan tidak terkendali

Selama sesi dalam database yang terkandung tetap terkandung, sesi harus tetap berada dalam database yang tersambung dengannya. Dalam hal ini, perilakunya jelas. Tetapi jika suatu sesi berpindah antara konteks contained dan non-contained, perilakunya menjadi lebih kompleks karena dua kumpulan aturan tersebut harus dijembatani. Ini dapat terjadi dalam database yang sebagian terisolasi, karena pengguna dapat USE ke database lain. Dalam hal ini, perbedaan aturan kolajeasi ditangani oleh prinsip berikut.

• Perilaku kolasi untuk suatu batch ditentukan oleh database tempat batch tersebut dimulai.

Keputusan ini dibuat sebelum perintah apa pun dikeluarkan, termasuk inisial USE. Artinya, jika batch dimulai dalam database yang terkandung, tetapi perintah pertama adalah USE ke database yang tidak terkandung, perilaku kolase yang terkandung masih digunakan untuk batch. Mengingat skenario ini, referensi ke variabel, misalnya, mungkin memiliki beberapa kemungkinan hasil:

• Referensi dapat menemukan tepat satu kecocokan. Dalam hal ini, referensi berfungsi tanpa kesalahan.

• Referensi mungkin tidak menemukan kecocokan dalam penyusunan saat ini, sementara sebelumnya mungkin ada. Ini menimbulkan kesalahan yang menunjukkan bahwa variabel tidak ada, meskipun rupanya dibuat.

• Referensi mungkin menemukan beberapa kecocokan yang awalnya berbeda. Ini juga menimbulkan kesalahan.

Kami mengilustrasikan ini dengan beberapa contoh. Untuk ini, kami berasumsi ada database yang sebagian berisi bernama MyCDB dengan kolase databasenya diatur ke kolase default, Latin1_General_100_CI_AS_WS_KS_SC. Kami berasumsi bahwa pengurutan instans adalah Latin1_General_100_CS_AS_WS_KS_SC. Kedua kolasi hanya berbeda dalam hal sensitivitas huruf besar/kecil.

Contoh 1

Contoh berikut mengilustrasikan kasus di mana referensi menemukan persis satu kecocokan.

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #a VALUES (1);
GO

USE master;
GO

SELECT * FROM #a;
GO

Results:

Berikut kumpulan hasilnya.

x
-----------
1

Dalam kasus ini, #a yang teridentifikasi cocok dalam kolasi katalog yang tidak peka huruf besar/kecil dan kolasi instans yang peka huruf besar/kecil, dan kode tersebut berfungsi.

Contoh 2

Contoh berikut mengilustrasikan kasus di mana referensi tidak menemukan kecocokan dalam kolake saat ini di mana ada satu sebelumnya.

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #A VALUES (1);
GO

Di sini, #A terikat ke #a dalam kolasi bawaan yang tidak peka terhadap huruf besar/kecil, dan penyisipan berhasil,

Berikut kumpulan hasilnya.

(1 row(s) affected)

Tapi jika kita melanjutkan naskahnya...

USE master;
GO

SELECT * FROM #A;
GO

Kami mendapatkan kesalahan saat mencoba melakukan pengikatan ke #A pada kolasi instans yang peka terhadap huruf besar/kecil;

Berikut kumpulan hasilnya.

Msg 208, Level 16, State 0, Line 2
Invalid object name '#A'.

Contoh 3

Contoh berikut mengilustrasikan kasus di mana referensi menemukan beberapa kecocokan yang awalnya berbeda. Pertama, kita mulai dari tempdb (yang memiliki kolasi peka huruf besar/kecil yang sama dengan instance kita) dan mengeksekusi pernyataan berikut.

USE tempdb;
GO

CREATE TABLE #a (x INT);
GO

CREATE TABLE #A (x INT);
GO

INSERT INTO #a VALUES (1);
GO

INSERT INTO #A VALUES (2);
GO

Kueri ini berhasil, karena tabel berbeda dalam kolase ini:

Berikut kumpulan hasilnya.

(1 row(s) affected)
(1 row(s) affected)

Namun, jika kita pindah ke database mandiri kita, kita menemukan bahwa kita tidak dapat lagi mengikat tabel ini.

USE MyCDB;
GO

SELECT * FROM #a;
GO

Berikut kumpulan hasilnya.

Msg 12800, Level 16, State 1, Line 2
The reference to temp table name #a is ambiguous and cannot be resolved. Possible candidates are #a and #A.

Konten terkait

• Database Mandiri