Removing Noise From deployment Scripts

ChrisM

We are using Octopus Deploy to deploy to test databases in our CI solution using DLM Automation. When I push static data through the process, the script created seems to be altering foreign key constraints for no good reason.

I have create a very simple mocked up database as follows (abridged) for testing with a couple of tables:

/****** Object: Table [dbo].[Name] Script Date: 03/08/2017 11:03:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Name](
[NameID] [int] IDENTITY(1,1) NOT NULL,
[FirstName] [nvarchar](30) NOT NULL,
[LastName] [nvarchar](50) NOT NULL,
[MiddleInitial] [nchar](1) NULL,
CONSTRAINT [PK_Name] PRIMARY KEY CLUSTERED
(
[NameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

/****** Object: Table [dbo].[Personnel] Script Date: 03/08/2017 11:03:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Personnel](
[PersonnelID] [INT] IDENTITY(1,1) NOT NULL,
[NameID] [INT] NOT NULL,
[AddressID] [INT] NOT NULL,
[PositionID] [INT] NOT NULL,
[Salary] [DECIMAL](27, 2) NOT NULL,
CONSTRAINT [PK_Personnel] PRIMARY KEY CLUSTERED
(
[PersonnelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Personnel] WITH CHECK ADD CONSTRAINT [FK_Personnel_Name] FOREIGN KEY([NameID])
REFERENCES [dbo].[Name] ([NameID])
GO

ALTER TABLE [dbo].[Personnel] CHECK CONSTRAINT [FK_Personnel_Name]
GO

I have added some data and integrated it to the target and all is working nicely. When I update some names on the dbo.Name table, the script that comes out after the CI process runs is as follows:

PRINT(N'Drop constraint FK_Personnel_Name from [dbo].[Personnel]')
ALTER TABLE [dbo].[Personnel] NOCHECK CONSTRAINT [FK_Personnel_Name]

PRINT(N'Update 3 rows in [dbo].[Name]')
UPDATE [dbo].[Name] SET [FirstName]=N'Bugs', [LastName]=N'Bunny', [MiddleInitial]=NULL WHERE [NameID] = 1
UPDATE [dbo].[Name] SET [FirstName]=N'Daffy', [LastName]=N'Duck' WHERE [NameID] = 2
UPDATE [dbo].[Name] SET [FirstName]=N'Wile', [LastName]=N'Coyote', [MiddleInitial]=N'E' WHERE [NameID] = 3
ALTER TABLE [dbo].[Personnel] WITH CHECK CHECK CONSTRAINT [FK_Personnel_Name]

In this very simple example I can see no reason at all why the ALTER TABLE statements are there. The only possible reason I could see for having them is if I were amending key values and I am not doing that.

Having these statements here in a tiny test database is one thing, but in a proper database there can be dozens of these statement which serve no purpose and just clutter the script which makes checking it difficult. When migration scripts are added to the mix, where the same thing seems to happen to an even greater degree as it appears to drop and reapply constraints rather than just use NOCHECK and CHECK statements in a lot of cases.

The question is, is there any way to turn these kind of statements off? I have tried various SQL Compare options in the OD package with no success.

Thanks,
Chris.