x

Advantages and disadvantages of non cluster index

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

asked Aug 06 '12 at 05:06 AM in Default

sravan.434 gravatar image

sravan.434
20 7 7 9

Advantages are faster reads, disadvantages are slower writes, updates and more disk storage usage
Aug 06 '12 at 05:18 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Aug 06 '12 at 08:59 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 73 77 107

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 ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x79

asked: Aug 06 '12 at 05:06 AM

Seen: 1334 times

Last Updated: Aug 08 '12 at 12:06 AM