How to change the collation of a Synapse Serverless database? Specifically Latin1_General_100_CI_AI_SC_UTF8, as that seems to be recommended, and I'm trying to performance tune.
alter database MyDatabase collate Latin1_General_100_CI_AI_SC_UTF8
Results in the error
The database MyDatabase could not be exclusively locked to perform the operation.
OK, set single user
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Results in the error
SINGLE_USER is not supported for ALTER DATABASE.
Microsoft learn has an article on switching Collation in Synapse
But the text mentions
"To change the default collation for dedicated SQL pool database,..."
I can't find anything for Serverless in the documentation.
Microsoft Learn also has an article on switching to Single User
But it just gives the same command (which errors), or using the GUI in SSMS - which doesn't have same options when I check it on a serverless database.
Got it. Although the command to set single user doesn't work in Synapse Serverless, I just worked around it by booting everyone off /restarting SSMS myself, and only connecting the query window (not object explorer), so only one connection was present.
Then I was able to execute
ALTER DATABASE MyDatabase COLLATE Latin1_General_100_BIN2_UTF8
That worked for 1 / 2 database. The other, was still complaining. For that one, I ran the following code:
select DB_NAME(database_id), 'kill '+cast(session_id as varchar(10)), *
from sys.dm_exec_sessions
where DB_NAME(database_id) = 'datawarehouseserverless'
order by 1
Then killed whatever came up. And at that point, was able to run the ALTER DATABSE...COLATE command.