SQL Server Notes by AB | Note #29 | Index Column Sort Order | #ABSQLNotes

SQL Server Notes by AB | Note #29 | Index Column Sort Order | #ABSQLNotes

Life is just so easy when you have to deal with single-column indexes. They are lovable. In contrast, multi-column indexes bring so many complexities along with them. Let's dig a bit deeper. In a multi-column index (col1, col2), the data is sorted by the first column. So if you try to order by col2, the optimizer will deploy a SORT operator to sort the data. And of course, if you are ordering by Col1, that data is pre-sorted and the SORT operator is not needed. In a multi-column index, the order of columns is important. Col1, Col2 is not the same as Col2, Col1. Let's say the structure is Col1, Col2. Now if both Col1 & Col2 are used as predicates (they are in your WHERE clause), the engine can seek on Col2 only if it can seek on Col1. Yes, that's a condition and a rule of the optimizer, so to say. If you omit Col1 from your query, you cannot seek on Col2. The optimizer will resort to an Index Scan, not Index Seek. Also, the equality operator plays a critical role here. If both the columns are in the WHERE clause, the optimizer can seek on Col2 only if Col1 is using the equality comparison operator. So there is a concept of partial seek vs full seek. Unfortunately, the execution plan does not depict this clearly. So if the optimizer cannot seek on a column, it is called a residual predicate. Check out a short demo on YouTube: https://www.youtube.com/watch?v=KOi16_bqOhA. Want to read more SQL notes like this one? Here:?https://bit.ly/ABSQLNotes.