Should the filtering column(s) always be in the keys / includes?











up vote
4
down vote

favorite












I'm considering creating a filtered index in my copy of the Stack Overflow database. Something like this, for example:



CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered
ON dbo.Users (DisplayName)
WHERE Reputation > 400000;


Should I always add the column in the filtering expression (Reputation in this example) to the key or includes for the index, or is having it in the filtering expression good enough?










share|improve this question


























    up vote
    4
    down vote

    favorite












    I'm considering creating a filtered index in my copy of the Stack Overflow database. Something like this, for example:



    CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered
    ON dbo.Users (DisplayName)
    WHERE Reputation > 400000;


    Should I always add the column in the filtering expression (Reputation in this example) to the key or includes for the index, or is having it in the filtering expression good enough?










    share|improve this question
























      up vote
      4
      down vote

      favorite









      up vote
      4
      down vote

      favorite











      I'm considering creating a filtered index in my copy of the Stack Overflow database. Something like this, for example:



      CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered
      ON dbo.Users (DisplayName)
      WHERE Reputation > 400000;


      Should I always add the column in the filtering expression (Reputation in this example) to the key or includes for the index, or is having it in the filtering expression good enough?










      share|improve this question













      I'm considering creating a filtered index in my copy of the Stack Overflow database. Something like this, for example:



      CREATE UNIQUE NONCLUSTERED INDEX IX_DisplayName_Filtered
      ON dbo.Users (DisplayName)
      WHERE Reputation > 400000;


      Should I always add the column in the filtering expression (Reputation in this example) to the key or includes for the index, or is having it in the filtering expression good enough?







      sql-server filtered-index






      share|improve this question













      share|improve this question











      share|improve this question




      share|improve this question










      asked Dec 4 at 21:15









      jadarnel27

      2,7381227




      2,7381227






















          1 Answer
          1






          active

          oldest

          votes

















          up vote
          5
          down vote



          accepted










          Yes!



          For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes



          The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.



          Key lookups when the query predicate doesn't match the filter expression



          First of all, the documentation has this to say about including filter expression columns:





          • A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.




          So if you have an inequality filter expression like Reputation > 400000, but your query uses a predicate like WHERE Reputation > 400000 AND Reputation < 450000;, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.



          Including the Reputation column in the index (key or includes) removes the need for this lookup.



          See Erik Darling's post Filtered Indexes: Just Add Includes for additional details and an example of this situation.



          Another example of this can be found in Paul White's answer here: Unnecessary key lookup being performed while using filtered index



          Key lookups when the filtering column is included in the resultset



          The documentation goes on to say this:





          • A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.




          This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).



          Poor row estimates when using equality expressions



          There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan). Including the filtering column can prevent these more-accurate estimates from being discarded.



          More details, and an example, can be found in Paul White's answer here: Incorrect row estimation given with a filtered index



          An additional example can be found here on dba.se: Query using a filtered index but wrong estimated number of rows



          Key lookups when using IS NULL in the filtering expression



          Creating an index with a filtering expression that uses IS NULL can produce a completely unnecessary key lookup. See this question, and the related bug report on SQL Server's feedback site: Why filtered index on IS NULL value is not used?



          As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.






          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: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: null,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            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%2f224149%2fshould-the-filtering-columns-always-be-in-the-keys-includes%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            1 Answer
            1






            active

            oldest

            votes








            1 Answer
            1






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            5
            down vote



            accepted










            Yes!



            For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes



            The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.



            Key lookups when the query predicate doesn't match the filter expression



            First of all, the documentation has this to say about including filter expression columns:





            • A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.




            So if you have an inequality filter expression like Reputation > 400000, but your query uses a predicate like WHERE Reputation > 400000 AND Reputation < 450000;, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.



            Including the Reputation column in the index (key or includes) removes the need for this lookup.



            See Erik Darling's post Filtered Indexes: Just Add Includes for additional details and an example of this situation.



            Another example of this can be found in Paul White's answer here: Unnecessary key lookup being performed while using filtered index



            Key lookups when the filtering column is included in the resultset



            The documentation goes on to say this:





            • A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.




            This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).



            Poor row estimates when using equality expressions



            There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan). Including the filtering column can prevent these more-accurate estimates from being discarded.



            More details, and an example, can be found in Paul White's answer here: Incorrect row estimation given with a filtered index



            An additional example can be found here on dba.se: Query using a filtered index but wrong estimated number of rows



            Key lookups when using IS NULL in the filtering expression



            Creating an index with a filtering expression that uses IS NULL can produce a completely unnecessary key lookup. See this question, and the related bug report on SQL Server's feedback site: Why filtered index on IS NULL value is not used?



            As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.






            share|improve this answer

























              up vote
              5
              down vote



              accepted










              Yes!



              For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes



              The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.



              Key lookups when the query predicate doesn't match the filter expression



              First of all, the documentation has this to say about including filter expression columns:





              • A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.




              So if you have an inequality filter expression like Reputation > 400000, but your query uses a predicate like WHERE Reputation > 400000 AND Reputation < 450000;, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.



              Including the Reputation column in the index (key or includes) removes the need for this lookup.



              See Erik Darling's post Filtered Indexes: Just Add Includes for additional details and an example of this situation.



              Another example of this can be found in Paul White's answer here: Unnecessary key lookup being performed while using filtered index



              Key lookups when the filtering column is included in the resultset



              The documentation goes on to say this:





              • A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.




              This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).



              Poor row estimates when using equality expressions



              There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan). Including the filtering column can prevent these more-accurate estimates from being discarded.



              More details, and an example, can be found in Paul White's answer here: Incorrect row estimation given with a filtered index



              An additional example can be found here on dba.se: Query using a filtered index but wrong estimated number of rows



              Key lookups when using IS NULL in the filtering expression



              Creating an index with a filtering expression that uses IS NULL can produce a completely unnecessary key lookup. See this question, and the related bug report on SQL Server's feedback site: Why filtered index on IS NULL value is not used?



              As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.






              share|improve this answer























                up vote
                5
                down vote



                accepted







                up vote
                5
                down vote



                accepted






                Yes!



                For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes



                The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.



                Key lookups when the query predicate doesn't match the filter expression



                First of all, the documentation has this to say about including filter expression columns:





                • A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.




                So if you have an inequality filter expression like Reputation > 400000, but your query uses a predicate like WHERE Reputation > 400000 AND Reputation < 450000;, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.



                Including the Reputation column in the index (key or includes) removes the need for this lookup.



                See Erik Darling's post Filtered Indexes: Just Add Includes for additional details and an example of this situation.



                Another example of this can be found in Paul White's answer here: Unnecessary key lookup being performed while using filtered index



                Key lookups when the filtering column is included in the resultset



                The documentation goes on to say this:





                • A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.




                This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).



                Poor row estimates when using equality expressions



                There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan). Including the filtering column can prevent these more-accurate estimates from being discarded.



                More details, and an example, can be found in Paul White's answer here: Incorrect row estimation given with a filtered index



                An additional example can be found here on dba.se: Query using a filtered index but wrong estimated number of rows



                Key lookups when using IS NULL in the filtering expression



                Creating an index with a filtering expression that uses IS NULL can produce a completely unnecessary key lookup. See this question, and the related bug report on SQL Server's feedback site: Why filtered index on IS NULL value is not used?



                As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.






                share|improve this answer












                Yes!



                For various reasons, it's always better to have the filtering column as part of the index: either in the keys, or in the includes



                The following are some specific examples of filtered index query problems that are resolved by including the filtering columns in the index.



                Key lookups when the query predicate doesn't match the filter expression



                First of all, the documentation has this to say about including filter expression columns:





                • A column in the filtered index expression should be a key or included column in the filtered index definition if the query predicate uses the column in a comparison that is not equivalent to the filtered index expression.




                So if you have an inequality filter expression like Reputation > 400000, but your query uses a predicate like WHERE Reputation > 400000 AND Reputation < 450000;, the filtered index might still be used - but a key lookup will be required to satisfy the query's predicate.



                Including the Reputation column in the index (key or includes) removes the need for this lookup.



                See Erik Darling's post Filtered Indexes: Just Add Includes for additional details and an example of this situation.



                Another example of this can be found in Paul White's answer here: Unnecessary key lookup being performed while using filtered index



                Key lookups when the filtering column is included in the resultset



                The documentation goes on to say this:





                • A column in the filtered index expression should be a key or included column in the filtered index definition if the column is in the query result set.




                This might feel like it goes without saying, but just to be complete: if your queries include the filtering column in the final resultset, you should probably include them in the index (key or includes).



                Poor row estimates when using equality expressions



                There are cases where useful row estimates based on actual statistics can be eliminated during the optimization process (specifically when the query plan produced by the optimizer is converted to a physical execution plan). Including the filtering column can prevent these more-accurate estimates from being discarded.



                More details, and an example, can be found in Paul White's answer here: Incorrect row estimation given with a filtered index



                An additional example can be found here on dba.se: Query using a filtered index but wrong estimated number of rows



                Key lookups when using IS NULL in the filtering expression



                Creating an index with a filtering expression that uses IS NULL can produce a completely unnecessary key lookup. See this question, and the related bug report on SQL Server's feedback site: Why filtered index on IS NULL value is not used?



                As you might have guessed, the workaround presented is to add the filtering column as an included column in the filtered index.







                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered Dec 4 at 21:15









                jadarnel27

                2,7381227




                2,7381227






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Database Administrators Stack Exchange!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.





                    Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                    Please pay close attention to the following guidance:


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f224149%2fshould-the-filtering-columns-always-be-in-the-keys-includes%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    How to change which sound is reproduced for terminal bell?

                    Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

                    Can I use Tabulator js library in my java Spring + Thymeleaf project?