0

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?)

Nitrox Tank
  • 1
  • 2
  • 1
    Does this answer your question? [Create table with CATALOG\_COLLATION fails with syntax error (Copy Azure database to local development SQL Server)](https://stackoverflow.com/questions/59594890/create-table-with-catalog-collation-fails-with-syntax-error-copy-azure-database) – SMor Apr 11 '22 at 12:54
  • From the docs: *"`CATALOG_COLLATION` Specifies the default collation for the metadata catalog."* so it specifies how the columns and tables are stored, as opposed to `COLLATE` which is the default collation for the data. SQL Server (on-prem, as opposed to Azure) does not allow such a distinction – Charlieface Apr 11 '22 at 12:58
  • The other question gives the practical tip of switching out WITH CATALOG-COLLATION for COLLATE, but it doesn't explain why WITH CATALOG_COLLATE basically doesn't work for local SQL Server instances. Note that I asked SQL Server 2019 to generate a CREATE DATABASE script, and it added the WITH CATALOG_COLLATION option automatically, even though I've never used Azure. The other question puts Azure in the title and tags it with azure-sql-database, which isn't always relevant to a reader. Also, the other question's title is kinda crap. – Nitrox Tank Apr 11 '22 at 13:20
  • "I asked SQL Server 2019 to generate a CREATE DATABASE script" SQL Server doesn't have such a feature. SSMS (which is a management tool and IDE) does, but it shouldn't create the wrong script. If it does, and you are not connected to an Azure instance, then file a bug – Charlieface Apr 11 '22 at 13:24
  • SQL Server on-premise and Azure SQL are different versions of SQL Server, so the simple explanation for why something works in one but not the other is "because MS made it that way". For on-prem SQL Server this option isn't documented at all. For Azure it's documented with exactly two valid values: `DATABASE_DEFAULT` and `SQL_Latin1_General_CP1_CI_AS` and nothing else. Basically, this is working as designed. Except, per Charlie, possibly the script generating tool, since it seems to output invalid syntax in a lot of cases. – Jeroen Mostert Apr 11 '22 at 15:33

0 Answers0