asked
Non-Clustered taking more space then Clustered
In theory, I read that Clustered Index takes more space than Non-Clustered. But when I tried it on two tables both having same records (upto 1 million) one with Clustered index on Id column and another table with Non-Clustered index. When I check using SP_SPACEUSED, table with Non-Clustered index is showing more index_size as compared to table with Clustered index. Any idea why? Create table table_cl_idx(c1 int) Create table table_noncl_idx(c1 int) declare @i int=1 while @i<10000 begin insert into table_cl_idx select @i insert into table_noncl_idx select @i set @i=@i+1 end create clustered index idx_table_cl_idx on table_cl_idx(c1) create nonclustered index idx_table_noncl_idx on table_noncl_idx(c1) exec sp_spaceused table_cl_idx --16 KB index size exec sp_spaceused table_noncl_idx --208 KB KB index sixe
Your NONcluster index , because it is on a heap table (second table), will store an additional column - the key of the heap table - a RowID. DBCC IND('XXX',table_noncl_idx,-1) GO DBCC TRACEON(3604,-1) Go DBCC PAGE ('xxx',1,382,3)-- WITH TABLERESULTS GO DBCC TRACEOFF(3604,-1) GO where 382 is a page number from the noncluster index and xxx is the database name also , you can check with dm_db_index_physical_stats SELECT index_id,index_type_desc,index_level ,min_record_size_in_bytes, max_record_size_in_bytes,avg_record_size_in_bytes FROM sys.dm_db_index_physical_stats(DB_ID('XXX') ,OBJECT_ID('table_noncl_idx'),2,NULL,'DETAILED') SELECT index_id,index_type_desc,index_level ,min_record_size_in_bytes, max_record_size_in_bytes,avg_record_size_in_bytes FROM sys.dm_db_index_physical_stats(DB_ID('XXX'),OBJECT_ID('table_cl_idx'),1 ,NULL,'DETAILED')
16 People are following this question.
