Can any one help me the advantages and disadvantages of using non clustered index
(comments are locked)
|
There is so much that could affect the answers to this question.
Generally however, adding an index will speed up data retrieval (SELECT) operations and slow down data updates(UPDATE and DELETE) operations - just as @scot hauder says in his comment. Thanks Allen for your reply my Table doesn't have clustered index only one non clustered index is there i am using varchar and timestamp data types below is the index i have created
Aug 06, 2012 at 09:21 AM
sravan.434
My advice then would be to look for a clustered index that would be appropriate for the table and then (re)consider NCIs after that.
Aug 06, 2012 at 09:58 AM
Fatherjack ♦♦
Thanks Fatherjack, for your reply but we have duplicates in that table i mean repeated columns
Aug 06, 2012 at 10:45 AM
sravan.434
@sravan you can have a non-unique clustered index on say (SiteID,TankID)...looking at your other question, you will want a non-clustered index on InventoryDate.
Aug 06, 2012 at 11:43 AM
Scot Hauder
@sravan regardless of if you have duplicate values, you can still place a clustered index on the table. They do work better if they're unique, but they don't have to be. You're storing this table as a heap and then adding non-clustered indexes. That is a very inefficient way to do it. Listen to @Fatherjack, he knows what he's talking about.
Aug 06, 2012 at 02:06 PM
Grant Fritchey ♦♦
(comments are locked)
|
Advantages are faster reads, disadvantages are slower writes, updates and more disk storage usage