Sql-server – Creating Clustered Index uses all the tempdb space. Why

Looks like your clustered index page splits are going to be painful because the clustered index holds the actual data and this will need new pages to be allocated and the data moved to these. This is likely to cause page locking and thus blocking.

Remember also that your clustered index key is 21 bytes and this will need to be stored in all of your secondary indexes as a bookmark.

Have you considered making your primary key identity column your clustered index, not only will this reduce the size of your other indexes, it will also mean that you will reduce the number of page splits in your clustered index. It's worth a try if you can stomach rebuilding your indexes.

OK, enough brain cells are dead.

SQL Fiddle

WITH cte AS
(
  SELECT 
    [ICFilterID], 
    [ParentID],
    [FilterDesc],
    [Active],
    CAST(0 AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters]
  WHERE [ParentID] = 0
  UNION ALL
  SELECT 
    i.[ICFilterID], 
    i.[ParentID],
    i.[FilterDesc],
    i.[Active],  
    Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
  FROM [dbo].[ICFilters] i
  INNER JOIN cte c
    ON c.[ICFilterID] = i.[ParentID]
)

SELECT 
  [ICFilterID], 
  [ParentID],
  [FilterDesc],
  [Active]
FROM cte
ORDER BY [Level];