SQL Server Data Tools (SSDT), the component of Visual Studio, has a couple of comparison tools for schema and data comparison. They have some default behavor on saving comparison and choosing a table key. It’s quite obvious how they work but I wanted to test some features.
1. In schema comparison I can uncheck some database objects to exclude them from applying to target database. Then I can save the comparison to file. What will be when I would open the saved comparison whereas the source or target schema has changed?
The question is about whether SSDT saves all database objects names in file or not. The answer is SSDT saves only check/uncheck marks. If you schema is changed, comparison will show you the exact changes, and unchecked items will remain unchecked.
2. Data comparison needs a primary key to see the differences between each table row. It’s reasonable. If you don’t have a primary key, this table won’t be shown for comparison. What else can be used for comparison? Unique key?
In fact, SSDT needs keys in both tables. It can use primary key or unique key, but not the combination of primary key in one table + unique key in another. If you have primary key and unique key on the same table, SSDT will choose primary key. That’s reasonable too, because primary key does not allow null values, it’s a bit more strict than unique key.