Hash Match inner join in simple query with in statement

I am running the execution plan for the following query:

select m_uid from EmpTaxAudit 
where clientid = 91682 
and  empuid = 42100176452603
and newvalue in('Deleted','DB-Deleted','Added')

Here is the execution plan:

I have a non clustered index on EmpTaxAudit Table on ClientId and NewValue columns which shows above as 14.9% of the execution:

CREATE NONCLUSTERED INDEX [idx_EmpTaxAudit_clientid_newvalue] ON [dbo].

[EmpTaxAudit]
(
    [ClientID] ASC,
    [NewValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

I also have a non clustered unique index PK as follows:

ALTER TABLE [dbo].[EmpTaxAudit] ADD  CONSTRAINT [PK_EmpTaxAudit] PRIMARY KEY NONCLUSTERED 
(
    [ClientID] ASC,
    [EmpUID] ASC,
    [m_uid] ASC,
    [m_eff_start_date] ASC,
    [ReplacedOn] ASC,
    [ColumnName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Trigger code in source table EmpTax:

```
CREATE trigger [dbo].[trins_EmpTax]
on [dbo].[emptax]
for insert
as
begin
declare
@intRowCount int,
@user varchar(30)

select @intRowCount = @@RowCount
IF @intRowCount > 0
begin
select @user = suser_sname()

insert EmpTaxAudit (Clientid, empuid,m_uid,m_eff_start_date, ColumnName, ReplacedOn, ReplacedBy, OldValue,dblogin,newvalue)
select Clientid, empuid,m_uid,m_eff_start_date,'taxcode', getdate(),IsNull(userid,@user), '', Left(@user,15),'Added'
from inserted i
where m_uid not in (select m_uid from EmpTaxAudit
where clientid = i.clientid and (newvalue = 'Deleted'
or newvalue = 'DB-Deleted'
or newvalue = 'Added') and empuid = i.empuid)
and i.m_eff_end_date is null

insert EmpTaxAudit (Clientid, empuid,m_uid,m_

For the 1st query, an index that uses all three columns from the WHERE clause and includes the column from the SELECT list would be much more useful:

-- index suggestion A
(clientid, empuid, newvalue) INCLUDE (m_uid)

or an index targeted specifically for this query:

-- index suggestion B
(clientid, empuid, m_uid)
WHERE newvalue in ('Deleted', 'DB-Deleted', 'Added')

Regarding the trigger, some comments:

• The first query you show does not appear in the trigger. What appears is a join from that table to the inserted rows to another table (which has the trigger).

• My suggestion B above seems better suited to be used by the trigger.

-
The trigger has 2 almost identical insert statements. Why? I think they could be combined in one - and simpler - insert and using NOT EXISTS instead of NOT IN:

insert EmpTaxAudit 
  ( Clientid, empuid, m_uid, m_eff_start_date, ColumnName, 
    ReplacedOn, ReplacedBy, OldValue, dblogin, 
    newvalue
  )
select 
    Clientid, empuid, m_uid, m_eff_start_date, 'taxcode',
    getdate(), IsNull(userid,@user), '', Left(@user,15),
    case when m_eff_end_date is null 
        then 'Added' else 'Deleted'
    end
from inserted i
where not exists 
      ( select 1 
        from EmpTaxAudit 
        where m_uid = i.m_uid 
          and clientid = i.clientid  
          and empuid = i.empuid
          and newvalue in ('Deleted', 'DB-Deleted', 'Added')
      ) ;

StackExchange Database Administrators Q#145175, answer score: 3