Technical: Microsoft – SQL Server – Referential Integrity – Review Query Plan
Last week while talking to someone, we touched on SQL Server Performance. Mostly how is this done, why will one use one technique over the other, etc.
One of the many areas we covered in how is Referential Integrity enforced.
In this posting, I will try to firm up my understanding by working on a straight forward two table example.
Here is our table design
Here is our DDL Statement
set noexec off go use [DBLab] go --drop TABLE [dbo].[order] --drop table dbo.Customer if object_id('dbo.customer') is not null begin set noexec on end go CREATE TABLE [dbo].[customer] ( [Customer_ID] [bigint] NOT NULL, [CustomerName] [nvarchar](255) NOT NULL, [inceptionDate] [datetime] NOT NULL, [expiryDate] [datetime] NULL, [active] [bit] NOT NULL default 1, [dateAdded] [datetime] NOT NULL default getdate(), [addedBy] [sysname] NOT NULL default SYSTEM_USER, CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ( [Customer_ID] ASC ) ) go set noexec off go if object_id('dbo.order') is not null begin set noexec on end go CREATE TABLE [dbo].[order] ( [Order_ID] [bigint] not null identity(1,1), [Customer_ID] [bigint] NOT NULL, [dateAdded] [datetime] NOT NULL default getdate(), [addedBy] [sysname] NOT NULL default SYSTEM_USER, , CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED ( [Order_ID] ASC ) , CONSTRAINT [FK_Customer] FOREIGN KEY ( [Customer_ID] ) references [dbo].[customer] ( [Customer_ID] ) ) go set noexec off go
Cleanup remnant data
delete from [dbo].[order]; delete from [dbo].[customer]; go
Prepare Referenced table
insert into [dbo].[customer] ( [Customer_ID] , [CustomerName] , [inceptionDate] , [expiryDate] , [active] , [dateAdded] , [addedBy] ) values ( 1 , 'Kirkland' , getdate() , null , 1 , getdate() , SYSTEM_USER ) go insert into [dbo].[customer] ( [Customer_ID] , [CustomerName] , [inceptionDate] , [expiryDate] , [active] , [dateAdded] , [addedBy] ) values ( 2 , 'Kirkland dup data' , getdate() , null , 1 , getdate() , SYSTEM_USER ) go
Review Referencing table
insert into [dbo].[order] ( [Customer_ID] , [dateAdded] , [addedBy] ) select 1 , getdate() , SYSTEM_USER union all select 2 , getdate() , SYSTEM_USER
- As we have a clustered index on the referencing table, the clustered index insert happens
- The system then initiates a Nested-Loops — Left semi join against our referenced table looking to see whether we have existing “parent” records
- Left semi join is a system join and it can not not be explicitly requested by User SQL. It basically means look for your first match, once you find it move along
- The gate-keeper is our Assert Statement which in this case says that flag occurrences where matching records are not found in the Referenced Table
Review Referenced table
delete from dbo.customer where customer_ID = 2;
- The Delete is represented as a Clustered index delete
- This time the system initiates a Nested-Loops — Left semi join against our referencing table
- It tries to find records in our Referencing tables that have matching values to the records we are deleting
- If found, the Assert statement raises an exception
SQL Server Engine is quite optimistic in nature, all of the work is conducted and recorded in the Transaction Log.
Again, I will go out on a limb here and say this:
If problem happens, the records are marked disposable in the Transaction Log and not carried over to the actual database files.
How do I know all this without following our transactions in the Transaction Log…
To prove/disprove is for another posting.
- A Join a Day – Left semi join