Lets explore the topic “Index Join in sql server” starting with “what is index join?”. Index join is a technique which uses an index intersection with two or more indexes to fulfill a query completely. If a table has multiple small index instead of a large covering index, the optimizer can choose index intersection on these small indexes to satisfy a query. In index intersection, it has to perform logical reads on all the indexes being used to serve the query.
If the querying table is large and it has multiple narrow indexes instead of one wide index, index intersection can be used by sql server to fetch the entire data to be returned.
Create a table as below;
CREATE TABLE DemoTable ( EmpID INT, EmpName VARCHAR(100), EmpAddress VARCHAR(500), EmpContact VARCHAR(15), EmpPinCode VARCHAR(15) )
Now we have created a table and as per the next step, we have to … More