CODEWITHSUNDEEP
sql-serverindexing
Gina
Gina

Reputation: 41

Filters on existing index

I have a homework assignment where I have a nonclustered index. Later on in the assignment, I have to add a filter to the index. Is it possible to add a filter to an existing index?

This is my assignment and the order I have to do them in. In the end they all have to run as a single script.

  1. Create an index following naming conventions on the transaction type column. It is used in ascending order.

  2. Alter the index to have a fill factor of 80. We opt for not keeping the index online, we want full optimization.

  3. We are actually only looking at transactions of type 'W', add a filter to the index.

  4. Drop the index

For #3 do I have to drop the index I created in #1 add a new one with a filter and then drop it again in #4?

Upvotes: 2

Views: 612

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

For #3 do I have to drop the index I created in #1 add a new one with a filter and then drop it again in #4?

SQL Server allows one to recreate an existing index using the syntax CREATE INDEX...WITH(DROP EXISTING=ON). This method avoids the need to drop the existing index beforehand and improves performance of the index recreate by leveraging the sort order of the original index. The WITH clause also provides additional options to meet your online requirement. See the documentation link above for details.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

You can drop and create the same index again with added filter options in the Index. Click this for more details.

Upvotes: 0

Related Questions