0

Per the SQL Server docs, it looks like if I have one or more varchar/nvarchar columns that cause row length to exceed 8KB, SQL Server will magically handle it.

Scenario:
- Table has 100 cols
- Max current row length is 3.4KB
- But if every varchar/nvarchar field was populated to max, the row length would hit 14kb

From how I interpret the Books Online details linked above, this scenario will work out OK (e.g. nothing will break and everything will work).

Is my assumption true?

  • SQL Server does all row size limit calculations on CREATE/ALTER TABLE. So if you can create your table without error or warning, you'll be ok. As the article you linked to points out, there are some performance impacts to this--so make sure you test that, too. – AMtwo Aug 28 '17 at 11:02
4

Using the undocumented features DBCC IND and DBCC PAGE, you can pretty easily tell exactly what SQL Server does with rows that exceed the "maximum" size of 8,060 bytes.

To show how this works, I'll create a simple table in tempdb where the row width is 10,000 bytes, then run DBCC IND to get a list of pages where the data is stored. We'll then run DBCC PAGE to see the content of those pages.

First, we create the table with a maximum width of 10,000 bytes:

SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.WidthTest', N'U') IS NOT NULL
DROP TABLE dbo.WidthTest;
CREATE TABLE dbo.WidthTest
(
      Col01 varchar(1000) NOT NULL
    , Col02 varchar(1000) NOT NULL
    , Col03 varchar(1000) NOT NULL
    , Col04 varchar(1000) NOT NULL
    , Col05 varchar(1000) NOT NULL
    , Col06 varchar(1000) NOT NULL
    , Col07 varchar(1000) NOT NULL
    , Col08 varchar(1000) NOT NULL
    , Col09 varchar(1000) NOT NULL
    , Col10 varchar(1000) NOT NULL
) ON [PRIMARY];

And populate that with a single row:

INSERT INTO dbo.WidthTest (Col01, Col02, Col03, Col04, Col05, Col06, Col07, Col08, Col09, Col10)
VALUES (
      REPLICATE('A', 1000)
    , REPLICATE('B', 1000)
    , REPLICATE('C', 1000)
    , REPLICATE('D', 1000)
    , REPLICATE('E', 1000)
    , REPLICATE('F', 1000)
    , REPLICATE('G', 1000)
    , REPLICATE('H', 1000)
    , REPLICATE('I', 1000)
    , REPLICATE('J', 1000)
    );

Here we're creating a table to hold the DBCC IND output, and populating that table:

DECLARE @fileID int;
DECLARE @pageID int;
DECLARE @slotID int
DECLARE @cmd nvarchar(max);

IF OBJECT_ID(N'dbo.IndexDetails', N'U') IS NOT NULL
DROP TABLE dbo.IndexDetails;
CREATE TABLE dbo.IndexDetails 
(
      PageFID int NULL
    , PagePID int NULL
    , IAMFID int NULL
    , IAMPID int NULL
    , ObjectID int NULL
    , IndexID int NULL
    , PartitionNumber int NULL
    , PartitionID bigint NULL
    , iam_chain_type varchar(100) NULL
    , PageType int NULL
    , IndexLevel int NULL
    , NextPageFID int NULL
    , NextPagePID int NULL
    , PrevPageFID int NULL
    , PrevPagePID int NULL
) ON [PRIMARY];

SET @cmd = 'DBCC IND (''tempdb'', ''dbo.WidthTest'', 1);';
INSERT INTO dbo.IndexDetails 
(
      PageFID 
    , PagePID 
    , IAMFID 
    , IAMPID 
    , ObjectID
    , IndexID 
    , PartitionNumber
    , PartitionID
    , iam_chain_type
    , PageType
    , IndexLevel
    , NextPageFID
    , NextPagePID
    , PrevPageFID
    , PrevPagePID
)
EXEC sys.sp_executesql @cmd;

Finally this cursor iterates over the contents of the dbo.IndexDetails table showing the page contents for each page used by the table:

DBCC TRACEON (3604); /* send DBCC PAGE output to the client (messages tab) */
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC
FOR
SELECT PageFID, PagePID
FROM dbo.IndexDetails id
WHERE id.PageType IN (1, 3, 4) /* data pages, and text pages */;
OPEN cur;
FETCH NEXT FROM cur INTO @fileID, @pageID;
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC PAGE (tempdb, @fileID, @pageID, 1)
    FETCH NEXT FROM cur INTO @fileID, @pageID;
END
CLOSE cur;
DEALLOCATE cur;
DBCC TRACEOFF (3604); /* send DBCC PAGE output to the error log (default) */

The interesting bits of output from the cursor are displayed on the Messages tab in SSMS.

This is the first page. Notice the Record Type = PRIMARY_RECORD:

PAGE: (1:319464)

BUFFER:

BUF @0x0000000203A1C0C0

bpage = 0x000000017018E000          bhash = 0x0000000000000000          bpageno = (1:319464)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 950                         bstat = 0x10b
blog = 0x212121cc                   bnext = 0x0000000000000000          

PAGE HEADER:

Page @0x000000017018E000

m_pageId = (1:319464)               m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 178209m_indexId (AllocUnitId.idInd) = 7680
Metadata: AllocUnitId = 2161727832816943104                              
Metadata: PartitionId = 1152921515708121088                              Metadata: IndexId = 0
Metadata: ObjectId = 1013578649     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 4                         m_slotCnt = 1                       m_freeCnt = 992
m_freeData = 7198                   m_reservedCnt = 0                   m_lsn = (60:220956:448)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          
PFS (1:315432) = 0x43 ALLOCATED  95_PCT_FULL                             DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Slot 0, Offset 0x60, Length 7102, DumpStyle BYTE

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 7102                  
Memory Dump @0x000000000DBAA060

0000000000000000:  0......ü..6.N.f.N.6.
0000000000000014:  ....î.Ö.¾..........>
0000000000000028:  ..è...àß............
000000000000003C:  ..I...è...àß........
0000000000000050:  ......2_..è...àß....
0000000000000064:  ..DDDDDDDDDDDDDDDDDD
0000000000000078:  DDDDDDDDDDDDDDDDDDDD
000000000000008C:  DDDDDDDDDDDDDDDDDDDD
00000000000000A0:  DDDDDDDDDDDDDDDDDDDD
00000000000000B4:  DDDDDDDDDDDDDDDDDDDD
00000000000000C8:  DDDDDDDDDDDDDDDDDDDD
00000000000000DC:  DDDDDDDDDDDDDDDDDDDD
00000000000000F0:  DDDDDDDDDDDDDDDDDDDD
0000000000000104:  DDDDDDDDDDDDDDDDDDDD
0000000000000118:  DDDDDDDDDDDDDDDDDDDD
000000000000012C:  DDDDDDDDDDDDDDDDDDDD
0000000000000140:  DDDDDDDDDDDDDDDDDDDD
0000000000000154:  DDDDDDDDDDDDDDDDDDDD
0000000000000168:  DDDDDDDDDDDDDDDDDDDD
000000000000017C:  DDDDDDDDDDDDDDDDDDDD
0000000000000190:  DDDDDDDDDDDDDDDDDDDD
00000000000001A4:  DDDDDDDDDDDDDDDDDDDD
00000000000001B8:  DDDDDDDDDDDDDDDDDDDD
00000000000001CC:  DDDDDDDDDDDDDDDDDDDD
00000000000001E0:  DDDDDDDDDDDDDDDDDDDD
00000000000001F4:  DDDDDDDDDDDDDDDDDDDD
0000000000000208:  DDDDDDDDDDDDDDDDDDDD
000000000000021C:  DDDDDDDDDDDDDDDDDDDD
0000000000000230:  DDDDDDDDDDDDDDDDDDDD
0000000000000244:  DDDDDDDDDDDDDDDDDDDD
0000000000000258:  DDDDDDDDDDDDDDDDDDDD
000000000000026C:  DDDDDDDDDDDDDDDDDDDD
0000000000000280:  DDDDDDDDDDDDDDDDDDDD
0000000000000294:  DDDDDDDDDDDDDDDDDDDD
00000000000002A8:  DDDDDDDDDDDDDDDDDDDD
00000000000002BC:  DDDDDDDDDDDDDDDDDDDD
00000000000002D0:  DDDDDDDDDDDDDDDDDDDD
00000000000002E4:  DDDDDDDDDDDDDDDDDDDD
00000000000002F8:  DDDDDDDDDDDDDDDDDDDD
000000000000030C:  DDDDDDDDDDDDDDDDDDDD
0000000000000320:  DDDDDDDDDDDDDDDDDDDD
0000000000000334:  DDDDDDDDDDDDDDDDDDDD
0000000000000348:  DDDDDDDDDDDDDDDDDDDD
000000000000035C:  DDDDDDDDDDDDDDDDDDDD
0000000000000370:  DDDDDDDDDDDDDDDDDDDD
0000000000000384:  DDDDDDDDDDDDDDDDDDDD
0000000000000398:  DDDDDDDDDDDDDDDDDDDD
00000000000003AC:  DDDDDDDDDDDDDDDDDDDD
00000000000003C0:  DDDDDDDDDDDDDDDDDDDD
00000000000003D4:  DDDDDDDDDDDDDDDDDDDD
00000000000003E8:  DDDDDDDDDDDDDDDDDDDD
00000000000003FC:  DDDDDDDDDDDDDDDDDDDD
0000000000000410:  DDDDDDDDDDDDDDDDDDDD
0000000000000424:  DDDDDDDDDDDDDDDDDDDD
0000000000000438:  DDDDDDDDDDDDDDDDDDDD
000000000000044C:  DDEEEEEEEEEEEEEEEEEE
0000000000000460:  EEEEEEEEEEEEEEEEEEEE
0000000000000474:  EEEEEEEEEEEEEEEEEEEE
0000000000000488:  EEEEEEEEEEEEEEEEEEEE
000000000000049C:  EEEEEEEEEEEEEEEEEEEE
00000000000004B0:  EEEEEEEEEEEEEEEEEEEE
00000000000004C4:  EEEEEEEEEEEEEEEEEEEE
00000000000004D8:  EEEEEEEEEEEEEEEEEEEE
00000000000004EC:  EEEEEEEEEEEEEEEEEEEE
0000000000000500:  EEEEEEEEEEEEEEEEEEEE
0000000000000514:  EEEEEEEEEEEEEEEEEEEE
0000000000000528:  EEEEEEEEEEEEEEEEEEEE
000000000000053C:  EEEEEEEEEEEEEEEEEEEE
0000000000000550:  EEEEEEEEEEEEEEEEEEEE
0000000000000564:  EEEEEEEEEEEEEEEEEEEE
0000000000000578:  EEEEEEEEEEEEEEEEEEEE
000000000000058C:  EEEEEEEEEEEEEEEEEEEE
00000000000005A0:  EEEEEEEEEEEEEEEEEEEE
00000000000005B4:  EEEEEEEEEEEEEEEEEEEE
00000000000005C8:  EEEEEEEEEEEEEEEEEEEE
00000000000005DC:  EEEEEEEEEEEEEEEEEEEE
00000000000005F0:  EEEEEEEEEEEEEEEEEEEE
0000000000000604:  EEEEEEEEEEEEEEEEEEEE
0000000000000618:  EEEEEEEEEEEEEEEEEEEE
000000000000062C:  EEEEEEEEEEEEEEEEEEEE
0000000000000640:  EEEEEEEEEEEEEEEEEEEE
0000000000000654:  EEEEEEEEEEEEEEEEEEEE
0000000000000668:  EEEEEEEEEEEEEEEEEEEE
000000000000067C:  EEEEEEEEEEEEEEEEEEEE
0000000000000690:  EEEEEEEEEEEEEEEEEEEE
00000000000006A4:  EEEEEEEEEEEEEEEEEEEE
00000000000006B8:  EEEEEEEEEEEEEEEEEEEE
00000000000006CC:  EEEEEEEEEEEEEEEEEEEE
00000000000006E0:  EEEEEEEEEEEEEEEEEEEE
00000000000006F4:  EEEEEEEEEEEEEEEEEEEE
0000000000000708:  EEEEEEEEEEEEEEEEEEEE
000000000000071C:  EEEEEEEEEEEEEEEEEEEE
0000000000000730:  EEEEEEEEEEEEEEEEEEEE
0000000000000744:  EEEEEEEEEEEEEEEEEEEE
0000000000000758:  EEEEEEEEEEEEEEEEEEEE
000000000000076C:  EEEEEEEEEEEEEEEEEEEE
0000000000000780:  EEEEEEEEEEEEEEEEEEEE
0000000000000794:  EEEEEEEEEEEEEEEEEEEE
00000000000007A8:  EEEEEEEEEEEEEEEEEEEE
00000000000007BC:  EEEEEEEEEEEEEEEEEEEE
00000000000007D0:  EEEEEEEEEEEEEEEEEEEE
00000000000007E4:  EEEEEEEEEEEEEEEEEEEE
00000000000007F8:  EEEEEEEEEEEEEEEEEEEE
000000000000080C:  EEEEEEEEEEEEEEEEEEEE
0000000000000820:  EEEEEEEEEEEEEEEEEEEE
0000000000000834:  EEFFFFFFFFFFFFFFFFFF
0000000000000848:  FFFFFFFFFFFFFFFFFFFF
000000000000085C:  FFFFFFFFFFFFFFFFFFFF
0000000000000870:  FFFFFFFFFFFFFFFFFFFF
0000000000000884:  FFFFFFFFFFFFFFFFFFFF
0000000000000898:  FFFFFFFFFFFFFFFFFFFF
00000000000008AC:  FFFFFFFFFFFFFFFFFFFF
00000000000008C0:  FFFFFFFFFFFFFFFFFFFF
00000000000008D4:  FFFFFFFFFFFFFFFFFFFF
00000000000008E8:  FFFFFFFFFFFFFFFFFFFF
00000000000008FC:  FFFFFFFFFFFFFFFFFFFF
0000000000000910:  FFFFFFFFFFFFFFFFFFFF
0000000000000924:  FFFFFFFFFFFFFFFFFFFF
0000000000000938:  FFFFFFFFFFFFFFFFFFFF
000000000000094C:  FFFFFFFFFFFFFFFFFFFF
0000000000000960:  FFFFFFFFFFFFFFFFFFFF
0000000000000974:  FFFFFFFFFFFFFFFFFFFF
0000000000000988:  FFFFFFFFFFFFFFFFFFFF
000000000000099C:  FFFFFFFFFFFFFFFFFFFF
00000000000009B0:  FFFFFFFFFFFFFFFFFFFF
00000000000009C4:  FFFFFFFFFFFFFFFFFFFF
00000000000009D8:  FFFFFFFFFFFFFFFFFFFF
00000000000009EC:  FFFFFFFFFFFFFFFFFFFF
0000000000000A00:  FFFFFFFFFFFFFFFFFFFF
0000000000000A14:  FFFFFFFFFFFFFFFFFFFF
0000000000000A28:  FFFFFFFFFFFFFFFFFFFF
0000000000000A3C:  FFFFFFFFFFFFFFFFFFFF
0000000000000A50:  FFFFFFFFFFFFFFFFFFFF
0000000000000A64:  FFFFFFFFFFFFFFFFFFFF
0000000000000A78:  FFFFFFFFFFFFFFFFFFFF
0000000000000A8C:  FFFFFFFFFFFFFFFFFFFF
0000000000000AA0:  FFFFFFFFFFFFFFFFFFFF
0000000000000AB4:  FFFFFFFFFFFFFFFFFFFF
0000000000000AC8:  FFFFFFFFFFFFFFFFFFFF
0000000000000ADC:  FFFFFFFFFFFFFFFFFFFF
0000000000000AF0:  FFFFFFFFFFFFFFFFFFFF
0000000000000B04:  FFFFFFFFFFFFFFFFFFFF
0000000000000B18:  FFFFFFFFFFFFFFFFFFFF
0000000000000B2C:  FFFFFFFFFFFFFFFFFFFF
0000000000000B40:  FFFFFFFFFFFFFFFFFFFF
0000000000000B54:  FFFFFFFFFFFFFFFFFFFF
0000000000000B68:  FFFFFFFFFFFFFFFFFFFF
0000000000000B7C:  FFFFFFFFFFFFFFFFFFFF
0000000000000B90:  FFFFFFFFFFFFFFFFFFFF
0000000000000BA4:  FFFFFFFFFFFFFFFFFFFF
0000000000000BB8:  FFFFFFFFFFFFFFFFFFFF
0000000000000BCC:  FFFFFFFFFFFFFFFFFFFF
0000000000000BE0:  FFFFFFFFFFFFFFFFFFFF
0000000000000BF4:  FFFFFFFFFFFFFFFFFFFF
0000000000000C08:  FFFFFFFFFFFFFFFFFFFF
0000000000000C1C:  FFGGGGGGGGGGGGGGGGGG
0000000000000C30:  GGGGGGGGGGGGGGGGGGGG
0000000000000C44:  GGGGGGGGGGGGGGGGGGGG
0000000000000C58:  GGGGGGGGGGGGGGGGGGGG
0000000000000C6C:  GGGGGGGGGGGGGGGGGGGG
0000000000000C80:  GGGGGGGGGGGGGGGGGGGG
0000000000000C94:  GGGGGGGGGGGGGGGGGGGG
0000000000000CA8:  GGGGGGGGGGGGGGGGGGGG
0000000000000CBC:  GGGGGGGGGGGGGGGGGGGG
0000000000000CD0:  GGGGGGGGGGGGGGGGGGGG
0000000000000E38:  GGGGGGGGGGGGGGGGGGGG
0000000000000E4C:  GGGGGGGGGGGGGGGGGGGG
0000000000000E60:  GGGGGGGGGGGGGGGGGGGG
0000000000000E74:  GGGGGGGGGGGGGGGGGGGG
0000000000000E88:  GGGGGGGGGGGGGGGGGGGG
0000000000000E9C:  GGGGGGGGGGGGGGGGGGGG
0000000000000EB0:  GGGGGGGGGGGGGGGGGGGG
0000000000000EC4:  GGGGGGGGGGGGGGGGGGGG
0000000000000ED8:  GGGGGGGGGGGGGGGGGGGG
0000000000000EEC:  GGGGGGGGGGGGGGGGGGGG
0000000000000F00:  GGGGGGGGGGGGGGGGGGGG
0000000000000F78:  GGGGGGGGGGGGGGGGGGGG
0000000000000F8C:  GGGGGGGGGGGGGGGGGGGG
0000000000000FA0:  GGGGGGGGGGGGGGGGGGGG
0000000000000FB4:  GGGGGGGGGGGGGGGGGGGG
0000000000000FC8:  GGGGGGGGGGGGGGGGGGGG
0000000000000FDC:  GGGGGGGGGGGGGGGGGGGG
0000000000000FF0:  GGGGGGGGGGGGGGGGGGGG
0000000000001004:  GGHHHHHHHHHHHHHHHHHH
0000000000001018:  HHHHHHHHHHHHHHHHHHHH
000000000000102C:  HHHHHHHHHHHHHHHHHHHH
0000000000001040:  HHHHHHHHHHHHHHHHHHHH
0000000000001054:  HHHHHHHHHHHHHHHHHHHH
0000000000001194:  HHHHHHHHHHHHHHHHHHHH
00000000000011A8:  HHHHHHHHHHHHHHHHHHHH
00000000000011BC:  HHHHHHHHHHHHHHHHHHHH
00000000000011D0:  HHHHHHHHHHHHHHHHHHHH
00000000000011E4:  HHHHHHHHHHHHHHHHHHHH
00000000000011F8:  HHHHHHHHHHHHHHHHHHHH
000000000000120C:  HHHHHHHHHHHHHHHHHHHH
0000000000001220:  HHHHHHHHHHHHHHHHHHHH
0000000000001234:  HHHHHHHHHHHHHHHHHHHH
0000000000001248:  HHHHHHHHHHHHHHHHHHHH
000000000000125C:  HHHHHHHHHHHHHHHHHHHH
0000000000001270:  HHHHHHHHHHHHHHHHHHHH
0000000000001284:  HHHHHHHHHHHHHHHHHHHH
0000000000001298:  HHHHHHHHHHHHHHHHHHHH
00000000000012AC:  HHHHHHHHHHHHHHHHHHHH
00000000000012C0:  HHHHHHHHHHHHHHHHHHHH
00000000000012D4:  HHHHHHHHHHHHHHHHHHHH
00000000000012E8:  HHHHHHHHHHHHHHHHHHHH
00000000000012FC:  HHHHHHHHHHHHHHHHHHHH
0000000000001310:  HHHHHHHHHHHHHHHHHHHH
0000000000001324:  HHHHHHHHHHHHHHHHHHHH
0000000000001338:  HHHHHHHHHHHHHHHHHHHH
000000000000134C:  HHHHHHHHHHHHHHHHHHHH
0000000000001360:  HHHHHHHHHHHHHHHHHHHH
0000000000001374:  HHHHHHHHHHHHHHHHHHHH
0000000000001388:  HHHHHHHHHHHHHHHHHHHH
000000000000139C:  HHHHHHHHHHHHHHHHHHHH
00000000000013B0:  HHHHHHHHHHHHHHHHHHHH
00000000000013C4:  HHHHHHHHHHHHHHHHHHHH
00000000000013D8:  HHHHHHHHHHHHHHHHHHHH
00000000000013EC:  HHIIIIIIIIIIIIIIIIII
0000000000001400:  IIIIIIIIIIIIIIIIIIII
0000000000001414:  IIIIIIIIIIIIIIIIIIII
0000000000001428:  IIIIIIIIIIIIIIIIIIII
000000000000143C:  IIIIIIIIIIIIIIIIIIII
0000000000001450:  IIIIIIIIIIIIIIIIIIII
0000000000001464:  IIIIIIIIIIIIIIIIIIII
0000000000001478:  IIIIIIIIIIIIIIIIIIII
000000000000148C:  IIIIIIIIIIIIIIIIIIII
00000000000014A0:  IIIIIIIIIIIIIIIIIIII
00000000000014B4:  IIIIIIIIIIIIIIIIIIII
00000000000014C8:  IIIIIIIIIIIIIIIIIIII
00000000000014DC:  IIIIIIIIIIIIIIIIIIII
00000000000014F0:  IIIIIIIIIIIIIIIIIIII
0000000000001504:  IIIIIIIIIIIIIIIIIIII
0000000000001518:  IIIIIIIIIIIIIIIIIIII
000000000000152C:  IIIIIIIIIIIIIIIIIIII
0000000000001540:  IIIIIIIIIIIIIIIIIIII
0000000000001554:  IIIIIIIIIIIIIIIIIIII
0000000000001568:  IIIIIIIIIIIIIIIIIIII
000000000000157C:  IIIIIIIIIIIIIIIIIIII
0000000000001590:  IIIIIIIIIIIIIIIIIIII
00000000000015A4:  IIIIIIIIIIIIIIIIIIII
00000000000015B8:  IIIIIIIIIIIIIIIIIIII
00000000000015CC:  IIIIIIIIIIIIIIIIIIII
00000000000015E0:  IIIIIIIIIIIIIIIIIIII
00000000000015F4:  IIIIIIIIIIIIIIIIIIII
0000000000001608:  IIIIIIIIIIIIIIIIIIII
000000000000161C:  IIIIIIIIIIIIIIIIIIII
0000000000001630:  IIIIIIIIIIIIIIIIIIII
0000000000001644:  IIIIIIIIIIIIIIIIIIII
0000000000001658:  IIIIIIIIIIIIIIIIIIII
000000000000166C:  IIIIIIIIIIIIIIIIIIII
0000000000001680:  IIIIIIIIIIIIIIIIIIII
0000000000001694:  IIIIIIIIIIIIIIIIIIII
00000000000016A8:  IIIIIIIIIIIIIIIIIIII
00000000000016BC:  IIIIIIIIIIIIIIIIIIII
00000000000016D0:  IIIIIIIIIIIIIIIIIIII
00000000000016E4:  IIIIIIIIIIIIIIIIIIII
00000000000016F8:  IIIIIIIIIIIIIIIIIIII
000000000000170C:  IIIIIIIIIIIIIIIIIIII
0000000000001720:  IIIIIIIIIIIIIIIIIIII
0000000000001734:  IIIIIIIIIIIIIIIIIIII
0000000000001748:  IIIIIIIIIIIIIIIIIIII
000000000000175C:  IIIIIIIIIIIIIIIIIIII
0000000000001770:  IIIIIIIIIIIIIIIIIIII
0000000000001784:  IIIIIIIIIIIIIIIIIIII
0000000000001798:  IIIIIIIIIIIIIIIIIIII
00000000000017AC:  IIIIIIIIIIIIIIIIIIII
00000000000017C0:  IIIIIIIIIIIIIIIIIIII
00000000000017D4:  IIJJJJJJJJJJJJJJJJJJ
00000000000017E8:  JJJJJJJJJJJJJJJJJJJJ
00000000000017FC:  JJJJJJJJJJJJJJJJJJJJ
0000000000001810:  JJJJJJJJJJJJJJJJJJJJ
0000000000001824:  JJJJJJJJJJJJJJJJJJJJ
0000000000001838:  JJJJJJJJJJJJJJJJJJJJ
000000000000184C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001860:  JJJJJJJJJJJJJJJJJJJJ
0000000000001874:  JJJJJJJJJJJJJJJJJJJJ
0000000000001888:  JJJJJJJJJJJJJJJJJJJJ
000000000000189C:  JJJJJJJJJJJJJJJJJJJJ
00000000000018B0:  JJJJJJJJJJJJJJJJJJJJ
00000000000018C4:  JJJJJJJJJJJJJJJJJJJJ
00000000000018D8:  JJJJJJJJJJJJJJJJJJJJ
00000000000018EC:  JJJJJJJJJJJJJJJJJJJJ
0000000000001900:  JJJJJJJJJJJJJJJJJJJJ
0000000000001914:  JJJJJJJJJJJJJJJJJJJJ
0000000000001928:  JJJJJJJJJJJJJJJJJJJJ
000000000000193C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001950:  JJJJJJJJJJJJJJJJJJJJ
0000000000001964:  JJJJJJJJJJJJJJJJJJJJ
0000000000001978:  JJJJJJJJJJJJJJJJJJJJ
000000000000198C:  JJJJJJJJJJJJJJJJJJJJ
00000000000019A0:  JJJJJJJJJJJJJJJJJJJJ
00000000000019B4:  JJJJJJJJJJJJJJJJJJJJ
00000000000019C8:  JJJJJJJJJJJJJJJJJJJJ
00000000000019DC:  JJJJJJJJJJJJJJJJJJJJ
00000000000019F0:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A04:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A18:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A2C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A40:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A54:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A68:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A7C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001A90:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AA4:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AB8:  JJJJJJJJJJJJJJJJJJJJ
0000000000001ACC:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AE0:  JJJJJJJJJJJJJJJJJJJJ
0000000000001AF4:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B08:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B1C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B30:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B44:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B58:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B6C:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B80:  JJJJJJJJJJJJJJJJJJJJ
0000000000001B94:  JJJJJJJJJJJJJJJJJJJJ
0000000000001BA8:  JJJJJJJJJJJJJJJJJJJJ
0000000000001BBC:  JJ     

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)                 


DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I've removed the hexaxdecimal content of the actual page dump to keep my post under the 30,000 character limit. As you can see from the page dump above, the primary page in my example does not contain the first 3 columns, A, B, and C - they are stored in the row overflow region, shown next, with Record Type = BLOB_FRAGMENT:

PAGE: (1:319456)

BUFFER:

BUF @0x0000000203A19240

bpage = 0x0000000170186000          bhash = 0x0000000000000000          bpageno = (1:319456)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 950                         bstat = 0x10b
blog = 0x1212121c                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x0000000170186000

m_pageId = (1:319456)               m_headerVersion = 1                 m_type = 3
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 178834m_indexId (AllocUnitId.idInd) = 7936
Metadata: AllocUnitId = 2233785426895831040                              
Metadata: PartitionId = 1152921515708121088                              Metadata: IndexId = 0
Metadata: ObjectId = 1013578649     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 0                         m_slotCnt = 3                       m_freeCnt = 5048
m_freeData = 3166                   m_reservedCnt = 0                   m_lsn = (60:220956:430)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          
PFS (1:315432) = 0x41 ALLOCATED  50_PCT_FULL                             DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

DATA:


Slot 0, Offset 0x60, Length 1014, DumpStyle BYTE

Record Type = BLOB_FRAGMENT         Record Attributes =                 Record Size = 1014

