If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)? Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Why does sql server prefer the nonclustered index over the clustered index?varchar performance impactAren't two writes required to update a clustered index recordChanging TEXT to VARCHARUsing wildcards in a like statement on an unindexed VARCHAR(MAX) column with more than 1 million recordsStorage size for varchar length in RedshiftWhy SQL Server has 900 byte index size limitSlow DELETEs of LOB data in SQL ServerHow do I compare large stored procedures?What are the current best practices concerning varchar sizing in SQL Server?Convert varbinary(max) with CONVERT(nvarchar/varchar(max) ,value,0) gives no logic results

When the Haste spell ends on a creature, do attackers have advantage against that creature?

Amount of permutations on an NxNxN Rubik's Cube

Why wasn't DOSKEY integrated with COMMAND.COM?

Closed form of recurrent arithmetic series summation

また usage in a dictionary

Do I really need recursive chmod to restrict access to a folder?

Using et al. for a last / senior author rather than for a first author

Is safe to use va_start macro with this as parameter?

What are the out-of-universe reasons for the references to Toby Maguire-era Spider-Man in ITSV

Fantasy story; one type of magic grows in power with use, but the more powerful they are, they more they are drawn to travel to their source

How do I make this wiring inside cabinet safer? (Pic)

What does the "x" in "x86" represent?

Do jazz musicians improvise on the parent scale in addition to the chord-scales?

Is CEO the profession with the most psychopaths?

Can melee weapons be used to deliver Contact Poisons?

How could we fake a moon landing now?

What causes the direction of lightning flashes?

If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?

What does this Jacques Hadamard quote mean?

Is "Reachable Object" really an NP-complete problem?

Would "destroying" Wurmcoil Engine prevent its tokens from being created?

Does classifying an integer as a discrete log require it be part of a multiplicative group?

What's the meaning of "fortified infraction restraint"?

Crossing US/Canada Border for less than 24 hours



If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?



Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Why does sql server prefer the nonclustered index over the clustered index?varchar performance impactAren't two writes required to update a clustered index recordChanging TEXT to VARCHARUsing wildcards in a like statement on an unindexed VARCHAR(MAX) column with more than 1 million recordsStorage size for varchar length in RedshiftWhy SQL Server has 900 byte index size limitSlow DELETEs of LOB data in SQL ServerHow do I compare large stored procedures?What are the current best practices concerning varchar sizing in SQL Server?Convert varbinary(max) with CONVERT(nvarchar/varchar(max) ,value,0) gives no logic results



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








4















I'm asking this out of curiosity, being inspired by this question.



We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a row with such value requires two or more logical IO operations (essentially, one more than otherwise would theoretically be necessary).



We can add a VARCHAR(MAX) column, as included, to a unique index, as demonstrated in the linked question. If this column has values that exceed 8000 bytes in length, would such values still be stored "inline" in the index leaf pages, or would they also be moved to LOB pages?










