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
385 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

776 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