INT to Bit

Question

INT to Bit

Viewing 13 posts - 16 through 28 (of 28 total)

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

Johan Bijnens SSC Guru Points: 135582 More actions · Answer 1

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.

Something is wrong in this picture ....

Your db needs some spare space !

(a 200MB database is a small db.)

You can rebuild a single table or index ( clustering indexes may cause NCI to be rebuild as well )

On a 2 or 3 MB table (data size), what will be the impact of a rebuild ??

Check out BOL for "Create index" and see what space influating options you have.

( PAD_INDEX / FILLFACTOR )

Can you post @@version info of your sql instance ?

ALTER INDEX [CLIX_Yourtable] ON [dbo].[T_Yourtable] REBUILD -- WITH ( ONLINE = ON ) ;

GO

ALTER INDEX [NCI1_Yourtable] ON [dbo].[T_Yourtable] REBUILD -- WITH ( ONLINE = ON ) ;

GO

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 and code to get the best help

- 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 2

all tables which are in question pretty much increased in space

version :

Johan Bijnens SSC Guru Points: 135582 More actions · Answer 3

Tara-1044200 (2/2/2010)
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 ?

SQLServer doesn't release space it uses.

Once a file has grown, it will stay that way untill you shrink it.

In many cases such a shrink is not advised, because sqlserver needs some spare space.

On production systems, do NOT enable autoshrink for your database, because you have no controle over the "when" the system will decide to start a shrink operation, and your db-users may notice such an operation to be quit intrusive.

use [yourdb]

exec sp_helpfile

go

/* show space consumption of objects */

Select quotename(object_schema_name(object_id)) + '.' + quotename(object_name(object_id)) as TbName

, Page_count * 8 /1024 as Pages_MB

, *

from SYS.DM_DB_INDEX_PHYSICAL_STATS (13,NULL,NULL,NULL,'detailed' )

order by Pages_MB desc, avg_fragmentation_in_percent desc

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 and code to get the best help

- 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 4

so far i didnt get any explanation for why space is not reclaimed when column are deleted even after shrinking.

technically when data is deleted and then shrinked i have to get space back but why not ?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 5

Tara-1044200
so far i didnt get any explanation for why space is not reclaimed when column are deleted even after shrinking.

For an explanation of what happened read these 2 articles:

Changing Table Schema – what goes behind the scenes – Part I at:

http://www.sqlservercentral.com/articles/Design+and+Theory/67552/

Changing Table Schema – what goes behind the scenes – Part II

http://www.sqlservercentral.com/articles/Design+and+Theory/67553/

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 6

Tara-1044200 (2/2/2010)
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?

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.

Ok... I'm officially confused. How did you reindex if you "don't have any indexes on any tables in that database"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 7

As a side bar, I don't use the BIT datatype for the most part. A single bit still takes a byte to store it and you can't so SUM aggregations on the BIT datatype should the need ever arise.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Actually.. I am sorry, i do have indexes.

I am changing INT data type to BIT, does it not save any sapce to me ?

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

Michael Swart in this blog post adds to animals argument:

http://sqlserverpedia.com/blog/sql-server-bloggers/removing-columns/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed:+sqlserverpedia+(SQLServerPedia)&utm_content=Google+Reader

This suggests that an index or table rebuild is the way to claim the space back...

Gethyn Elliswww.gethynellis.com

Johan Bijnens SSC Guru Points: 135582 More actions · Answer 10

Tara-1044200 (2/4/2010)
Actually.. I am sorry, i do have indexes.
I am changing INT data type to BIT, does it not save any sapce to me ?

it can save you 3 bytes per row for a single INT column.

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 and code to get the best help

- 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

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 11

Tara-1044200 (2/4/2010)
Actually.. I am sorry, i do have indexes.
I am changing INT data type to BIT, does it not save any sapce to me ?

Like ALZDBA stated, 3 bytes for each row... a single BIT column will still occupy one byte. IIRC correctly, SQL Server will do a bit of conservation by combining up to 8 bit columns in a single byte but I'm not 100% sure on that... I don't know the internals as well as some folks. I also avoid the BIT datatype whenever possible for the reasons I previously stated so I haven't spent a whole lot of time researching it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Indeed the first bit will take a byte, but a byte can host 7 bits (the 8-th one is used for marking another bit-hosting byte is in the row).

That's how I understood it works.

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 and code to get the best help

- 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

Jeff Moden SSC Guru Points: 1004649 More actions · Answer 13

Very cool. Thanks Johan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)