Stranger Danger... The need for trust with constraints

I ran into an issue with errors with an database upgrade running into a violation of a foreign key constraint. Don’t know how it happened. Figured that while I’m at it, I’d go ahead and evaluate every single check constraint in the database to see if I could identify any other violations, because they shouldn’t be happening.

improve the execution plan by checking the data

In my reading, I found out that checking the constraints can enable the constraint to be marked as trusted. The trusted constraints are then able to be used to build a better query plan execution. I knew that constraints could help the execution, but didn’t know that they could have a trusted or untrusted trait.

Brentozar to the rescue

I’m serious, this guy and his team are awesome. This one single team and their web resources have single handled helped me gain more understanding on SQL server than any other resource. I love how they give back to the community, and their communication always is full of humor and good examples. Kudos! Anyway, commendation aside, the explanation from sp_blitz was fantastic at summarizing the issue.

After this change, you may see improved query performance for tables with trusted keys and constraints. - Blitz Result: Foreign Keys or Check Constraints Not Trusted As the site further mentions, this can cause locks and performance issues, so this validation might be better done off hours. The benefit might be worth it though!

my adaption of the check constraint script

I appreciate the script as a starting point (see link above). I adapted to run this individually on each check constraint and log the errors that occurred. This runs though all FK and CHECK constraints in the database you are in, and then checks the data behind the constraint to ensure it is noted as trusted.

Code

GitHub


/*******************************************************
    Check all constraints in db, FK, and check. 
    Run through each one and log if error occurs


    ------------------------- history ------------------------- 
    2015-08-06          sheldonhull         @ 13:27:21; adapted from the original basic info provided by [brentozar spblitz info](http://www.brentozar.com/blitz/foreign-key-trusted/) Thanks Brent!
*******************************************************/
set nocount on;
set xact_abort on;
declare @PrintOnly bit = 1;


/*******************************************************
    run check on each constraint to evaluate if errors
*******************************************************/
if object_id('tempdb..##CheckMe') is not null
	drop table ##CheckMe;

select
	temp_k =	identity(int, 1, 1)
	,X.*
into ##CheckMe
from
	(select

			type_of_check =											'FK'
			,'[' + s.name + '].[' + o.name + '].[' + i.name + ']'	as keyname
			,CheckMe =												'alter table ' + quotename(s.name) + '.' + quotename(o.name) + ' with check check constraint ' + quotename(i.name)
			,IsError =												convert(bit, null)
			,ErrorMessage =											convert(varchar(max), null)
		from
			sys.foreign_keys i
			inner join sys.objects o
				on i.parent_object_id = o.object_id
			inner join sys.schemas s
				on o.schema_id = s.schema_id
		where
			i.is_not_trusted = 1
			and i.is_not_for_replication = 0
		union all
		select
			type_of_check =											'CHECK'
			,'[' + s.name + '].[' + o.name + '].[' + i.name + ']'	as keyname
			,CheckMe =												'alter table ' + quotename(s.name) + '.' + quotename(o.name) + ' with check check constraint ' + quotename(i.name)
			,IsError =												convert(bit, null)
			,ErrorMessage =											convert(varchar(max), null)
		from
			sys.check_constraints i
			inner join sys.objects o
				on i.parent_object_id = o.object_id
			inner join sys.schemas s
				on o.schema_id = s.schema_id
		where
			i.is_not_trusted = 1
			and i.is_not_for_replication = 0
			and i.is_disabled = 0) as X





/*******************************************************
loop through all databases and execute query
*******************************************************/

declare	@TempK		int
		,@CheckMe	nvarchar(max)

declare curDatabase cursor fast_forward read_only local for select
			temp_k
			,CheckMe
		from
			##CheckMe

open curDatabase

fetch next from curDatabase into @TempK, @CheckMe

while @@fetch_status = 0
begin try

	if @PrintOnly =0
		exec sys.sp_executesql @CheckMe

	if @PrintOnly = 1
		print 'WHATIF: ' + @CheckMe


	update ##CheckMe
		set IsError = 0
	where
		temp_k = @TempK
	fetch next from curDatabase into @TempK, @CheckMe
end try
begin catch

	declare @ErrorMessage nvarchar(max) = isnull(error_procedure()+': ', '') + 'Error Line #: ' + convert(varchar(10), error_line()) + '; Error Message: ' + error_message()
	update ##CheckMe
		set	ErrorMessage	= @ErrorMessage
			,IsError		= 1
	where
		temp_k = @TempK

end catch

close curDatabase
deallocate curDatabase


select	* from ##CheckMe as i
-- where IsError = 1;