Recently I had a client that wanted to know how they could use SQL Compare to catch actual changes in their code, but not have comments show up as changes. This is fairly easy to do, and this post looks at how this works.
Setting up a Scenario
Let’s say I have two databases that are empty. I’ll name them Compare1 and Compare2. I’ll run this code in Compare1:
CREATE TABLE MyTable
( MyKey INT NOT NULL IDENTITY(1, 1) CONSTRAINT MyTablePk PRIMARY KEY
, MyVal VARCHAR(100));
GO
CREATE PROCEDURE GetMyTable @MyKey INT = NULL
AS
IF @MyKey IS NOT NULL
SELECT
@MyKey AS MyKey, mt.MyVal
FROM dbo.MyTable AS mt
WHERE mt.MyKey = @MyKey;
ELSE
SELECT mt.MyKey, mt.MyVal
FROM dbo.MyTable AS mt;
SELECT 1 AS One;
RETURN;
GO
I’ll run the same code in Compare2 and then run SQL Compare 14 against these two databases. As expected, I find no differences.
I used the default options here, just picking the databases and running the comparison. Let’s now change some code. In Compare2, I’ll adjust the procedure code to look like this:
CREATE OR ALTER PROCEDURE GetMyTable @MyKey INT = NULL
AS
/*
Check for a parameter not passed in. If it is missing, then
get all data.
*/
IF @MyKey IS NOT NULL
SELECT
@MyKey AS MyKey, mt.MyVal
FROM dbo.MyTable AS mt
WHERE mt.MyKey = @MyKey;
ELSE
SELECT mt.MyKey, mt.MyVal
FROM dbo.MyTable AS mt;
SELECT 1 AS One;
RETURN;
GO
I can refresh my project, and now I see there is a difference. This procedure is flagged as having 4 different lines, as you see in the image below.
However, the procedure isn’t different. I’ve just added comments to one of the procs. You might view this as different, in terms of how you run software development, but to the SQL Server engine, these procs are the same. How can I avoid flagging this as a difference and causing a deployment of this code?
Changing Project Options
Redgate has thought of this. In the SQL Compare toolbar, there is an “Edit Project” button.
If I click this, I get the dialog that normally starts SQL Compare, with my project and the databases selected. Notice that there are actually four choices at the top of this dialog, with the rightmost one being “Options”.
If I click this, there are lots of options. I’ve scrolled down a bit, to the Ignore section. In here, you can see my mouse on the “Ignore comments” option.
I’ll click that, click Compare Now, which then refreshes my project. Now I all objects shown as identical. However, if I expand the stored procedure object, I can still see the difference. The difference is just ignored by SQL Compare.
This lets me track the differences, see them, but not have the project flag them for deployment. If I’m using any of the Redgate automation tools, the command line option for this is IgnoreComments, or icm. You can pass this into any of the tools to prevent comments from causing a deployment by themselves.
This also works with inline comments. I’ll alter the procedure in Compare1 with this code:
CREATE OR ALTER PROCEDURE GetMyTable @MyKey INT = NULL
AS
IF @MyKey IS NOT NULL
SELECT
@MyKey AS MyKey, mt.MyVal
FROM dbo.MyTable AS mt
WHERE mt.MyKey = @MyKey; -- parameter value filter
ELSE
SELECT mt.MyKey, mt.MyVal
FROM dbo.MyTable AS mt;
SELECT 1 AS One; -- second result set.
RETURN;
GO
The refreshed project sees the differences, but this is still seen as an identical object for the purposes of deployment.
If you are refactoring code, perhaps by just adding comments or clarifying something, you often may not want a deployment triggered just from changing the notes you leave for other developers. SQL Compare can help here, as can all the Redgate tools.
I would recommend this option always be set, unless you have a good reason to allow comments to trigger a deployment.
Give SQL Compare a try today if you’ve never used it, and if you have it, enable this in your projects.
Voice of the DBA 