Case Sensitive column names in Sql Azure Database

user_253732 0
Viewed 4523

Forever I've used a case sensitive collation in Sql Server (SQL_Latin1_General_CP1_CS_AS). I'm trying to move to Sql Azure Database and I've run into an unexpected problem. It looks like it's impossible to have case sensitive column names. Can this be true?

I create my database...

CREATE DATABASE MyDatabase
COLLATE SQL_Latin1_General_CP1_CS_AS

And I create my table...

CREATE TABLE [MyTable]
(
    [Name] NVarChar (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
    [name] NVarChar (4000) COLLATE SQL_Latin1_General_CP1_CS_AS NULL                                                                                
)

And I get the error: Column names in each table must be unique. Column name 'name' in table 'MyTable' is specified more than once.

Ugh, disaster. This works perfectly in Sql Server 2012. However on Sql Azure I can't seem to make it happen. Does anyone know why this is not working in Sql Azure? Does anyone know how I can make this work in Sql Azure? Thanks.

azure sql-server sql database
3 Answers

To solve your case you need to add CATALOG_COLLATION.

CREATE DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1_CS_AS WITH CATALOG_COLLATION = DATABASE_DEFAULT

Source: What will happen with CATALOG_COLLATION and Case Sensitive vs Case Insensitive

Unfortunately it looks like you can put only two values there: SQL_Latin1_General_CP1_CI_AS or DATABASE_DEFAULT

I had mirror problem. I wanted to get schema objects (table names, column names etc.) to be CI and data inside of database to be CS. - This works in Azure by default with CS COLLATE eg. SQL_Latin1_General_CP1_CS_AS set on database creation. But it does not work on local SQLEXPRESS database which is a problem to test application locally. When you create database case sensitive CS then all queries starts to be case sensitive too. This makes my hibernate stop to work. (Object does not exists etc).

To solve this I need to create database with SQL_Latin1_General_CP1_CI_AS COLLATE and define CS COLLATE for each column separately:

create table test (
 pk int PRIMARY KEY,
 data_cs varchar(123) COLLATE SQL_Latin1_General_CP1_CS_AS,
 CONSTRAINT [unique_data] UNIQUE NONCLUSTERED (data_cs)
)

insert into TEST values (1, 'ABC');
insert into tEsT values (2, 'abc');

select * from TEST where DATA_cs like 'A%'

CATALOG_COLLATION works in Azure SQL only I guess.

user_23319200
Related