Ask a Question
Ask Question Login
Corporate Training
  1. Community
  2. SQL Server
  3. Question
SQL Server

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

Asked by Angela Baker Jul 19, 2021 280 views 1 answer
Share

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?

Your answer

1 Answer

More SQL Server discussions

Learn & Explore

Free tutorials and interview questions from industry experts — learn the skill, then get ready to prove it.

Latest SQL Server Blogs

Guides, tips and career advice on SQL Server from JanBask experts.