Q&A
Denny Cherry's Top Tips for Indexing SQL Server Tables
A SQL Server expert gives his take on the absolute, No. 1, very worst thing IT pros can do when it comes to indexing.
Consultant, author and Microsoft MVP Denny Cherry travels the world helping companies implement complex SQL Server projects and fix their toughest issues.
He'll be speaking on several topics at the upcoming SQL Server Live! conference (part of Live! 360) in Orlando in December. We recently got a chance to ask him more about one of his sessions dealing with best practices for indexing SQL Server tables.
Your upcoming session at SQL Server Live!/Live! 360 talks about the "best and worst practices for indexing tables within your SQL Server 2000-2016 database." What is the absolute, No. 1, very worst thing any IT pro can do when it comes to indexing their tables in their database?
Not indexing the tables is the worst thing someone can do. This makes SQL Server do a table scan for every query instead of going directly to the rows that it needs. So performance is, as you'd expect, horrible.
What's a really, really bad thing that many IT pros/DBAs might not realize is so bad when it comes to indexing?
A lot of times, people will create lots of indexes assuming that this will fix the problem. SQL Server will almost always only use one index per table, and the extra indexes are getting updated but never used to look up data. And the indexes that SQL Server is using aren't handling all the user cases correctly.
What is your No. 1 best practice recommendation?
Look at the indexes that need to be added regularly. Indexes change as the application changes.
"Not indexing the tables is the worst thing someone can do. This makes SQL Server do a table scan for every query instead of going directly to the rows that it needs. So performance is, as you'd expect, horrible."
Denny Cherry, Consultant, Author and Microsoft MVP
What is your top favorite new indexing feature in SQL Server 2016 and why?
My favorite feature hands-down is the Query Store. It'll let me easily see the worst offenders on the system so I can see why they are so bad. Then I can add the indexes that are needed to make the query faster.
What is a subtlety one must consider when choosing between clustered and non-clustered indexing that IT pros may not necessarily be aware of?
Ninety-nine percent of the time, you'll be working with non-clustered indexes. Very rarely do changes to the clustered index need to be made.
What about security? What's your favorite security-related feature in SQL Server 2016?
Always Encrypted is probably my favorite security feature of SQL Server 2016. It lets people that don't have DBAs quickly and easily encrypt the data in the database without having to have any understanding of how encryption really works.
For more about Live! 360, go here.
About the Author
Becky Nagel serves as vice president of AI for 1105 Media specializing in developing media, events and training for companies around AI and generative AI technology. She also regularly writes and reports on AI news, and is the founding editor of PureAI.com. She's the author of "ChatGPT Prompt 101 Guide for Business Users" and other popular AI resources with a real-world business perspective. She regularly speaks, writes and develops content around AI, generative AI and other business tech. Find her on X/Twitter @beckynagel.