SQL Server – SSMS – Query Plan – Compare

Background

One of my favorite go to tool with SQL Server Query troubleshooting is to simple use SQL Server Management Studio (SSMS) to compare the query plans generated via running the same SQL across different machines or SQL Server Versions.\

Use

To use, please follow these simple steps :-

  1. Invoke SSMS
    • Query 1
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Query 2
      • Enable “Include Actual Execution Plan”
      • Run Query
      • Save resultant plan
    • Access Query Plan pane
      • Access either Query’s Plan tab
      • Right click in an empty area of the pane
      • The drop-down menu appears
      • From the drop down menu, please choose “Compare Showplan”
      • From the File Open window,  choose the saved query plan from the opposing query

 

Sample

Sample – 01

Showplan Comparison

Pictorial

queryPlan.compare.showPlanComparison.20190109.0535PM.PNG

Explanation

  1. Certificates
    • Top Query :- Index Seek
    • Bottom Query :- Index Scan

 

Showplan Analysis

queryPlan.compare.showPlanAnalysis.20190109.0534PM.PNG

Explanation

  1. Scenarios
    • Difference :- 15316%
    • Estimated top/left :- 3274
    • Estimated bottom/right :- 504816

 

Requirement

As for client, requires at least SQL Server Management Studio v2017.

Works across various SQL Server Database Engine Versions.

Tested here against Server versions :-

  1. v2005
  2. v2017

 

Transact SQL – Warning – “No Join Predicate”

 

Background

A couple of weeks ago, I noticed a warning in a query plan.

The warning read “No Join Predicate“.

Query Plan

Warning – No Join Predicate

Sample Queries

Conventional Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP_Legacy') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP_Legacy as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP_Legacy]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip]
				 = tblT.[name]
            , [tripDate]
				 = [tblT].tripDate

            , [participant]
				=
				  tblP.firstname
				+ ' '
				+ tblP.lastname

    from
          [school].[tripParticipant] tblTP
        , [school].[trip] tblT
        , cteParticipant tblP

    where tblTP.tripID = tblT.id

    -- and   tblTP.participantID = tblP.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1145A

Explanation
  1. In this example we are using the old classic join
  2. We have three tables, but only joined the first two tables

 

ANSI Join

Code

use [DBLab]
go

if object_id('school.usp_TripParticipants_List_NJP') is null
begin

    exec('create procedure school.usp_TripParticipants_List_NJP as ')

end
go

alter procedure [school].[usp_TripParticipants_List_NJP]
as

begin

    ; with cteParticipant
    as
    (
        select *

        from   [school].[student]

        union 

        select *

        from   [school].[faculty]

    )
    select 

              [trip] = tblT.[name]
            , [tripDate] = [tblT].tripDate
            , tblP.firstname
            , tblP.lastname

    from   [school].[tripParticipant] tblTP

    inner join [school].[trip] tblT

        on tblTP.tripID
            = tblT.id

    inner join cteParticipant tblP
        /*
            on tblTP.participantID
                = tblP.id
        */
        on tblTP.tripID
             = tblT.id

end

go

Query Plan

school__usp_TripParticipants_List_NJ__20180618__1150AM

Explanation
  1. In this example we are using ANSI join
  2. We have three tables and two join clauses
  3. Unfortunately the second join does not actually join the two tables that it is meant to join
    • We intentionally cut and pasted the early join.
    • But, did not modify the pasted clause and reference the actual tables we are trying to join

 

Summary

The samples listed above are simple and easy to correct.

The one I actually ran into is a bit more difficult to address.

 

 

SQL Server – Operator – Top – Top Expression (0)

Background

Earlier today I found myself pressing to make sure I had done right by a query.

 

Query Plan

Query Plan – 01

Here is the original query Plan

Image

queryPlan_computeScaler_HashMatch_20180517_1139AM [clipped]

Explanation

  1. I know that I don’t really want a Hash Match
    • Took care of the Hash Match by reducing the query from two tables to a single table
    • There are a few novel ways to do so, and will cover that later

 

Query Plan – 02

Here is the query Plan once we got rid of the secondary table

Image

queryPlan_computeScaler_HashMatch_20180517_1143AM [clipped]

 

Top ?

I was stuck at the Top Operator for a very long time

Explanation

  1. Rowcount
    • Do I have a set rowcount somewhere
    • Is my environment introducing a constraint for maximum number of records to “touch
    • Is my edition of SQL Server throttling performance
  2. Top
    • Do I have a top N clause somewhere

 

Operator – Top – Default

Overview

Here is what our Top Operators looks like when we do not have “set rowcount” set and we do not have an actual TOP Clause.

Image

Explanation

  1. Actual Number of Rows :- 65
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

 

Operator – Top – “Set rowcount”

Overview

What if we add an actual set rowcount

Image

 

Explanation

  1. Actual Number of Rows :- 2
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (0)

Operator – Top – “Select TOP N”

Overview

Here is what we see when we add a “Top 1” Clause.

Image

Explanation

  1. Actual Number of Rows :- 1
  2. Output List :- sysschobjs.id & sysschobjs.nsid
  3. Top Expression :- (1)

 

Other Things

Overview

I was stuck and so tried other things; such as

  1. Newer version of SQL Server ; v2017 to be exact
  2. Took out the insert into and performed a simple select

Could not reproduce…

 

Craig Freedman ( MSFT )

Finally goggled on the right terms and read what Craig Freedman has to say.

The particular post that I will be quoting is:

ROWCOUNT Top
Link

  1. TOP Operator
    • If you’ve looked at any insert, update, or delete plans, including those used in some of my posts, you’ve probably noticed that nearly all such plans include a top operator.
  2. SET ROWCOUNT
    •  It is a ROWCOUNT top. It is used to implement SET ROWCOUNT functionality.
  3. Why doesn’t SQL Server add a ROWCOUNT top to select statements?
    • SQL Server implements SET ROWCOUNT for select statements by simply counting and returning the correct number of rows from the root of the plan.  Although this strategy might work for a really trivial update plan such as the one above, it would not work for more complex update plans.  For instance, if we add a unique index to our table, the update plan becomes substantially more complex
  4. Placement
    • By placing the ROWCOUNT top above the table scan, the optimizer can ensure that the server updates exactly the correct number of rows regardless of the complexity of the remainder of the plan.

 

Martin Smith

The good thing about blogging and allowing comments is that people can come back and provide helpful feedback.

Here is one from Martin Smith:

Martin Smith
December 29, 2012 at 8:15 am

In 2012 it looks like this operator is only added to plans run under “SET ROWCOUNT” of other than zero. As far as I can discern it is added in to the set_options used as a plan cache key.

SQL Server Versions

Here are the versions of SQL Server where you will be able to reproduce the Top (0) Operator preceding data effecting operators :-

  1. 2005
  2. 2008-R2

Dedication

Thankfully I have a far better grasp courtesy of two able men, Craig Freedman & Martin Smith.

SSMS – Query Plan shows XML ( not graphics )

Background

I have a nice query that is looking at our cached plan for a certain pattern.

Scenario

SQL Server Management Studio ( v2014 )

Here is where we start out…

Grid

And, so I click on the query_plan column and saw what I pasted below…

Query Plan as XML

 

And, yes I am good with XML, but I was hoping for nice intuitive query plan.

 

Remediation

SQL Server Management Studio ( v2017 )

Download and Install SSMS v2017

Please download v2017 from here.

Use it as it is a far better tool.

SQL Server Management Studio ( v2014 )

To continue to use SSMS v2014 please do the following

Outline

  1. Launch Windows Explorer
  2. Access the following folder C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan
    • Make a backup of the showplanxml.xsd file
    • Access the v2017 version from C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan
    • Overwrite the showplanxml.xsd file in C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan with C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan

 

Reference

 

Marketing Version Internal Version Folder File Info
 2017  140  C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\schemas\sqlserver\2004\07\showplan  File Date :- Saturday, ‎August ‎22nd, ‎2017, ‏‎2:51:26 AM

File Size :- 95.1 KB (97,406 bytes)

 2016  130  C:\Program Files (x86)\Microsoft SQL Server\130\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :-  ‎Tuesday, ‎February ‎23, ‎2016, ‏‎9:31:06 AM

File Size :- 85.5 KB (87,650 bytes)

 2014  120  C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Friday, ‎June ‎17, ‎2016, ‏‎7:31:38 PM

