Clustered Indexes Have Statistics Too

SQL Server, T-SQL
It may seem obvious, but I've heard more than one person suggest to me that statistics on a clustered index just don't matter. That if the clustered index can satisfy a given query, it's going to get selected. That just didn't make any sense to me, but I haven't seen anyone set up a test that shows how it might work one way or the other. Here you go. First, I'm going to create a table and load it up with data. I'm intentionally using strings because I don't want to confuse the ease of management of integers within indexes. I also went for one column that would have a very attractive set of statistics and one that would have a very ugly set. Also, because we're only dealing with…
Read More

Which SELECT * Is Better?

SQL Server, T-SQL
The short answer is, of course, none of them, but testing is the only way to be sure. I was asked, what happens when you run ‘SELECT *’ against a clustered index, a non-clustered index, and a columnstore index. The answer is somewhat dependent on whether or not you have a WHERE clause and whether or not the indexes are selective (well, the clustered & non-clustered indexes, columnstore is a little different). Let’s start with the simplest: SELECT    * FROM    Production.ProductListPriceHistory AS plph; This query results in a clustered index scan and 5 logical reads. To do the same thing with a non-clustered index… well, we’ll have to cheat and it’ll look silly, but let’s be fair. Here’s my new index: CREATE NONCLUSTERED INDEX TestIndex ON Production.ProductListPriceHistory (ProductID,StartDate,EndDate,ListPrice,ModifiedDate); When I…
Read More

SQL University–Recommendations for a Clustered Index

SQL Server, T-SQL
Welcome, SQL University Students to another extension class here at Miskatonic University, home to the Fighting Cephalopods (GO PODS!). Never mind the stains on the floor, or the walls…or those really nasty ones on the ceiling. There was a… oh what did the dean call it… an incident last week when one of the students had a little accident after reading Die Vermiss Mysteriis one too many times. But we’re not here to talk about arcane tomes and unspeakable horrors today. No, today we’re here to talk about clustered indexes. SQL Server storage is really predicated around the idea of clustered indexes. Don’t believe me? Let’s list a few places that require a clustered index: Partitioning. A table in SQL Azure In order to create XML indexes What about the…
Read More