SQL Server Notes by AB | Note #23 | Multi-Column Index Seek | #ABSQLNotes

SQL Server Notes by AB?| Note #23 | Multi-Column Index Seek | #ABSQLNotes

In SQL Server, you can create single-column and multi-column indexes. Life is beautiful and easy with single-column indexes and full of ups & downs with multi-column indexes. With single-column indexes, SQL Server has to deal with only one column - just go to a value in the column and start seeking from that point. With mult-column indexes, seeking happens from left to right. Let's say you have a three-column index on Customer table, columns being FirstName, LastName & EmailAddress. First things first, data is sorted by these three columns, exactly in the specified order. Therefore FirstName, LastName, EmailAddress is not the same as FirstName, EmailAddress, LastName - the structure and storage, both change. Next, let's talk about seeking. In a multi-column index, SQL Server will seek from left to right. This means if you wish to seek on LastName, you have to seek on FirstName too. That's the catch. SQL Server cannot directly jump to LastName bypassing FirstName. Likewsie, if it has to seek on EmailAddress, it has to seek on FirstName & LastName also. Remember, left to right. So what if you put only the LastName in the predicate. Let's say your query looks like SELECT * FROM CUSTOMER WHERE LastName = 'Bansal' - what will SQL Server do now? Can it seek? No it cannot. So? Yeah, you guess it right. It will scan the entire index. Demo URL: https://www.youtube.com/watch?v=nUpZRx7Sgdg. Want to read more SQL notes like this one? Here: https://bit.ly/ABSQLNotes.