Notes on SQL

Having been bitten by the delights of a Foreign Key(FK) with NOCHECK I thought it time to do a little digging.

I’ve always been aware of the perils of this option but have encountered a table with this feature and I was caught out by it.
I’m also aware of the impact it can have on the Optimiser but I’ve never looked into it, so I’m taking this opportunity to look at the impact there too.

What is NOCHECK?

Basically, it allows you to add a constraint onto a table without validating the existing contents of the appropriate column. So, for example, I can create a FK constraint for a column against a reference table even though not all of the values currently held will match against that reference table.

I’m going to create a couple of tables:

CREATE TABLE dbo.Locations( 
	ID		VARCHAR(10), 
	[Description]	VARCHAR(20), 
CONSTRAINT PK_Locations PRIMARY KEY CLUSTERED( 
	[ID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 

GO 


CREATE TABLE dbo.StockItems( 
	ID		INT IDENTITY, 
	[Description]	VARCHAR(50) NOT NULL, 
	Location	VARCHAR(10) NOT NULL 
CONSTRAINT PK_StockItems PRIMARY KEY CLUSTERED( 
	[ID] ASC 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 
) ON [PRIMARY] 

GO 

And then load them with some simple data:

INSERT INTO [dbo].Locations (ID, [Description]) 
VALUES('W1', 'Warehouse 1'), 
      ('W2', 'Warehouse 2'), 
	  ('W3', 'Warehouse 3'), 
	  ('W4', 'Warehouse 4'), 
	  ('OS1', 'Off-Site Storage 1'); 
GO 

INSERT INTO dbo.StockItems( [Description], Location ) 
VALUES  ( 'Blue Widgets', 'W1'), 
		( 'Green Widgets', 'W3'), 
		( 'Red Widgets', 'OS1'), 
		( 'Round Widgets', 'X2'), 
		( 'Square Widgets', 'W2'); 
GO 

If I wanted to create a FK between dbo.StockItems Location and dbo.Locations ID you would expect a problem, because the value ‘X2’ within the StockItems table doesn’t exist in the Locations table:

And you’d be right, as long as I didn’t specify ‘NOCHECK’.
So, with ‘NOCHECK’ instead:

And the Foreign Key has been created.
Although the table ‘StockItems’ still contains an ‘invalid’ entry it is no longer possible to add other invalid values:

Because the constraint now checks that column against the reference table.

Reloading Data

Occasionally it may be necessary to remove the data from a table and re-insert it (altering a table but keeping fragmentation to a minimum is one example) and this is where the NOCHECK can act against you:
Let’s move all of the data out of ‘StockItems’ into a temporary table and truncate the DB table:

SELECT *
INTO ##temp
FROM dbo.StockItems;

SELECT * FROM ##temp;

TRUNCATE TABLE dbo.StockItems;
GO

Now try putting the data back with:

SET IDENTITY_INSERT dbo.StockItems ON;

INSERT INTO [dbo].[StockItems](ID, [Description],[Location])
	SELECT ID, [Description], Location
	FROM ##temp
	ORDER BY ID;

SET IDENTITY_INSERT dbo.StockItems OFF;
GO

Data that was in that table now cannot be re-inserted, unless I remove the FK constraint, run the insert and then add the FK back.

Validating the Data

From version 2008 onwards there is a DBCC command which will provide information about the integrity of a constraint:

This shows what values are currently breaking the constraint and will need dealing with before you can change the constraint to ‘CHECK’.

NOCHECK and the Optimiser

Another, ‘quieter’ issue with using NOCHECK is that the Optimiser ignores Foreign Keys that have that option specified.

Looking at sys.foreign_keys, there is a property ‘is_not_trusted’, and for a FK constraint with NOCHECK this is set to 1. It implies that the data in that column cannot be trusted to be valid, when comparing it against the column that it references.

From https://msdn.microsoft.com/en-us/library/ms190273.aspx
“The query optimizer does not consider constraints that are defined WITH NOCHECK. Such constraints are ignored until they are re-enabled by using ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.”

Removing the invalid data won’t fix this, but removing the invalid data and changing the FK constraint will.

DELETE dbo.stockitems
WHERE [location] = 'X2';
GO

ALTER TABLE [dbo].[StockItems]  WITH CHECK CHECK CONSTRAINT ALL
--Or
ALTER TABLE [dbo].[StockItems] WITH CHECK CHECK CONSTRAINT [FK_Location]

Comparing the Query Plan and Statistics with the ‘before and after’ for ‘is_not_trusted’ :
When ‘is_not_trusted’ = 1:

–(4 row(s) affected)
–Table ‘Locations’. Scan count 0, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
–Table ‘StockItems’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

When ‘is_not_trusted’ = 0:

–(4 row(s) affected)
–Table ‘StockItems’. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So, where the Optimiser can trust a FK constraint it can make improvements to the efficiency of the plan.

NOCHECK does have its place, but the impact to performance and behaviour needs to be understood before using it.