Create Table SparseColTest_NonSparse
(
	AddressID int identity(1,1) not null,
	AddressLine1 varchar(500)  null,
	AddressLine2 varchar(500)  null,
	AddressLine3 varchar(500)  null,
	PostalCode varchar(20)  null,
	Country varchar(50) 
)

Create Table SparseColTest_Sparse
(
	AddressID int identity(1,1) not null,
	AddressLine1 varchar(500) sparse null,
	AddressLine2 varchar(500)  sparse null,
	AddressLine3 varchar(500)  sparse null,
	PostalCode varchar(20)  sparse null,
	Country varchar(50) 
)


declare @i int
set @i = 0

while(@i <= 100000)
BEGIN

	insert into SparseColTest_NonSparse Default values
	insert into SparseColTest_Sparse default values

	set @i = @i + 1
END

exec sp_spaceUsed 'SparseColTest_NonSparse'
exec sp_spaceUsed 'SparseColTest_Sparse'

/*
name                          rows                 reserved           data               index_size         unused
----------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
SparseColTest_NonSparse       210003               2888 KB            2840 KB            8 KB               40 KB


name                          rows                 reserved           data               index_size         unused
----------------------------- -------------------- ------------------ ------------------ ------------------ ------------------
SparseColTest_Sparse                210003               2888 KB            2840 KB            8 KB               40 KB

****NOTE - even with 210k rows sparse and non sparse tables are identical in size.
*/

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

*/