SQL Server Performance

Impact of change of structure on table size

Discussion in 'Getting Started' started by khchaitanyaprabhu, May 29, 2007.

  1. khchaitanyaprabhu New Member

    Hi..

    I have created 2 tables
    Emp_Int _Table
    Emp_Id int not null,
    Amount int
    Clustered index on Emp_Id

    Emp_Varchar_Table
    Emp_Id int not null,
    Amount varchar(15)
    Non-Clustered index on Emp_Id

    Entered 10 similar rows for both tables.
    Checked the size occupied by each table and output is
    name rows reserved data index_size unused
    Emp_Varchar_Table 10 24 KB 8 KB 16 KB 0 KB
    Emp_Int _Table 10 32 KB 8 KB 24 KB 0 KB

    Now..
    If I change the Amount field in Emp_Int _Table to varchar(15) or change Amount field in Emp_Varchar_Table to int, the size remains the same.

    How the size remain same after changing the structure ?
    What will be the impact of change in structure on Transaction logging and on Index ?

    I will be grateful if some clarifies.

    Thanks
  2. satya Moderator

    How many rows you have within these tables?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. khchaitanyaprabhu New Member


    10 rows in each table.
    Emp_Id Amount
    1 100
    2 200
    .
    .
    .
    10 1000

  4. satya Moderator

    Sounds like a classroom based question.

    Anyway there will not be that difference in the transaction log when you change the structure, after that you can check the sizes after performing DBCC UPDATEUSAGE for correct values.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page