Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not seeing savings in sparse columns Expand / Collapse
Author
Message
Posted Sunday, March 3, 2013 6:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:24 AM
Points: 896, Visits: 1,846
Ive ran a test comparing a table with a few sparse columns to a table with no sparse columns, and i am seeing zero space saving.

I have two tables, both storing Address info mainly in varchar columns.
both tables allow nulls, one has columns sparse property set.

I insert 1000 rows of default values in each (default values are null). Sparse columns store nulls differently so i believe i should see a space saving. but on running sp_spaceUsed i dont see any saving. Any ideas on what i am doing wrong or where my understanding is incorrect?

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.
*/


Post #1425940
Posted Sunday, March 3, 2013 8:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
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

*/



__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1425952
Posted Sunday, March 3, 2013 8:14 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:24 AM
Points: 896, Visits: 1,846
Cool. Thanks for that.

I have done some tests since confirming that, so char and nchar show much better savings, and int's dependant on the size of the values show a lot of savings also.
But, in most applications i know, text is stored as varchar, such as in address tables etc, and this is where i thought sparse columns could help a lot as address data can be very variable, i.e some people have postal codes, some dont, some dont have a 3rd address line etc.

It seems though that there is no benefit of adding sparse property to varchar fields, and there may actually be a detrimental effect.


You say that there is little difference due to the way variable length col's store nulls. can you elaborate on that please?
Post #1425955
Posted Sunday, March 3, 2013 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
NULL variable-length columns are returned as NULL based on the metadata of the table (and the NULL-bitmap of the record) when the result set is constructed but occupy no space on the data page.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1425959
Posted Sunday, March 3, 2013 8:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:24 AM
Points: 896, Visits: 1,846
opc.three (3/3/2013)
NULL variable-length columns are returned as NULL based on the metadata of the table (and the NULL-bitmap of the record) when the result set is constructed but occupy no space on the data page.


That's very helpful. Thanks a mil!
Post #1425962
Posted Sunday, March 3, 2013 8:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:52 PM
Points: 7,079, Visits: 12,569
Anytime, thanks for the feedback.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1425964
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse