INT to Bit

Question

Post reply

INT to Bit

Tara-1044200

SSCoach

Points: 15951
More actions
February 1, 2010 at 9:13 am

#156634

I have a indicator column with data type INT and i changed it to Bit hoping that would save some space but it incresed the size, any thougts?
Also i remove some columns to save space but the result was opposite. Before and after doing these changes i shrinked the database too.
Before:
DB Size :201.30 MB
Table1: 2.45 MB
Table2:3.45 MB
After
Db Size: 203.6717 MB
Table1:3.4453 MB ** changed 1 column from INT to BIT
Table2:4.8125 MB ** Deleted 1 column

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29451 More actions · Answer 1

Can't explain what happened with table 2 but for table 1, I suspect that what is happening behind the scenes is

- the change in datatype is really implemented as

- a logical deletion of the original integer column (i.e. no change in space allocated, just a meta data change)

- the addition of the new bit column.

SQL Server does not necessarilly do exactly what you may, at first, expect. There are many rules involved. When dropping columns, the rule is something like "I may have re-write a very large amount of data if I actually release the space occupied by the column being dropped. I can achieve the same effect by logically deleting the column".

You may need to reload the table to release the space.

Hence, you are observing a net increase in size of the table.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 2

surprisingly after deelteing the column in a table the space used is increasing instead of decreasing though shrinked the databse hoping that it would release space used by the column which was dropped, why?

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

Here is another interesting thing... I did an export of the whole database into a new test database and the size seems to went down to 142MB

Can some one explain technical reason behind this behaviour ?

Animal Magic SSChampion Points: 13964 More actions · Answer 4

I remember reading on here some months back about how sql still holds the space for the data with the old type AND the new data type hence why the space increases. I seem to remember that to shrink the size you can rebuild the clustered index. Let me see if i can find it.

Tara-1044200 SSCoach Points: 15951 More actions · Answer 5

I dont have any indexes on any tables in that database.

Can you some one give correct explanation as to why space is not released when some column are deleted?

Animal Magic SSChampion Points: 13964 More actions · Answer 6

if you rename the table and then rename it back does the space change?

GRE (Gethyn Ellis) SSCrazy Eights Points: 9602 More actions · Answer 7

If you copy the data from both tables to a new table, with a new name, what is the size of the new tables?

Gethyn Elliswww.gethynellis.com

Tara-1044200 SSCoach Points: 15951 More actions · Answer 8

when i export the table to a new table the space is reduce by 30%. Actually i want to know the reason why space is not claimed back when columns deleted.

Animal Magic SSChampion Points: 13964 More actions · Answer 9

Example that proves the reindex theory

use beta

go

create table JM

(

colA int identity(1,1) primary key,

col2 varchar(20),

col3 int

)

go

declare @count smallint

set @count = 1

while @count < 1000

begin

insert into JM (col2, col3)

select 'Number ' + cast(@count as varchar(4)), @count

set @count = @count + 1

end

go

exec sp_spaceused JM

go

alter table JM

alter column col3 bit

go

exec sp_spaceused JM

go

dbcc dbreindex(JM)

go

exec sp_spaceused JM

go

drop table JM

run that and look at the data sizes and reserved space. Just working on the rename one now to see if that works

Tara-1044200 SSCoach Points: 15951 More actions · Answer 10

are you asking me to run dbcc dbreindex on the database where deleted few columns and check if i got my space back.

Animal Magic SSChampion Points: 13964 More actions · Answer 11

just on the table, you dont need to run it on the whole database

Johan Bijnens SSC Guru Points: 135550 More actions · Answer 12

also keep in mind a "drop column xyz" is only a catalog operation !

That columns space will only get reclaimed after maintenance or reindex.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Tara-1044200 SSCoach Points: 15951 More actions · Answer 13

I dont think reindexing helped me because reindex will rebuild all indexes and my database grown instead of reclaiming space back.

Only 1 solution i see getting my space back after deleting columns is exporting the data to a new database.

Animal Magic SSChampion Points: 13964 More actions · Answer 14

Tara-1044200 (2/3/2010)
I dont think reindexing helped me because reindex will rebuild all indexes and my database grown instead of reclaiming space back.
Only 1 solution i see getting my space back after deleting columns is exporting the data to a new database.

what about the size of the table in question though? not too bothered about the DB size