Ingesloten databasesorteringen

Van toepassing op:SQL ServerAzure SQL Managed Instance

Verschillende eigenschappen zijn van invloed op de sorteervolgorde en gelijkheidssemantiek van tekstgegevens, waaronder hoofdlettergevoeligheid, accentgevoeligheid en de basistaal die wordt gebruikt. Deze kwaliteiten worden uitgedrukt in SQL Server door de keuze van sortering voor de gegevens. Zie sortering en Unicode-ondersteuning voor een uitgebreidere bespreking van sorteringen zelf.

Sorteringen zijn niet alleen van toepassing op gegevens die zijn opgeslagen in gebruikerstabellen, maar op alle tekst die wordt verwerkt door SQL Server, inclusief metagegevens, tijdelijke objecten, variabelenamen, enzovoort. De verwerking van deze verschillen in ingesloten en niet-ingesloten databases. Deze wijziging heeft geen invloed op veel gebruikers, maar helpt om instantie-onafhankelijkheid en uniformiteit te bieden. Maar dit kan ook verwarring veroorzaken en problemen voor sessies die toegang hebben tot zowel ingesloten als niet-ingesloten databases.

Het sorteringsgedrag van ingesloten databases verschilt subtly van het gedrag in niet-ingesloten databases. Dit gedrag is over het algemeen nuttig, waardoor instantie-onafhankelijkheid en eenvoud worden geboden. Sommige gebruikers kunnen problemen ondervinden, met name wanneer een sessie toegang heeft tot zowel ingesloten als niet-ingesloten databases.

Dit artikel verduidelijkt de inhoud van de wijziging en onderzoekt gebieden waar de wijziging problemen kan veroorzaken.

Niet-ingesloten databases

Alle databases hebben een standaardsortering (die kan worden ingesteld bij het maken of wijzigen van een database). Deze sortering wordt gebruikt voor alle metagegevens in de database en de standaardinstelling voor alle tekenreekskolommen in de database. Gebruikers kunnen een andere sortering kiezen voor een bepaalde kolom met behulp van de COLLATE component.

Voorbeeld 1

Als we bijvoorbeeld in Beijing zouden werken, zouden we een Chinese sorteervolgorde kunnen gebruiken.

ALTER DATABASE MyDB
    COLLATE Chinese_Simplified_Pinyin_100_CI_AS;

Als we nu een kolom maken, is de standaardsortering deze Chinese sortering, maar we kunnen desgewenst een andere kolom kiezen:

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

Hier is het resultatenoverzicht.

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

Dit lijkt relatief eenvoudig, maar er ontstaan verschillende problemen. Omdat de sortering voor een kolom afhankelijk is van de database waarin de tabel wordt gemaakt, ontstaan er problemen met het gebruik van tijdelijke tabellen die zijn opgeslagen in tempdb. De sortering van tempdb komt meestal overeen met de sortering voor het exemplaar, wat niet noodzakelijk overeen hoeft te komen met de databasesortering.

Voorbeeld 2

Denk bijvoorbeeld aan de eerder weergegeven (Chinese) database, wanneer deze wordt gebruikt voor een instantie met een Latin1_General sortering.

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

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

Op het eerste gezicht zien deze twee tabellen eruit alsof ze hetzelfde schema hebben, maar omdat de sorteringen van de databases verschillen, zijn de waarden niet compatibel:

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

Hier is het resultatenoverzicht.

Msg 468, Niveau 16, Staat 9, Regel 2

Kan het sorteringsconflict tussen 'Latin1_General_100_CI_AS_KS_WS_SC' en 'Chinese_Simplified_Pinyin_100_CI_AS' niet worden opgelost bij de gelijkheidsbewerking.

We kunnen dit oplossen door de tijdelijke tabel expliciet te sorteren. SQL Server maakt dit eenvoudiger door het DATABASE_DEFAULT trefwoord voor de COLLATE component op te geven.

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;

Deze query wordt nu zonder fouten uitgevoerd.

We kunnen ook sorteringsafhankelijk gedrag met variabelen zien. Houd rekening met de volgende functie:

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

Dit is een nogal eigenaardige functie. In een hoofdlettergevoelige sortering kan de @i in de return-clausule niet binden aan @I of @İ. In een niet-hoofdlettergevoelige Latin1_General sortering, verbindt @i zich met @I, en de functie retourneert 1. Maar in een hoofdletterongevoelige Turkse sortering bindt @i aan @İ, en retourneert de functie 2. Dit kan ernstige gevolgen hebben voor een database die wordt verplaatst tussen instantiaties met verschillende collaties.

Ingesloten databases

Aangezien een ontwerpdoel van zelfstandige databases is om ze onafhankelijk te maken, moet de afhankelijkheid van de instantie en tempdb collaties worden verbroken. Hiervoor introduceren ingesloten databases het concept van de catalogussortering. De catalogussortering wordt gebruikt voor systeemmetagegevens en tijdelijke objecten. Details worden als volgt opgegeven.

In een afgesloten database is de catalogussortering Latin1_General_100_CI_AS_WS_KS_SC. Deze sortering is hetzelfde voor alle ingesloten databases op alle exemplaren van SQL Server en kan niet worden gewijzigd.

