Q

Adding non-clustered indexes to SQL Server columns

by:
Jeremy Kadlec
Edgewood Solutions

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.

Related Q&A; from Jeremy Kadlec, Contributor

View query execution plan in SQL Server with this method

Learn how to view a query execution plan in SQL Server with SQL Server Management Studio or Query Analyzer. Review and decrease query plan sub tree ...continue reading

Change collation to SQL Server 2005 after upgrading databases

Want to change the collation to SQL Server 2005 standards after upgrading databases from SQL Server 2000? Learn the best approach involving DTS and ...continue reading

Is my SQL Server 2000 optimized?

continue reading

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.

Meet all of our SQL Server experts

View all SQL Server questions and answers

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close