File Size :- 85.2 KB (87,333 bytes)

 2012  110  C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Thursday, ‎February ‎20, ‎2014, ‏‎6:32:11 PM

File Size :- 80.3 KB (82,266 bytes)

 2008  100  C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\showplan File Date :- ‎Wednesday, ‎July ‎22, ‎2009, ‏‎1:17:32 AM

File Size :- 70.5 KB (72,272 bytes)

 2005  90
 2000  80

 

Credit

Crediting Aaron Bertrand.

Here is his post

Title :- Getting graphical ShowPlans back in SQL Server 2008 R2
Date Published :- October 28, 2011
Link

 

Backdrop

The reason for the breakage is that each SQL Server Version expands beyond previous ones.

New functionalities are added.

We have new operators, performance lag causation are exposed.

Query plans reflect these changes.

Unfortunately SSMS bundled with previous engines are not aware of this changing landscape; and thus break.

It is not clear whether the change occurs during metadata discovery or doing the actual analysis.

The clear path is to install newer SSMS as they are released or at least one has SQL Server Instances running the equivalent versions.

 

 

XPath Query Tools – XmlToolBox

Background

Lately I have been working more and more with Extended Events and prior to that Cached Plans exploration.

And, has both are stored as XML, it makes sense to see what tools are available for accurately querying XML data.

 

Body

Here is the collected event.


<event name="sql_batch_completed" package="sqlserver" timestamp="2017-05-01T16:44:02.507Z">
  <data name="cpu_time">
    <value>250000</value>
  </data>
  <data name="duration">
    <value>226002957</value>
  </data>
  <data name="physical_reads">
    <value>16349</value>
  </data>
  <data name="logical_reads">
    <value>16197</value>
  </data>
  <data name="writes">
    <value>0</value>
  </data>
  <data name="row_count">
    <value>0</value>
  </data>
  <data name="result">
    <value>2</value>
    <text>Abort</text>
  </data>
  <data name="batch_text">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = 'A' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </data>
  <action name="sql_text" package="sqlserver">
    <value>SELECT "dbo"."basetblb"."base_social" FROM "dbo"."basetblb" WHERE (("base_payroll_status" = 'A' ) AND ("base_service_credit" &gt; 10 ) ) </value>
  </action>
  <action name="session_server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="session_id" package="sqlserver">
    <value>72</value>
  </action>
  <action name="server_principal_name" package="sqlserver">
    <value>tracie</value>
  </action>
  <action name="server_instance_name" package="sqlserver">
    <value>HRDBDEV</value>
  </action>
  <action name="is_system" package="sqlserver">
    <value>false</value>
  </action>
  <action name="database_name" package="sqlserver">
    <value>rbpivr1</value>
  </action>
  <action name="database_id" package="sqlserver">
    <value>10</value>
  </action>
  <action name="client_hostname" package="sqlserver">
    <value>WEBSERVER01</value>
  </action>
  <action name="client_app_name" package="sqlserver">
    <value>Microsoft® Windows® Operating System</value>
  </action>
  <action name="collect_system_time" package="package0">
    <value>2017-05-01T16:44:02.508Z</value>
  </action>
  <action name="attach_activity_id_xfer" package="package0">
    <value>E2EDC810-A11C-4C62-988D-2420F86D8EAD-0</value>
  </action>
  <action name="attach_activity_id" package="package0">
    <value>008E9A10-DF7E-4C85-9C85-95629D3B647F-1</value>
  </action>
</event>

 

Googled

Googled on XML Query XPath and found many tools.

In this post we will discuss XMLToolBox.

 

XMLToolBox

I liked XMLToolBox has unlike many other tools one does not have to start with a query, just paste the the XML body unto the canvas, and point at the XML element or attribute you want to generate the query on.

URL

Here is the Link for XMLToolbox.

Usage

Here is what happens when we :

  1. Copy and paste the XML noted above unto the XML field textarea
  2. Find the result element and click on the text element

Analysis

Here is the generated Xpath:

/event[@name=”sql_batch_completed”]/data[7]/text/text()

 

Interpretation

  1. Parse using event ( /event )
  2. Look for the attribute name matching sql_batch_completed
  3. Transverse to the 7th data element
  4. Find the text element within the the 7th element and return the text portion of that element

 

Summary

XMLToolBox is easy to use and capable.