Memory Dump @0x000000000DBAA060

0000000000000000:  ..ö....>......AAAAAA
0000000000000014:  AAAAAAAAAAAAAAAAAAAA
0000000000000028:  AAAAAAAAAAAAAAAAAAAA
000000000000003C:  AAAAAAAAAAAAAAAAAAAA
0000000000000050:  AAAAAAAAAAAAAAAAAAAA
0000000000000064:  AAAAAAAAAAAAAAAAAAAA
0000000000000078:  AAAAAAAAAAAAAAAAAAAA
000000000000008C:  AAAAAAAAAAAAAAAAAAAA
00000000000000A0:  AAAAAAAAAAAAAAAAAAAA
00000000000000B4:  AAAAAAAAAAAAAAAAAAAA
00000000000000C8:  AAAAAAAAAAAAAAAAAAAA
00000000000000DC:  AAAAAAAAAAAAAAAAAAAA
00000000000000F0:  AAAAAAAAAAAAAAAAAAAA
0000000000000104:  AAAAAAAAAAAAAAAAAAAA
0000000000000118:  AAAAAAAAAAAAAAAAAAAA
000000000000012C:  AAAAAAAAAAAAAAAAAAAA
0000000000000140:  AAAAAAAAAAAAAAAAAAAA
0000000000000154:  AAAAAAAAAAAAAAAAAAAA
0000000000000168:  AAAAAAAAAAAAAAAAAAAA
000000000000017C:  AAAAAAAAAAAAAAAAAAAA
0000000000000190:  AAAAAAAAAAAAAAAAAAAA
00000000000001A4:  AAAAAAAAAAAAAAAAAAAA
00000000000001B8:  AAAAAAAAAAAAAAAAAAAA
00000000000001CC:  AAAAAAAAAAAAAAAAAAAA
00000000000001E0:  AAAAAAAAAAAAAAAAAAAA
00000000000001F4:  AAAAAAAAAAAAAAAAAAAA
0000000000000208:  AAAAAAAAAAAAAAAAAAAA
000000000000021C:  AAAAAAAAAAAAAAAAAAAA
0000000000000230:  AAAAAAAAAAAAAAAAAAAA
0000000000000244:  AAAAAAAAAAAAAAAAAAAA
0000000000000258:  AAAAAAAAAAAAAAAAAAAA
000000000000026C:  AAAAAAAAAAAAAAAAAAAA
0000000000000280:  AAAAAAAAAAAAAAAAAAAA
0000000000000294:  AAAAAAAAAAAAAAAAAAAA
00000000000002A8:  AAAAAAAAAAAAAAAAAAAA
00000000000002BC:  AAAAAAAAAAAAAAAAAAAA
00000000000002D0:  AAAAAAAAAAAAAAAAAAAA
00000000000002E4:  AAAAAAAAAAAAAAAAAAAA
00000000000002F8:  AAAAAAAAAAAAAAAAAAAA
000000000000030C:  AAAAAAAAAAAAAAAAAAAA
0000000000000320:  AAAAAAAAAAAAAAAAAAAA
0000000000000334:  AAAAAAAAAAAAAAAAAAAA
0000000000000348:  AAAAAAAAAAAAAAAAAAAA
000000000000035C:  AAAAAAAAAAAAAAAAAAAA
0000000000000370:  AAAAAAAAAAAAAAAAAAAA
0000000000000384:  AAAAAAAAAAAAAAAAAAAA
0000000000000398:  AAAAAAAAAAAAAAAAAAAA
00000000000003AC:  AAAAAAAAAAAAAAAAAAAA
00000000000003C0:  AAAAAAAAAAAAAAAAAAAA
00000000000003D4:  AAAAAAAAAAAAAAAAAAAA
00000000000003E8:  AAAAAAAAAAAAAA


Slot 1, Offset 0x464, Length 1014, DumpStyle BYTE

Record Type = BLOB_FRAGMENT         Record Attributes =                 Record Size = 1014

Memory Dump @0x000000000DBAA464

0000000000000000:  ..ö...I.......BBBBBB
0000000000000014:  BBBBBBBBBBBBBBBBBBBB
0000000000000028:  BBBBBBBBBBBBBBBBBBBB
000000000000003C:  BBBBBBBBBBBBBBBBBBBB
0000000000000050:  BBBBBBBBBBBBBBBBBBBB
0000000000000064:  BBBBBBBBBBBBBBBBBBBB
0000000000000078:  BBBBBBBBBBBBBBBBBBBB
000000000000008C:  BBBBBBBBBBBBBBBBBBBB
00000000000000A0:  BBBBBBBBBBBBBBBBBBBB
00000000000000B4:  BBBBBBBBBBBBBBBBBBBB
00000000000000C8:  BBBBBBBBBBBBBBBBBBBB
00000000000000DC:  BBBBBBBBBBBBBBBBBBBB
00000000000000F0:  BBBBBBBBBBBBBBBBBBBB
0000000000000104:  BBBBBBBBBBBBBBBBBBBB
0000000000000118:  BBBBBBBBBBBBBBBBBBBB
000000000000012C:  BBBBBBBBBBBBBBBBBBBB
0000000000000140:  BBBBBBBBBBBBBBBBBBBB
0000000000000154:  BBBBBBBBBBBBBBBBBBBB
0000000000000168:  BBBBBBBBBBBBBBBBBBBB
000000000000017C:  BBBBBBBBBBBBBBBBBBBB
0000000000000190:  BBBBBBBBBBBBBBBBBBBB
00000000000001A4:  BBBBBBBBBBBBBBBBBBBB
00000000000001B8:  BBBBBBBBBBBBBBBBBBBB
00000000000001CC:  BBBBBBBBBBBBBBBBBBBB
00000000000001E0:  BBBBBBBBBBBBBBBBBBBB
00000000000001F4:  BBBBBBBBBBBBBBBBBBBB
0000000000000208:  BBBBBBBBBBBBBBBBBBBB
000000000000021C:  BBBBBBBBBBBBBBBBBBBB
0000000000000230:  BBBBBBBBBBBBBBBBBBBB
0000000000000244:  BBBBBBBBBBBBBBBBBBBB
0000000000000258:  BBBBBBBBBBBBBBBBBBBB
000000000000026C:  BBBBBBBBBBBBBBBBBBBB
0000000000000280:  BBBBBBBBBBBBBBBBBBBB
0000000000000294:  BBBBBBBBBBBBBBBBBBBB
00000000000002A8:  BBBBBBBBBBBBBBBBBBBB
00000000000002BC:  BBBBBBBBBBBBBBBBBBBB
00000000000002D0:  BBBBBBBBBBBBBBBBBBBB
00000000000002E4:  BBBBBBBBBBBBBBBBBBBB
00000000000002F8:  BBBBBBBBBBBBBBBBBBBB
000000000000030C:  BBBBBBBBBBBBBBBBBBBB
0000000000000320:  BBBBBBBBBBBBBBBBBBBB
0000000000000334:  BBBBBBBBBBBBBBBBBBBB
0000000000000348:  BBBBBBBBBBBBBBBBBBBB
000000000000035C:  BBBBBBBBBBBBBBBBBBBB
0000000000000370:  BBBBBBBBBBBBBBBBBBBB
0000000000000384:  BBBBBBBBBBBBBBBBBBBB
0000000000000398:  BBBBBBBBBBBBBBBBBBBB
00000000000003AC:  BBBBBBBBBBBBBBBBBBBB
00000000000003C0:  BBBBBBBBBBBBBBBBBBBB
00000000000003D4:  BBBBBBBBBBBBBBBBBBBB
00000000000003E8:  BBBBBBBBBBBBBB

