Technical: Microsoft – SQL Server – Inserts\Updates and Foreign Keys

Technical: Microsoft – SQL Server – Inserts\Updates and Foreign Keys

 

Introduction

Reviewing expensive queries and wanted to talk about a relatively simple insert statement.

Areas we will touch:

  • Create Table
  • Populate Lookup/Support table
  • Analysis
  • Conclusion

Create Tables


set nocount on
go

use tempdb
go

if OBJECT_ID('dbo.[order]') is not null
begin
   drop table dbo.[order]
end

if OBJECT_ID('dbo.[orderFK]') is not null
begin
  drop table dbo.[orderFK]
end

if OBJECT_ID('dbo.[orderFKMultiple]') is not null
begin
   drop table dbo.[orderFKMultiple]
end

if OBJECT_ID('dbo.[color]') is not null
begin
   drop table dbo.[color]
end

if OBJECT_ID('dbo.[item]') is not null
begin
    drop table dbo.[item]
end

go

create table dbo.[item]
(
   [itemID] int not null constraint PK_Item primary key
)

create table dbo.[color]
(
   [color] sysname not null  constraint PK_Color  primary key
)

create table dbo.[order]
(
    [orderID] bigint not null identity(1,1)
  , [orderDate] datetime not null constraint DefaultOrderDate2 default getutcdate()
  , [itemID] int not null
)

create table dbo.[orderFK]
(
    [orderID] bigint not null identity(1,1)
  , [orderDate] datetime not null constraint DefaultOrderFKDate default getutcdate()
  , [itemID] int not null
)

ALTER TABLE [dbo].[orderFK]  
WITH CHECK ADD CONSTRAINT [fk_Item_ItemID] 
FOREIGN KEY([ItemID])
REFERENCES [dbo].[Item] 
([ItemID])
GO

ALTER TABLE [dbo].[orderFK] CHECK CONSTRAINT [fk_Item_ItemID]
GO

create table dbo.[orderFKMultiple]
(
    [orderID] bigint not null identity(1,1)
  , [orderDate] datetime not null 
       constraint DefaultOrderFKMultipleDate default getutcdate()

  , [itemID] int not null
  , [color]  sysname null
)

ALTER TABLE [dbo].[orderFKMultiple]  
WITH CHECK 
	ADD CONSTRAINT [fkMultiple_Item_ItemID] 
		FOREIGN KEY([ItemID])
	REFERENCES [dbo].[Item] 
	([ItemID])
GO

ALTER TABLE [dbo].[orderFKMultiple] 
	CHECK CONSTRAINT [fkMultiple_Item_ItemID]
GO

ALTER TABLE [dbo].[orderFKMultiple]  
WITH CHECK 
	ADD CONSTRAINT [fkMultiple_Item_Color] 
	FOREIGN KEY([color])
	REFERENCES [dbo].[color] 
	([color])
GO

ALTER TABLE [dbo].[orderFKMultiple] 
	CHECK CONSTRAINT [fkMultiple_Item_Color]
GO

Populate Support / Lookup tables



set nocount on;

declare @id int
declare @idMax int

set @id =1
set @idMax = 100

while (@id <= @idMax)
begin

	insert into dbo.[item]([itemID])
        values (@id)

	set @id = @id +1

end

insert into dbo.[color] values ('blue');
insert into dbo.[color] values ('red');
insert into dbo.[color] values ('black');
insert into dbo.[color] values ('brown');
insert into dbo.[color] values ('white');

Insert data into main tables



set nocount on;
set statistics io on;
go

use tempdb
go

begin tran

	insert into dbo.[order]
	(
		[itemID] 
	)
	values
	(
	   1
	)   

commit tran

begin tran

	insert into dbo.[orderFK]
	(
		[itemID] 
	)
	values
	(
	   1
	)   

commit tran

begin tran

	insert into dbo.[orderFKMultiple]
	(
		[itemID] 
	)
	values
	(
	   1
	)   

commit tran

begin tran

	insert into dbo.[orderFKMultiple]
	(
		[itemID], [color]
	)
	values
	(
	   2, 'blue'
	)   

commit tran

Execution Plan

Here is the Execution Plan

ForeignKeys - ExecutionPlan

Statistic IO

ForeignKeys - Statistics IO

Analysis

Analysis – Query Plan

  • Once a constraint is added, you will introduce an “Assert” block
  • For each Foreign Constraint key where the column is actually populated, you will pay the price of an index/scan to make sure that the value being added is in “FK” table
  • A “Nested Loop” block is also added for each FK relationship

Analysis – Statistics I/O

For Statistics I/O tracking, FKs are a bit pricey as the system has to expend IO and check each FK table.

The price of each check can be more than the actual IO for our actual table.

Conclusion

Foreign Key introduces complexity to your Query Plan and locks have to be acquired to make sure that things to do not change in your Foreign key tables during inserts and updates.

MS SQL Server Query Engine has an optimization track that “NOPS” FK columns that are not referred to in your Insert\Update statement.

Think about each Foreign Key.  And, if you need it make sure you have a corresponding index on the Referenced table.

Remember, NoSQL and MS SQL Server (In Memory – OLTP / Hekaton), do not support foreign key.

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