October 17, 2013 at 12:57 am
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
October 17, 2013 at 3:05 am
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
October 17, 2013 at 4:38 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2013 at 4:54 am
tried with 7000000 rows still no difference
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply