November 10, 2015 at 12:06 am
Hi
I recently did a compare between 2 databases and one of the chnages was a data type on a table collumn.
In the generated script Redgate compare dropped and recreated the table, dropping indexes first and recreating them after.
The table is identical now in terms of data, the only change is the datatype.
do you know why it would do this?
Thanks in advance.
November 11, 2015 at 2:23 am
Without knowing the before and after data types, and the DDL for the table, it is difficult to determine why a data type change required a table to be dropped and recreated. There are times when a data type change does not need to drop an recreate a table (usually it is when all that needs to be changed is sys.columns), but those are rare situations. While the data may look the same, what needs to be considered is the storage space needed for the old and new data type, on the page. A good introduction (with some deep dives) is Paul Randal's Inside the Storage Engine: Anatomy of a page[/url].
In general, a data type change will change the size of a row, because the bytes required to store the original data type are unlikely to match the bytes required for the new data type. When the size of a row is changed, the number of rows that fit on a page's payload will change. And if the changed column was part of the clustered index, every index would also need to be rebuilt, because the cluster index key is included in every nonclustered index.
If you are instead asking why redgate did what it did, here too it depends. For example, the last time I used Compare, it offered me an option to script out changes (instead of running the script). I typically want to test such a script (before implementing it in production).
Or, if you are wondering why the data type was changed even though the values were not: When data types do not match there can be a performance impact and results may not be what was expected.You might want to keep Data Type Precedence and Data Type Conversion (Database Engine) handy while you read Implicit Conversions.
November 11, 2015 at 8:52 am
There are certain changes that could make recreating the table more efficient than just the ALTERs. Would need details of the specific change(s) to know for sure. It's also possible they just wrote a "least common denominator" method of applying changes that would always work no matter what the change was, and that the specific change you're making this time wouldn't really require a table reconstruction.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 18, 2015 at 4:57 am
Thanks a lot for the replies, all makes sense
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply