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