How to demonstrate the space usage of a Null Varchar(Max) column

An empty Varchar(Max) column uses a negligible amount of disk space. The script below demonstrates this by creating the table TestTb which contains one column named NullColumn that has a Varchar(Max) data type. When the table is created the column NullColumn is populated with 100,000 rows of Null.

The two readings below show the table when it has just been created and the table with 100,000 rows entered.

Results

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

CREATE TABLE TestTb (NullColumn VARCHAR(MAX));
GO

sp_spaceused 'TestTb';

DECLARE @i AS INT;

SET @i = 0;

WHILE @i < 100000
BEGIN
	INSERT INTO TestTb (NullColumn)
	VALUES (NULL)

	SET @i = @i + 1
END;
GO

sp_spaceused 'TestTb';

IF OBJECT_ID('dbo.TestTb', 'U') IS NOT NULL
	DROP TABLE dbo.TestTb;

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s