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?
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation