Problem solve Get help with specific problems with your technologies, process and projects.

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.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.