October 9, 2008 at 11:44 pm
Does anyone know whether or not there is any difference in the way database stores data between SQL 2005 and SQL 2000?
Some people claims that user database sizes are changed after upgrading from SQL 2000 to SQL 2005.
There are no unused space in the database in either version.
Any input will be greatly appreciated.
October 10, 2008 at 2:31 am
Answering your questions.
1- Yes. There are differences in the way SQL Server 2005 stores data.
Perhaps the most notorious one is the way SQL Server 2005 overcomes the old 8K limitation for the size of a single row.
SQL Server 2005 has worked around a solution by creating some kind of migrated-columns in way pretty similar Oracle creates migrated-rows.
When your row is bound to exceed 8K SQL Server 2005 picks one or more of your variable size columns and MOVES THEM TO A DIFFERENT PAGE!!! leaving behind a pointer.
The good news is you can have rows larger than 8K, the bad news is the solution works against performance... the system has to do additional I/O to get the migrated-columns.
2- I really don't know what you mean there is no unused space on SQL Server 2005, believe me... it is there.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply