In an earlier tip I discussed how indexes can speed up processing. It's only fair that I also talk about when indexes don't help.
First: Because indexes don't always help but do always increase the time it takes to update a table (though usually by a negligible amount) you should always run a test. Before applying an index to a table, take a moment to run a query that you think your index will help run faster -- ideally, some query used in production. Run the query twice and measure how long the query takes to complete the second time. Then, after applying your index, run the query twice again and compare the results of the second run.
If you don't get a difference that you can measure with your wrist watch, drop the index: SQL Server has decided that your index isn't helpful and is ignoring it.
Which raises the question: Why is SQL Server ignoring the index? If your table has a lot of rows and SQL Server is ignoring your index, typically the reason is that your index isn't subdividing the table sufficiently to make your index worthwhile to use. If using the index will still require SQL Server to look at "too many" rows, SQL Server will decide it's faster to just do a scan of the whole table to find the rows it needs.
How many is "too many" will vary from one version of SQL Server to another (and other factors, including the size of the table) but SQL Server typically requires an index to return a relatively small percentage of the total number of rows in the table. This number is often as small as 20 percent -- if SQL Server has to look at more than 20 percent of your rows, SQL Server will decide that it's faster just to look at all of the rows in the table. An index on a column holding gender information (where 51 percent are F and 49 percent are M) is probably going to be ignored, for example.
Posted by Peter Vogel on 04/03/2015 at 2:19 PM
With advanced generative AI systems reshaping software development, Microsoft's Mads Kristensen detailed the many ways AI will improve coding in Visual Studio.
With Google recently releasing a generative AI-powered search bot called Bard to rival Microsoft's "new Bing" search experience, we put both to the test, feeding them identical questions about Visual Studio and .NET.
GPT-4, the advanced generative AI model from Microsoft partner OpenAI, is now powering the new GitHub Copilot X and the Azure OpenAI Service.
Microsoft shipped TypeScript 5.0 with new features claimed to make the language smaller, simpler and faster.
A new tool that can generate code is being previewed in the Visual Studio Code Insiders channel seeks to ease the tedious data preparation process that data scientists need to go through to get good data for successful analysis projects.
> More Webcasts