De databasesortering wordt bewaard, maar wordt alleen gebruikt als de standaardsortering voor gebruikersgegevens. De databasesortering is standaard gelijk aan de model databasesortering, maar kan door de gebruiker worden gewijzigd via een CREATE of ALTER DATABASE opdracht, net als bij niet-ingesloten databases.

Er is een nieuw trefwoord beschikbaar CATALOG_DEFAULTin de COLLATE component. Dit wordt gebruikt als snelkoppeling naar de huidige sortering van metagegevens in zowel ingesloten als niet-ingesloten databases. Dat betekent dat in een niet-ingesloten database CATALOG_DEFAULT de huidige databasesortering retourneert, omdat metagegevens worden gesorteerd in de databasesortering. In een ingesloten database kunnen deze twee waarden afwijken, omdat de gebruiker de databasesortering kan wijzigen zodat deze niet overeenkomt met de catalogussortering.

Het gedrag van verschillende objecten in zowel niet-ingesloten als ingesloten databases wordt samengevat in deze tabel:

In het voorbeeld van de tijdelijke tabel dat eerder is beschreven, kunnen we zien dat dit sorteringsgedrag de noodzaak voor een expliciete COLLATE component in de meeste tijdelijke tabellen elimineert. In een ingesloten database wordt deze code nu zonder fouten uitgevoerd, zelfs als de database- en instantiesorteringen verschillen:

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;

Deze query werkt omdat beide T1_txt en T2_txt zijn gesorteerd in de databasesortering van de ingesloten database.

Kruis tussen ingesloten en niet-bevlekte contexten

Zolang een sessie in een ingesloten database zich blijft bevinden, moet deze zich in de database bevinden waarmee de database is verbonden. In dit geval is het gedrag eenvoudig. Maar wanneer een sessie overgaat van ingesloten naar niet-ingesloten contexten, wordt het gedrag complexer, omdat de twee sets regels met elkaar moeten worden verbonden. Dit kan gebeuren in een gedeeltelijk ingesloten database, omdat een gebruiker een andere database kan USE gebruiken. In dit geval wordt het verschil in sorteringsregels afgehandeld door het volgende principe.

• Het sorteringsgedrag voor een batch wordt bepaald door de database waarin de batch begint.

Deze beslissing wordt genomen voordat opdrachten worden uitgegeven, inclusief een initiële USEopdracht. Als een batch in een ingesloten database begint, maar de eerste opdracht is USE voor een niet-ingesloten database, wordt het ingesloten sorteringsgedrag nog steeds gebruikt voor de batch. In dit scenario kan een verwijzing naar een variabele bijvoorbeeld meerdere mogelijke resultaten hebben:

• De verwijzing kan precies één overeenkomst vinden. In dit geval werkt de verwijzing zonder fouten.

• Deze verwijzing kan mogelijk geen overeenkomst vinden in de huidige sorteervolgorde, hoewel er eerder wel een overeenkomst was. Hiermee wordt een fout gegenereerd die aangeeft dat de variabele niet bestaat, ook al is deze blijkbaar gemaakt.

• De verwijzing kan meerdere overeenkomsten vinden die oorspronkelijk van elkaar verschilden. Hiermee wordt ook een fout gegenereerd.

We illustreren dit met een paar voorbeelden. Hiervoor gaan we ervan uit dat er een gedeeltelijk ingesloten database genaamd MyCDB is, waarvan de databasesortering is ingesteld op de standaardsortering Latin1_General_100_CI_AS_WS_KS_SC. We gaan ervan uit dat de instantiesortering is Latin1_General_100_CS_AS_WS_KS_SC. De twee collaties verschillen alleen in hoofdlettergevoeligheid.

Voorbeeld 1

In het volgende voorbeeld ziet u hoe de verwijzing precies één overeenkomst vindt.

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #a VALUES (1);
GO

USE master;
GO

SELECT * FROM #a;
GO

Results:

Hier is het resultatenoverzicht.

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

In dit geval werkt de geïdentificeerde #a zowel met de hoofdletterongevoelige catalogussortering als met de hoofdlettergevoelige instantiesortering, en de code functioneert.

Voorbeeld 2

Het volgende voorbeeld illustreert het geval waarin de verwijzing geen overeenkomst vindt in de huidige sortering waar er eerder een overeenkomst was.

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #A VALUES (1);
GO

Hier bindt #A aan #a in de niet-hoofdlettergevoelige standaardsortering, en de invoegbewerking werkt,

Hier is het resultatenoverzicht.

(1 row(s) affected)

Maar als we het script voortzetten...

USE master;
GO

SELECT * FROM #A;
GO

We krijgen een foutmelding bij het proberen te binden aan #A in de hoofdlettergevoelige instantiesortering.

Hier is het resultatenoverzicht.

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

Voorbeeld 3

In het volgende voorbeeld ziet u hoe de verwijzing meerdere overeenkomsten vindt die oorspronkelijk uniek waren. We beginnen eerst in tempdb (dat dezelfde hoofdlettergevoelige sortering heeft als ons exemplaar) en voeren daarna de volgende instructies uit.

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

Deze query slaagt, omdat de tabellen verschillend zijn in deze collatie.

Hier is het resultatenoverzicht.

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

Als we echter naar onze ingesloten database gaan, vinden we dat we deze tabellen niet meer kunnen binden.

USE MyCDB;
GO

SELECT * FROM #a;
GO

Hier is het resultatenoverzicht.

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.

Verwante inhoud

• ingesloten databases