July 15, 2010 at 9:58 am
Assume that I have two tables one with Clustered index and other table with Non Clustered Index. Now I want to insert 1 million rows in both the table Then which table will perform faster (Is it Clustered index table or Non clustered Index table) and why?
July 15, 2010 at 10:07 am
Probably the clustered index, since the nonclustered is a second copy of the data that would have to be separately maintained.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2010 at 10:15 am
I'm not clear on your reason. Could you explain little further.Assume that the data inserting in clustered & non clustered index tables are different.
July 15, 2010 at 10:19 am
The clustered index is the table, so the table with the cluster there's only one copy of the data.
The second table has only a nonclustered index, that means there's two structures, the heap (the table itself) and the nonclustered index. Hence two copies of the data. Hence likely to take longer to insert into.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2010 at 10:23 am
Got it. Thank you for your response
July 15, 2010 at 10:43 am
Not to mention that even if you index a very large table with a nonclustered index, if you omit a clustered index from that table, the nonclustered index may not be a huge help. If the underlying table is a heap, it takes more work for the processor to walk from the index leaf to the actual row, whereas if there is a clustered index the data is arranged in a predetermined order.
July 15, 2010 at 10:51 am
Clear and Nice. Appreciated!!
July 15, 2010 at 11:03 am
jeff.mason (7/15/2010)
If the underlying table is a heap, it takes more work for the processor to walk from the index leaf to the actual row, whereas if there is a clustered index the data is arranged in a predetermined order.
If the base table is a heap, the nonclustered index has the actual physical RID as a location for the data row. Hence it can go straight to the page that the row is on and fetch it.
Order of data is irrelevant if the location of the row is known (which it will be with a heap)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 3:13 pm
Hi Gail,
Can you please explain, when can I use Non Clustered Index more effectively.
Ram..
🙂
July 16, 2010 at 3:27 pm
Most oftenly Nonclustered indexes are created on a columns where those are columns are heavily used in query's to improve the query performance. especially where Clustered index columns are not used.
July 16, 2010 at 3:29 pm
Have a read through this, see if it helps - http://www.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 16, 2010 at 3:49 pm
Gail..
Your article is very clear for me.. thanks
Raju.. Thanks for your reply..
🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply