Dropping a Non clustered Index

Question

Dropping a Non clustered Index

Saumil A

Mr or Mrs. 500

Points: 595
More actions
June 20, 2005 at 3:05 pm

#66291

Hello,
I am working on dropping unnecessary Non Clustered Indexes on tables in my database. As part (1 of the columns) of these NC indexes, the clustered index (only 1 column Clustered Index) for that table is also included. This Clustered Index column is not the leading column in the index so no performance benefit.
I have some questions:
1. Are there any considerations to look at before dropping the NC index because it contains Clustered Index column? (Already researched possibilities of where this NC index may be an advantage such as covering queries etc.)
2. Can I then drop the NC index with the DROP INDEX statement? I know you cannot drop a Clustered Index on PRIMARY using DROP INDEX and you have to use ALTER TABLE. Is this also true for a NC Index that has as one of its columns - the clustered index column?
3. Sample Code
Thanks in advance.
-Saumil.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

1. Not really the fact is that it still points to the clustered no materr how many columns or which it contains.

2.Sure! the reason for the alters is that those indexes are created behind the scene to enforce constraints then all you do is drop the constraint!!

3. drop index [tablename].[indexname]

hth

* Noel

Saumil A Mr or Mrs. 500 Points: 595 More actions · Answer 2

Thanks a lot for the information.

Will it have a performance impact if an ORDER BY uses the column that has the non clustered index? I will be dropping this non clustered index.

Thanks,

-Saumil.

noeld SSC Guru Points: 96590 More actions · Answer 3

I may depending on how much weight the order by have in the Total Query cost: ie.: if the order by operates in small resultant set it may be not a big deal, but if it operates in a large one you may observe a degradation in performance.

When looking at indexes this is the order of importance:

1. Where

2. Joins

3. Group by

4. Order by

hth

* Noel

Saumil A Mr or Mrs. 500 Points: 595 More actions · Answer 4

Saumil A

Mr or Mrs. 500

Points: 595

June 21, 2005 at 10:50 am

#567145

Wonderful Thanks.

noeld SSC Guru Points: 96590 More actions · Answer 5

noeld

SSC Guru

Points: 96590

June 21, 2005 at 12:03 pm

#567165

Glad to help!

* Noel

Saumil A Mr or Mrs. 500 Points: 595 More actions · Answer 6

Sorry another question popped - I was just told that I need to alter the non clustered index and not drop it.

So here's what I need to do:

Table has 2 indexes - one clustered (one col - say X) and one non clustered (2 cols - say A and X). I need to drop column X (the clustered index col) from the non clustered index.

How do I do this - DROP and Create Index or is there someway to merely ALTER it?

Thanks,

-Saumil.

noeld SSC Guru Points: 96590 More actions · Answer 7

you could drop it and then recreated with the new columns

or

create index idx_Name on Tablename ( ColA ) with drop_existing --keep same index name

* Noel

Saumil A Mr or Mrs. 500 Points: 595 More actions · Answer 8

Hi,

I am getting a very wierd error in the process of dropping and re-creating indexes:

Server: Msg 1505, Level 16, State 1, Line 1

CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 3. Most significant primary key is 'High'.

The statement has been terminated.

This is the SQL i am using:

IF EXISTS (SELECT s.name FROM dbo.sysindexes s, sysobjects o

WHERE s.name = 'ixDesc' and s.id = o.id and o.name not like 'S%_C%')

begin

DROP INDEX dbo.TABLE.ixDesc

******Create unique nonclustered index ixDesc on TABLE(DESC)

end;

the create statement gives the above error. Note that the column I am creating new NC index on is NOT a primary key or a candidate. Also there is no column named High in this TABLE.

Please help!!!

-Saumil.

Saumil A Mr or Mrs. 500 Points: 595 More actions · Answer 9

P.S. to above question:

the original NC index had 2 columns - one of which was the primary key. I am re-creating the index with 1 column and dropping the primary key column from the NC index.

noeld SSC Guru Points: 96590 More actions · Answer 10

from you previous post:

Create unique nonclustered index ...

The error you are getting indicates that you have DUPLICATES on a column that you are specifiying as unique

You must Either "check the data" or remove the "unique" requirement from the index creation clause

hth

* Noel

Saumil A Mr or Mrs. 500 Points: 595 More actions · Answer 11

HOLY ...! wow! That never struck me!

So you are saying that the table TABLE has duplicates on column DESC that I am now trying to create a UNIQUE NON CLUSTERED index on. Earlier, since the PRIMARY key was part of the index, this error did not appear.

Oh wow! So I either have to clean the data or make this a non-unique index. Is there a downside to a NC index not being UNIQUE?

Thanks a bunch. whew. This was bothering me for 1 whole day now.

-Saumil.

noeld SSC Guru Points: 96590 More actions · Answer 12

PRECISELY!

the downside side is that selectivity will be affected because you are going to have some duplicates. If the duplicates are a lot ex: a column with on very few possible values you may be better off just droping the index because it will be of no use for the optimizer but if you have not that many duplicates then you will be ok

hth

* Noel

Dropping a Non clustered Index

Cookies on SQLServerCentral