Modifying Existing Indexes to add Included Fields

As is not unusual in our profession, we occasionally have weird things that drive us nuts.  I was rewriting a fairly important view and examining the actual query plan after I’d completed my changes but before it went live.  The query had a key lookup: there were three fields that were being pulled from that lookup at a moderate cost to the overall plan.  I decided to change one of the indexes to include the fields in question.

It turns out to not be as simple as I would’ve liked.

First, I tried scripting out the index from SSMS.  And I get an error popup saying ‘Discover dependencies failed’.  Well, that’s not much fun.

Next, I fall back to an old favorite: script the table at the database level.  Right click on the database, select the specific table, script it out to a window.  Find the index that you want, copy it in to a new query window, modify the index.  Execute.  Done.

Of course not.  When I went to run the original query, the key lookup was there.  So I double-clicked on the index, clicked on the Included Columns folder tab, and the columns that I wanted were not there.  I don’t know why, when I executed the code to rebuild the index everything seemed to run without an error, it just didn’t build.  And yes, after modifying the index I did rebuild the index, just to make sure.

But with that Included Columns tab open, I just added the fields that I wanted, and the next time I ran my original query I had a seek instead of a key lookup.

Win.

So the takeaway is that, as we’re fond of saying on SQL Server Central, there’s more than one way to do that.  It’s definitely helpful to know other ways to do things to get it done.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s