SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Space is not reclaimed while changing of datatype.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shaggy
Posting Yak Master

India
245 Posts

Posted - 03/05/2013 :  01:28:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4613 Posts

Posted - 03/05/2013 :  16:25:26  Show Profile  Reply with Quote
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.
Go to Top of Page

shaggy
Posting Yak Master

India
245 Posts

Posted - 03/06/2013 :  00:34:45  Show Profile  Reply with Quote
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.


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000