SQL Server 2014 execution plan takes a long time to generate (fast in older versions)

I am facing a problem with a query in SQL Server 2014. The first time I run this query, it takes age to generate the execution plan.

The weird thing is that it did a great job on all previous versions of SQL Server (2012, 2008 R2, 2008, etc.). It seems to have something to do with a unique index on one of the tables involved, combined with a certain number of subqueries in the main query.

Here are the tables involved in the query. I have simplified the tables compared to the originals, but the problem persists. Note the unique constraint on table2, this seems to be causing the problem. It doesn't matter if it's a unique constraint, a unique index, or even a primary key on table 2, the result is the same.

IF OBJECT_ID('Table2') IS NOT NULL DROP TABLE [Table2]
IF OBJECT_ID('Table1') IS NOT NULL DROP TABLE [Table1]
CREATE TABLE [dbo].[Table1] ( [ReferencedColumn] [int] NOT NULL PRIMARY KEY)
CREATE TABLE [dbo].[Table2] ( [ReferencedColumn] [int] NOT NULL FOREIGN KEY REFERENCES [Table1] ([ReferencedColumn]), [IntColumn] [int] NOT NULL, [AnotherIntColumn] [int] NULL )
CREATE UNIQUE NONCLUSTERED INDEX [IX_Table2] ON [dbo].[Table2] ([ReferencedColumn], [IntColumn])

      

        
        
        
      

    

If then I do some subqueries from the indexed table in the select statement to complete the first time (more than 30 seconds in my tests it takes age).

SELECT  (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 1 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 2 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 3 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 4 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 5 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 6 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 7 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 8 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 9 ),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 10),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 11),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 12),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 13),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 14),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 15),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 16),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 17),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 18),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 19),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 20),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 21),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 22),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 23),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 24),
        (SELECT F2.IntColumn FROM Table2 F2 WHERE F2.[ReferencedColumn] = F.[ReferencedColumn] AND F2.IntColumn = 25)
FROM    Table1 F

      

        
        
        
      

    

Since the tables have no rows in them, and since the query is executed right after the first run, it seems to me that this must be an execution plan that takes a long time to create.

However, if you make one of the following changes, the execution plan is created instantly instead.

• Delete index
• Delete the UNIQUE part of the index
• Add AnotherIntColumn to Index
• Set Database Compatibility Level on SQL Server 2012

It should be noted that the created execution plan is the same for all versions, but only the time of its modification. The plan includes many "Calculate scalar" operations, but I don't see how there could be a problem when the same plan is generated instantly in 2012/2008.

I've only tested it on a few instances of SQL Server 2014 Enterprise and Web editions, but I assume the same behavior will happen in other 2014 editions as well.

I already have several solutions to the problem (changing the index, changing the compatibility level, rewriting the query), but I am curious why there is such a significant performance degradation compared to older versions of SQL Server?