Partition elimination and variables

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

    God is real, unless declared integer.

  • This was removed by the editor as SPAM

  • Interesting question, thanks.


  • Good question!


  • Very interesting question.

    Thank you for posting this!

  • 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?"

  • 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?

  • 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.

  • Thanks for the question.

  • 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

  • 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