I had a customer recently ask about a change in one of their constraints on production, where a new option appeared when they went to deploy some changes from QA. They asked how this could happen, and I’ll show how in this post.
Suppose I create a table like this in a development environment.
CREATE TABLE [dbo].[Logger](
[LogID] [INT] NOT NULL CONSTRAINT LoggerPK PRIMARY KEY,
[LogDate] [DATETIME] NULL,
[LogMsg] [VARCHAR](2000) NULL
)
GO
I (hopefully) have a process to get this to production (version control, automation, etc.). Once in production, if I were to script this on SQL Server 2022, I’d get this from SMO.
CREATE TABLE [dbo].[Logger](
[LogID] [INT] NOT NULL,
[LogDate] [DATETIME] NULL,
[LogMsg] [VARCHAR](2000) NULL,
CONSTRAINT [LoggerPK] PRIMARY KEY CLUSTERED
(
[LogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
This looks different, but really this includes the defaults that existed in dev, and also in production. Hopefully all my SETtings and configuration is the same, to ensure no surprises.
Now, let’s imagine a DBA has some index maintenance, perhaps Ola’s scripts or some other script that works through all tables and indexes. If a DBA decides they’d like to edit the script to change a setting, they might end up running this code for my Logger table:
ALTER INDEX ALL ON dbo.logger
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);
There’s a small change in here from the defaults, which I’d see if I were to run a SQL Compare comparison. Now I’d see this type of change, which might not be a problem, but it might be an issue where each deployment wants to reset this setting.
If you don’t think this is a big deal, here’s the deployment code:
I would not want this going through my deployments. And it might if our team were no diligent in looking at the deployment script.
Be explicit with defaults, and be careful about making changes in production. You might end up creating problems in your update process if you don’t feed these changes back to development.
Voice of the DBA 