Is there a way to allow case sensitive column names in Azure Synapse?

Question

I want to have column name which are case sensitive in Azure synapse, i tried to have it within double quotes but still it was not working.

For e.g, "Column", "COLUMN", "column" I want to have all these column in a single table.

Is there any way of doing this.

Answer 1

@Nachiket Satpute - Thanks for the question and using MS Q&A platform.

Yes, you can change the default collation of your database to be case sensitive. This will allow you to have column names that are case sensitive.

To change the default collation, you can update the Collation field in the provisioning experience when creating a new dedicated SQL pool database. For example, if you wanted to change the default collation to case sensitive, you would change the collation from SQL_Latin1_General_CP1_CI_AS to SQL_Latin1_General_CP1_CS_AS.

Once you have changed the default collation, you can create a table with case sensitive column names. For example, you can create a table with columns named "Column", "COLUMN", and "column".

Please note that changing the default collation will affect all columns and expressions requiring collation information in your database. Also, keep in mind that query text (including variables, constants, etc.) is always handled using the database-level collation, and not the server-level collation as in other SQL Server offerings.

For more details, refer to Database collation support for Synapse SQL in Azure Synapse Analytics

Hope this helps. Do let us know if you any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.