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.

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.

By David Bird, 2007/05/24

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.

• Part 1, Part 2, Part 3, Part 4
  

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>
*/

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">

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)
*/