SQL: When does (MAX) go LOB?
Author: jason
Date: 2015-09-02 14:40:53
Category: Technical

When does a NVARCHAR(MAX) or VARCHAR(MAX) column go LOB? Is it right away? Is it after 8KB? A Large Object is stored separately from the table row


First Insert with a small value (no lob reads)


Second Insert with a large string (lots of lob reads)


Deleting the row with the large string, going back to normal (no lob reads)


Code

/*
When do SQL MAX fields (NVARCHAR and VARCHAR) get treated like LOB?

https://msdn.microsoft.com/en-us/library/ms130896.aspx
*/


SET STATISTICS IO ON;

--Create our Test Table with the MAX field
CREATE TABLE [dbo].[LoBReview](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](255) NULL,
[Body] [varchar](max) NULL
)

--But a decidely small value in the MAX field
INSERT INTO dbo.LoBReview
SELECT 'One', 'Two'

--LOB Reads show 0
SELECT * FROM dbo.LoBReview

--Make a LARGE string
DECLARE @sql AS VARCHAR(MAX)
SET @sql = (SELECT
COALESCE(
STUFF(
(SELECT ',' + name AS [text()]
FROM master.dbo.sysobjects
FOR XML PATH(''))
, 1, 1, '')
, ''))

--Put the large string in
INSERT INTO dbo.LoBReview
SELECT 'Three', @sql

--LOB Reads show activity
SELECT * FROM dbo.LoBReview

--Delete the large string
DELETE FROM dbo.LoBReview WHERE ID = 5

--Lob Reads are back to zero
SELECT * FROM dbo.LoBReview



You can also use the physical stats to check out the table


Code

SELECT
OBJECT_NAME([object_id]) AS TableName,
alloc_unit_type_desc AS AllocUnitTp,
page_count AS PgCt,
avg_page_space_used_in_percent AS AvgPgSpcUsed,
record_count AS RcdCt,
min_record_size_in_bytes AS TableName,
max_record_size_in_bytes AS MaxRcdSz,
forwarded_record_count AS FwdRcdCt
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , 'DETAILED');


More details here:
https://www.simple-talk.com/sql/database-administration/whats-the-point-of-using-varchar(n)-anymore/



jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009