August 9, 2018 at 3:09 am
Can anybody tell me the difference b/w index join and index intersection.
August 9, 2018 at 6:24 am
arvind.patil 60171 - Thursday, August 9, 2018 3:09 AMCan anybody tell me the difference b/w index join and index intersection.
Is that from Microsoft SQL Server? Or are you using some other product?
August 9, 2018 at 7:40 am
Its is for Microsoft SQL Server 2014
August 9, 2018 at 9:19 am
arvind.patil 60171 - Thursday, August 9, 2018 3:09 AMCan anybody tell me the difference b/w index join and index intersection.
They are the same thing with two different names. An index intersection is simply when a where clause uses two different indexes to return the rows. A typical example would be looking up a persons name with both LastName and FirstName when there is an index on each column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2018 at 9:44 am
Sean Lange - Thursday, August 9, 2018 9:19 AMarvind.patil 60171 - Thursday, August 9, 2018 3:09 AMCan anybody tell me the difference b/w index join and index intersection.They are the same thing with two different names. An index intersection is simply when a where clause uses two different indexes to return the rows. A typical example would be looking up a persons name with both LastName and FirstName when there is an index on each column.
Almost correct, one is SQL speak and the other is Set speak for the same thing.
😎
August 9, 2018 at 9:55 am
Eirikur Eiriksson - Thursday, August 9, 2018 9:44 AMSean Lange - Thursday, August 9, 2018 9:19 AMarvind.patil 60171 - Thursday, August 9, 2018 3:09 AMCan anybody tell me the difference b/w index join and index intersection.They are the same thing with two different names. An index intersection is simply when a where clause uses two different indexes to return the rows. A typical example would be looking up a persons name with both LastName and FirstName when there is an index on each column.
Almost correct, one is SQL speak and the other is Set speak for the same thing.
😎
What is "Set speak"?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2018 at 10:27 am
Sean Lange - Thursday, August 9, 2018 9:55 AMEirikur Eiriksson - Thursday, August 9, 2018 9:44 AMSean Lange - Thursday, August 9, 2018 9:19 AMarvind.patil 60171 - Thursday, August 9, 2018 3:09 AMCan anybody tell me the difference b/w index join and index intersection.They are the same thing with two different names. An index intersection is simply when a where clause uses two different indexes to return the rows. A typical example would be looking up a persons name with both LastName and FirstName when there is an index on each column.
Almost correct, one is SQL speak and the other is Set speak for the same thing.
😎What is "Set speak"?
Set theory or in other words set theory based speak, not the technology (SQL) 😉
😎
August 9, 2018 at 11:07 am
Reference / URL for where you see this?
August 9, 2018 at 12:33 pm
Steve Jones - SSC Editor - Thursday, August 9, 2018 11:07 AMReference / URL for where you see this?
I found several. Here are a few.
https://www.brentozar.com/archive/2016/06/lets-make-match-index-intersection/
https://www.databasejournal.com/features/mssql/article.php/1438821/Using-Index-Intersection.htm
http://sqlblog.com/blogs/kalen_delaney/archive/2013/02/14/index-intersection.aspx
https://sqlperformance.com/2014/01/sql-plan/starjoininfo-in-execution-plans
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 9, 2018 at 2:02 pm
I have examples in my book.
It's actually hard to get these things. You won't see them very often, The optimizer almost seems to have a bias against making them happen. However, you will see a join between two indexes instead of a key/rid lookup if it can find the exact right indexes that both satisfy the query and the filtering requirements.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 9, 2018 at 5:03 pm
Sean Lange - Thursday, August 9, 2018 9:55 AMEirikur Eiriksson - Thursday, August 9, 2018 9:44 AMSean Lange - Thursday, August 9, 2018 9:19 AMarvind.patil 60171 - Thursday, August 9, 2018 3:09 AMCan anybody tell me the difference b/w index join and index intersection.They are the same thing with two different names. An index intersection is simply when a where clause uses two different indexes to return the rows. A typical example would be looking up a persons name with both LastName and FirstName when there is an index on each column.
Almost correct, one is SQL speak and the other is Set speak for the same thing.
😎What is "Set speak"?
More interesting question: which name is "Set speak" and which "SQL speak"?
Anyway, they aren't both the same thing, because "Set speak" ensures the absence of duplicates (look at the defnition of "set" - try something a lot less advanced than Halmos' "Naive Set Theory" if you thinks sets can have duplicates), while "SQL Speak" allows duplicates (and will deliver derived tables with duplicates) because SQL doesn't require tables (whether base or derived) even to be 1NF let alone to have no duplicate rows.
Tom
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply