unexpected differences from Schema Compare in Visual Studio 2017

Question

I have this weird behavior of Schema Compare in Visual Studio 2017.

1. When comparing 2 databases, two identical tables I get difference that one of those has no PK name. If I would script out tables using SSMS I would see that indeed on one of the servers table script seems not to have PK name, but if I would query sys.indexes table I can find that this index has a same name on both of the servers.

   One possible solution I found is to update the table, let's say add some random index and remove it, so I am guessing that it has something to do with how SQL Server stores the objects definition. But it would be nice to know what is causing this and how to solve it in the right way and not with some kind of workarounds.

   

   p.s. I know that the PK name is not "correct".

2. Similar situation when comparing two identical SPs. I tried to run sp_refreshsqlmodule and sp_recompile but it didn't help. Updating SP helps, but again this is not very nice workaround.

   

EDIT 1: In the first example PK with the same name exists on both databases (even though the name is not following standards). The following query ran on both databases returns identical result:

SELECT I.name 
FROM sys.indexes AS I
INNER JOIN sys.objects AS O
    ON O.object_id = I.object_id
INNER JOIN sys.schemas AS S
    ON S.schema_id = O.schema_id
WHERE S.name = 'MySchema'
AND O.name = 'MyName'

Result: PK__TraceDat__AAAC09D801ED28A5

Answer 1

Both things you're noting are due to bad practices on the scripts, and are concrete issues that could cause issues down the line. While right now there may be no immediate issues, there can be on the future.

In the first case, you've pointed out the root problem, there is no name for the PK in the left side, while there is some name on the right. Actually, all constraints always have a name, you can indicate it explicitly and it will be the name. But if the creation script doesn't indicates a name (like your left side) the server will create a random name for the constraint (that's what you're seeing on the right side). This is a problem because different servers will have different names for the constraint (if there are multiple instances of the DB) and you won't have a concrete name to refer to the constraint if you later want to remove or change it. The real solution is simple, just give a name to the constraint yourself, so it have a fixed reference.

The second case is even worse. The only difference is that the left side has a procedure without a schema, while the right side it's explicitly defined as dbo. Every object within a database lives within a schema, and when one isn't explicitly indicated, the default schema of the user is used to determine it. There are a variety of reasons why that is a bad practice (for one, in a multischema DB there is a chance of ambiguity or choosing the wrong table, performance is also negatively affected), so the solution is to simply name the proper schema when creating or modifying tables (querying is also important too, although unrelated here).

Answer 2

I sometimes have similar issues using Apex sql diff after renaming objects. Try drop/create instead. It is potential reason of problem.