SQL Server：带SORT_IN_TEMPDB的主键创建及非聚集索引问题咨询

Hey there, it sounds like you've got a solid standardized script for rolling out non-clustered indexes across your tables, and most are running smoothly—great call on locking down those WITH clause options! When one table breaks this pattern, it's almost always due to a specific edge case your general script doesn't account for. Let's walk through the most likely culprits and fixes:

1. ONLINE = ON is hitting compatibility limits #

That ONLINE = ON flag is perfect for minimizing downtime, but it has strict rules depending on your SQL Server version and table structure:

• Heap tables (no clustered index): Prior to SQL Server 2019, you can't create non-clustered indexes online on heaps. Even in 2019+, you need to set your database compatibility level to 150 or higher to use this feature.
• Tables with LOB columns: If the problem table has columns like VARCHAR(MAX), NVARCHAR(MAX), XML, or VARBINARY(MAX), online index creation fails in SQL Server 2016 and earlier.

How to check & fix: #

• Verify if it's a heap:

  SELECT OBJECTPROPERTY(OBJECT_ID('prt.ProblemTable'), 'IsHeap') AS IsHeap
  

  If it returns 1, either temporarily set ONLINE = OFF for this index, or consider adding a clustered index to the table long-term (heaps have other performance quirks too).
• Check for LOB columns:

  SELECT name AS LOBColumnName 
  FROM sys.columns 
  WHERE object_id = OBJECT_ID('prt.ProblemTable') 
    AND max_length = -1
  

  If you find LOB columns and can't upgrade SQL Server, flip ONLINE = OFF for this specific index creation.

2. DROP_EXISTING = ON is conflicting with an existing index #

If there's already an index named IX_blah_blah_blah on this table, but it's a different type (e.g., clustered instead of non-clustered) or has a conflicting definition, DROP_EXISTING = ON will throw an error instead of replacing it.

How to check & fix: #

• List all indexes on the table:

  SELECT name, type_desc 
  FROM sys.indexes 
  WHERE object_id = OBJECT_ID('prt.ProblemTable')
  

  If the target index name exists and is the wrong type, either:
  • Manually drop it first: DROP INDEX IX_blah_blah_blah ON prt.ProblemTable;
  • Or rename your new index to something unique (e.g., IX_ProblemTable_blahID)

3. Lock conflicts or permission issues #

Sometimes the issue isn't with the script itself, but with the environment:

• Double-check that your account has ALTER permissions on prt.ProblemTable
• Check if another session is holding locks on the table (blocking your index creation):

  SELECT 
      request_session_id AS SessionID,
      resource_type AS LockType,
      resource_description AS LockDetails,
      request_mode AS LockMode
  FROM sys.dm_tran_locks
  WHERE resource_associated_entity_id = OBJECT_ID('prt.ProblemTable')
  

  If you see active locks, you might need to wait for the blocking process to finish, or terminate it (with caution!)

4. TempDB space issues (if using SORT_IN_TEMPDB = ON) #

Your script uses SORT_IN_TEMPDB = ON, which offloads index sorting to tempdb. If tempdb is out of space, the index creation will fail—especially if the problem table is large.

How to check: #

SELECT 
    name AS TempDBFile,
    ROUND((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0), 2) AS AvailableSpaceInMB
FROM sys.master_files 
WHERE database_id = DB_ID('tempdb')

If space is tight, you can either expand tempdb files or temporarily set SORT_IN_TEMPDB = OFF for this table.

Example adjusted script for heap/LOB scenarios: #

CREATE NONCLUSTERED INDEX IX_ProblemTable_blahID ON prt.ProblemTable ([blahID]) 
WITH (
    PAD_INDEX = ON, 
    STATISTICS_NORECOMPUTE = OFF, 
    SORT_IN_TEMPDB = ON, 
    IGNORE_DUP_KEY = OFF, 
    DROP_EXISTING = ON, 
    ONLINE = OFF, -- Temporarily disabled for heap/LOB compatibility
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON, 
    FILLFACTOR = 90
);

内容的提问来源于stack exchange，提问作者Joe