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

Question

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

dwivedialok

SSC Enthusiast

Points: 124
More actions
August 5, 2009 at 12:09 am

#209047

Comments posted to this topic are about the item Changing Table Schema – what goes behind the scenes – Part II

Viewing 7 posts - 1 through 6 (of 6 total)

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

anilkhandekar SSC Journeyman Points: 77 More actions · Answer 1

For your first comment about reducing the field length from INT to SMALLINT, you said the change will take place only in the "metadata", what is a "metadata" and why is it even needed? Is this is the database "catalog"?

Is that not confusing, the user will think the change has taken place and in reality it does not?

dwivedialok SSC Enthusiast Points: 124 More actions · Answer 2

I have briefly explained it in Part 1. Sorry if this is not very clear.

Meta data in this scenario means information SQL server holds to know what your table schema is i.e. how many columns it has , their data types and so on. This is something you get back by querying system catalogue views for e.g. sys.columns.

Any ways meta data is generic term frequently used in any data storage back ends. So any data storage back end will store the actual data you asked it to store (like rows added to a table ) but the system itself needs to store information about hows/whats/wheres of this data(like what table schema is and so on) .This bit is commonly referred to as Meta data.

Refer to Wiki definition http://en.wikipedia.org/wiki/Metadata for further details.

Now coming to changing int to smallint, this will cause meta data to change (obvious) and SQL Server needs to ensure that existing data is valid for new data type range. Hence it has to examine all the data pages.

Hope this helps

anilkhandekar SSC Journeyman Points: 77 More actions · Answer 3

I do apologize as my question was not very clear.

I know what a "metadata" is, the question should have been "Why would the metadata (or catalog) definition be different from the physical implementation?"

From what I understood from your original post, if you change the field length of an existing table from INT to SMALLINT, and assuming existing data is SMALLINT compatible, then your metadata definition changes (and will show SMALLINT when you query the catalog), but the field can still hold INT values for future rows inserted.

What I would expect is after the field length change I cannot insert values greater than SMALLINT's.

I did not see the point of the metadata being different from the physical implementation, I have worked with UDB, Teradata and Oracle and I have never seen the Catalog being different from the physical implementation.

Did I not understand the original post correctly?

dwivedialok SSC Enthusiast Points: 124 More actions · Answer 4

In that example, I have tried to show that once fixed length data type size is reduced, it only changes meta data but continues to use orginal fixed length size. Once the meta data has changed then it implicitly means that now you are only allowed to insert the data that can fit in to new data type range. That is one of many reasons why meta data is there for 🙂 .

So in simple terms all it means is when an int changes to smallint the data pages continue to use 4 bytes but now you are "only allowed to insert data that can fit just smallint range" (so effectively you are not using 2 bytes out of that 4 bytes). The field no longer hold int values. That was whole point of examining data rows that it conforms to new data type and only new data type will be allowed for further inserts.

The point being highlighted here is that if storage space matters a lot to you and you are shortening the data type of fixed length column then storage space will not be claimed until you rebuild clustered index. The only thing that changes is now your data type range is according to new column of smaller size data type(in this example now data type range is smallint and not int).

Hope it makes it clearer now

anilkhandekar SSC Journeyman Points: 77 More actions · Answer 5

That makes perfect sense!

I was confused by the original statement

Quote

Run DBCC PAGE again and you will find that new rows continue to take 2 bytes for Col1 (and not 1 byte even though it’s now TINYINT). Memory dump shows that Col3 continues to start at same offset.

Unquote

I thought you meant that the database would allow inserts of 2 byte data, whereas you actually meant that the physical space consumed in 2 bytes (at least till the reorg).

Thanks for the explanation.

dbaInTraining Ten Centuries Points: 1032 More actions · Answer 6

Thank you for the articles. They do shed light on what is going on behind the scenes in SQL. I find them very helpful.