Technical: Microsoft – SQL Server – Referential Integrity – Review Query Plan

Technical: Microsoft – SQL Server – Referential Integrity – Review Query Plan

 

Background

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.

Table Design

Here is our table design

TableDesign

DDL Statements

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




 

 

DDL Statements

 

Cleanup remnant data


delete from [dbo].[order];
delete from [dbo].[customer];
go

 

 

Prepare Referenced table

DML



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

 

ScreenShot:

InsertIntoCustomer

 

 

Review Referencing table

DML

insert into [dbo].[order]
(
 [Customer_ID]
 , [dateAdded]
 , [addedBy]
)
select
   1
 , getdate()
 , SYSTEM_USER
union all
select
   2
 , getdate()
 , SYSTEM_USER

 

ScreenShot:

 

InsertIntoDependentTable

 

Explanation:

  • 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

DML

delete from dbo.customer where customer_ID = 2;

 

ScreenShot:

DeleteFromReferencedTable_v2

Explanation:

  • 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

 

 

Conclusion

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.

 

Reference

 

 

 

 

 

 

 

One thought on “Technical: Microsoft – SQL Server – Referential Integrity – Review Query Plan

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s