INTERSECT 1

  • Comments posted to this topic are about the item INTERSECT 1

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Nice question.

    Link to the INTERSECT page:

    http://msdn.microsoft.com/en-us/library/ms188055(SQL.105).aspx

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Thanks for the question, Ron. And thanks Koen for the extra link.

    In case someone is wondering why the different data types have no effect - Ron included the relevant link, but didn't explicitly describe this in his explanation, so I will. The data types are compared to find the one with the highest precedence. It's char(2) vs bigint, so if you check Ron's link, you'll see that bigint wins. SQL Server then checks to see if implicit conversion is allowed for char to biging; this is the case, so the query compiles and a plan is created. During execution time, all char(2) values are converted to bigint, and then the INTERSECT operation is carried out.

    If you change one of the values in the char(2) column to be something that doesn't convert to biging (e.g. 'a'), you'll get a run-time error.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • This was removed by the editor as SPAM

  • Hugo Kornelis (6/12/2012)


    Thanks for the question, Ron. And thanks Koen for the extra link.

    In case someone is wondering why the different data types have no effect - Ron included the relevant link, but didn't explicitly describe this in his explanation, so I will. The data types are compared to find the one with the highest precedence. It's char(2) vs bigint, so if you check Ron's link, you'll see that bigint wins. SQL Server then checks to see if implicit conversion is allowed for char to biging; this is the case, so the query compiles and a plan is created. During execution time, all char(2) values are converted to bigint, and then the INTERSECT operation is carried out.

    If you change one of the values in the char(2) column to be something that doesn't convert to biging (e.g. 'a'), you'll get a run-time error.

    Thanks for the additional explanation which you have explaned in a clear simple manner something one does not often find in BOL ... I am sure it will be appreciated by all those who read your posting.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • Thanks for the question!

  • Good one.

    M&M

  • Thanks for the question - cheers

  • Interesting! Thanks for the question, and thanks for the additional explaination of the type conversion Hugo!

  • A really good one - thanks, Ron!

  • Really nice question. Thanks, Ron.

    Hugo, thank you for the additional explanation, too. I would have not been able to understand it just by reading BOL.

    "El" Jerry.

    "El" Jerry.

    "A watt of Ottawa" - Gerardo Galvan

    To better understand your help request, please follow these best practices.[/url]

  • Great question and dialogue. Thanks!

  • Nice tidy question, that brings up two importan fundamentals:

    (1) intersection returns distinct values not duplicates

    (2) equality tests do implicit conversion where needed, from lower precedence type to higher.

    Tom

  • Hugo,

    Thanks for the further explanation that supports the correct answer.

  • Nice question on the topic.

    Thanks.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply