Changing the Database Collation–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was testing some changes recently and needed to verify how things worked in a different collation. Here’s the scenario: I had this situation and ran a query.

2019-02-13 12_22_07-SQLQuery7.sql - Plato_SQL2017.sandbox2 (PLATO_Steve (64))_ - Microsoft SQL Serve

As you can see, the query rates these two Unicode strings as equal, which makes some sense as this is a Case Insensitive, Accent Sensitive, default US English database.

I then went to the database properties to change this. When I did so, I picked a case sensitive collation.

2019-02-13 12_22_55-Database Properties - sandbox2

I clicked OK, and a few minutes, later I get this:

2019-02-13 12_25_38-Database Properties - sandbox2

Hmmm, why is this locked on my workstation? I have a query window open. Changing a collation is potentially a disruptive operation and requires an exclusive lock on the database. This means no other users can be inside the database with a connection.

The solution? Change the context of my query window. I can change this to master (or any other database) and make the change again.

While collation isn’t something you often change, if you do find a database that needs this alteration, you will need to drain off any connections before you can make this change.

SQLNewblogger

I ran across this while doing testing and realized this was a nice, short piece of information that I should remember. Not because I do this often, but because as I automate changes and use pipelines, I’d need a way to remove all connections before this would deploy.

An example of a nice short piece of information that I can document about my knowledge, and that might get asked about in an interview.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

1 Response to Changing the Database Collation–#SQLNewBlogger

  1. Hi Steve. I realize that this is just a simple post about getting around that particular error, but I wanted to mention two related things to raise awareness of them:

    1. There are quite a few restrictions that can prevent changing a database’s default collation. I detailed nearly everything in the following post (currently working on impact, if any, to Full Text Search):
    https://sqlquantumleap.com/2018/06/11/changing-the-collation-of-the-instance-and-all-columns-across-all-user-databases-what-could-possibly-go-wrong/

    2. Regarding your choice of case-sensitive collation: you should ideally use a Windows collation, and unless there is a specific reason not to, then use the most recent version of the desired collation. Windows collations are better than SQL Server collations for a variety of reasons. In your case the preferred collation is: “Latin1_General_100_CS_AS_SC”. It is quite sad that Windows collations were introduced in SQL Server 2000, yet Microsoft STILL uses “SQL_Latin1_General_CP1_CI_AS” as the default for installations on an OS using “US English” as its language. Even worse: it’s the only collation that is allowed as the instance-level collation for SQL Server Express LocalDB and Azure SQL Database Managed Instances. It’s crazy, and it’s a disservice to their users / customers.

    Take care, Solomon…

Comments are closed.