Earlier I wrote two blog post on the topic of COUNT(*) and Index and today I have to build a video on that topic as many of you really wanted.
Count Rows in a Heap: Which Scan Does SQL Server Use?
Count rows in a heap and SQL Server scans every page. See the plan change with a clustered and a narrow index, and why a heap does not compact its pages.
Count Distinct in Access: Use a Subquery in FROM
Count distinct in Access with a subquery in the FROM clause, because Access SQL does not accept COUNT(DISTINCT). Includes NULL and multi-column cases.
SQL SERVER – Identity Column is Difficult to Remove
The IDENTITY property is difficult to remove through a column alteration because SQL Server doesn’t support that operation. Existing values need protection.
Index Used for COUNT(*): SQL Server Scans the Narrowest One
The index used for COUNT(*) is the narrowest one that holds every row. See the pages, the scans and the reads that prove it in SQL Server.
Index Scans – Good or Bad? – SQL in Sixty Seconds #174
Index Scans – Good or Bad? Lots of people ask about this question and I have built a blog post on the topic – SQL SERVER – Index Scans are Not Always Bad.
SQL SERVER – Index Scans are Not Always Bad
Are Index Scans Bad? The answer No. Index Scans are Not Always Bad. This was in-depth discussed with my client during the Health Check.