Advantages and disadvantages of non cluster index

Question

Can any one help me the advantages and disadvantages of using non clustered index

Answer 1

0

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.

answered Aug 06 '12 at 08:59 AM

Fatherjack ♦♦
38.8k ● 55 ● 69 ● 104

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

Aug 06 '12 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 '12 at 09:58 AM Fatherjack ♦♦

Thanks Fatherjack, for your reply but we have duplicates in that table i mean repeated columns

Aug 06 '12 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 '12 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 '12 at 02:06 PM Grant Fritchey ♦♦

show 2 more replies

(comments are locked)

Advantages and disadvantages of non cluster index

Follow this question

Related Questions