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 |
|
navipro
Starting Member
5 Posts |
 Posted - 2010-01-05 : 20:21:52
|
| Hi all!I have a question regarding the database size changes when migrating from sql 2005 to sql 2008. Basically the question is: Will the database go down in size? I was told that it will becuase of the different way how data is stored inside. More specifically that VARCHAR field with length 100 and value of "test" will consume only 4 bytes when in SQL 2005 it will consume all 100. I am taking this with the grain of salt becuase as far as I remember VARCHAR was present in sql 2005 already and we did not see the reduction in size when we upgraded from 200 to 2005. Thanks a lot for your time. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-06 : 01:48:00
|
you were told wrong  varchar(100) value 'test' will still consume 4 bytes (not counting bitmap overhead in the page header) exactly as it did in previous versions. |
 |
|
|
navipro
Starting Member
5 Posts |
Posted - 2010-01-06 : 09:07:10
|
quote:
Originally posted by russell
you were told wrong varchar(100) value 'test' will still consume 4 bytes (not counting bitmap overhead in the page header) exactly as it did in previous versions.
Well, that is what I have expected. Just to confirm. I should not expect any reduction in the DB size when upgraded to 2008, right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
navipro
Starting Member
5 Posts |
Posted - 2010-01-06 : 11:26:56
|
| Thnks, tkizer,Where can I read about compression? I will answer to myself here (so others who might read this will be able to find info):[url]http://www.infoq.com/news/2007/11/SQL-Server-Compression[/url][url]http://www.sqlskills.com/blogs/kimberly/post/SQL-Server-2005-and-2008-Compression.aspx[/url]Will the speed of the DB be affected? What is the trade-off? Answer here is: If the process is CPU bound then perforance will go down, if it is I/O bound then it will go up. Trade off is CPU cycles. What would be your guess on the space saved? Sorry for the questions but am trying to justify the upgrade to 2008 for my management. Thanks a lot for your help! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-01-06 : 13:00:20
|
| space saving depends on the nature of the data, but you can typically expect at least 50% savings. i've heard of instances with 80% savings.the tradeoff is cpu cycles. as with everything, it should be tested before being put into production |
|
|
|
|
|
|
SQL 2005->2008 upgrade and DB size