Sample Header Ad - 728x90

SQL server Cardinality Estimation for LIKE query

5 votes
1 answer
346 views
I have this statistics histogram vector for my non-clustered index made on LastName column of a table named AspNetUsers. enter image description here If I run a query as SELECT * FROM dbo.AspNetUsers WHERE LastName = 'Baker' it returns 6 rows as estimated rows, cause **Baker** is the **RANGE_HI_KEY** of the one of the step so the EQ_ROWS value is my Estimated rows count. Similarly, If i run a query as SELECT * FROM dbo.AspNetUsers WHERE LastName = 'Bacilia', it returns 1 row as estimated rows, cause **Bacilia** fells in to 'Baker' step range, so the **AVG_RAGE_ROWS** value of that step is my estimated rows count. Similarly, to my understanding if i do query as SELECT * FROM dbo.AspNetUsers WHERE LastName LIKE 'Ba%' it matches 2 steps (**Baker** and **Batagoda**), so it should return 27 + 51 (RANGE_ROWS) + 6 + 4 (EQ_ROWS) = 88. But it returns 99 rows as estimation. How does this Cardinality Estimation is works with a LIKE Query? Does it use different formulae for estimating number of rows when doing a LIKE query?
Asked by Dhanuka Jayasinghe (185 rep)
Feb 5, 2023, 10:52 AM
Last activity: Feb 5, 2023, 06:11 PM