Space is not reclaimed while changing of datatype.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Space is not reclaimed while changing of datatype.

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2013-03-05 : 01:28:16

create database test
go
use test
go
create table pageread
(
id int identity,
name varchar(10)
)
go
insert pageread select 'some thing'
go 1000

dbcc ind('test','Pageread',0)
go
alter table Pageread alter column name char(100) --data page no is increasing
go
dbcc ind('test','Pageread',0)
go
alter table Pageread alter column name varchar(100) --data page no is still the same
go
dbcc ind('test','Pageread',0)
go
DBCC CLEANTABLE (test,"Pageread", 0)WITH NO_INFOMSGS;
go
dbcc ind('test','Pageread',0)
go
alter table Pageread rebuild --data page no is still the same
go
dbcc ind('test','Pageread',0)
go
alter table Pageread alter column name varchar(10) --data page no is still the same
go
dbcc ind('test','Pageread',0)
go
alter table Pageread rebuild
go
dbcc ind('test','Pageread',0) --data page size is now same as after insert of records

I understand that varchar(10) & (100) are different even though we store same data lenght value because varchar will
record the unused space storage in some bytes but my question is when i add data for varchar(10) & (100) it occupies
same no of data pages and if i change the datatype to varchar(10) to char(100) and then to varchar(100) it is not reclaiming the
storage space after again if i change it to varchar(10) the space is reclaimed

2)

drop table pageread
go
create table pageread
(
id int identity,
name varchar(100)
)
go
insert pageread select 'some thing'
go 1000

dbcc ind('test','Pageread',0) --data page no is now as same as varchar(10)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-03-05 : 16:25:26

I'm not sure I undderstand your example, as if I REBUILD a table I get the space back (at least according ot DBCC IND). At any rate, I'm not sure what your question is, so I can't help you. If you wanted to ask a question, I bet someone can help.

shaggy
Posting Yak Master

248 Posts

Posted - 2013-03-06 : 00:34:45

Thanks Lamprey

When i create a table pageread and insert as above with columns (id int identity,name varchar(10)) my total page count is around 5 after changing the datatype for column (name) varchar(10) to char(100) my page countt got increased then am changing it to varchar(100) and rebuilding, my page count got decreased but not as when it was like varchar(10) and again i changed the datatype as varchar(10) and rebuild my page count is now 5 as same aa older stage. second i created table with name varchar(100) with same set of insert my page count is 5 as it was like varchar(10)

My question is when am changing from cahr(100) to varchar(100) with same set of data the space should be reclaimed as it was like varchar(10) but it reclaimed only 25% of space i want to know why this behaviour.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources