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

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

How to Make a Beautiful Stacked 3D Plot

Is it a good idea to use CNN to classify 1D signal?

How do I stop a creek from eroding my steep embankment?

Delete nth line from bottom

Is it fair for a professor to grade us on the possession of past papers?

Can a new player join a group only when a new campaign starts?

Find the length x such that the two distances in the triangle are the same

How to answer "Have you ever been terminated?"

Around usage results

Withdrew £2800, but only £2000 shows as withdrawn on online banking; what are my obligations?

Is it cost-effective to upgrade an old-ish Giant Escape R3 commuter bike with entry-level branded parts (wheels, drivetrain)?

Quick way to create a symlink?

Amount of permutations on an NxNxN Rubik's Cube

How to react to hostile behavior from a senior developer?

Maximum summed powersets with non-adjacent items

How do I find out the mythology and history of my Fortress?

Is it ethical to give a final exam after the professor has quit before teaching the remaining chapters of the course?

Is there such thing as an Availability Group failover trigger?

What does this Jacques Hadamard quote mean?

Irreducible of finite Krull dimension implies quasi-compact?

How could we fake a moon landing now?

How to compare two different files line by line in unix?

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



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;








2















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




























    2















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
























      2












      2








      2








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










      asked 2 hours ago









      mustacciomustaccio

      10.1k72240




      10.1k72240




















          1 Answer
          1






          active

          oldest

          votes


















          3














          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









            3














            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



























              3














              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

























                3












                3








                3







                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 1 hour ago









                Joe ObbishJoe Obbish

                22k43392




                22k43392



























                    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

                    Are there any AGPL-style licences that require source code modifications to be public? Planned maintenance scheduled April 23, 2019 at 23:30 UTC (7:30pm US/Eastern) Announcing the arrival of Valued Associate #679: Cesar Manara Unicorn Meta Zoo #1: Why another podcast?Force derivative works to be publicAre there any GPL like licenses for Apple App Store?Do you violate the GPL if you provide source code that cannot be compiled?GPL - is it distribution to use libraries in an appliance loaned to customers?Distributing App for free which uses GPL'ed codeModifications of server software under GPL, with web/CLI interfaceDoes using an AGPLv3-licensed library prevent me from dual-licensing my own source code?Can I publish only select code under GPLv3 from a private project?Is there published precedent regarding the scope of covered work that uses AGPL software?If MIT licensed code links to GPL licensed code what should be the license of the resulting binary program?If I use a public API endpoint that has its source code licensed under AGPL in my app, do I need to disclose my source?

                    2013 GY136 Descoberta | Órbita | Referências Menu de navegação«List Of Centaurs and Scattered-Disk Objects»«List of Known Trans-Neptunian Objects»

                    Mortes em março de 2019 Referências Menu de navegação«Zhores Alferov, Nobel de Física bielorrusso, morre aos 88 anos - Ciência»«Fallece Rafael Torija, o bispo emérito de Ciudad Real»«Peter Hurford dies at 88»«Keith Flint, vocalista do The Prodigy, morre aos 49 anos»«Luke Perry, ator de 'Barrados no baile' e 'Riverdale', morre aos 52 anos»«Former Rangers and Scotland captain Eric Caldow dies, aged 84»«Morreu, aos 61 anos, a antiga lenda do wrestling King Kong Bundy»«Fallece el actor y director teatral Abraham Stavans»«In Memoriam Guillaume Faye»«Sidney Sheinberg, a Force Behind Universal and Spielberg, Is Dead at 84»«Carmine Persico, Colombo Crime Family Boss, Is Dead at 85»«Dirigent Michael Gielen gestorben»«Ciclista tricampeã mundial e prata na Rio 2016 é encontrada morta em casa aos 23 anos»«Pagan Community Notes: Raven Grimassi dies, Indianapolis pop-up event cancelled, Circle Sanctuary announces new podcast, and more!»«Hal Blaine, Wrecking Crew Drummer, Dies at 90»«Morre Coutinho, que editou dupla lendária com Pelé no Santos»«Cantor Demétrius, ídolo da Jovem Guarda, morre em SP»«Ex-presidente do Vasco, Eurico Miranda morre no Rio de Janeiro»«Bronze no Mundial de basquete de 1971, Laís Elena morre aos 76 anos»«Diretor de Corridas da F1, Charlie Whiting morre aos 66 anos às vésperas do GP da Austrália»«Morreu o cardeal Danneels, da Bélgica»«Morreu o cartoonista Augusto Cid»«Morreu a atriz Maria Isabel de Lizandra, de "Vale Tudo" e novelas da Tupi»«WS Merwin, prize-winning poet of nature, dies at 91»«Atriz Márcia Real morre em São Paulo aos 88 anos»«Mauritanie: décès de l'ancien président Mohamed Mahmoud ould Louly»«Morreu Dick Dale, o rei da surf guitar e de "Pulp Fiction"»«Falleció Víctor Genes»«João Carlos Marinho, autor de 'O Gênio do Crime', morre em SP»«Legendary Horror Director and SFX Artist John Carl Buechler Dies at 66»«Morre em Salvador a religiosa Makota Valdina»«مرگ بازیکن‌ سابق نساجی بر اثر سقوط سنگ در مازندران»«Domingos Oliveira morre no Rio»«Morre Airton Ravagniani, ex-São Paulo, Fla, Vasco, Grêmio e Sport - Notícias»«Morre o escritor Flavio Moreira da Costa»«Larry Cohen, Writer-Director of 'It's Alive' and 'Hell Up in Harlem,' Dies at 77»«Scott Walker, experimental singer-songwriter, dead at 76»«Joseph Pilato, Day of the Dead Star and Horror Favorite, Dies at 70»«Sheffield United set to pay tribute to legendary goalkeeper Ted Burgin who has died at 91»«Morre Rafael Henzel, sobrevivente de acidente aéreo da Chapecoense»«Morre Valery Bykovsky, um dos primeiros cosmonautas da União Soviética»«Agnès Varda, cineasta da Nouvelle Vague, morre aos 90 anos»«Agnès Varda, cineasta francesa, morre aos 90 anos»«Tania Mallet, James Bond Actress and Helen Mirren's Cousin, Dies at 77»e