Solved

What is the fastest way to remove the index on Column A and C but keep the index on column B

Posted on 2011-02-24
4
384 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:tesla764
  • 2
4 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 34971382
use
drop index

this will drop indexes one by one...
0
 
LVL 26

Expert Comment

by:tigin44
ID: 34971421
an example syntax for drop index is

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tableName]') AND name = N'indexName')
DROP INDEX [ndexName] ON [dbo].[tableName] WITH ( ONLINE = OFF )
GO
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 34971489
If you have an index on columns A,B,C and you want it only on column B then do following:

Add new index with ONLINE = ON on column B so you don't lock the table; you may want to look at SORT_IN_TEMPDB = ON and MAXDOP = 1 if you have a huge table indeed and is used on line.

update statistics table_name;
exec sp_recompile table_name;

drop index  idx_columns_a_b_c  on table_name;

update statistics table_name;
exec sp_recompile table_name;
0
 

Author Closing Comment

by:tesla764
ID: 34971846
Thanks, that worked.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now