Inneslutna databassortering

gäller för:SQL ServerAzure SQL Managed Instance

Olika egenskaper påverkar sorteringsordningen och likhetssemantiken för textdata, inklusive skiftlägeskänslighet, accentkänslighet och det basspråk som används. Dessa egenskaper uttrycks för SQL Server genom valet av sortering för data. En mer ingående diskussion om sortering finns i Stöd för sortering och Unicode.

Sortering gäller inte bara för data som lagras i användartabeller, utan för all text som hanteras av SQL Server, inklusive metadata, temporära objekt, variabelnamn osv. Hanteringen av dessa skiljer sig åt i inneslutna och icke-inneslutna databaser. Den här ändringen påverkar inte många användare, men hjälper till att ge instansen oberoende och enhetlighet. Men detta kan också orsaka viss förvirring och problem för sessioner som har åtkomst till både inneslutna och icke-inneslutna databaser.

Sorteringsbeteendet för inneslutna databaser skiljer sig subtilt från beteendet i icke-inneslutna databaser. Det här beteendet är i allmänhet fördelaktigt, vilket ger instansoberoende och enkelhet. Vissa användare kan ha problem, särskilt när en session kommer åt både inneslutna och icke-inneslutna databaser.

Den här artikeln förtydligar innehållet i ändringen och undersöker områden där ändringen kan orsaka problem.

Icke-inneslutna databaser

Alla databaser har en standardsortering (som kan anges när du skapar eller ändrar en databas). Den här sorteringen används för all metadata i databasen och som standardinställning för alla strängkolumner i databasen. Användare kan välja en annan sortering för en viss kolumn genom att använda COLLATE-satsen.

Exempel 1

Om vi till exempel arbetade i Peking skulle vi kunna använda en kinesisk sortering:

ALTER DATABASE MyDB
    COLLATE Chinese_Simplified_Pinyin_100_CI_AS;

Om vi nu skapar en kolumn är standardsortering den här kinesiska sorteringen, men vi kan välja en annan om vi vill:

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

Här är resultatet.

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

Detta verkar relativt enkelt, men flera problem uppstår. Eftersom sortering för en kolumn är beroende av databasen där tabellen skapas uppstår problem med användningen av temporära tabeller som lagras i tempdb. Sorteringen av tempdb matchar vanligtvis sorteringen för instansen, som inte behöver matcha databassortering.

Exempel 2

Tänk till exempel på den (kinesiska) databas som visades tidigare, när den används på en instans med en Latin1_General sortering:

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

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

Vid första anblicken ser dessa två tabeller ut som om de har samma schema, men eftersom databasernas sorteringar skiljer sig åt är värdena inkompatibla:

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

Här är resultatet.

Msg 468, nivå 16, delstat 9, rad 2

Kan inte lösa sorteringskonflikten mellan "Latin1_General_100_CI_AS_KS_WS_SC" och "Chinese_Simplified_Pinyin_100_CI_AS" i likhetsoperationen.

Vi kan åtgärda detta genom att uttryckligen sortera den tillfälliga tabellen. SQL Server gör detta enklare genom att ange nyckelordet DATABASE_DEFAULTCOLLATE för -satsen.

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;

Den här frågan körs nu utan fel.

Vi kan också se sorteringsberoende beteende med variabler. Tänk på följande funktion:

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

Detta är en ganska märklig funktion. I en skiftlägeskänslig sorteringsordning kan @i i retursatsen inte binda till varken @I eller @İ. I en skiftlägesokänslig Latin1_General kollationering binder @i till @I och funktionen returnerar 1. Men i en icke skiftlägeskänslig turkisk sortering @i binder till @İ, och funktionen returnerar 2. Detta kan orsaka förödelse på en databas som flyttas mellan instanser med olika sortering.

Avgränsade databaser

Eftersom ett designmål för inneslutna databaser är att göra dem självständiga, måste beroendet av instansen och tempdb sorteringarna vara avskurna. För att göra detta introducerar inneslutna databaser begreppet katalogsortering. Katalogsortering används för systemmetadata och tillfälliga objekt. Information tillhandahålls på följande sätt.

I en begränsad databas är katalogsorteringsordningen Latin1_General_100_CI_AS_WS_KS_SC. Den här sorteringen är densamma för alla inneslutna databaser på alla instanser av SQL Server och kan inte ändras.

Databassortering behålls, men används endast som standardsortering för användardata. Som standardinställning är sorteringsordningen för databasen samma som model sorteringsordning, men kan ändras av användaren med ett CREATE- eller ALTER DATABASE-kommando precis som med okapslade databaser.

