Post reply

Partition elimination and variables

  • May 18, 2016 at 11:12 pm

    #307834

    Comments posted to this topic are about the item Partition elimination and variables

    God is real, unless declared integer.

  • May 19, 2016 at 12:33 am

    This was removed by the editor as SPAM

  • May 19, 2016 at 12:35 am

    #1880094

    Interesting question, thanks.

    ...

  • May 19, 2016 at 1:58 am

    #1880109

    Good question!

    Thanks.

  • May 19, 2016 at 7:04 am

    #1880215

    Very interesting question.

    Thank you for posting this!

  • May 19, 2016 at 10:05 am

    #1880299

    I got this right by guessing but still have no idea why it is the correct answer. Thanks for the question and reference, though, I need to read up on it!

    - webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • May 19, 2016 at 10:31 am

    #1880302

    When the variable is INT, there are two seek keys on the clustered index seek:

    Seek Keys[1]: Start: PtnId1000 >= Scalar Operator((1)), End: PtnId1000 <= Scalar Operator((4))

    Seek Keys[2]: Prefix: [Sandbox].[dbo].[test_part].year = Scalar Operator([@year])

    With a SMALLINT, there is only one seek key:

    Seek Keys[1]: Prefix: PtnId1000, [Sandbox].[dbo].[test_part].year = Scalar Operator(RangePartitionNew ([@year], (1), (2014), (2015), (2016))), Scalar Operator([@year])

    I suppose that it was unable to use the RangePartitionNew operator without the exact match on the data type?

  • May 20, 2016 at 12:26 am

    #1880430

    Yes - it seems so. But please don't ask me, if this is a bug or intended behavior (I tend to bug or at least misbehavior, because MSSQL usually do int <-> smallint conversations internal without problems)

    God is real, unless declared integer.

  • May 23, 2016 at 6:45 am

    #1880719

    Thanks for the question.

  • July 11, 2016 at 6:49 am

    #1889394

    It's a classic implicit conversion issue. SQL Server will never convert a "bigger" datatype to a smaller.

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • July 11, 2016 at 6:56 am

    #1889397

    You are right, if I declare the year column (and the partition function) with BIGINT instead of SMALLINT the implicit convertation will work and it scans only one partition

    God is real, unless declared integer.

Viewing 11 posts - 1 through 10 (of 10 total)

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