Slot 2, Offset 0x868, Length 1014, DumpStyle BYTE

Record Type = BLOB_FRAGMENT         Record Attributes =                 Record Size = 1014

Memory Dump @0x000000000DBAA868

0000000000000000:   ..ö...2_......CCCCCC
0000000000000014:   CCCCCCCCCCCCCCCCCCCC
0000000000000028:   CCCCCCCCCCCCCCCCCCCC
000000000000003C:   CCCCCCCCCCCCCCCCCCCC
0000000000000050:   CCCCCCCCCCCCCCCCCCCC
0000000000000064:   CCCCCCCCCCCCCCCCCCCC
0000000000000078:   CCCCCCCCCCCCCCCCCCCC
000000000000008C:   CCCCCCCCCCCCCCCCCCCC
00000000000000A0:   CCCCCCCCCCCCCCCCCCCC
00000000000000B4:   CCCCCCCCCCCCCCCCCCCC
00000000000000C8:   CCCCCCCCCCCCCCCCCCCC
00000000000000DC:   CCCCCCCCCCCCCCCCCCCC
00000000000000F0:   CCCCCCCCCCCCCCCCCCCC
0000000000000104:   CCCCCCCCCCCCCCCCCCCC
0000000000000118:   CCCCCCCCCCCCCCCCCCCC
000000000000012C:   CCCCCCCCCCCCCCCCCCCC
0000000000000140:   CCCCCCCCCCCCCCCCCCCC
0000000000000154:   CCCCCCCCCCCCCCCCCCCC
0000000000000168:   CCCCCCCCCCCCCCCCCCCC
000000000000017C:   CCCCCCCCCCCCCCCCCCCC
0000000000000190:   CCCCCCCCCCCCCCCCCCCC
00000000000001A4:   CCCCCCCCCCCCCCCCCCCC
00000000000001B8:   CCCCCCCCCCCCCCCCCCCC
00000000000001CC:   CCCCCCCCCCCCCCCCCCCC
00000000000001E0:   CCCCCCCCCCCCCCCCCCCC
00000000000001F4:   CCCCCCCCCCCCCCCCCCCC
0000000000000208:   CCCCCCCCCCCCCCCCCCCC
000000000000021C:   CCCCCCCCCCCCCCCCCCCC
0000000000000230:   CCCCCCCCCCCCCCCCCCCC
0000000000000244:   CCCCCCCCCCCCCCCCCCCC
0000000000000258:   CCCCCCCCCCCCCCCCCCCC
000000000000026C:   CCCCCCCCCCCCCCCCCCCC
0000000000000280:   CCCCCCCCCCCCCCCCCCCC
0000000000000294:   CCCCCCCCCCCCCCCCCCCC
00000000000002A8:   CCCCCCCCCCCCCCCCCCCC
00000000000002BC:   CCCCCCCCCCCCCCCCCCCC
00000000000002D0:   CCCCCCCCCCCCCCCCCCCC
00000000000002E4:   CCCCCCCCCCCCCCCCCCCC
00000000000002F8:   CCCCCCCCCCCCCCCCCCCC
000000000000030C:   CCCCCCCCCCCCCCCCCCCC
0000000000000320:   CCCCCCCCCCCCCCCCCCCC
0000000000000334:   CCCCCCCCCCCCCCCCCCCC
0000000000000348:   CCCCCCCCCCCCCCCCCCCC
000000000000035C:   CCCCCCCCCCCCCCCCCCCC
0000000000000370:   CCCCCCCCCCCCCCCCCCCC
0000000000000384:   CCCCCCCCCCCCCCCCCCCC
0000000000000398:   CCCCCCCCCCCCCCCCCCCC
00000000000003AC:   CCCCCCCCCCCCCCCCCCCC
00000000000003C0:   CCCCCCCCCCCCCCCCCCCC
00000000000003D4:   CCCCCCCCCCCCCCCCCCCC
00000000000003E8:   CCCCCCCCCCCCCC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.