When I started working with SQL Server and Windows, it seemed that the administrative side of many actions was limited. We could add and drop many items, but that was it. Relatively few tools allowed setting limits or disabling them, which was a pain. After all, I'd gotten used to setting up accounts for vendors and contractors in Active Directory, often disabling them when they weren't in use. I couldn't do this in SQL Server for many objects, which was a pain and an administrative burden to reset them up when troubleshooting issues. This was pre-PowerShell and .NET when any SMO access was a project in and of itself.

These days SQL Server has done a great job of adding in the ability to pause or disable many objects. We've had the ability to lock out an account for many versions, which is a great way to setup a vendor tech support account when it's needed. This is especially important for security these days, as we may want to be sure that we prevent access by any suspect accounts. Disabling them allows us to prevent their use, but keep all their rights and permissions in the even they are valid accounts.

We can disable indexes, which can be useful as a precursor to deleting them at some point. We can disable triggers, which is incredibly useful when you are testing or debugging actions on a table. We can disable Extended Events, audits, and more. All of these are useful actions for a developer or administrator, if you use them.

When things go wrong, we're often stressed and pressed for time. If there are issues with a system, many of us make snap decisions, which might fix the problem or make the situation worse. Even in those cases where we fix an issue, deleting or dropping objects might cause is extra work later. My question for you is what's your first reaction? When you need to make a quick change to remove something, security access, an index, etc., do you drop or disable?

I would hope that you disable, as this removes the effects but keeps the object in the system with associated meta data. Rebuilding permissions or trying to get the old trigger code is a pain (since few people use a VCS, please start doing this). It's possible that you won't even be able to get things reset back up in the same manner. That might be fine, but it's not ideal as new code should be tested, and in a crisis, it likely won't be looked at too closely. At least the previous version of the code was tested in production.

Build the habit to disable, not drop, and I think you'll be glad you did.

Join the debate, and respond to today's editorial on the forums

Rules are added to alias data types or columns with sp_bindrule. the rule name and the column should be qualified in the statement.

NOTE: Rules are deprecated. Check constraints should be used instead.

Ref: CREATE RULE - click here

» Discuss this question and answer on the forums