Because of how NULL variable-length columns are stored you should not see a difference in your two sample tables.

Try changing one of the columns to fixed-length though and you'll see a difference:

IF EXISTS ( SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'SparseColTest_NonSparse')

AND type IN ( N'U' ) )

DROP TABLE SparseColTest_NonSparse;

GO

IF EXISTS ( SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N'SparseColTest_Sparse')

AND type IN ( N'U' ) )

DROP TABLE SparseColTest_Sparse;

GO

CREATE TABLE SparseColTest_NonSparse

(

AddressID INT IDENTITY(1, 1)

NOT NULL,

AddressLine1 INT NULL,

AddressLine2 VARCHAR(500) NULL,

AddressLine3 VARCHAR(500) NULL,

PostalCode VARCHAR(20) NULL,

Country VARCHAR(50)

)

GO

CREATE TABLE SparseColTest_Sparse

(

AddressID INT IDENTITY(1, 1)

NOT NULL,

AddressLine1 INT SPARSE

NULL,

AddressLine2 VARCHAR(500) SPARSE

NULL,

AddressLine3 VARCHAR(500) SPARSE

NULL,

PostalCode VARCHAR(20) SPARSE

NULL,

Country VARCHAR(50)

);

GO

DECLARE @i INT = 100000;

INSERT INTO dbo.SparseColTest_NonSparse

(

AddressLine1,

AddressLine2,

AddressLine3,

PostalCode,

Country

)

SELECT TOP (@i)

NULL AS AddressLine1,

NULL AS AddressLine2,

NULL AS AddressLine3,

NULL AS PostalCode,

NULL AS Country

FROM sys.columns c1

CROSS JOIN sys.columns c2;

INSERT INTO dbo.SparseColTest_Sparse

(

AddressLine1,

AddressLine2,

AddressLine3,

PostalCode,

Country

)

SELECT TOP (@i)

NULL AS AddressLine1,

NULL AS AddressLine2,

NULL AS AddressLine3,

NULL AS PostalCode,

NULL AS Country

FROM sys.columns c1

CROSS JOIN sys.columns c2;

EXEC sp_spaceUsed

'SparseColTest_NonSparse'

EXEC sp_spaceUsed

'SparseColTest_Sparse'

/*

name rows reserved data index_size unused

----------------------------- -------------------- ------------------ ------------------ ------------------ ------------------

SparseColTest_NonSparse 100000 1736 KB 1688 KB 8 KB 40 KB

name rows reserved data index_size unused

----------------------------- -------------------- ------------------ ------------------ ------------------ ------------------

SparseColTest_Sparse 100000 1352 KB 1288 KB 8 KB 56 KB

*/