The impact of CHECK constraint on query plans

Experimenting with CHECK constraint impact.txt

Refer to other files.

z0_Setup.sql

/*******************************************************************************
SETUP:
Run this script within SQL Server Management Studio (SSMS) to drop and
re-create the two tables we're working with.

This script will insert 500 rows into each table. The execution plan viewer
will show proportional costs of running the query, so the number of rows will
not impact these query cost calculations.

NOTE: Script written for SQL Server 2016.
*******************************************************************************/
set nocount on

drop security policy if exists SecurityFilter_CheckConstrainedTable
drop security policy if exists SecurityFilter_UnconstrainedTable
drop function if exists dbo.TenantIdPredicate

drop table if exists TableWithConstrainedTenantId
create table TableWithConstrainedTenantId (
	RowNumber int,
	TenantId int not null,
	primary key (RowNumber),
	check(TenantId = 1)
)

drop table if exists TableWithUnconstrainedTenantId
create table TableWithUnconstrainedTenantId (
	RowNumber int,
	TenantId int not null,
	primary key (RowNumber)
)

-- Or if you don't have access to the sys tables use an in-line
-- Tally table known as a "Ben-Gan" style Tally
-- Original found here: http://www.sqlservercentral.com/blogs/dwainsql/2014/03/27/tally-tables-in-t-sql/
;WITH lv0 AS (SELECT 0 g UNION ALL SELECT 0)
    ,lv1 AS (SELECT 0 g FROM lv0 a CROSS JOIN lv0 b) -- 4
    ,lv2 AS (SELECT 0 g FROM lv1 a CROSS JOIN lv1 b) -- 16
    ,lv3 AS (SELECT 0 g FROM lv2 a CROSS JOIN lv2 b) -- 256
    ,lv4 AS (SELECT 0 g FROM lv3 a CROSS JOIN lv3 b) -- 65,536
    ,lv5 AS (SELECT 0 g FROM lv4 a CROSS JOIN lv4 b) -- 4,294,967,296
    ,Tally (Number) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 FROM lv5)

-- Use the CTE to insert 500 rows into the first table.
insert into TableWithConstrainedTenantId (RowNumber, TenantId)
select top 500
	Number,
	TenantId = 1
from Tally

-- And then use the first table to insert the same number of rows into the
-- second table.
insert into TableWithUnconstrainedTenantId (RowNumber, TenantId)
select RowNumber, 1
from TableWithConstrainedTenantId


/*

select * from TableWithConstrainedTenantId
select * from TableWithUnconstrainedTenantId

*/

z1_CheckConstraintImpactOnQueryPlan.sql

/*******************************************************************************
NOTE:
Be sure to check the 'Messages' tab in SQL Server Management Studio
(SSMS) to view the query timings.

You can also enable thew 'Include Actual Execution Plan' feature within
SSMS to confirm the relative cost of each SELECT query in the batch.
*******************************************************************************/

set statistics time off
set nocount on
set statistics time on

print '
/*
--------------------------------------------------------------------------------
Query 1:
	Selecting records with TenantId = 1.
	This table has a CHECK constraint that enforces TenantId = 1.

	The query utilises the a "Clustered Index Scan" to return all rows.

	This query costs 33% of the batch.
--------------------------------------------------------------------------------
*/
'
select * from TableWithConstrainedTenantId where TenantId = 1

print '
/*
--------------------------------------------------------------------------------
Query 2:
	Selecting records with TenantId <> 1.
	This table has a CHECK constraint that enforces TenantId = 1.

	Because the WHERE clause in this query would only return rows that would
	violate this CHECK constraint, the query can be performed almost instantly
	using a "Constant Scan"

	This query costs 0% (zero percent) of the batch.
--------------------------------------------------------------------------------
*/
'
select * from TableWithConstrainedTenantId where TenantId <> 1

print '
/*
--------------------------------------------------------------------------------
Query 3:
	Selecting records with TenantId = 1.
	This table does NOT have a CHECK constraint that enforces TenantId = 1.

	The query utilises the a "Clustered Index Scan" to return all rows.

	This query costs 33% of the batch.
--------------------------------------------------------------------------------
*/
'
select * from TableWithUnconstrainedTenantId where TenantId = 1

print '
/*
--------------------------------------------------------------------------------
Query 4:
	Selecting records with TenantId <> 1.
	This table does NOT have a CHECK constraint that enforces TenantId = 1.

	The query utilises the a "Clustered Index Scan" to attempt to return all
	rows where TenantId <> 1.

	This query costs 33% of the batch, despite returning zero results.
	This is because the query planner cannot deduce that there are no rows in
	the table where TenantId <> 1 through the existence of a CHECK constraint
	as is the case with the other table.
--------------------------------------------------------------------------------
*/
'
select * from TableWithUnconstrainedTenantId where TenantId <> 1

set statistics time off

z2_CheckConstraintImpactOnRowLevelSecurity.sql

/*******************************************************************************
OVERVIEW:

I'm disappointed with this one. Having the CHECK index is actually imposes
a higher cost for these queries than not having the CHECK index.
This occurs even when there's an explicit WHERE clause in the query, such that
the CHECK constraint should allow the query planner to deduce that there will
be zero rows that require filtering.
*******************************************************************************/

set nocount on
go

drop security policy if exists SecurityFilter_CheckConstrainedTable
drop security policy if exists SecurityFilter_UnconstrainedTable
drop function if exists dbo.TenantIdPredicate
go

create function dbo.TenantIdPredicate(@TenantId int)
returns table
with schemabinding
as
return select 1 as result where @TenantId = CAST(SESSION_CONTEXT(N'user_id') AS int)

go

create security policy SecurityFilter_CheckConstrainedTable
add filter predicate dbo.TenantIdPredicate(TenantId)
on dbo.TableWithConstrainedTenantId
with (state = on)

go

create security policy SecurityFilter_UnconstrainedTable
add filter predicate dbo.TenantIdPredicate(TenantId)
	on dbo.TableWithUnconstrainedTenantId,
add block predicate dbo.TenantIdPredicate(TenantId)
	on dbo.TableWithUnconstrainedTenantId after insert
with (state = on)

go

-- Set the current 'user_id' (which is used by the security predicate).
exec sp_set_session_context @key = 'user_id', @value = 1, @readonly = 0

select
	CAST(SESSION_CONTEXT(N'user_id') AS int) as CurrentUserId,
	RowNumber,
	TenantId
from TableWithConstrainedTenantId

select
	CAST(SESSION_CONTEXT(N'user_id') AS int) as CurrentUserId,
	RowNumber,
	TenantId
from TableWithUnconstrainedTenantId