Why Non-Clustered Index takes much longer to create than clustered Index?

Create Clustered Index

When you create a clustered index, you are actually sorting the data in the table in the order of the clustered index you are creating. They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another time. A long time from now. When I’ve raised an army, in accordance with ancient prophecy.

Create Nonclustered Index In Sql Server-+

The column order of the clustered index is totally different from the order of the columns in the non-clustered index, which has a direct impact on the creation of the index. The database engine has to search through your clustered index multiple times until it has all the information together in the correct order to create the non-clustered index. That is why it is taking so long to create the non-clustered index. Then there is the overhead that a clustered index is always part of the non- clustered index.

Differences between SQL Server 2014 and SQL Server 2016

As for the difference in index creation on the various versions: Are you really comparing apples with apples and not with pears? Or to put it differently: Are the SQL Server 2014 servers running on identical hardware with the same CPU core frequency, with the same amount of RAM and identical MAX_DOP settings? There are some limitations/restrictions regarding index creation when using Standard or Enterprise Edition..

Enterprise vs. Standard Edition

Parallel index operations are not available in every SQL Server edition. For more information, see Features Supported by the Editions of SQL Server 2016[...] Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:

• CREATE INDEX
  
• ALTER INDEX REBUILD
  
• DROP INDEX (This applies to clustered indexes only.)
  
• ALTER TABLE ADD (index) CONSTRAINT
  
• ALTER TABLE DROP (clustered index) CONSTRAINT
  

If you then have a quick look at the features for SQL Server 2014 and 2016 then both editions limit MAXDOP for Standard Edition: SQL Server 2014 - Editions and supported features of SQL Server 2016 - RDBMS Manageability Enterprise Standard Parallel indexed operations Yes -  SQL Server 2016 - Editions and supported features of SQL Server 2016 - RDBMS Manageability Enterprise Standard Parallel indexed operations Yes No This option has a direct impact on index creation and this is probably the reason why you are seeing great differences in index creation time.