Speeding up "duplicate" checks
Improving the Original Query

The query as written will use the nonclustered index that you created, but it won't seek into it - the whole index gets scanned.  

I reproduced the query and data locally - the DATEDIFF version has a plan that looks like this:



Table 'Events'. Scan count 1, logical reads 28323

 SQL Server Execution Times:
   CPU time = 1891 ms,  elapsed time = 1943 ms.


This plan will scan the Events table once for each row in your #NewEvents temp table.  It filters rows at the Nested Loops join operator after they've already been read.  This is not ideal.

To get a seek, you could write your query like this:

SELECT *
FROM #NewEvents
WHERE EXISTS (SELECT *
    FROM [dbo].[Events] e
    WHERE 
    e.EventDate &gt;= DATEADD(dd, -7, #NewEvents.EventDate)
    AND e.EventDate &lt;= #NewEvents.EventDate
    AND e.EventTitle = #NewEvents.EventTitle
    AND e.EventContent = #NewEvents.EventContent
);


That plans looks like this:



Table 'Events'. Scan count 1, logical reads 7

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 149 ms.


For each row in the temp table, there is a seek into your nonclustered index to just get the events that are within 7 days of the new event.  A filter is applied to qualifying rows to check if the title and content are equal.

The nice thing about this seek is that will, at most, read all the events within a 7 day span.  Which should be a fairly small number.

Thoughts about the Indexed View


  I created an index on the view like the one above INCLUDING the computed columns, but for whatever reason I can't get the query to use that index on the view


I don't think the view is really necessary, if the change mentioned above provides reasonable performance.  But you could try adding a WITH (NOEXPAND) hint after the view name in the query to see if you can force the view to be used.