question

sravan.434 avatar image
sravan.434 asked

Advantages and disadvantages of non cluster index

Can any one help me the advantages and disadvantages of using non clustered index
indexes
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder commented ·
Advantages are faster reads, disadvantages are slower writes, updates and more disk storage usage
6 Likes 6 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered
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.
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Scot Hauder avatar image Scot Hauder commented ·
@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.
2 Likes 2 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
@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.
1 Like 1 ·
sravan.434 avatar image sravan.434 commented ·
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
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
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.
0 Likes 0 ·
sravan.434 avatar image sravan.434 commented ·
Thanks Fatherjack, for your reply but we have duplicates in that table i mean repeated columns
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.