Post reply

sparse column

  • October 17, 2013 at 12:57 am

    #278978

    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

    #1659174

    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

    #1659210

    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

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

  • October 17, 2013 at 4:54 am

    #1659218

    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