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.
Author |
Topic |
shaggy
Posting Yak Master
248 Posts |
Posted - 2013-03-05 : 01:28:16
|
create database testgouse testgocreate table pageread(id int identity,name varchar(10) )goinsert pageread select 'some thing'go 1000dbcc ind('test','Pageread',0)goalter table Pageread alter column name char(100) --data page no is increasinggodbcc ind('test','Pageread',0) goalter table Pageread alter column name varchar(100) --data page no is still the samegodbcc ind('test','Pageread',0) goDBCC CLEANTABLE (test,"Pageread", 0)WITH NO_INFOMSGS;godbcc ind('test','Pageread',0) goalter table Pageread rebuild --data page no is still the samegodbcc ind('test','Pageread',0) goalter table Pageread alter column name varchar(10) --data page no is still the samegodbcc ind('test','Pageread',0) goalter table Pageread rebuild godbcc 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 willrecord 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 pagereadgocreate table pageread(id int identity,name varchar(100) )goinsert pageread select 'some thing'go 1000dbcc 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 LampreyWhen 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. |
|
|
|
|
|
|
|