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.

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?

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]'
begin
    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]';
end
go

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

@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.