Unfortunately, it lacks flexibility because it is based on the position of elements.

If the schema changes a bit, the XPath might return the wrong element as it would refer to the original position.

 

SQL Server – Query Plan – Which queries on a Specific Column

Background

As a quick follow up to our last post which looks for a specific table in the Query Plan, let us be a bit more succinct and look for a specific column.

 

Code

Look for all Column References



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go

DECLARE @SchemaName AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128) 

set @SchemaName = 'dbo';
set @TableName = 'ContactEvent';


-- Make sure the name passed is appropriately quoted

if (@SchemaName is not null)
begin
 
    IF (LEFT(@SchemaName, 1) <> '[' AND RIGHT(@SchemaName, 1) <> ']')
    begin
        SET @SchemaName = QUOTENAME(@SchemaName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@SchemaName, 1) <> '['
    begin
        SET @SchemaName = '['+@SchemaName;
    end
    IF RIGHT(@SchemaName, 1) <> ']'
    begin
        SET @SchemaName = @SchemaName + ']';
    end
 
end
 
-- Make sure the name passed is appropriately quoted
if (@TableName is not null)
begin
 
    IF (LEFT(@TableName, 1) <> '[' AND RIGHT(@TableName, 1) <> ']')
    begin
        SET @TableName = QUOTENAME(@TableName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@TableName, 1) <> '['
    begin
        SET @TableName = '['+ @TableName;
    end
    IF RIGHT(@TableName, 1) <> ']'
    begin
        SET @TableName = @TableName + ']';
    end
 
end
 
;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT

          [queryPlan]
			= cast(cast(qp.query_plan as nvarchar(max)) as XML)
        , cp.usecounts
        , [queryObject]
            = quotename(object_schema_name(qp.objectid))
                + '.'
                + quotename(object_name(qp.objectid))
        , [query]
            = st.text
        , DatabaseName
            = o.n.value('@Database', 'sysname')
        , SchemaName
            = o.n.value('@Schema', 'sysname')
        , TableName
            = o.n.value('@Table', 'sysname')
        , IndexName
            = o.n.value('@Index', 'sysname')

FROM sys.dm_exec_cached_plans AS cp 

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st

cross apply qp.query_plan.nodes('//RelOp') as r(n)

cross apply r.n.nodes('*/Object') as o(n)

where qp.dbid = db_id()
and o.n.value('@Schema', 'sysname') = @SchemaName
and o.n.value('@Table', 'sysname') = @TableName

OPTION(MAXDOP 1, RECOMPILE)
;

 

Output:
Result

 

 

Look for Column Reference in specific Where Clauses

In this new example, we went in and looked at specific queries and examined the Query Plan.

Sample Query Plans

SeekPredicates –  SeekPredicate – Prefix – RangeColumns – ColumnReference

QueryPlan

 

Sample Query Plan 2

T2

Sample Query


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go

DECLARE @DatabaseName AS NVARCHAR(128)
DECLARE @SchemaName AS NVARCHAR(128)
DECLARE @TableName AS NVARCHAR(128)
DECLARE @ColumnName AS NVARCHAR(128) 

set @DatabaseName = quoteName(db_name())

set @SchemaName = 'dbo';
set @TableName = 'Contacts';
set @ColumnName = 'memberIdentifier'

set @SchemaName = 'dbo';
set @TableName = 'Contacts';
set @ColumnName = 'active'

-- Make sure the name passed is appropriately quoted


SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
 
DECLARE @databaseName AS NVARCHAR(128)
DECLARE @schemaName AS NVARCHAR(128)
DECLARE @tableName AS NVARCHAR(128)
DECLARE @columnName AS NVARCHAR(128) 
 
set @DatabaseName = quoteName(db_name())

set @SchemaName = 'dbo';
set @TableName = 'Contacts';
set @ColumnName = 'memberIdentifier'
 
-- Make sure the name passed is appropriately quoted

if (@schemaName is not null)
begin
 
    IF (LEFT(@schemaName, 1) <> '[' AND RIGHT(@schemaName, 1) <> ']')
    begin
        SET @schemaName = QUOTENAME(@schemaName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@schemaName, 1) <> '['
    begin
        SET @schemaName = '['+@schemaName;
    end
    IF RIGHT(@schemaName, 1) <> ']'
    begin
        SET @schemaName = @schemaName + ']';
    end
 
