fastest way to remove index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

fastest way to remove index

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-02-17 : 13:51:28

I have a table with columns A, B, and C, where a unique clustered index exists on column A, a unique non-clustered index exists on Column B, and a non-unique non-clustered index exists on column C. Assuming there are 100 million records in this table, what is the fastest way to remove the index on Column A and C but keep the index on column B? (You can do it any way you like.) Please give a short description on why as well

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 13:59:08

Why you need to delete clustered index? It holds pointers for all non-clustered indexes. Deleting Clustered index means you are locking whole table during the deletion process and your table is heap. Heap table means you can't remove fragmentation of indexes. So avoid that thought to delete clustered index. Well you can delete non-clustered index with no issue.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 14:05:27

If its just a hypothetical question, I'd think deleting non-clustered and then deleting clustered index will be faster. Since, deleting clustered index would involve rebuilding of non-clustered indexes.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-17 : 14:05:30

Are these homework or interview questions?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-02-17 : 14:31:18

its a interview quesstions:

quote:
Originally posted by tkizer

Are these homework or interview questions?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2009-02-18 : 01:20:46

can anyone help?

quote:
Originally posted by sent_sara

I have a table with columns A, B, and C, where a unique clustered index exists on column A, a unique non-clustered index exists on Column B, and a non-unique non-clustered index exists on column C. Assuming there are 100 million records in this table, what is the fastest way to remove the index on Column A and C but keep the index on column B? (You can do it any way you like.) Please give a short description on why as well

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 08:57:29

I guess we have already answered your question.

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-18 : 21:20:44

Funny

quote:
Originally posted by sent_sara

I have a table with columns A, B, and C,

where a unique clustered index exists on column A

a unique non-clustered index exists on Column B

and a non-unique non-clustered index exists on column C.

Funny, no need to mention the other indexes are non clustered, if the first one is clustered...you're only allowed 1.

quote:

Assuming there are 100 million records in this table,

what is the fastest way to remove the index on Column A and C

but keep the index on column B?

I guess I would bcp the data out

Truncate the rows

Drop the Indexes, all of them

bcp the data in

CREATE the indexes you want

However, if b is unique, then I'd cluster it anyway

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources