Clustered Index 'Seek predicate' and 'predicate' on the same column

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
7
down vote

favorite












I have a clustered index primary key column and I'm doing a range query on it.
The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a residual predicate.
which causes to read all the rows up to the @upper limit



I'm using two parameters for the range:



declare
@lower numeric(18,0) = 1000,
@upper numeric(18,0) = 1005;

select * from messages
where msg_id between @lower+1 and @upper;


In that case the actual execution plan shows:



  • predicate: messages.msg_id >= lower

  • seek predicate: messages.msg_id < @upper

  • rows read: 1005

Table definition (Simplified):



CREATE TABLE [dbo].[messages](
[msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
[col2] [varchar](32) NOT NULL,
CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
(
[msg_id] ASC
)


More information



When used with constants instead of variables both of the predicates are 'Seek'
Have tried Option (Optimize for (@lower=1000)), without success










share|improve this question



























    up vote
    7
    down vote

    favorite












    I have a clustered index primary key column and I'm doing a range query on it.
    The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a residual predicate.
    which causes to read all the rows up to the @upper limit



    I'm using two parameters for the range:



    declare
    @lower numeric(18,0) = 1000,
    @upper numeric(18,0) = 1005;

    select * from messages
    where msg_id between @lower+1 and @upper;


    In that case the actual execution plan shows:



    • predicate: messages.msg_id >= lower

    • seek predicate: messages.msg_id < @upper

    • rows read: 1005

    Table definition (Simplified):



    CREATE TABLE [dbo].[messages](
    [msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
    [col2] [varchar](32) NOT NULL,
    CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
    (
    [msg_id] ASC
    )


    More information



    When used with constants instead of variables both of the predicates are 'Seek'
    Have tried Option (Optimize for (@lower=1000)), without success










    share|improve this question























      up vote
      7
      down vote

      favorite









      up vote
      7
      down vote

      favorite











      I have a clustered index primary key column and I'm doing a range query on it.
      The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a residual predicate.
      which causes to read all the rows up to the @upper limit



      I'm using two parameters for the range:



      declare
      @lower numeric(18,0) = 1000,
      @upper numeric(18,0) = 1005;

      select * from messages
      where msg_id between @lower+1 and @upper;


      In that case the actual execution plan shows:



      • predicate: messages.msg_id >= lower

      • seek predicate: messages.msg_id < @upper

      • rows read: 1005

      Table definition (Simplified):



      CREATE TABLE [dbo].[messages](
      [msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
      [col2] [varchar](32) NOT NULL,
      CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
      (
      [msg_id] ASC
      )


      More information



      When used with constants instead of variables both of the predicates are 'Seek'
      Have tried Option (Optimize for (@lower=1000)), without success










      share|improve this question













      I have a clustered index primary key column and I'm doing a range query on it.
      The problem is the scan only uses the first range part for the seek predicate, leaving the other side of the range as a residual predicate.
      which causes to read all the rows up to the @upper limit



      I'm using two parameters for the range:



      declare
      @lower numeric(18,0) = 1000,
      @upper numeric(18,0) = 1005;

      select * from messages
      where msg_id between @lower+1 and @upper;


      In that case the actual execution plan shows:



      • predicate: messages.msg_id >= lower

      • seek predicate: messages.msg_id < @upper

      • rows read: 1005

      Table definition (Simplified):



      CREATE TABLE [dbo].[messages](
      [msg_id] [numeric](18, 0) IDENTITY(0,1) NOT NULL,
      [col2] [varchar](32) NOT NULL,
      CONSTRAINT [PK_Message] PRIMARY KEY CLUSTERED
      (
      [msg_id] ASC
      )


      More information



      When used with constants instead of variables both of the predicates are 'Seek'
      Have tried Option (Optimize for (@lower=1000)), without success







      sql-server index






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Aug 13 at 8:26









      Abir Stolov

      384




      384




















          2 Answers
          2






          active

          oldest

          votes

















          up vote
          8
          down vote



          accepted










          Starting with your original query:



          declare
          @lower numeric(18,0) = 1000,
          @upper numeric(18,0) = 1005;

          select * from [messages]
          where msg_id between @lower+1 and @upper;


          The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:



          declare
          @lower numeric(18,0) = 1000,
          @upper numeric(18,0) = 1005;

          select * from [messages]
          where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;


          A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:



          ╔═══════════╦═════════════════════════════════════╦════════════════╗
          ║ Operation ║ Result precision ║ Result scale * ║
          ╠═══════════╬═════════════════════════════════════╬════════════════╣
          ║ e1 + e2 ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2) ║
          ╚═══════════╩═════════════════════════════════════╩════════════════╝


          For your expression, the resulting precision is:



          max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19


          and the resulting scale is 0. You can verify this by running the following code in SQL Server:



          declare
          @lower numeric(18,0) = 1000,
          @upper numeric(18,0) = 1005;

          SELECT
          SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
          , SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
          , SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;


          This means that your original query is equivalent to the following:



          declare
          @lower numeric(19,0) = 1000 + 1,
          @upper numeric(18,0) = 1005;

          select * from [messages]
          where msg_id between @lower and @upper;


          SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:



          declare
          @lower numeric(18,0) = 1000,
          @upper numeric(18,0) = 1005;

          select * from [messages]
          where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;


          That query can process both filters as seek predicates:



          seek predicates



          My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.






          share|improve this answer



























            up vote
            4
            down vote













            There is an expression on one of your filters (@lower+1) which is making the engine do a regular predicate rather than a seek predicate (makes it non SARG-able).



            Try changing the filter's value before the SELECT statement and you will see that both ends will be correctly used as the seek boundaries.



            declare
            @lower numeric(18,0) = 1000 + 1,
            @upper numeric(18,0) = 1005;

            select * from messages
            where msg_id between @lower and @upper;


            enter image description here






            share|improve this answer






















              Your Answer







              StackExchange.ready(function()
              var channelOptions =
              tags: "".split(" "),
              id: "182"
              ;
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function()
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled)
              StackExchange.using("snippets", function()
              createEditor();
              );

              else
              createEditor();

              );

              function createEditor()
              StackExchange.prepareEditor(
              heartbeatType: 'answer',
              convertImagesToLinks: false,
              noModals: false,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: null,
              bindNavPrevention: true,
              postfix: "",
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              );



              );













               

              draft saved


              draft discarded


















              StackExchange.ready(
              function ()
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214757%2fclustered-index-seek-predicate-and-predicate-on-the-same-column%23new-answer', 'question_page');

              );

              Post as a guest






























              2 Answers
              2






              active

              oldest

              votes








              2 Answers
              2






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes








              up vote
              8
              down vote



              accepted










              Starting with your original query:



              declare
              @lower numeric(18,0) = 1000,
              @upper numeric(18,0) = 1005;

              select * from [messages]
              where msg_id between @lower+1 and @upper;


              The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:



              declare
              @lower numeric(18,0) = 1000,
              @upper numeric(18,0) = 1005;

              select * from [messages]
              where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;


              A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:



              ╔═══════════╦═════════════════════════════════════╦════════════════╗
              ║ Operation ║ Result precision ║ Result scale * ║
              ╠═══════════╬═════════════════════════════════════╬════════════════╣
              ║ e1 + e2 ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2) ║
              ╚═══════════╩═════════════════════════════════════╩════════════════╝


              For your expression, the resulting precision is:



              max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19


              and the resulting scale is 0. You can verify this by running the following code in SQL Server:



              declare
              @lower numeric(18,0) = 1000,
              @upper numeric(18,0) = 1005;

              SELECT
              SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
              , SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
              , SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;


              This means that your original query is equivalent to the following:



              declare
              @lower numeric(19,0) = 1000 + 1,
              @upper numeric(18,0) = 1005;

              select * from [messages]
              where msg_id between @lower and @upper;


              SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:



              declare
              @lower numeric(18,0) = 1000,
              @upper numeric(18,0) = 1005;

              select * from [messages]
              where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;


              That query can process both filters as seek predicates:



              seek predicates



              My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.






              share|improve this answer
























                up vote
                8
                down vote



                accepted










                Starting with your original query:



                declare
                @lower numeric(18,0) = 1000,
                @upper numeric(18,0) = 1005;

                select * from [messages]
                where msg_id between @lower+1 and @upper;


                The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:



                declare
                @lower numeric(18,0) = 1000,
                @upper numeric(18,0) = 1005;

                select * from [messages]
                where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;


                A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:



                ╔═══════════╦═════════════════════════════════════╦════════════════╗
                ║ Operation ║ Result precision ║ Result scale * ║
                ╠═══════════╬═════════════════════════════════════╬════════════════╣
                ║ e1 + e2 ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2) ║
                ╚═══════════╩═════════════════════════════════════╩════════════════╝


                For your expression, the resulting precision is:



                max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19


                and the resulting scale is 0. You can verify this by running the following code in SQL Server:



                declare
                @lower numeric(18,0) = 1000,
                @upper numeric(18,0) = 1005;

                SELECT
                SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
                , SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
                , SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;


                This means that your original query is equivalent to the following:



                declare
                @lower numeric(19,0) = 1000 + 1,
                @upper numeric(18,0) = 1005;

                select * from [messages]
                where msg_id between @lower and @upper;


                SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:



                declare
                @lower numeric(18,0) = 1000,
                @upper numeric(18,0) = 1005;

                select * from [messages]
                where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;


                That query can process both filters as seek predicates:



                seek predicates



                My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.






                share|improve this answer






















                  up vote
                  8
                  down vote



                  accepted







                  up vote
                  8
                  down vote



                  accepted






                  Starting with your original query:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between @lower+1 and @upper;


                  The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;


                  A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:



                  ╔═══════════╦═════════════════════════════════════╦════════════════╗
                  ║ Operation ║ Result precision ║ Result scale * ║
                  ╠═══════════╬═════════════════════════════════════╬════════════════╣
                  ║ e1 + e2 ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2) ║
                  ╚═══════════╩═════════════════════════════════════╩════════════════╝


                  For your expression, the resulting precision is:



                  max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19


                  and the resulting scale is 0. You can verify this by running the following code in SQL Server:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  SELECT
                  SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
                  , SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
                  , SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;


                  This means that your original query is equivalent to the following:



                  declare
                  @lower numeric(19,0) = 1000 + 1,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between @lower and @upper;


                  SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;


                  That query can process both filters as seek predicates:



                  seek predicates



                  My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.






                  share|improve this answer












                  Starting with your original query:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between @lower+1 and @upper;


                  The 1 that you added has a data type of integer by default. When adding an integer value to a numeric(18,0) value SQL Server applies the rules of data type precedence. int has a lower precedence so it gets converted to a numeric(1,0). Your query is equivalent to the following:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between @lower+CAST(1 AS NUMERIC(1, 0)) and @upper;


                  A different set of rules around Precision, scale, and Length is applied to determine the data type of the expression involving @lower. It isn't safe to just use NUMERIC(18,0) because that could be overflowed (consider 999,999,999,999,999,999 and 1 as an example). The rule that applies here is:



                  ╔═══════════╦═════════════════════════════════════╦════════════════╗
                  ║ Operation ║ Result precision ║ Result scale * ║
                  ╠═══════════╬═════════════════════════════════════╬════════════════╣
                  ║ e1 + e2 ║ max(s1, s2) + max(p1-s1, p2-s2) + 1 ║ max(s1, s2) ║
                  ╚═══════════╩═════════════════════════════════════╩════════════════╝


                  For your expression, the resulting precision is:



                  max(0, 0) + max(18 - 0, 1 - 0) + 1 = 0 + 18 + 1 = 19


                  and the resulting scale is 0. You can verify this by running the following code in SQL Server:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  SELECT
                  SQL_VARIANT_PROPERTY(@lower+1, 'BaseType') lower_exp_BaseType
                  , SQL_VARIANT_PROPERTY(@lower+1, 'Precision') lower_exp_Precision
                  , SQL_VARIANT_PROPERTY(@lower+1, 'Scale') lower_exp_Scale;


                  This means that your original query is equivalent to the following:



                  declare
                  @lower numeric(19,0) = 1000 + 1,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between @lower and @upper;


                  SQL Server can only use @lower to do a clustered index seek if the value can be implicitly converted to NUMERIC(18, 0). It is not safe to convert a NUMERIC(19,0) value to NUMERIC(18,0). As a result the value is applied as a predicate instead of as a seek predicate. One workaround is to do the following:



                  declare
                  @lower numeric(18,0) = 1000,
                  @upper numeric(18,0) = 1005;

                  select * from [messages]
                  where msg_id between TRY_CAST(@lower+1 AS NUMERIC(18,0)) and @upper;


                  That query can process both filters as seek predicates:



                  seek predicates



                  My advice is to change the data type in the table to BIGINT if possible. BIGINT requires one fewer byte than NUMERIC(18,0) and benefits from performance optimizations not available to NUMERIC(18,0) including better support for bitmap filters.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Aug 14 at 1:26









                  Joe Obbish

                  18.7k32477




                  18.7k32477






















                      up vote
                      4
                      down vote













                      There is an expression on one of your filters (@lower+1) which is making the engine do a regular predicate rather than a seek predicate (makes it non SARG-able).



                      Try changing the filter's value before the SELECT statement and you will see that both ends will be correctly used as the seek boundaries.



                      declare
                      @lower numeric(18,0) = 1000 + 1,
                      @upper numeric(18,0) = 1005;

                      select * from messages
                      where msg_id between @lower and @upper;


                      enter image description here






                      share|improve this answer


























                        up vote
                        4
                        down vote













                        There is an expression on one of your filters (@lower+1) which is making the engine do a regular predicate rather than a seek predicate (makes it non SARG-able).



                        Try changing the filter's value before the SELECT statement and you will see that both ends will be correctly used as the seek boundaries.



                        declare
                        @lower numeric(18,0) = 1000 + 1,
                        @upper numeric(18,0) = 1005;

                        select * from messages
                        where msg_id between @lower and @upper;


                        enter image description here






                        share|improve this answer
























                          up vote
                          4
                          down vote










                          up vote
                          4
                          down vote









                          There is an expression on one of your filters (@lower+1) which is making the engine do a regular predicate rather than a seek predicate (makes it non SARG-able).



                          Try changing the filter's value before the SELECT statement and you will see that both ends will be correctly used as the seek boundaries.



                          declare
                          @lower numeric(18,0) = 1000 + 1,
                          @upper numeric(18,0) = 1005;

                          select * from messages
                          where msg_id between @lower and @upper;


                          enter image description here






                          share|improve this answer














                          There is an expression on one of your filters (@lower+1) which is making the engine do a regular predicate rather than a seek predicate (makes it non SARG-able).



                          Try changing the filter's value before the SELECT statement and you will see that both ends will be correctly used as the seek boundaries.



                          declare
                          @lower numeric(18,0) = 1000 + 1,
                          @upper numeric(18,0) = 1005;

                          select * from messages
                          where msg_id between @lower and @upper;


                          enter image description here







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited Aug 13 at 8:41

























                          answered Aug 13 at 8:35









                          EzLo

                          2,2011420




                          2,2011420



























                               

                              draft saved


                              draft discarded















































                               


                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function ()
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214757%2fclustered-index-seek-predicate-and-predicate-on-the-same-column%23new-answer', 'question_page');

                              );

                              Post as a guest













































































                              Popular posts from this blog

                              How to check contact read email or not when send email to Individual?

                              Displaying single band from multi-band raster using QGIS

                              How many registers does an x86_64 CPU actually have?