SQL: Updating a VARCHAR value with a Large value, aka SQL can push it real good...
Author: jason
Date: 2015-09-02 15:52:53
Category: Technical

Interesting SQL behavior... You have a table with a couple columns, one or two are variable length string data. You INSERT with a small strings, but then later update with larger strings. For instance Fred changes to Freddy. What happens inside SQL? SQL pushes the data to the right (or down, whatever), which can mean rewriting a lot of data...

I'm demo'ing this on SQL 2012, 11.0.5343

Put One, Two Three in the Title, Body, Footer fields, and see that the values are all right next to each other in the page



Put a larger string than "two" into the Body, and the footer column value "three" pushes down.



Now, put in 8000 characters into the Body column (no picture, but it stays on the same original page because its all still smaller than 8KB). And then make the Title column bigger... The whole 8000 string Body "moves" to an entirely new page...




Code

SET STATISTICS IO ON

--https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/


CREATE TABLE [dbo].[PageReview](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Title] [varchar](255) NULL,
[Body] [varchar](MAX) NULL,
[Footer] varchar(255) NULL,
[Date] [datetime] NULL
PRIMARY KEY CLUSTERED
( [Id] ASC ) )

INSERT INTO [PageReview]
SELECT 'One', 'Two', 'Three', GetDate()

SELECT * FROM [PageReview]

--So you can see DBCC PAGE results
DBCC TRACEON(3604);

--Look at the Table/Index to get the PageFID and PagePID
--Page Type 1 is the Data Page that we want
DBCC IND (JasonTestDB, PageReview, -1);

DBCC PAGE (JasonTestDB, 1, 281, 2) WITH TABLERESULTS; --1 is my PageFID and 282 is my PagePID

--DBCC PAGE (JasonTestDB, 1, 281, 3); --Another view of the page...

--Doing an Update just pushes everything down the page
UPDATE PageReview SET Body = 'Some other really big string that definitely is bigger than the previous' where Body = 'two'

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

--Set the field bigger
UPDATE PageReview SET Body = @sql

--Set the Title a little bigger
UPDATE PageReview SET Title = 'Some other value thats a bit bigger than the original and should push us over...'



References
https://msdn.microsoft.com/en-us/library/ms176089.aspx

http://www.practicalsqldba.com/2012/08/sql-server-understanding-data-page.html

https://www.mssqltips.com/sqlservertip/1578/using-dbcc-page-to-examine-sql-server-table-and-index-data/



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