Post reply

INTERSECT 1

  • June 11, 2012 at 8:39 pm

    #275663

    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]

  • June 11, 2012 at 11:15 pm

    #1499582

    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

  • June 12, 2012 at 1:48 am

    #1499627

    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/

  • June 12, 2012 at 1:59 am

    This was removed by the editor as SPAM

  • June 12, 2012 at 2:33 am

    #1499648

    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]

  • June 12, 2012 at 6:50 am

    #1499761

    Thanks for the question!

  • June 12, 2012 at 7:56 am

    #1499807

    Good one.

    M&M

  • June 12, 2012 at 8:33 am

    #1499826

    Thanks for the question - cheers

  • June 12, 2012 at 8:55 am

    #1499847

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

  • June 12, 2012 at 9:05 am

    #1499856

    A really good one - thanks, Ron!

  • June 12, 2012 at 9:10 am

    #1499863

    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]

  • June 12, 2012 at 11:43 am

    #1499948

    Great question and dialogue. Thanks!

  • June 13, 2012 at 8:42 am

    #1500417

    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

  • June 18, 2012 at 11:35 am

    #1502356

    Hugo,

    Thanks for the further explanation that supports the correct answer.

  • June 18, 2012 at 11:36 am

    #1502358

    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