share|improve this question






























    4















    I'm asking this out of curiosity, being inspired by this question.



    We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a row with such value requires two or more logical IO operations (essentially, one more than otherwise would theoretically be necessary).



    We can add a VARCHAR(MAX) column, as included, to a unique index, as demonstrated in the linked question. If this column has values that exceed 8000 bytes in length, would such values still be stored "inline" in the index leaf pages, or would they also be moved to LOB pages?










    share|improve this question


























      4












      4








      4








      I'm asking this out of curiosity, being inspired by this question.



      We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a row with such value requires two or more logical IO operations (essentially, one more than otherwise would theoretically be necessary).



      We can add a VARCHAR(MAX) column, as included, to a unique index, as demonstrated in the linked question. If this column has values that exceed 8000 bytes in length, would such values still be stored "inline" in the index leaf pages, or would they also be moved to LOB pages?










      share|improve this question
















      I'm asking this out of curiosity, being inspired by this question.



      We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a row with such value requires two or more logical IO operations (essentially, one more than otherwise would theoretically be necessary).



      We can add a VARCHAR(MAX) column, as included, to a unique index, as demonstrated in the linked question. If this column has values that exceed 8000 bytes in length, would such values still be stored "inline" in the index leaf pages, or would they also be moved to LOB pages?







      sql-server varchar






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 50 mins ago







      mustaccio

















      asked 3 hours ago









      mustacciomustaccio

      10.2k72240




      10.2k72240




















          1 Answer
          1






          active

          oldest

          votes


















          5














          Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:



          DROP TABLE IF EXISTS #LOB_FOR_ME;

          CREATE TABLE #LOB_FOR_ME (
          ID BIGINT,
          MAX_VERNON_WAS_HERE VARCHAR(MAX)
          );

          CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);


          Now insert some rows with values that take 8000 bytes for the VARCHAR(MAX) column and check out the DMF:



          INSERT INTO #LOB_FOR_ME
          SELECT 1, REPLICATE('Z', 8000)
          FROM master..spt_values;

          SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
          FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


          There are no LOB pages in the index:



          ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
          ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
          ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
          ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2540 ║ 2540 ║
          ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2540 ║
          ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
          ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


          But if I add rows with values that take 8001 bytes:



          INSERT INTO #LOB_FOR_ME
          SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
          FROM master..spt_values;

          SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
          FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


          Now I have 1 LOB page in the index for every row that I just inserted:



          ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
          ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
          ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
          ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2556 ║ 5080 ║
          ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2556 ║
          ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
          ║ 0 ║ NONCLUSTERED INDEX ║ LOB_DATA ║ 2540 ║ 2540 ║
          ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


          You can also see this with SET STATISTICS IO ON; and the right query. Consider the following query that only looks at rows with 8000 bytes:



          SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
          FROM #LOB_FOR_ME
          WHERE ID = 1;


          Results upon executing:




          Scan count 1, logical reads 2560, physical reads 0, read-ahead reads
          0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.




          If I instead query the rows with 8001 bytes:



          SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
          FROM #LOB_FOR_ME
          WHERE ID = 2;


          Now I see lob reads:




          Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0,
          lob logical reads 5080, lob physical reads 0, lob read-ahead reads 0.







          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',
            autoActivateHeartbeat: false,
            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%2f235102%2fif-a-varcharmax-column-is-included-in-an-index-is-the-entire-value-always-sto%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









            5














            Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:



            DROP TABLE IF EXISTS #LOB_FOR_ME;

            CREATE TABLE #LOB_FOR_ME (
            ID BIGINT,
            MAX_VERNON_WAS_HERE VARCHAR(MAX)
            );

            CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);


            Now insert some rows with values that take 8000 bytes for the VARCHAR(MAX) column and check out the DMF:



            INSERT INTO #LOB_FOR_ME
            SELECT 1, REPLICATE('Z', 8000)
            FROM master..spt_values;

            SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
            FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


            There are no LOB pages in the index:



            ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
            ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
            ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
            ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2540 ║ 2540 ║
            ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2540 ║
            ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
            ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


            But if I add rows with values that take 8001 bytes:



            INSERT INTO #LOB_FOR_ME
            SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
            FROM master..spt_values;

            SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
            FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


            Now I have 1 LOB page in the index for every row that I just inserted:



            ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
            ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
            ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
            ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2556 ║ 5080 ║
            ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2556 ║
            ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
            ║ 0 ║ NONCLUSTERED INDEX ║ LOB_DATA ║ 2540 ║ 2540 ║
            ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


            You can also see this with SET STATISTICS IO ON; and the right query. Consider the following query that only looks at rows with 8000 bytes:



            SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
            FROM #LOB_FOR_ME
            WHERE ID = 1;


            Results upon executing:




            Scan count 1, logical reads 2560, physical reads 0, read-ahead reads
            0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.




            If I instead query the rows with 8001 bytes:



            SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
            FROM #LOB_FOR_ME
            WHERE ID = 2;


            Now I see lob reads:




            Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0,
            lob logical reads 5080, lob physical reads 0, lob read-ahead reads 0.







            share|improve this answer



























              5














              Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:



              DROP TABLE IF EXISTS #LOB_FOR_ME;

              CREATE TABLE #LOB_FOR_ME (
              ID BIGINT,
              MAX_VERNON_WAS_HERE VARCHAR(MAX)
              );

              CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);


              Now insert some rows with values that take 8000 bytes for the VARCHAR(MAX) column and check out the DMF:



              INSERT INTO #LOB_FOR_ME
              SELECT 1, REPLICATE('Z', 8000)
              FROM master..spt_values;

              SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
              FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


              There are no LOB pages in the index:



              ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
              ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
              ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
              ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2540 ║ 2540 ║
              ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2540 ║
              ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
              ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


              But if I add rows with values that take 8001 bytes:



              INSERT INTO #LOB_FOR_ME
              SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
              FROM master..spt_values;

              SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
              FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


              Now I have 1 LOB page in the index for every row that I just inserted:



              ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
              ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
              ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
              ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2556 ║ 5080 ║
              ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2556 ║
              ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
              ║ 0 ║ NONCLUSTERED INDEX ║ LOB_DATA ║ 2540 ║ 2540 ║
              ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


              You can also see this with SET STATISTICS IO ON; and the right query. Consider the following query that only looks at rows with 8000 bytes:



              SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
              FROM #LOB_FOR_ME
              WHERE ID = 1;


              Results upon executing:




              Scan count 1, logical reads 2560, physical reads 0, read-ahead reads
              0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.




              If I instead query the rows with 8001 bytes:



              SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
              FROM #LOB_FOR_ME
              WHERE ID = 2;


              Now I see lob reads:




              Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0,
              lob logical reads 5080, lob physical reads 0, lob read-ahead reads 0.







              share|improve this answer

























                5












                5








                5







                Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:



                DROP TABLE IF EXISTS #LOB_FOR_ME;

                CREATE TABLE #LOB_FOR_ME (
                ID BIGINT,
                MAX_VERNON_WAS_HERE VARCHAR(MAX)
                );

                CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);


                Now insert some rows with values that take 8000 bytes for the VARCHAR(MAX) column and check out the DMF:



                INSERT INTO #LOB_FOR_ME
                SELECT 1, REPLICATE('Z', 8000)
                FROM master..spt_values;

                SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
                FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


                There are no LOB pages in the index:



                ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
                ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
                ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
                ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2540 ║ 2540 ║
                ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2540 ║
                ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
                ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


                But if I add rows with values that take 8001 bytes:



                INSERT INTO #LOB_FOR_ME
                SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
                FROM master..spt_values;

                SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
                FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


                Now I have 1 LOB page in the index for every row that I just inserted:



                ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
                ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
                ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
                ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2556 ║ 5080 ║
                ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2556 ║
                ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
                ║ 0 ║ NONCLUSTERED INDEX ║ LOB_DATA ║ 2540 ║ 2540 ║
                ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


                You can also see this with SET STATISTICS IO ON; and the right query. Consider the following query that only looks at rows with 8000 bytes:



                SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
                FROM #LOB_FOR_ME
                WHERE ID = 1;


                Results upon executing:




                Scan count 1, logical reads 2560, physical reads 0, read-ahead reads
                0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.




                If I instead query the rows with 8001 bytes:



                SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
                FROM #LOB_FOR_ME
                WHERE ID = 2;


                Now I see lob reads:




                Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0,
                lob logical reads 5080, lob physical reads 0, lob read-ahead reads 0.







                share|improve this answer













                Values that exceed 8000 bytes cannot be stored "inline". They are stored on LOB pages. You can see this with sys.dm_db_index_physical_stats. Start with a simple table:



                DROP TABLE IF EXISTS #LOB_FOR_ME;

                CREATE TABLE #LOB_FOR_ME (
                ID BIGINT,
                MAX_VERNON_WAS_HERE VARCHAR(MAX)
                );

                CREATE INDEX IX ON #LOB_FOR_ME (ID) INCLUDE (MAX_VERNON_WAS_HERE);


                Now insert some rows with values that take 8000 bytes for the VARCHAR(MAX) column and check out the DMF:



                INSERT INTO #LOB_FOR_ME
                SELECT 1, REPLICATE('Z', 8000)
                FROM master..spt_values;

                SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
                FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


                There are no LOB pages in the index:



                ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
                ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
                ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
                ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2540 ║ 2540 ║
                ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2540 ║
                ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
                ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


                But if I add rows with values that take 8001 bytes:



                INSERT INTO #LOB_FOR_ME
                SELECT 2, REPLICATE(CAST('Z' AS VARCHAR(MAX)), 8001)
                FROM master..spt_values;

                SELECT index_level, index_type_desc, alloc_unit_type_desc, page_count, record_count
                FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('#LOB_FOR_ME'), 2, NULL , 'DETAILED');


                Now I have 1 LOB page in the index for every row that I just inserted:



                ╔═════════════╦════════════════════╦══════════════════════╦════════════╦══════════════╗
                ║ index_level ║ index_type_desc ║ alloc_unit_type_desc ║ page_count ║ record_count ║
                ╠═════════════╬════════════════════╬══════════════════════╬════════════╬══════════════╣
                ║ 0 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 2556 ║ 5080 ║
                ║ 1 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 18 ║ 2556 ║
                ║ 2 ║ NONCLUSTERED INDEX ║ IN_ROW_DATA ║ 1 ║ 18 ║
                ║ 0 ║ NONCLUSTERED INDEX ║ LOB_DATA ║ 2540 ║ 2540 ║
                ╚═════════════╩════════════════════╩══════════════════════╩════════════╩══════════════╝


                You can also see this with SET STATISTICS IO ON; and the right query. Consider the following query that only looks at rows with 8000 bytes:



                SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
                FROM #LOB_FOR_ME
                WHERE ID = 1;


                Results upon executing:




                Scan count 1, logical reads 2560, physical reads 0, read-ahead reads
                0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.




                If I instead query the rows with 8001 bytes:



                SELECT SUM(LEN(MAX_VERNON_WAS_HERE))
                FROM #LOB_FOR_ME
                WHERE ID = 2;


                Now I see lob reads:




                Scan count 1, logical reads 20, physical reads 0, read-ahead reads 0,
                lob logical reads 5080, lob physical reads 0, lob read-ahead reads 0.








                share|improve this answer












                share|improve this answer



                share|improve this answer










                answered 3 hours ago









                Joe ObbishJoe Obbish

                22.1k43392




                22.1k43392



























                    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.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function ()
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f235102%2fif-a-varcharmax-column-is-included-in-an-index-is-the-entire-value-always-sto%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

                    Oświęcim Innehåll Historia | Källor | Externa länkar | Navigeringsmeny50°2′18″N 19°13′17″Ö / 50.03833°N 19.22139°Ö / 50.03833; 19.2213950°2′18″N 19°13′17″Ö / 50.03833°N 19.22139°Ö / 50.03833; 19.221393089658Nordisk familjebok, AuschwitzInsidan tro och existensJewish Community i OświęcimAuschwitz Jewish Center: MuseumAuschwitz Jewish Center

                    Valle di Casies Indice Geografia fisica | Origini del nome | Storia | Società | Amministrazione | Sport | Note | Bibliografia | Voci correlate | Altri progetti | Collegamenti esterni | Menu di navigazione46°46′N 12°11′E / 46.766667°N 12.183333°E46.766667; 12.183333 (Valle di Casies)46°46′N 12°11′E / 46.766667°N 12.183333°E46.766667; 12.183333 (Valle di Casies)Sito istituzionaleAstat Censimento della popolazione 2011 - Determinazione della consistenza dei tre gruppi linguistici della Provincia Autonoma di Bolzano-Alto Adige - giugno 2012Numeri e fattiValle di CasiesDato IstatTabella dei gradi/giorno dei Comuni italiani raggruppati per Regione e Provincia26 agosto 1993, n. 412Heraldry of the World: GsiesStatistiche I.StatValCasies.comWikimedia CommonsWikimedia CommonsValle di CasiesSito ufficialeValle di CasiesMM14870458910042978-6

                    Typsetting diagram chases (with TikZ?) Announcing the arrival of Valued Associate #679: Cesar Manara Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)How to define the default vertical distance between nodes?Draw edge on arcNumerical conditional within tikz keys?TikZ: Drawing an arc from an intersection to an intersectionDrawing rectilinear curves in Tikz, aka an Etch-a-Sketch drawingLine up nested tikz enviroments or how to get rid of themHow to place nodes in an absolute coordinate system in tikzCommutative diagram with curve connecting between nodesTikz with standalone: pinning tikz coordinates to page cmDrawing a Decision Diagram with Tikz and layout manager