0

I have a database with a case sensitive collation because the data inside of it should be treated as case sensitive in the context of how it's used.

The entities and their column names within that same database don't need to be case sensitive and we'd prefer that they weren't for querying purposes.

Is it possible to keep the data itself as case sensitive but allow the entities to be case insensitive within the same database?

2

Yes. Turn on partial database containment, and you’ll get a case-insensitive catalog collation along with the collation of your choice for the data.

In a contained database, the catalog collation Latin1_General_100_CI_AS_WS_KS_SC. This collation is the same for all contained databases on all instances of SQL Server and cannot be changed. The database collation is retained, but is only used as the default collation for user data. By default, the database collation is equal to the model database collation, but can be changed by the user through a CREATE or ALTER DATABASE command as with non-contained databases.

See https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-database-collations?view=sql-server-ver15

And note that Azure SQL Database always uses contained database collations.

| improve this answer | |
  • Great, thank you! A follow up question, is it possible to do this only on the secondary replica database of a synchronous AlwaysOn AG (but leave the primary replica with it's original collation and settings)? – J.D. Sep 22 at 23:43
  • 1
    No. AG replicas are physically identical. – David Browne - Microsoft Sep 22 at 23:49
  • Ah ok thanks. So if I didn't want to go mucking about in a vendor application's database, but wanted to solve the above collation problem, is my next best bet to use replication? Would I run into any issues with one way transactional replication if the subscriber database was a different collation than the publisher's? – J.D. Sep 23 at 0:03
  • 1
    You'd want to set the column collation of the subscriber tables to match the publisher, or you might run into key duplicate errors for columns with unique indexes on character data. But if you create the subscriber with a matching database collation, and then change the database collation after the subsriber tables are created, that should work. docs.microsoft.com/en-us/sql/relational-databases/collations/… – David Browne - Microsoft Sep 23 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.