If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)? ...
Why aren't air breathing engines used as small first stages
Closed form of recurrent arithmetic series summation
Why do we bend a book to keep it straight?
How would a mousetrap for use in space work?
Do jazz musicians improvise on the parent scale in addition to the chord-scales?
Is it a good idea to use CNN to classify 1D signal?
Delete nth line from bottom
Dating a Former Employee
How to compare two different files line by line in unix?
What do you call the main part of a joke?
Does classifying an integer as a discrete log require it be part of a multiplicative group?
Is the Standard Deduction better than Itemized when both are the same amount?
How do I stop a creek from eroding my steep embankment?
How to Make a Beautiful Stacked 3D Plot
Should I use a zero-interest credit card for a large one-time purchase?
Is it common practice to audition new musicians 1-2-1 before rehearsing with the entire band?
Crossing US/Canada Border for less than 24 hours
What are the out-of-universe reasons for the references to Toby Maguire-era Spider-Man in ITSV
Do I really need to have a message in a novel to appeal to readers?
2001: A Space Odyssey's use of the song "Daisy Bell" (Bicycle Built for Two); life imitates art or vice-versa?
What does the "x" in "x86" represent?
Find the length x such that the two distances in the triangle are the same
Do wooden building fires get hotter than 600°C?
Why are both D and D# fitting into my E minor key?
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;
}
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
add a comment |
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
add a comment |
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
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
sql-server varchar
asked 2 hours ago
mustacciomustaccio
10.1k72240
10.1k72240
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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.
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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.
add a comment |
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.
add a comment |
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.
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.
answered 1 hour ago
Joe ObbishJoe Obbish
22k43392
22k43392
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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