Q

Adding non-clustered indexes to SQL Server columns

Adding non-clustered indexes to SQL Server columns and tables frequently queried is possible. Learn how to add non-clustered indexes to SQL Server databases.

I have a massive customer table. The id is the primary key and thus SQL Server makes a clustered index on that. There is no other index on the table. Can I add an index on fields like age, country and gender to make the results faster. We have pages querying the table with these columns in the WHERE clause a lot, so the table gets updated and written to often. I'm worried that making a non-clustered index on these fields will affect performance. What would you advise?
Yes, you can add non-clustered indexes to those additional columns if you are querying (WHERE, ORDER BY, GROUP BY, etc.) or JOINing based on those columns. Just keep in mind that you can only have one clustered index and up to 255 non-clustered indexes.

In SQL Server 2005, you also have the ability to add columns to your indexes to have indexes on columns that were traditionally not indexable, such as columns with the text data type. In addition, at some point adding too many indexes into a database with a large number of insert, update or delete transactions will degrade your performance, so choose wisely.

This was first published in December 2007

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close