What will happen with CATALOG_COLLATION and Case Sensitive vs Case Insensitive


One of the options available when creating a database is the CATALOG_COLLATION, by default this is going to be SQL_Latin1_General_CP1_CI_AS , even if you take the trouble of specifying a database collation , the catalog collation will be SQL_Latin1_General_CP1_CI_AS unless you say otherwise. If you do not set this parameter it will not use the database collation as the default it will revert back to the collation for master.

When we create a database CREATE DATABASE (Azure SQL DB), we have the option to specify the collation for both Database and Catalog, but what difference does it make if you make your catalog CS (Case Sensitive).

NOTE: The database collation can be changed, however the catalog collation can’t be changed once it is set.

 

Prerequisites:
1- Login to Azure SQL DB use SSMS
2- Create 2 databases by execute the below commands.

Execute the below commands to create required sample databases in our test!

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;

Create couple of tables for demonstration

CREATE TABLE dbo.EmployeeID (EmployeeID int Not Null,
FirstName nvarchar(30) not null,
LastName nvarchar(30) not null
PRIMARY KEY CLUSTERED);

CREATE TABLE dbo.DepartmentID (DeptID int Not Null,
DeptName nvarchar(10) not null
PRIMARY KEY CLUSTERED);

Insert couple of rows for demonstration

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');

Run below command to (DB-CI)

SELECT * FROM EmployeeID
SELECT * FROM employeeId

 

rows are returned without any errors!

Run below command to (DB-CS)

SELECT * FROM EmployeeID
SELECT * FROM employeeId

below error message returned!

(2 rows affected)
Msg 208, Level 16, State 1, Line 18
Invalid object name 'employeeId'.

 

To explain why first query returned results. Database [DB-CI] created follow (CS) as a collation from Database-Level, but CATALOG_COLLATION use (CI) collation, in that case when execute any query use (Case Insensitive) column name as follow [UserId & userID] both queries returned results.

So why second query returned an error when specify userid column in SELECT statement!?. This happened when CATALOG_COLLATION follow DATABSE_DEFAULT as shown here in this command CATALOG_COLLATION = DATABASE_DEFAULT; and follow DATABASE_DEFAULT collation was Latin1_General_100_CS_AS_SC.

As mentioned here in Database Identifiers document.

The collation of an identifier depends on the level at which it is defined. Identifiers of instance-level objects, such as logins and database names, are assigned the default collation of the instance. Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation.

 

Update 10th May, 2018:

Recently have been working on another support ticket where customer on daily bases used to export Azure SQL Database to Azure Storage, then download *.bacpac file locally, later import to SQL Server on-premises.

Recently customer start to receive below error message when trying to import bacpac file to local SQL Server!

"The Element or Annotation class SqlDatabaseOptions does not contain the property class CatalogCollation.   (Microsoft.Data.Tools.Schema.Sql)"

Looking inside model.xml file. we found this new property added and prevent complete import bacpac to local SQL Server:

<Property Value="2" Name="CatalogCollation"/>

Sample model.xml file property:

<?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">
<Property Value="SQL_Latin1_General_CP1_CI_AS" Name="Collation"/>
<Property Value="False" Name="IsAnsiPaddingOn"/>
<Property Value="False" Name="IsQuotedIdentifierOn"/>
<Property Value="True" Name="IsCursorDefaultScopeGlobal"/>
<Property 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"/>
<Property 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">

We test import bacpac file locally use latest SSMS release version 17.6 build number : 14.0.17230.0; can be found here

This help resolve and successfully we are able to import bacpac file to local SQL Server, new release version of SSMS, understand this Property, in such cases this property only available in Azure SQL DB, where used to Export/Import between Azure SQL DBs.

Note: in case using SqlPackage.exe command line utility to import bacpac to local SQL Server, its recommended to run SqlPackage command using the recent version that can be found in "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\SqlPackage.exe"

I hope that has bought some clarity what will happen with CATALOG_COLLATION and Case Sensitive vs Case Insensitive.

Comments (0)

Skip to main content