Storage differences of on-row and off-row data

Let's now take a look at the difference in storage and processing between two tables with variable length character columns. One table has varchar(5) columns, which will be small enough to fit in the in-row storage. The other table will have a series of varchar(max) columns, which will automatically be stored in the off-row storage. These tables will be created and filled with 100,000 rows each to demonstrate both the storage and also the performance differences between the two storage types. We will create the two tables using the following code:

USE InMemoryTest GO DROP TABLE IF EXISTS dbo.InMemoryTableMax DROP TABLE IF EXISTS dbo.InMemoryTableNotMax GO CREATE TABLE dbo.InMemoryTableMax ( UserId ...

Get SQL Server 2017 Developer's Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.