April 24, 2020 at 4:28 pm
As the database is using a case-sensitive collation, I don't see why the 2nd option wouldn't work.
Hi Chris. For the question, the DB is actually using a case-insensitive collation: Latin1_General_100_CI_AS_SC. There is (at least currently) a minor typo in that collation name in the question where the final "_SC" is showing as "_CS" in the question. I suppose that final "_CS" (as it appears in the question) can be misleading as it looks like it means "case-sensitive", but the key factor is the position of the "_XYZ" option in the collation name:
_CI or _CS ) is always present and always the first option after the name, code page (for the SQL_* collations), or version number._SC or not present), if present in the name, is either the final option or, starting in SQL Server 2019, can be 2nd to last when using the new _UTF8 collations since the _UTF8 will be the final option in the name, if present.
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
April 24, 2020 at 7:54 pm
Henrik Staun Poulsen wrote:Hi Frederico Fonseca,
Yes, that might change the collation of my database, but not that of TEMPDB. So any query using a #temp table would need to be checked (and possibly revised) for any collation conflicts.
I once had a test server with 3 different collations in Master, Msdb and mydatabase. That was "fun". It ended up with a re-install of SQL Server.
Best regards, Henrik
yes I am aware of those issues with On Prem - in Azure I don't know if it works the same as I don't have any setup - if you do maybe you could test it.
regardless if any code using temp tables is created using collate default database it will work fine.
in any case I was just pointing out that you can indeed change the user database collation which you said could not be done.
Henrik:
[msdb] being a different collation is due to restoring it from another instance that had a different collation. The same goes for the other system DBs. Of course, since [tempdb] is recreated from [model] upon each startup of the service, if [tempdb] was restored from another instance and has a different collation, restarting will fix it. Not so easy in the case of the other 3 system DBs.
Frederico:
COLLATE DATABASE_DEFAULT will help in such cases, it is not necessarily the DATABASE_DEFAULT keyword that does the trick. The focus here is on the COLLATE clause since explicitly specifying the collation rather than using [tempdb]'s default collation is the issue. There are times when you do not want to use DATABASE_DEFAULT, such as when the column is using a collation that is not the same as the local DB's default. The benefit of DATABASE_DEFAULT is for situations in which you will be deploying the schema to multiple DBs and cannot guarantee the collation of them (like 3rd party code).
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply