Maddie helps Wildcats to a 10-0 shutout!!  

Posted by ReelTym

The "mercy rule" kicked in after 5 innings tonight. Maddie hit a double early on and triple in the top of the 5th, but the big news was the 10-0 shut-out she pitched. She had them chasing balls all around the strike zone and finding nothing but air. Lots of great defensive plays by the whole team: squeeze plays, catcher throwing clear to 2nd base to pick-off runners trying to steal and many others. It was definitely their best game of the year.



Way to go Wildcats!! Way to go Maddie!!

An Introduction to SQL Server Query Tuning  

Posted by ReelTym

A little dated but still approach.

By : Randy Dyess
Oct 15, 2004

During a recent interview to find a new job before my contract ran out, I was asked a question that took me by surprise. The interviewer simply asked me what steps I took to decide which stored procedures needed optimizing and what steps I used to optimize those queries. What took me by surprise was not the question; it was the realization that I did not have a "formal" plan of attack to determine stored procedures that needed to be optimized or a plan to actually optimize them. After the interview, I decided to formalize the basic steps I used to find stored procedures to optimize, and the steps I took whenever I decided a query needed to be optimized. After jotting down some notes, I decided that I would turn this "plan" into an article so others would know what basic steps are needed to optimize a query.

Free Tools for the SQL Server DBA  

Posted by ReelTym

By David Bird, 2007/05/24

Brent Ozar Blog  

Posted by ReelTym

Change Data Capture - Microsoft SQL Server 2008  

Posted by ReelTym

By Muthusamy Anantha Kumar aka The MAK

One of the new features in Microsoft SQL Server 2008 is the ability to track changes on a table. You can enable change tracking on a table using the Change Data Capture feature.

Generating XML Schema as part of query  

Posted by ReelTym


create table complex
(
id bigint identity(1,1) primary key clustered,
sysnamecol sysname not null,
datetimecol datetime null,
bigintcol bigint null,
intcol int null,
smallintcol smallint null,
tinyintcol tinyint null,
bitcol bit default(0),
nvarcharcol nvarchar(max) null,
varcharcol varchar(max) null,
ncharcol nchar(255) null,
charcol char(255) null,
varbinarycol varbinary(max) null,
moneycol money null,
decimalcol decimal(13,9) null,
floatcol float null
)

insert into complex ( sysnamecol,datetimecol,bigintcol,intcol,smallintcol,tinyintcol,bitcol,nvarcharcol,varcharcol,ncharcol,charcol,varbinarycol,moneycol,decimalcol,floatcol)
select 'sysname column',GETDATE(),100000000,1000000,1000,100,1,'nvarchar column','varchar column','nchar column','char column',convert(varbinary(max),'varbinary column'),123.45,67.890,123456.7890123

select *
from complex
FOR XML AUTO, BINARY BASE64, ELEMENTS, XMLSCHEMA

/*
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:schema="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="complex">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="id" type="sqltypes:bigint" />
<xsd:element name="sysnamecol">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="128" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="datetimecol" type="sqltypes:datetime" minOccurs="0" />
<xsd:element name="bigintcol" type="sqltypes:bigint" minOccurs="0" />
<xsd:element name="intcol" type="sqltypes:int" minOccurs="0" />
<xsd:element name="smallintcol" type="sqltypes:smallint" minOccurs="0" />
<xsd:element name="tinyintcol" type="sqltypes:tinyint" minOccurs="0" />
<xsd:element name="bitcol" type="sqltypes:bit" minOccurs="0" />
<xsd:element name="nvarcharcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52" />
</xsd:simpleType>
</xsd:element>
<xsd:element name="varcharcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:varchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52" />
</xsd:simpleType>
</xsd:element>
<xsd:element name="ncharcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="charcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:char" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52">
<xsd:maxLength value="255" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="varbinarycol" type="sqltypes:varbinary" minOccurs="0" />
<xsd:element name="moneycol" type="sqltypes:money" minOccurs="0" />
<xsd:element name="decimalcol" minOccurs="0">
<xsd:simpleType>
<xsd:restriction base="sqltypes:decimal">
<xsd:totalDigits value="13" />
<xsd:fractionDigits value="9" />
</xsd:restriction>
</xsd:simpleType>
</xsd:element>
<xsd:element name="floatcol" type="sqltypes:float" minOccurs="0" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<complex xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2">
<id>1</id>
<sysnamecol>sysname column</sysnamecol>
<datetimecol>2009-04-29T06:47:25.407</datetimecol>
<bigintcol>100000000</bigintcol>
<intcol>1000000</intcol>
<smallintcol>1000</smallintcol>
<tinyintcol>100</tinyintcol>
<bitcol>1</bitcol>
<nvarcharcol>nvarchar column</nvarcharcol>
<varcharcol>varchar column</varcharcol>
<ncharcol>nchar column </ncharcol>
<charcol>char column </charcol>
<varbinarycol>dmFyYmluYXJ5IGNvbHVtbg==</varbinarycol>
<moneycol>123.4500</moneycol>
<decimalcol>67.890000000</decimalcol>
<floatcol>1.234567890123000e+005</floatcol>
</complex>
*/

Auditing Using Triggers, Merge, CTE  

Posted by ReelTym


