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, 2012 at 05:06 AM in Default

avatar image

20 7 7 10

Advantages are faster reads, disadvantages are slower writes, updates and more disk storage usage

Aug 06, 2012 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, 2012 at 08:59 AM

avatar image

Fatherjack ♦♦
43.8k 79 102 118

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], 

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)
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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Aug 06, 2012 at 05:06 AM

Seen: 4222 times

Last Updated: Aug 08, 2012 at 12:06 AM

Copyright 2018 Redgate Software. Privacy Policy