CODEWITHSUNDEEP
sql-serverazure-sql-databasecollate
Nitrox Tank
Nitrox Tank

Reputation: 1

CREATE DATABASE: Difference between CATALOG_COLLATION and COLLATE

There seems to be a big difference between CREATE DATABASE COLLATE and CREATE DATABASE WITH CATALOG_COLLATION.

DECLARE @CollationName NVARCHAR(4000) = 'Latin1_General_CI_AS'

SELECT [Name], COLLATIONPROPERTY([Name], 'CodePage') as Code_Page, [Description]
FROM sys.fn_HelpCollations()
WHERE [Name] = @CollationName
;

GO

CREATE DATABASE SomeDatabaseName1
COLLATE Latin1_General_CI_AS
;

GO

CREATE DATABASE SomeDatabaseName2
WITH CATALOG_COLLATION = Latin1_General_CI_AS;

Running the first statement confirms that the collation does indeed exist on the server. However, running this script gives the output:

(1 row affected)

Msg 448, Level 16, State 5, Line 17

Invalid collation 'Latin1_General_CI_AS'

So SomeDatabaseName1 is created but SomeDatabaseName2 is not.

I tried with a bunch of random collations from the sys.fn_HelpCollations() function and got the same results, except for my server's collation SQL_Latin1_General_CP1_CI_AS. Using that collation creates SomeDatabaseName2 without throwing an error.

Running this with DATABASE_DEFAULT causes the creation of SomeDatabase1 to fail, but the creation of SomeDatabase2 succeeds. That's probably a syntax error of some kind, I'm less interested in that question.

It seems that WITH CATALOG_COLLATION specifically refers to how the column names, table names etc. are stored in the metadata catalogue, whereas COLLATE refers to how the data is stored in those columns. This functions differently depending on whether you're using Azure or not. So why does the latter fail with most collations on a local SQL Server instance, whereas it's fine on Azure? Surely I could store column names with any collation regardless of whether it's in the cloud or not? (Is this related to the collation's Unicode support?)

Upvotes: 0

Views: 1544

Answers (0)

Related Questions