2

We are currently using SQL Server 2016 and currently the collation set on our database is SQL_Latin1_General_CP1_CI_AS. Our Analaysis services' collation is set to SQL_Latin1_General_CP1_CS_AS.

The recommendation from our vendor is that the relational database and the Analysis services collation should both be case sensitive.

We would like to understand if there are any downside to changing the collation on our relational database to be case sensitive as well, to match with the Analysis services.

Improve this question
3
  • 1
    Well sure, if you make your database case sensitive you risk breaking every line of code that touches it. :-) So, it depends. How faithful have your folks been about properly referring to columns, tables, etc. using their exact names as stored in the metadata vs. whether they could find their shift key that day?
    – Aaron Bertrand
    Commented Feb 22, 2019 at 17:57
  • I think changing the default collation is very tricky, and likely to cause all kinds of errors, so be very cautious. Why not change the Analysis services collation?
    – George Barwood
    Commented Feb 22, 2019 at 18:08
  • I don't think Analysis Services supports legacy SQL collations. Perhaps you mean the Windows Latin1_General_CP1_CS_AS collation.
    – Dan Guzman
    Commented Feb 22, 2019 at 18:57

1 Answer 1

Reset to default
3

I posted a detailed analysis of the implications of making any collation changes to an instance and/or database:

Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?

There are quite a few areas that could be affected, and it depends greatly on if you are talking just about one particular database, or the instance and one or more of the databases on that instance.

HOWEVER, perhaps in addition to asking what might the affect of such a change be, you should probably also be asking the vendor:

  1. Why they are making this recommendation? If they do not have a very specific reason for it, then maybe you shouldn't do it.
  2. What is the specific recommendation: is it to make the instance case-sensitive, or just this particular database?
  3. Is the vendor installing objects into an existing database, or do they create their own? If they create their own database, they can ensure that it is case-sensitive by setting the collation when the database is created. And if they are installing into an existing database, they can simply use the COLLATE clause in the CREATE TABLE statements to override the default from the database; they can use the COLLATE clause in any expression to override the database's default collation.
  4. Why are they using obsolete SQL Server collations instead of the newer, more appropriate Windows collations? For anyone using SQL Server 2008 or newer , Latin1_General_100_CS_AS_SC would be the preferred equivalent of SQL_Latin1_General_CP1_CS_AS.
Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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