SQL Server Performance Forum – Threads Archive
Impact of change of structure on table size
Hi.. I have created 2 tablesEmp_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
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.
10 rows in each table.
Emp_Id Amount
1 100
2 200
.
.
.
10 1000
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.
]]>