About this question
I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap. The problem I have is the clustered index takes about 2 minutes to create: CREATE UNIQUE CLUSTERED INDEX UCX_Idx ON myTable ( IdCol1 ASC ,Col2 ASC ,IdCol4 ASC ,IdCol3 ASC ,Col1 ASC ,Col6 ASC ) ON PS_1 (IdCol1) END But the non-clustered index takes about 1.5 hours to create: CREATE NONCLUSTERED INDEX IX_Idx1 ON myTable ( IdCol1 ASC ,IdCol2 ASC ,IdCol3 ASC ,IdCol4 ASC ,Col1 ASC ) INCLUDE ( Col2 ,DateCol1 ,Col2 ,Col3 ,Col4 ,Col5 ,Col6 ) WITH (SORT_IN_TEMPDB = ON) ON PS_1(IdCol1) END I'm not seeing this behavior with SQL Server 2014 but I am with SQL Server 2016. Same amount of RAM and CPU. I've tried it with out the SORT_IN_TEMPDB = ON but it has a similar problem. I've actually been seeing this in different places in my environment and all with SQL Server 2016 (Standard Edition) installations. How to create nonclustered index in sql server?