Ett nytt nyckelord, CATALOG_DEFAULT, är tillgängligt i COLLATE -satsen. Detta används som en genväg till den aktuella sorteringen av metadata i både inneslutna och icke-inneslutna databaser. I en databas som inte är innesluten returnerar CATALOG_DEFAULT alltså den aktuella databassorteringen, eftersom metadata är sorterade enligt databassorteringen. I en innesluten databas kan dessa två värden vara olika, eftersom användaren kan ändra databassortering så att den inte matchar katalogsortering.

Beteendet för olika objekt i både icke-inneslutna och inneslutna databaser sammanfattas i den här tabellen:

I exemplet med temp-tabellen som beskrevs tidigare kan vi se att det här sorteringsbeteendet eliminerar behovet av en explicit COLLATE sats i de flesta temporära tabeller som används. I en innesluten databas körs den här koden nu utan fel, även om databas- och instanssorteringarna skiljer sig åt:

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;

Den här frågan fungerar eftersom både T1_txt och T2_txt är sorterade i databassortering av den inneslutna databasen.

Korsning mellan inneslutna och obehindrade kontexter

Så länge en session i en innesluten databas finns kvar måste den finnas kvar i den databas som den är ansluten till. I det här fallet är beteendet enkelt. Men om en session korsar mellan inneslutna och icke-inneslutna kontexter blir beteendet mer komplext, eftersom de två regeluppsättningarna måste överbryggas. Detta kan inträffa i en delvis innesluten databas, eftersom en användare kan USE komma till en annan databas. I det här fallet hanteras skillnaden i sorteringsregler av följande princip.

• Sorteringsbeteendet för en batch bestäms av databasen där batchen börjar.

Det här beslutet fattas innan några kommandon utfärdas, inklusive en första USE. Om en batch börjar i en innesluten databas, men det första kommandot är USE till en icke-innesluten databas, används det inneslutna sorteringsbeteendet fortfarande för batchen. Med tanke på det här scenariot kan en referens till en variabel till exempel ha flera möjliga resultat:

• Referensen kan eventuellt hitta exakt en matchning. I det här fallet fungerar referensen utan fel.

• Referensen kanske inte hittar någon matchning i den aktuella sorteringen där det fanns en tidigare. Detta genererar ett fel som anger att variabeln inte finns, även om den uppenbarligen skapades.

• Referensen kan hitta flera matchningar som ursprungligen var distinkta. Detta ger också upphov till ett fel.

Vi illustrerar detta med några exempel. För dessa förutsätter vi att det finns en delvis innesluten databas med namnet MyCDB med dess databassortering inställd på standardsortering, Latin1_General_100_CI_AS_WS_KS_SC. Vi antar att instanskollationen är Latin1_General_100_CS_AS_WS_KS_SC. De två sorteringarna skiljer sig bara åt i skiftlägeskänslighet.

Exempel 1

I följande exempel visas det fall där referensen hittar exakt en matchning.

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #a VALUES (1);
GO

USE master;
GO

SELECT * FROM #a;
GO

Results:

Här är resultatet.

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

I det här fallet binder den identifierade #a i både skiftlägesokänslig katalogsortering och skiftlägeskänslig instanssortering, och koden fungerar.

Exempel 2

I följande exempel visas det fall där referensen inte hittar någon matchning i den aktuella sorteringen där det fanns en tidigare.

USE MyCDB;
GO

CREATE TABLE #a (x INT);

INSERT INTO #A VALUES (1);
GO

Här binder #A till #a i skiftlägesokänslig standardsortering, och infogningen fungerar,

Här är resultatet.

(1 row(s) affected)

Men om vi fortsätter skriptet...

USE master;
GO

SELECT * FROM #A;
GO

Vi får ett fel när vi försöker binda till #A i skiftlägeskänslig instanskollation.

Här är resultatet.

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

Exempel 3

I följande exempel visas det fall där referensen hittar flera matchningar som ursprungligen var distinkta. Först börjar vi i tempdb (som har samma skiftlägeskänsliga sortering som vår instans) och kör följande instruktioner.

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

Den här frågan lyckas eftersom tabellerna är distinkta i den här sorteringen:

Här är resultatet.

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

Om vi flyttar till vår inneslutna databas upptäcker vi dock att vi inte längre kan binda till dessa tabeller.

USE MyCDB;
GO

SELECT * FROM #a;
GO

Här är resultatet.

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.

Relaterat innehåll

• inneslutna databaser