Ce se va intampla cu CATALOG_COLLATION si Case Sensitive vs Case Insensitive

Publicata pentru prima data pe MSDN pe 15 februarie 2018

Una dintre optiunile disponibile la crearea unei baze de date este CATALOG_COLLATION , in mod implicit, aceasta va fi SQL_Latin1_General_CP1_CI_AS , chiar daca va confruntati cu specificarea unei colatii de baze de date, colationarea catalogului va fi SQL_Latin1_General_CP1_CI_AS, cu exceptia cazului in care spui altfel. Daca nu setati acest parametru, acesta nu va utiliza colationarea bazei de date ca implicit, va reveni la colatia pentru master.

Cand cream o baza de date CREATE DATABASE (Azure SQL DB), avem optiunea de a specifica colationarea atat pentru baza de date, cat si pentru catalog, dar ce diferenta are daca faceti catalogul dvs. CS (Case Case Sensitive).

NOTA: colationarea bazei de date poate fi modificata, totusi colatia catalogului nu poate fi modificata odata ce este setata.

Cerinte preliminare:

1- Conectati-va la Azure SQL DB utilizati SSMS

2- Creati 2 baze de date executand comenzile de mai jos.

Executati comenzile de mai jos pentru a crea probe de baze de date necesare in testul nostru!

CREATE DATABASE [DB-CI] COLLATE Latin1_General_100_CS_AS_SC (MAXSIZE = 100 MB, EDITION = ‘basic’)

WITH CATALOG_COLLATION = SQL_Latin1_General_CP1_CI_AS;

CREATE DATABASE [DB-CS] COLLATE Latin1_General_100_CS_AS_SC (MAXSIZE = 100 MB, EDITION = ‘basic’)

WITH CATALOG_COLLATION = DATABASE_DEFAULT;

Creati cateva tabele pentru demonstratie

CREATE TABLE dbo.EmployeeID (EmployeeID int Not Null,

FirstName nvarchar (30) not null,

LastName nvarchar (30) not nul

PRIMARY KEY CLUSTERED);

CREATE TABLE dbo.DepartmentID (DeptID int Not Null,

DeptName nvarchar (10) not nul

PRIMARY KEY CLUSTERED);

Introduceti cateva randuri pentru demonstrare

INSERT INTO dbo.EmployeeID VALUES (N’1234 ‘, N’Mohamed’, N’Baioumy ‘), (N’5678’, N’Clive ‘, N’Challinor’);

INSERT INTO dbo.DepartmentID VALUES (N’1222 ‘, N’HR’), (N’13333 ‘, N’Sales’);

Rulati comanda de mai jos catre (DB-CI)

SELECT * FROM EmployeeID

SELECT * FROM employeeId

randurile sunt returnate fara erori!

Rulati comanda de mai jos catre (DB-CS)

SELECT * FROM EmployeeID

SELECT * FROM employeeId

mesajul de eroare de mai jos a fost returnat!

(2 randuri afectate)

Msg 208, Nivelul 16, Starea 1, Linia 18

Denumire obiect nevalid „angajatId”.

Pentru a explica de ce prima interogare a returnat rezultate. Baza de date [DB-CI] creata urmareste (CS) ca o colatie de la nivel de baza de date, dar CATALOG_COLLATION foloseste colatia (CI) , in acest caz, atunci cand executati orice interogare utilizati numele de coloana (Insensibil la majuscule), dupa cum urmeaza [UserId & userID] ambele interogari rezultatele returnate.

Deci, de ce a doua interogare a returnat o eroare atunci cand specificati coloana userid in instructiunea SELECT!?. Acest lucru s-a intamplat cand CATALOG_COLLATION urmeaza DATABSE_DEFAULT asa cum se arata aici in aceasta comanda CATALOG_COLLATION = DATABASE_DEFAULT; si urmati colectionarea DATABASE_DEFAULT a fost Latin1_General_100_CS_AS_SC .

Dupa cum se mentioneaza aici in documentul Identificatori de baze de date.

