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

sparse column Expand / Collapse
Author
Message
Posted Thursday, October 17, 2013 12:57 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
Dear All

Want to check the differnce in table size for sparse and normal column. Following code used for testing this.
create table a1( a1 varchar(4) null)
create table a2( a2 varchar(4) sparse null )

insert into a1 (a1) values ('aa')
insert into a2 (a2) values ('aa')

insert into a1 (a1) values (null)
insert into a2 (a2) values (null)

sp_spaceused "a1"
sp_spaceused "a2"


Spaces used procedure returns same size for both the tables. Is it a correct methos to check the storage size or there is another method to chekc it?

Regards
Post #1505498
Posted Thursday, October 17, 2013 3:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 14, 2014 6:07 AM
Points: 182, Visits: 343
Hi,
You can not see difference with few rows as it will be adjusted with same page.
Try inserting 1 million rows and then compare.
If you do not find the difference, truncate both, add a primary key in both the table and then insert 1 million rows again.

Thanks,
Seraj Alam
Post #1505542
Posted Thursday, October 17, 2013 4:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:01 PM
Points: 42,432, Visits: 35,486
Tables can never use less than a page, with so few rows you won't see a difference as both tables fit within a page.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1505583
Posted Thursday, October 17, 2013 4:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:55 PM
Points: 218, Visits: 556
tried with 7000000 rows still no difference
Post #1505591
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse