I have a Visual Studio Database project (SQL Server) with tables, stored procedures etc. The tables have collation defined ex:
CREATE TABLE [dbo].[TestTable]
(
[TestColumn] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);
The database default collation is also SQL_Latin1_General_CP1_CI_AS.
I use sqlpackage to publish and the ScriptDatabaseCollation set to True.
When I modify the table from any direction (like adding a new column), and use the SSDT compare tool, it shows the collation as a different, even though the "Ignore collation" is set to True:
Also, another interesting is that, when I click on the generate script, it won't contain any collation modifications, just the new column.
It's even worst when I try to compare from the other direction (update the DB directly and use compare from DB to local project), because it updates my file and removes the collation.
Sytem information:
- SSDT Version 17.0.62204.01010
- MSSQL Server Express 15.0.4153.1
- Visual Studio Professional 2022 17.2.2
Does anybody know how can I solve this problem?
CodePudding user response：
I can only presume that SSDT is thus trying to remove "excessive" DML which it thinks is unnecessary. Since your column's collation is the same as that of the project, repeating it again doesn't really make much sense (at least from SSDT's point of view).
You probably will appreciate this behaviour if / when you will have multiple instances of your database with different default collations. Speaking of which, I hope you know what you are doing, choosing a very old, problematic SQL collation as a default for your system.
Having said that, SSDT doesn't always remove collate clauses from your DML. If you specify a column collation which is different from the project's default one, it won't disappear after schema comparison (assuming both source and target have the same one). In one of my recent projects, for example, I needed a couple of columns to be case-sensitive, so I set them to Latin1_General_100_CS_AS in SSDT. These clauses didn't go anywhere after several months of development work.
If, for some unknown reason, it is absolutely paramount for you to keep these collate clauses in your code, you may set the project's default collation to something else. This will prevent SSDT from cleaning up the noise. However, you need to be careful with schema comparison and DACPAC deployment. In the former, you have the following options:
- "Compare using target collation" (cleared by default),
- "Ignore column collation" (cleared by default),
- "Verify collation compatibility" (set by default).
(Not sure about the latter, as I never used it.)
However, going to the Schema Compare settings dialog every time you need to compare schemas will soon become too tedious to bear. I would recommend to just agree with SSDT and remove the stuff you don't really need.