March 6, 2014 at 12:30 pm
Hello! This is my first post on here... hope I did it right! 🙂
I have a database which is centered around two date tables (approx. 5.5 million records each). We are finally making the big leap from SQL Server 2005 to 2012. The data is currently stored as datetime, and we are hoping to take advantage of the new date datatype, since the time component is not needed.
The first table has 13 different date columns. In testing on the 2012 server I have changed 3 columns so far, and have seen that changing the datatype to date is actually increasing the Index size and not affecting the data size. Only 3 of the columns are associated with indexes, and modifying a non-indexed column still increased the index size. I am running the Disk Usage by Table report to view the sizes.
Any thoughts on why this is occurring? We were hoping that moving from a 8 byte datatype to a 3 byte datatype would give us some space savings.
Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller
March 6, 2014 at 12:59 pm
Welcome to the SSC.com forums!
Have you rebuilt any/all indexes, especially the clustered index if there is one? And if there isn't, why isn't there? 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 6, 2014 at 1:25 pm
I did need to drop and re-create all of the non-clustered indexes in order to do the datatype change. Unfortunately, the clustered index wasn't included in this and he got missed!
I just rebuilt the clustered index and the data size was reduced by over half.
It's always the little things. 🙂
Thank you!
Thanks,
Jessica
What would you attempt to do if you knew you could not fail? -Robert H. Schuller
March 6, 2014 at 3:30 pm
"It's always the little things" ... indeed it is!!! Glad you got your space back!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply