Determine SQL collation during installation

Userlevel 3
Badge +5


As an ISV we install our end-user databases on our customer’s servers. That means that we cannot always set the default collation of the server. How can we set the collation for a new Thinkwise database generated with the Thinkwise Deployment Center? 
Or could it be set in the model?


Best answer by Mark Jongeling 7 June 2024, 10:54

View original

5 replies

Userlevel 7
Badge +23

Hi Tejo, 

By default, any new installation (or deployment / creation of a new database) will use the Server collation unless otherwise specified in the DB code file script.

… If <the collation is> not specified, the database is assigned the default collation of the instance of SQL Server.

Source: CREATE DATABASE (Transact-SQL) - SQL Server | Microsoft Learn

To always ensure compatibility between the database and the tepdb collation, you can set the collation of alphanumeric fields when creating temporary tables to "Database_default”. This is what the Software Factory also does as not all of our users use the same collation for the Software Factory as for their TempDB.

Userlevel 3
Badge +5

Thanks @Mark Jongeling , can we set the db create file with a code group? Or do we have to change this manually after creating a deployment package?

Userlevel 7
Badge +23

I'm not aware of any native possibility to preset the collation of a new database. You can opt to uitlize the Manual code file for that. In there you can verify the collation and alter it when it doesn't match the collation you want the database to be.

Dynamic SQL example; replace [COLLATION] with the desired collation:

if (select collation_name
from sys.databases
where name = db_name()) <> '[COLLATION]'
declare @alter_database_collation nvarchar(max) =
'ALTER DATABASE ' + quotename(db_name()) + ' COLLATE [COLLATION]';

exec sp_executesql @alter_database_collation;
print 'Database collation has been changed to [COLLATION]';


Userlevel 3
Badge +5

ah, ok, so it can be done after the creation, thanks!

Userlevel 3
Badge +8

@Tejo van de Bor, you may want to consider testing this thoroughly, because actions like this after the database has been provided with all kinds of data and other objects can lead to critical issues that you only will notice when it is way too late to revert.

One common issue that may occur is character misinterpretation: If the new collation uses a different character encoding than the old one, data containing special characters or symbols might be misinterpreted, leading to corruption. Which leads to data loss: In some cases, characters unsupported by the new collation might be dropped or replaced with substitute characters, resulting in data loss.

I’d strongly advice to set the collation as soon as possible, preferably when creating the database.

Just my 2 cents.