Alter a scalar UDF that is used in a check constraint in SQL Server 2012

Question

I have a check constraint on a column that uses a scalar UDF. I have a requirement where I need to make changes to the UDF. Based on what I know, I need to drop the constraint, make changes to the UDF, and then re-add the constraint. During the period when the constraint is removed, I am concerned that bad data might be inserted into the table. Is there a way to alter the UDF without dropping the constraint? If not, is there a way to prevent bad data from entering the table?

Answer 1

you shouldn't

When you put a scalar UDF in a computed column or check constraint, you run into two problems:

• Queries that touch the table won't be able to generate a parallel query plan (more detail here)
• If they're not persisted or indexed, they will execute once per row needed to process the query

Where possible:

• Use plain T-SQL outside of a function to do the calculation (possible if all the columns are in a single table)
• Write a trigger to catch rows that shouldn't make it into the table
• Use a different kind of constraint (foreign key, etc.) to catch rows that shouldn't exist in the table

scripted

/*A table*/
CREATE TABLE
    dbo.bad_idea
(
    id int PRIMARY KEY IDENTITY,
    some_date date
);
GO 

/*A silly function*/
CREATE FUNCTION
    dbo.please_do_not
(
    @some_date date
)
RETURNS bit
AS
BEGIN
      RETURN 
      (
          CASE 
              WHEN @some_date < '19000101' 
              THEN 0 
              ELSE 1 
          END
      );
END;
GO 

/*A bad idea for a check constraint*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no 
CHECK 
(
    dbo.please_do_not(some_date) = 1
);
GO 

/*An updated silly function*/
CREATE FUNCTION
    dbo.please_i_beg_you_do_not
(
    @some_date date
)
RETURNS bit
AS
BEGIN
      RETURN 
      (
          CASE 
              WHEN @some_date < '19800101' 
              THEN 0 
              ELSE 1 
          END
      );
END;
GO 

/*Another bad idea*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no 
CHECK 
(
    dbo.please_do_not(some_date) = 1
);
GO 

/*An equally bad idea*/
ALTER TABLE
    dbo.bad_idea
WITH CHECK
ADD CONSTRAINT  
    god_no_please
CHECK 
(
    dbo.please_i_beg_you_do_not(some_date) = 1
);
GO 

/*Drop the old one off*/
ALTER TABLE
    dbo.bad_idea
DROP CONSTRAINT
    god_no;

Answer 2

No, unfortunately you can't alter a function used by a check constraint, even when disabled with NOCHECK.

You can workaround this (with no risk of bad data) by:-

1. creating a second function with the new definition
2. creating a second check constraint using the new function
3. dropping the original check constraint
4. dropping the first function

Alternatively, if you need to retain the names of the check constraint and function, you can do this by:-

1. creating a second function with the new definition
2. creating a second check constraint using the new function
3. dropping the original check constraint
4. altering the first function with the new definition
5. recreating the original check constraint
6. dropping the second check constraint
7. dropping the second function

Answer 3

You can do the whole thing inside a transaction, which will force a Sch-M schema-modification lock on the table, preventing any data being inserted.

For example:

BEGIN TRAN;

ALTER TABLE YourTable
DROP CONSTRAINT YourConstraint;

GO

CREATE OR ALTER FUNCTION dbo.YourFunction.....

/*
etc
*/

GO

ALTER TABLE YourTable
ADD CONSTRAINT YourConstraint CHECK (dbo.YourFunction(YourColumn) = 1);

COMMIT;

I agree that functions in check constraints are a bad idea and should be avoided if possible.

A common reason to need such a function is to create a multi-table constraint. However this can be better done using a little-known trick using indexed views. A somewhat less efficient method is a trigger.