Advantages and disadvantages of non cluster index
Can any one help me the advantages and disadvantages of using non clustered index
Advantages are faster reads, disadvantages are slower writes, updates and more disk storage usage
There is so much that could affect the answers to this question.
> Does the table have a **clustered** index already?
> How many other indexes are there on the table?
> Do you need to add this index or alter an existing one?
> What datatypes are going to be in this index?
> How many columns will be in it, and how many included columns will be in it?
> How often does the query run that needs this index?
> What are the details of the table - How many rows, How many columns?
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 CREATE NONCLUSTERED INDEX [Inx_SiteTank] ON [dbo].[SiteTankInventory] ( [InventoryDate] ASC ) INCLUDE ( [SiteID], [TankID], [InventoryTime], [InventoryGross], [InventoryNet], [ProductHeight], [Ullage], [WaterHeight], [WaterVolume], [Temperature]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO
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.
Thanks Fatherjack, for your reply but we have duplicates in that table i mean repeated columns
@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.
@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.