CREATE SCHEMA Audit
GO

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_Host' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_Host;
GO
IF OBJECT_ID( 'Audit.Host' ) IS NOT NULL
DROP TABLE Audit.Host;
GO
CREATE TABLE Audit.Host
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_Host_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_App' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_App;
GO
IF OBJECT_ID( 'Audit.App' ) IS NOT NULL
DROP TABLE Audit.App;
GO
CREATE TABLE Audit.App
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_App_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_SystemUser' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_SystemUser;
GO
IF OBJECT_ID( 'Audit.SystemUser' ) IS NOT NULL
DROP TABLE Audit.SystemUser;
GO
CREATE TABLE Audit.SystemUser
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_SystemUser_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME = 'Domain_Locale_Audit_FK_Audit_Action' )
ALTER TABLE Domain.Locale_Audit DROP CONSTRAINT Domain_Locale_Audit_FK_Audit_Action;
GO
IF OBJECT_ID( 'Audit.Action' ) IS NOT NULL
DROP TABLE Audit.Action;
GO
CREATE TABLE Audit.Action
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Audit_Action_PK PRIMARY KEY CLUSTERED ( ID )
);
GO

CREATE SCHEMA Domain;
GO

IF OBJECT_ID( 'Domain.Locale' ) IS NOT NULL
DROP TABLE Domain.Locale;
GO
CREATE TABLE Domain.Locale
(
ID tinyint IDENTITY(1,1),
Code char(5) NOT NULL,
Description nvarchar(max) NOT NULL,

CONSTRAINT Domain_Locale_PK PRIMARY KEY CLUSTERED ( ID ),
CONSTRAINT Domain_Locale_UC_Code UNIQUE ( Code )
);
GO
IF OBJECT_ID( 'Domain.Locale_Audit' ) IS NOT NULL
DROP TABLE Domain.Locale_Audit;
GO
CREATE TABLE Domain.Locale_Audit
(
ID tinyint,
Code char(5) NOT NULL,
Description nvarchar(max) NOT NULL,

Ordinal bigint IDENTITY(1,1) NOT NULL,
HostID int NOT NULL,
AppID int NOT NULL,
SystemUserID int NOT NULL,
ActionID int NOT NULL,
WhenUpdated datetime2(7) NOT NULL,
ColumnsUpdated varbinary(max) NOT NULL,

CONSTRAINT Domain_Locale_Audit_PK PRIMARY KEY CLUSTERED ( ID, Ordinal ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_Host FOREIGN KEY ( HostID ) REFERENCES Audit.Host ( ID ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_App FOREIGN KEY ( AppID ) REFERENCES Audit.App ( ID ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_SystemUser FOREIGN KEY ( SystemUserID ) REFERENCES Audit.SystemUser ( ID ),
CONSTRAINT Domain_Locale_Audit_FK_Audit_Action FOREIGN KEY ( ActionID ) REFERENCES Audit.Action ( ID )
);
GO
IF OBJECT_ID( 'Domain_Locale_Audit_IDX_ActionID_HostID_SystemUserID_AppID' ) IS NOT NULL
DROP INDEX Domain_Locale_Audit_IDX_ActionID_HostID_SystemUserID_AppID ON Domain.Locale_Audit;
GO
CREATE NONCLUSTERED INDEX Domain_Locale_Audit_IDX_ActionID_HostID_SystemUserID_AppID
ON [Domain].[Locale_Audit]
(
ActionID ASC,
SystemUserID ASC,
AppID ASC,
HostID ASC,
ID ASC,
Ordinal ASC
)
INCLUDE ( Code, Description, WhenUpdated, ColumnsUpdated )
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
IF OBJECT_ID( 'Domain.Locale_Audit_VW' ) IS NOT NULL
DROP VIEW Domain.Locale_Audit_VW;
GO
CREATE VIEW Domain.Locale_Audit_VW
AS
WITH Ordered AS
(
SELECT
l.ID,
l.Code,
l.Description,

ROW_NUMBER() OVER( PARTITION BY l.ID ORDER BY Ordinal DESC ) AS CurrentFirst,
ROW_NUMBER() OVER( PARTITION BY l.ID ORDER BY Ordinal ) AS OriginalFirst,
l.Ordinal,
h.Name as Host,
a.Name as App,
su.Name as SystemUser,
ac.Name as Action,
l.WhenUpdated,
l.ColumnsUpdated

FROM Domain.Locale_Audit l
JOIN Audit.Host h ON l.HostID = h.ID
JOIN Audit.App a ON l.AppID = a.ID
JOIN Audit.SystemUser su ON l.SystemUserID = su.ID
JOIN Audit.Action ac ON l.ActionID = ac.ID
)
SELECT
*
FROM Ordered
GO
IF OBJECT_ID( 'Domain.Locale_Audit_Current_VW' ) IS NOT NULL
DROP VIEW Domain.Locale_Audit_Current_VW;
GO
CREATE VIEW Domain.Locale_Audit_Current_VW
AS
SELECT
*
FROM Locale_Audit_VW
WHERE CurrentFirst = 1
GO
CREATE TRIGGER Domain_Locale_TR_Audit
ON Domain.Locale
FOR DELETE, INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
DECLARE @HostID int, @AppID int, @SystemUserID int, @ActionID int
DECLARE @Lookup TABLE ( ID int )

--Lookup HostID
MERGE Audit.Host AS t
USING ( SELECT Host_Name() AS [Name] ) AS s ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name ) VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @HostID = ID FROM @Lookup;

--Lookup AppID
MERGE Audit.App AS t
USING ( SELECT App_Name() AS [Name] ) AS s
ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET
t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name )
VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @AppID = ID FROM @Lookup;

--Lookup SystemUserID
MERGE Audit.SystemUser AS t
USING ( SELECT System_User AS [Name] ) AS s
ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET
t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name )
VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @SystemUserID = ID FROM @Lookup;

--Lookup ActionID
DECLARE @Action sysname;
SELECT @Action =
CASE
WHEN NOT EXISTS ( SELECT 1 FROM Deleted ) THEN 'Inserted'
WHEN NOT EXISTS ( SELECT 1 FROM Inserted ) THEN 'Deleted'
ELSE 'Updated'
END
MERGE Audit.Action AS t
USING ( SELECT @Action AS [Name] ) AS s
ON ( t.Name = s.Name )
WHEN MATCHED THEN
UPDATE SET
t.Name = s.Name
WHEN NOT MATCHED BY TARGET THEN
INSERT ( Name )
VALUES ( s.Name )
OUTPUT Inserted.ID INTO @Lookup;
SELECT @ActionID = ID FROM @Lookup;

INSERT INTO Domain.Locale_Audit ( HostID, AppID, SystemUserID, ActionID, ColumnsUpdated, WhenUpdated, ID, Code, Description )
SELECT @HostID, @AppID, @SystemUserID, @ActionID, COLUMNS_UPDATED(), GETDATE(), s.ID, s.Code, s.Description FROM
(
SELECT * FROM Inserted UNION ALL
SELECT * FROM Deleted WHERE NOT EXISTS( SELECT 1 FROM Inserted )
) s;
END;
GO

SET IDENTITY_INSERT Domain.Locale ON;
SET NOCOUNT ON;
DECLARE @Source TABLE ( ID tinyint identity(1,1) primary key clustered, Code char(5) not null, Description nvarchar(max) not null );
DECLARE @Results TABLE ( [Action] sysname, d_ID tinyint, d_Code char(5), d_Description nvarchar(max), i_ID tinyint, i_Code char(5), i_Description nvarchar(max) );
INSERT INTO @Source ( Description, Code )
VALUES
( 'Afrikaans - South Africa', 'af-ZA' ),
( 'Albanian - Albania', 'sq-AL' ),
( 'Arabic - Algeria', 'ar-DZ' ),
( 'Arabic - Morocco', 'ar-MA' ),
( 'Arabic - Oman', 'ar-OM' ),
( 'Arabic - Qatar', 'ar-QA' ),
( 'Arabic - Saudi Arabia', 'ar-SA' ),
( 'Arabic - Syria', 'ar-SY' ),
( 'Arabic - Tunisia', 'ar-TN' ),
( 'Arabic - United Arab Emirates', 'ar-AE' ),
( 'Arabic - Yemen', 'ar-YE' ),
( 'Arabic - Bahrain', 'ar-BH' ),
( 'Arabic - Egypt', 'ar-EG' ),
( 'Arabic - Jordan', 'ar-JO' ),
( 'Arabic - Kuwait', 'ar-KW' ),
( 'Arabic - Lebanon', 'ar-LB' ),
( 'Arabic - Libya', 'ar-LY' ),
( 'Armenian - Armenia', 'hy-AM' ),
( 'Azeri - Azerbaijan', 'az-AZ' ),
( 'Basque - Basque', 'eu-ES' ),
( 'Belarusian - Belarus', 'be-BY' ),
( 'Bulgarian - Bulgaria', 'bg-BG' ),
( 'Catalan - Spain', 'ca-ES' ),
( 'Chinese - China (Simplified Chinese)', 'zh-CN' ),
( 'Chinese - Hong Kong SAR', 'zh-HK' ),
( 'Chinese - Macao SAR', 'zh-MO' ),
( 'Chinese - Singapore', 'zh-SG' ),
( 'Chinese - Taiwan (Traditional Chinese)', 'zh-TW' ),
( 'Croatian - Croatia', 'hr-HR' ),
( 'Czech - Czech Republic', 'cs-CZ' ),
( 'Danish - Denmark', 'da-DK' ),
( 'Dutch - Belgium', 'nl-BE' ),
( 'Dutch - The Netherlands', 'nl-NL' ),
( 'English - Australia', 'en-AU' ),
( 'English - Belize', 'en-BZ' ),
( 'English - Canada', 'en-CA' ),
( 'English - Caribbean', 'en-CB' ),
( 'English - Ireland', 'en-IE' ),
( 'English - Jamaica', 'en-JM' ),
( 'English - New Zealand', 'en-NZ' ),
( 'English - Philippines', 'en-PH' ),
( 'English - South Africa', 'en-ZA' ),
( 'English - Trinidad and Tobago', 'en-TT' ),
( 'English - United Kingdom', 'en-GB' ),
( 'English - United States', 'en-US' ),
( 'English - Zimbabwe', 'en-ZW' ),
( 'Estonian - Estonia', 'et-EE' ),
( 'Faroese - Faroe Islands', 'fo-FO' ),
( 'Farsi - Iran', 'fa-IR' ),
( 'Finnish - Finland', 'fi-FI' ),
( 'French - Belgium', 'fr-BE' ),
( 'French - Canada', 'fr-CA' ),
( 'French - France', 'fr-FR' ),
( 'French - Luxembourg', 'fr-LU' ),
( 'French - Monaco', 'fr-MC' ),
( 'French - Switzerland', 'fr-CH' ),
( 'Galician - Galician', 'gl-ES' ),
( 'Georgian - Georgia', 'ka-GE' ),
( 'German - Austria', 'de-AT' ),
( 'German - Germany', 'de-DE' ),
( 'German - Liechtenstein', 'de-LI' ),
( 'German - Luxembourg', 'de-LU' ),
( 'German - Switzerland', 'de-CH' ),
( 'Greek - Greece', 'el-GR' ),
( 'Gujarati - India', 'gu-IN' ),
( 'Hebrew - Israel', 'he-IL' ),
( 'Hindi - India', 'hi-IN' ),
( 'Hungarian - Hungary', 'hu-HU' ),
( 'Icelandic - Iceland', 'is-IS' ),
( 'Indonesian - Indonesia', 'id-ID' ),
( 'Italian - Italy', 'it-IT' ),
( 'Italian - Switzerland', 'it-CH' ),
( 'Japanese - Japan', 'ja-JP' ),
( 'Kannada - India', 'kn-IN' ),
( 'Kazakh - Kazakhstan', 'kk-KZ' ),
( 'Korean - Korea', 'ko-KR' ),
( 'Kyrgyz - Kyrgyzstan', 'ky-KG' ),
( 'Latvian - Latvia', 'lv-LV' ),
( 'Lithuanian - Lithuania', 'lt-LT' ),
( 'Macedonian - Former Yugoslav Republic of Macedonia', 'mk-MK' ),
( 'Malay - Brunei', 'ms-BN' ),
( 'Malay - Malaysia', 'ms-MY' ),
( 'Marathi - India', 'mr-IN' ),
( 'Mongolian - Mongolia', 'mn-MN' ),
( 'Norwegian (Bokmål) - Norway', 'nb-NO' ),
( 'Norwegian (Nynorsk) - Norway', 'nn-NO' ),
( 'Polish - Poland', 'pl-PL' ),
( 'Portuguese - Brazil', 'pt-BR' ),
( 'Portuguese - Portugal', 'pt-PT' ),
( 'Punjabi - India', 'pa-IN' ),
( 'Romanian - Romania', 'ro-RO' ),
( 'Russian - Russia', 'ru-RU' ),
( 'Sanskrit - India', 'sa-IN' ),
( 'Serbian - Serbia', 'sr-SP' ),
( 'Slovak - Slovakia', 'sk-SK' ),
( 'Slovenian - Slovenia', 'sl-SI' ),
( 'Spanish - Argentina', 'es-AR' ),
( 'Spanish - Bolivia', 'es-BO' ),
( 'Spanish - Chile', 'es-CL' ),
( 'Spanish - Colombia', 'es-CO' ),
( 'Spanish - Costa Rica', 'es-CR' ),
( 'Spanish - Dominican Republic', 'es-DO' ),
( 'Spanish - Ecuador', 'es-EC' ),
( 'Spanish - El Salvador', 'es-SV' ),
( 'Spanish - Guatemala', 'es-GT' ),
( 'Spanish - Honduras', 'es-HN' ),
( 'Spanish - Mexico', 'es-MX' ),
( 'Spanish - Nicaragua', 'es-NI' ),
( 'Spanish - Panama', 'es-PA' ),
( 'Spanish - Paraguay', 'es-PY' ),
( 'Spanish - Peru', 'es-PE' ),
( 'Spanish - Puerto Rico', 'es-PR' ),
( 'Spanish - Spain', 'es-ES' ),
( 'Spanish - Uruguay', 'es-UY' ),
( 'Spanish - Venezuela', 'es-VE' ),
( 'Swahili - Kenya', 'sw-KE' ),
( 'Swedish - Finland', 'sv-FI' ),
( 'Swedish - Sweden', 'sv-SE' ),
( 'Tamil - India', 'ta-IN' ),
( 'Tatar - Russia', 'tt-RU' ),
( 'Telugu - India', 'te-IN' ),
( 'Thai - Thailand', 'th-TH' ),
( 'Turkish - Turkey', 'tr-TR' ),
( 'Ukrainian - Ukraine', 'uk-UA' ),
( 'Urdu - Pakistan', 'ur-PK' ),
( 'Uzbek - Uzbekistan', 'uz-UZ' ),
( 'Vietnamese - Vietnam', 'vi-VN' )

declare @count int = 0
while @count < id =" s.ID)" code =" s.Code," description =" s.Description" id =" s.ID)" code =" s.Code," description =" s.Description">

Using LEFT JOIN vs. NOT IN vs. NOT EXISTS vs. EXCEPT  

Posted by ReelTym

Bottom-line NOT EXISTS typically will perform best, followed by NOT IN, then LEFT JOIN and EXCEPT.


IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'Empl_FK_Dept'))
ALTER TABLE Empl DROP CONSTRAINT Empl_FK_Dept;
GO
IF OBJECT_ID( 'Dept' ) IS NOT NULL DROP TABLE Dept;
GO
CREATE TABLE Dept
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Dept_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'ProjEmpl_FK_Proj'))
ALTER TABLE ProjEmpl DROP CONSTRAINT ProjEmpl_FK_Proj;
GO
IF OBJECT_ID( 'Proj' ) IS NOT NULL DROP TABLE Proj;
GO
CREATE TABLE Proj
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
CONSTRAINT Proj_PK PRIMARY KEY CLUSTERED ( ID )
);
GO
IF EXISTS (SELECT * from sysconstraints WHERE constid = OBJECT_ID (N'ProjEmpl_FK_Empl'))
ALTER TABLE ProjEmpl DROP CONSTRAINT ProjEmpl_FK_Empl;
GO
IF OBJECT_ID( 'Empl' ) IS NOT NULL DROP TABLE Empl;
GO
CREATE TABLE Empl
(
ID int IDENTITY(1,1),
[Name] sysname NOT NULL,
DeptID int NULL,
MgrID int NULL,
CONSTRAINT Empl_PK PRIMARY KEY CLUSTERED ( ID ),
CONSTRAINT Empl_FK_Dept FOREIGN KEY ( DeptID ) REFERENCES Dept ( ID ),
CONSTRAINT Empl_FK_Empl FOREIGN KEY ( MgrID ) REFERENCES Empl ( ID )
);
GO
IF OBJECT_ID( 'ProjEmpl' ) IS NOT NULL DROP TABLE ProjEmpl;
GO
CREATE TABLE ProjEmpl
(
ProjID int NOT NULL,
EmplID int NOT NULL,
CONSTRAINT ProjEmpl_PK PRIMARY KEY CLUSTERED ( ProjID, EmplID ),
CONSTRAINT ProjEmpl_FK_Proj FOREIGN KEY ( ProjID ) REFERENCES Proj ( ID ),
CONSTRAINT ProjEmpl_FK_Empl FOREIGN KEY ( EmplID ) REFERENCES Empl ( ID )
);
GO

SET NOCOUNT ON;
DECLARE @CNT int = 0;
WHILE @CNT < 100
BEGIN
SELECT @CNT += 1
INSERT INTO Dept ( Name ) SELECT 'Dept ' + CONVERT( varchar, @CNT, 0 )
INSERT INTO Proj ( Name ) SELECT 'Proj ' + CONVERT( varchar, @CNT, 0 )
INSERT INTO Empl ( Name, DeptID, MgrID ) SELECT 'Empl ' + CONVERT( varchar, @CNT, 0 ), (@CNT+1)/2, case when @CNT%2 = 1 THEN NULL ELSE @CNT - 1 END
END
SELECT @CNT = 0;
WHILE @CNT < 100
BEGIN
SELECT @CNT += 1
INSERT INTO ProjEmpl ( ProjID, EmplID )
SELECT @CNT, @CNT WHERE @CNT%10 IN ( 1, 3, 4, 6, 9 )
UNION ALL
SELECT @CNT, @CNT - 1 WHERE @CNT%10 = 7
UNION ALL
SELECT @CNT, @CNT + 1 WHERE @CNT%10 = 9
END
SET NOCOUNT OFF;
select * from ProjEmpl

SET SHOWPLAN_ALL ON
GO

select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl )

select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID )

select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null

select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID

GO

SET SHOWPLAN_ALL OFF
GO
/*
StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------

select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl ) 1 1 0 NULL NULL 1 NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 1 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 1 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Top(TOP EXPRESSION:((1))) 1 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID])) 1 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
|--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 1 10 2 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50

select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID ) 2 11 0 NULL NULL 2 NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 2 12 11 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
|--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 2 13 12 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 2 14 13 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
| |--Top(TOP EXPRESSION:((1))) 2 16 13 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID])) 2 17 16 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
|--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 2 20 12 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50

select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null 3 21 0 NULL NULL 3 NULL 49.5 NULL NULL NULL 0.04512281 NULL NULL SELECT 0 NULL
|--Filter(WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL)) 3 22 21 Filter Filter WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL) NULL 49.5 0 4.9896E-05 285 0.04512281 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
|--Hash Match(Right Outer Join, HASH:([pe].[EmplID])=([e].[ID])) 3 23 22 Hash Match Right Outer Join HASH:([pe].[EmplID])=([e].[ID]) NULL 103.95 0 0.01896592 289 0.04507292 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name], [pe].[EmplID] NULL PLAN_ROW 0 1
|--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe])) 3 24 23 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]) [pe].[EmplID] 70 0.003125 0.000234 11 0.003359 [pe].[EmplID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 3 25 23 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 99 0 0.000418 285 0.022745 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
|--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 3 26 25 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
|--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 3 27 25 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100

select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID 4 28 0 NULL NULL 4 NULL 99 NULL NULL NULL 0.04415589 NULL NULL SELECT 0 NULL
|--Merge Join(Left Anti Semi Join, MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL)) 4 29 28 Merge Join Left Anti Semi Join MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL) NULL 99 0 0.0060143 151 0.04415589 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
|--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID])) 4 30 29 Nested Loops Inner Join OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID]) NULL 99 0 0.000418 151 0.022745 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
| |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD) 4 31 30 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] 100 0.003125 0.000267 15 0.003392 [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
| |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD) 4 32 30 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100
|--Sort(ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC)) 4 33 29 Sort Sort ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC) NULL 70 0.01126126 0.0007693287 11 0.01538959 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1
|--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK])) 4 34 33 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]) [Test].[dbo].[ProjEmpl].[EmplID] 70 0.003125 0.000234 11 0.003359 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1

(28 row(s) affected)
*/

SET STATISTICS PROFILE ON
GO

select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl )

select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID )

select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null

select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID
GO

SET STATISTICS PROFILE OFF
GO

/*
ID Name ID Name DeptID MgrID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 2 Empl 2 1 1
3 Dept 3 5 Empl 5 3 NULL
4 Dept 4 7 Empl 7 4 NULL
4 Dept 4 8 Empl 8 4 7
6 Dept 6 12 Empl 12 6 11
8 Dept 8 15 Empl 15 8 NULL
9 Dept 9 17 Empl 17 9 NULL
9 Dept 9 18 Empl 18 9 17
11 Dept 11 22 Empl 22 11 21
13 Dept 13 25 Empl 25 13 NULL
14 Dept 14 27 Empl 27 14 NULL
14 Dept 14 28 Empl 28 14 27
16 Dept 16 32 Empl 32 16 31
18 Dept 18 35 Empl 35 18 NULL
19 Dept 19 37 Empl 37 19 NULL
19 Dept 19 38 Empl 38 19 37
21 Dept 21 42 Empl 42 21 41
23 Dept 23 45 Empl 45 23 NULL
24 Dept 24 47 Empl 47 24 NULL
24 Dept 24 48 Empl 48 24 47
26 Dept 26 52 Empl 52 26 51
28 Dept 28 55 Empl 55 28 NULL
29 Dept 29 57 Empl 57 29 NULL
29 Dept 29 58 Empl 58 29 57
31 Dept 31 62 Empl 62 31 61
33 Dept 33 65 Empl 65 33 NULL
34 Dept 34 67 Empl 67 34 NULL
34 Dept 34 68 Empl 68 34 67
36 Dept 36 72 Empl 72 36 71
38 Dept 38 75 Empl 75 38 NULL
39 Dept 39 77 Empl 77 39 NULL
39 Dept 39 78 Empl 78 39 77
41 Dept 41 82 Empl 82 41 81
43 Dept 43 85 Empl 85 43 NULL
44 Dept 44 87 Empl 87 44 NULL
44 Dept 44 88 Empl 88 44 87
46 Dept 46 92 Empl 92 46 91
48 Dept 48 95 Empl 95 48 NULL
49 Dept 49 97 Empl 97 49 NULL
49 Dept 49 98 Empl 98 49 97

(40 row(s) affected)

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
40 1 select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where e.ID not in ( select EmplID from ProjEmpl ) 1 1 0 NULL NULL NULL NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
40 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 1 2 1 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
40 1 |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 1 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
100 1 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 1 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
60 100 | |--Top(TOP EXPRESSION:((1))) 1 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
60 100 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID])) 1 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
40 40 |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 1 10 2 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50

(7 row(s) affected)

ID Name ID Name DeptID MgrID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 2 Empl 2 1 1
3 Dept 3 5 Empl 5 3 NULL
4 Dept 4 7 Empl 7 4 NULL
4 Dept 4 8 Empl 8 4 7
6 Dept 6 12 Empl 12 6 11
8 Dept 8 15 Empl 15 8 NULL
9 Dept 9 17 Empl 17 9 NULL
9 Dept 9 18 Empl 18 9 17
11 Dept 11 22 Empl 22 11 21
13 Dept 13 25 Empl 25 13 NULL
14 Dept 14 27 Empl 27 14 NULL
14 Dept 14 28 Empl 28 14 27
16 Dept 16 32 Empl 32 16 31
18 Dept 18 35 Empl 35 18 NULL
19 Dept 19 37 Empl 37 19 NULL
19 Dept 19 38 Empl 38 19 37
21 Dept 21 42 Empl 42 21 41
23 Dept 23 45 Empl 45 23 NULL
24 Dept 24 47 Empl 47 24 NULL
24 Dept 24 48 Empl 48 24 47
26 Dept 26 52 Empl 52 26 51
28 Dept 28 55 Empl 55 28 NULL
29 Dept 29 57 Empl 57 29 NULL
29 Dept 29 58 Empl 58 29 57
31 Dept 31 62 Empl 62 31 61
33 Dept 33 65 Empl 65 33 NULL
34 Dept 34 67 Empl 67 34 NULL
34 Dept 34 68 Empl 68 34 67
36 Dept 36 72 Empl 72 36 71
38 Dept 38 75 Empl 75 38 NULL
39 Dept 39 77 Empl 77 39 NULL
39 Dept 39 78 Empl 78 39 77
41 Dept 41 82 Empl 82 41 81
43 Dept 43 85 Empl 85 43 NULL
44 Dept 44 87 Empl 87 44 NULL
44 Dept 44 88 Empl 88 44 87
46 Dept 46 92 Empl 92 46 91
48 Dept 48 95 Empl 95 48 NULL
49 Dept 49 97 Empl 97 49 NULL
49 Dept 49 98 Empl 98 49 97

(40 row(s) affected)

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ---------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
40 1 select d.*, e.*
from Dept d
join Empl e on d.ID = e.DeptID
where not exists ( select 1 from ProjEmpl pe where e.ID = pe.EmplID ) 2 1 0 NULL NULL NULL NULL 49.5 NULL NULL NULL 0.0356025 NULL NULL SELECT 0 NULL
40 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 2 2 1 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 49.5 0 0.000209 285 0.0356025 [d].[ID], [d].[Name], [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
40 1 |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([e].[ID])) 2 3 2 Nested Loops Left Anti Semi Join OUTER REFERENCES:([e].[ID]) NULL 50 0 0.000418 151 0.0243635 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
100 1 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 2 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
60 100 | |--Top(TOP EXPRESSION:((1))) 2 6 3 Top Top TOP EXPRESSION:((1)) NULL 1 0 1E-07 9 0.0205435 NULL NULL PLAN_ROW 0 100
60 100 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID])) 2 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]), WHERE:([Test].[dbo].[Empl].[ID] as [e].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID]) NULL 1 0.0032035 0.0001555 11 0.0176535 NULL NULL PLAN_ROW 0 100
40 40 |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 2 10 2 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.01103 [d].[ID], [d].[Name] NULL PLAN_ROW 0 50

(7 row(s) affected)

ID Name ID Name DeptID MgrID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 2 Empl 2 1 1
3 Dept 3 5 Empl 5 3 NULL
4 Dept 4 7 Empl 7 4 NULL
4 Dept 4 8 Empl 8 4 7
6 Dept 6 12 Empl 12 6 11
8 Dept 8 15 Empl 15 8 NULL
9 Dept 9 17 Empl 17 9 NULL
9 Dept 9 18 Empl 18 9 17
11 Dept 11 22 Empl 22 11 21
13 Dept 13 25 Empl 25 13 NULL
14 Dept 14 27 Empl 27 14 NULL
14 Dept 14 28 Empl 28 14 27
16 Dept 16 32 Empl 32 16 31
18 Dept 18 35 Empl 35 18 NULL
19 Dept 19 37 Empl 37 19 NULL
19 Dept 19 38 Empl 38 19 37
21 Dept 21 42 Empl 42 21 41
23 Dept 23 45 Empl 45 23 NULL
24 Dept 24 47 Empl 47 24 NULL
24 Dept 24 48 Empl 48 24 47
26 Dept 26 52 Empl 52 26 51
28 Dept 28 55 Empl 55 28 NULL
29 Dept 29 57 Empl 57 29 NULL
29 Dept 29 58 Empl 58 29 57
31 Dept 31 62 Empl 62 31 61
33 Dept 33 65 Empl 65 33 NULL
34 Dept 34 67 Empl 67 34 NULL
34 Dept 34 68 Empl 68 34 67
36 Dept 36 72 Empl 72 36 71
38 Dept 38 75 Empl 75 38 NULL
39 Dept 39 77 Empl 77 39 NULL
39 Dept 39 78 Empl 78 39 77
41 Dept 41 82 Empl 82 41 81
43 Dept 43 85 Empl 85 43 NULL
44 Dept 44 87 Empl 87 44 NULL
44 Dept 44 88 Empl 88 44 87
46 Dept 46 92 Empl 92 46 91
48 Dept 48 95 Empl 95 48 NULL
49 Dept 49 97 Empl 97 49 NULL
49 Dept 49 98 Empl 98 49 97

(40 row(s) affected)

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- ------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
40 1 select d.*, e.*
from Empl e
join Dept d on e.DeptID = d.ID
left join ProjEmpl pe on e.ID = pe.EmplID
where pe.EmplID is null 3 1 0 NULL NULL NULL NULL 49.5 NULL NULL NULL 0.04512281 NULL NULL SELECT 0 NULL
40 1 |--Filter(WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL)) 3 2 1 Filter Filter WHERE:([Test].[dbo].[ProjEmpl].[EmplID] as [pe].[EmplID] IS NULL) NULL 49.5 0 4.9896E-05 285 0.04512281 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
110 1 |--Hash Match(Right Outer Join, HASH:([pe].[EmplID])=([e].[ID])) 3 3 2 Hash Match Right Outer Join HASH:([pe].[EmplID])=([e].[ID]) NULL 103.95 0 0.01896592 289 0.04507292 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name], [pe].[EmplID] NULL PLAN_ROW 0 1
70 1 |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe])) 3 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK] AS [pe]) [pe].[EmplID] 70 0.003125 0.000234 11 0.003359 [pe].[EmplID] NULL PLAN_ROW 0 1
100 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([e].[DeptID])) 3 5 3 Nested Loops Inner Join OUTER REFERENCES:([e].[DeptID]) NULL 99 0 0.000418 285 0.022745 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID], [d].[ID], [d].[Name] NULL PLAN_ROW 0 1
100 1 |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e])) 3 6 5 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK] AS [e]) [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] 100 0.003125 0.000267 151 0.003392 [e].[ID], [e].[Name], [e].[DeptID], [e].[MgrID] NULL PLAN_ROW 0 1
100 100 |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD) 3 7 5 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID] as [e].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100

(7 row(s) affected)

ID Name ID DeptID
----------- -------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1 Dept 1 1 1
1 Dept 1 2 1
2 Dept 2 3 2
2 Dept 2 4 2
3 Dept 3 5 3
3 Dept 3 6 3
4 Dept 4 7 4
4 Dept 4 8 4
5 Dept 5 9 5
5 Dept 5 10 5
6 Dept 6 11 6
6 Dept 6 12 6
7 Dept 7 13 7
7 Dept 7 14 7
8 Dept 8 15 8
8 Dept 8 16 8
9 Dept 9 17 9
9 Dept 9 18 9
10 Dept 10 19 10
10 Dept 10 20 10
11 Dept 11 21 11
11 Dept 11 22 11
12 Dept 12 23 12
12 Dept 12 24 12
13 Dept 13 25 13
13 Dept 13 26 13
14 Dept 14 27 14
14 Dept 14 28 14
15 Dept 15 29 15
15 Dept 15 30 15
16 Dept 16 31 16
16 Dept 16 32 16
17 Dept 17 33 17
17 Dept 17 34 17
18 Dept 18 35 18
18 Dept 18 36 18
19 Dept 19 37 19
19 Dept 19 38 19
20 Dept 20 39 20
20 Dept 20 40 20
21 Dept 21 41 21
21 Dept 21 42 21
22 Dept 22 43 22
22 Dept 22 44 22
23 Dept 23 45 23
23 Dept 23 46 23
24 Dept 24 47 24
24 Dept 24 48 24
25 Dept 25 49 25
25 Dept 25 50 25
26 Dept 26 51 26
26 Dept 26 52 26
27 Dept 27 53 27
27 Dept 27 54 27
28 Dept 28 55 28
28 Dept 28 56 28
29 Dept 29 57 29
29 Dept 29 58 29
30 Dept 30 59 30
30 Dept 30 60 30
31 Dept 31 61 31
31 Dept 31 62 31
32 Dept 32 63 32
32 Dept 32 64 32
33 Dept 33 65 33
33 Dept 33 66 33
34 Dept 34 67 34
34 Dept 34 68 34
35 Dept 35 69 35
35 Dept 35 70 35
36 Dept 36 71 36
36 Dept 36 72 36
37 Dept 37 73 37
37 Dept 37 74 37
38 Dept 38 75 38
38 Dept 38 76 38
39 Dept 39 77 39
39 Dept 39 78 39
40 Dept 40 79 40
40 Dept 40 80 40
41 Dept 41 81 41
41 Dept 41 82 41
42 Dept 42 83 42
42 Dept 42 84 42
43 Dept 43 85 43
43 Dept 43 86 43
44 Dept 44 87 44
44 Dept 44 88 44
45 Dept 45 89 45
45 Dept 45 90 45
46 Dept 46 91 46
46 Dept 46 92 46
47 Dept 47 93 47
47 Dept 47 94 47
48 Dept 48 95 48
48 Dept 48 96 48
49 Dept 49 97 49
49 Dept 49 98 49
50 Dept 50 99 50
50 Dept 50 100 50

(100 row(s) affected)

Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------------------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
100 1 select d.*, e.*
from Dept d
join (
select ID, DeptID from Empl
except
select EmplID, null from ProjEmpl
) e on d.ID = e.DeptID 4 1 0 NULL NULL NULL NULL 99 NULL NULL NULL 0.04415589 NULL NULL SELECT 0 NULL
100 1 |--Merge Join(Left Anti Semi Join, MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL)) 4 2 1 Merge Join Left Anti Semi Join MERGE:([Test].[dbo].[Empl].[ID])=([Test].[dbo].[ProjEmpl].[EmplID]), RESIDUAL:([Test].[dbo].[Empl].[ID]=[Test].[dbo].[ProjEmpl].[EmplID] AND [Test].[dbo].[Empl].[DeptID] IS NULL) NULL 99 0 0.0060143 151 0.04415589 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
100 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID])) 4 3 2 Nested Loops Inner Join OUTER REFERENCES:([Test].[dbo].[Empl].[DeptID]) NULL 99 0 0.000418 151 0.022745 [d].[ID], [d].[Name], [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
100 1 | |--Clustered Index Scan(OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD) 4 4 3 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[Empl].[Empl_PK]), ORDERED FORWARD [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] 100 0.003125 0.000267 15 0.003392 [Test].[dbo].[Empl].[ID], [Test].[dbo].[Empl].[DeptID] NULL PLAN_ROW 0 1
100 100 | |--Clustered Index Seek(OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD) 4 5 3 Clustered Index Seek Clustered Index Seek OBJECT:([Test].[dbo].[Dept].[Dept_PK] AS [d]), SEEK:([d].[ID]=[Test].[dbo].[Empl].[DeptID]) ORDERED FORWARD [d].[ID], [d].[Name] 1 0.003125 0.0001581 143 0.018935 [d].[ID], [d].[Name] NULL PLAN_ROW 0 100
70 1 |--Sort(ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC)) 4 6 2 Sort Sort ORDER BY:([Test].[dbo].[ProjEmpl].[EmplID] ASC) NULL 70 0.01126126 0.0007693287 11 0.01538959 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1
70 1 |--Clustered Index Scan(OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK])) 4 7 6 Clustered Index Scan Clustered Index Scan OBJECT:([Test].[dbo].[ProjEmpl].[ProjEmpl_PK]) [Test].[dbo].[ProjEmpl].[EmplID] 70 0.003125 0.000234 11 0.003359 [Test].[dbo].[ProjEmpl].[EmplID] NULL PLAN_ROW 0 1

(7 row(s) affected)
*/