SELECT @@VERSION
--Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
--  Jun  9 2015 12:06:16
--  Copyright (c) Microsoft Corporation
--  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

/*************************************************************************************
* SETUP
*************************************************************************************/

-- Create a million rows of previously existing "customers"
-- We use a string representation because the customer identifer provided
-- by the client may not be a number (or may be NULL in some cases).
SELECT TOP 1000000 id = IDENTITY(INT, 1, 1),
    CONVERT(VARCHAR(50),ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
INTO #existingCustomers
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
GO

-- Create 10K rows of customers who might be new, based on the string
-- customer identifier provided by the client.
SELECT TOP 10000 CONVERT(VARCHAR(100), 1000 * ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS cust_nbr
INTO #potentialNewCustomers
FROM master..spt_values v1
CROSS JOIN master..spt_values v2
GO

/*************************************************************************************
* TEST QUERY
*************************************************************************************/

-- Prune any existing customers from the set of potential new customers
-- This query is much slower than expected in SQL 2014
SELECT *
FROM #potentialNewCustomers -- 10K rows
WHERE cust_nbr NOT IN (
    SELECT cust_nbr
    FROM #existingCustomers -- 1MM rows
)
--OPTION (QUERYTRACEON 9481) -- Use SQL 2012 CE
-- 2012 CE
    -- CPU time = 3323 ms,  elapsed time = 861 ms.
    -- Table '#existingCustomers'. Scan count 13, logical reads 31685
    -- Table '#potentialNewCustomers'. Scan count 5, logical reads 25
-- 2014 CE
    -- CPU time = 656514 ms,  elapsed time = 170265 ms.
    -- Table '#existingCustomers'. Scan count 12, logical reads 22004958
    -- Table '#potentialNewCustomers'. Scan count 5, logical reads 25
GO