User Defined Functions, UDF, Scalar and Constraints

Some of you probably already know this, but UserDefined functions acting as a constraint aren't as intuitive as you might imagine. For example, I created a function that checks two values on a data row and returns the count. Simple enough. Sure. Unit Test It against different records with different values. Works everytime. Now put it in as a constraint and it acts a bit differently. For example, given the following scalar UDF:

CREATE function IsItemNameWithFlagUnique(@name varchar(50))
returns int
as
begin
     -- check for existence
     return(select count(*) from [dbo].[CatalogItemInfo] where upper(Name) =upper(@name) and someFlag=1)
end

Now setup a constraint(this is the constraint code). I want a constraint that will only allow 1 record in the table with that name and a flag value of 1. So if the count returns a 0...all is well....right?

([dbo].[IsNameWithFlagUnique]([Name]) = 0)

Not so fast, SQLServer actually performs the insert first and then rolls it back if the constraint fails. This means that it will return a count of 1 the first time you insert a record. To quote the Brits....bloody hell. This means your constraint code should look like this:

([dbo].[IsNameWithFlagUnique]([Name]) <= 1)

-Mathew Nolton