Colactionarea unui identificator depinde de nivelul la care este definit. Identificatorilor de obiecte la nivel de instanta, cum ar fi datele de conectare si numele bazei de date, li se atribuie colationarea implicita a instantei. Identificatorilor de obiecte dintr-o baza de date, cum ar fi tabelele, vizualizarile si numele coloanelor, li se atribuie colationarea implicita a bazei de date. De exemplu, doua tabele cu nume care difera numai in cazul in care pot fi create intr-o baza de date care are o colationare diferentiata de majuscule, dar nu poate fi creata intr-o baza de date care are o colatie care sa nu distinga majusculele si minusculele.

Actualizare 10 mai 2018:

Recent s-a lucrat la un alt bilet de asistenta in care clientul zilnic a folosit pentru a exporta baza de date SQL Azure in stocarea Azure, apoi a descarca fisierul * .bacpac local, ulterior a importa in SQL Server local.

Recent, clientul incepe sa primeasca mai jos un mesaj de eroare atunci cand incearca sa importe fisierul bacpac in SQL Server local!

“Clasa Element sau Adnotare SqlDatabaseOptions nu contine clasa de proprietati CatalogCollation. (Microsoft.Data.Tools.Schema.Sql)”

Privind in interiorul fisierului model.xml. am gasit aceasta noua proprietate adaugata si impiedica importul complet bacpac pe SQL Server local:

<Value Value = “2” Name = “CatalogCollation” />

Exemplu de proprietate a fisierului model.xml:

<? xml version = “1.0” encoding = “UTF-8”?>

<DataSchemaModel xmlns = “http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02” CollationCaseSensitive = “False” CollationLcid = ” 1033 “DspName =” Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider “SchemaVersion =” 3.0 “FileFormatVersion =” 1.2 “>

<Model>

<Element Type =” SqlDatabaseOptions “>

<Value Value =” SQL_Latin1_General_ “/>

<Value Value =” False “Name =” IsAnsiPaddingOn “/>

<Property Value =” False “Name =” IsQuotedIdentifierOn “/>

<Property Value =” True “Name =”IsCursorDefaultScopeGlobal “/>

<Value Value =” False “Name =” IsTornPageProtectionOn “/>

<Property Value =” True “Name =” IsFullTextEnabled “/>

<Property Value = “140” Name = “CompatibilityMode” />

<Property Value = “1” Name = “ServiceBrokerOption” />

<Property Value = “True” Name = “IsAllowSnapshotIsolation” />

<Property Value = “True” Name = “IsReadCommittedSnapshot” />

<Value Value = “True” Name = “IsChangeTrackingOn” />

<Property Value = “1” Name = “ChangeTrackingRetentionPeriod” />

<Property Value = “True” Name = “IsEncryptionOn” />

<Property Value = “2” Name = “CatalogCollation” />

<Property Value = “” Name = “FileStreamDirectoryName” />

<Property Value = “120”Name = “TargetRecoveryTimePeriod” />

<Relationship Name = “DefaultFilegroup”>

Testam importarea fisierului bacpac la nivel local, folosind cea mai recenta versiune a versiunii 17.6 a numarului de versiune SSMS: 14.0.17230.0; pot fi gasite aici

Acest ajutor rezolva si reusim sa importam fisierul bacpac in SQL Server local, versiunea noua a SSMS, intelegem aceasta proprietate, in astfel de cazuri aceasta proprietate este disponibila numai in Azure SQL DB, unde este utilizata pentru Export / Import intre Azure SQL DBs.

Nota: in cazul utilizarii utilitarului de linie de comanda SqlPackage.exe pentru a importa bacpac in SQL Server local, este recomandat sa executati comanda SqlPackage folosind versiunea recenta care poate fi gasita in „C: \ Program Files (x86) \ Microsoft SQL Server \ 140 \ DAC \ bin \ SqlPackage.exe ”

Sper ca a cumparat o claritate ce se va intampla cu CATALOG_COLLATION si Case Sensitive vs Case Insensitive.