end
 
-- Make sure the name passed is appropriately quoted
if (@tableName is not null)
begin
 
    IF (LEFT(@tableName, 1) <> '[' AND RIGHT(@tableName, 1) <> ']')
    begin
        SET @tableName = QUOTENAME(@tableName);
    end
    --Handle the case where the left or right was quoted manually but not the opposite side
    IF LEFT(@tableName, 1) <> '['
    begin
        SET @tableName = '['+@tableName;
    end
    IF RIGHT(@tableName, 1) <> ']'
    begin
        SET @tableName = @tableName + ']';
    end
 
end


 
;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')    
 
SELECT
	top 1
 
          query_plan
            = cast((cast(qp.query_plan as nvarchar(max))) as XML)
 
        , cp.usecounts
 
        , [queryObject]
            = quotename(object_schema_name(qp.objectid))
                + '.'
                + quotename(object_name(qp.objectid))
 
        , [queryText]
            = (st.text)
 
        , DatabaseName
            = (r.node.value('@Database', 'sysname'))
 
        , SchemaName
            = (r.node.value('@Schema', 'sysname'))
 
        , TableName
            = (r.node.value('@Table', 'sysname'))
 
        , ColumnName
            = (r.node.value('@Column', 'sysname'))
 
        , ColumnName2
            = (r2.node2.value('@Column', 'sysname'))
 
         , xmlFragment
            = cast((cast(node.query('.') as nvarchar(max)) ) as xml)
 
         , xmlFragmentParent
            = cast((cast(node.query('../..') as nvarchar(max))) as xml)
 
         , xmlFragmentGrandParent
            = cast((cast(node.query('../../..') as nvarchar(max))) as xml)
 
FROM sys.dm_exec_cached_plans AS cp 
 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp 
 
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
 
--outer apply qp.query_plan.nodes('//RelOp/IndexScan/SeekPredicates/SeekPredicateNew/SeekKeys/Prefix/RangeColumns/ColumnReference') as r(node)
outer apply qp.query_plan.nodes('//SeekPredicates//RangeColumns/ColumnReference') as r(node)
 
outer apply qp.query_plan.nodes('//Predicate//Identifier/ColumnReference') as r2(node2)
 
--where qp.dbid = db_id()
where 1=1
 
and
    (
 
        (
 
                ( r.node.value('@Schema', 'sysname') = @SchemaName )
            and ( r.node.value('@Table', 'sysname') = @TableName )
            and ( r.node.value('@Column', 'sysname') = @ColumnName )
 
        )       
 
        or
        (
 
                ( r2.node2.value('@Schema', 'sysname') = @SchemaName )
            and ( r2.node2.value('@Table', 'sysname') = @TableName )
            and ( r2.node2.value('@Column', 'sysname') = @ColumnName )
        )       
 
    )
 


/* 
group by
          cast(qp.query_plan as nvarchar(max))
        , cp.usecounts
        , quotename(object_schema_name(qp.objectid))
                + '.'
                + quotename(object_name(qp.objectid))
*/
 
OPTION
(
	  MAXDOP 1
	, RECOMPILE
)
;

Explanation

  1. We used the Query.Plan exist to dig into the plan and latched on to filtering elements
    • The specific elements we are using are SeekPredicates and Predicate
    • Once we have the ColumnReference node we matched on the specific Schema\Table\Column we are looking for

 

Summary

I was really stuck for a couple of days trying to figure out how to use Wildcard in XPath.

And, so googled until I felt faint and got on last Trains, past when  the last Buses ran.

Finally Saturday morning, no Work day, found that I needed two forward slashes to get deep descendants.

Courtesy of MSFT – XPATH Examples 

 

Expression Refers
bookstore//title All <title> elements one or more levels deep in the <bookstore> element (arbitrary descendants). Note that this is different from the expression in the next row.
bookstore/*/title
All <title> elements that are grandchildren of <bookstore> elements.

 

References

  1. XPath Examples
    Link
  2. Brad Vander Zanden, Knoxville | The University of Tennessee, Knoxville
    Link

Dedicated

Dedicated to the Mikael Eriksson’s of the World!
MikaelEriksson

With stats like Top 0.15 and 6 million people reached, you are HERO!

 

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