SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
dwivedialok
dwivedialok
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 139
Comments posted to this topic are about the item Changing Table Schema – what goes behind the scenes – Part II
anilkhandekar
anilkhandekar
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 4
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
dwivedialok
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 139
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
anilkhandekar
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 4
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
dwivedialok
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 139
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
anilkhandekar
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 4
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
dbaInTraining
SSC Eights!
SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)SSC Eights! (883 reputation)

Group: General Forum Members
Points: 883 Visits: 263
Thank you for the articles. They do shed light on what is going on behind the scenes in SQL. I find them very helpful.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search