Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Changing Table Schema – what goes behind the scenes – Part II Expand / Collapse
Author
Message
Posted Wednesday, August 05, 2009 12:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 05, 2013 3:42 AM
Points: 10, Visits: 139
Comments posted to this topic are about the item Changing Table Schema – what goes behind the scenes – Part II
Post #765259
Posted Wednesday, August 05, 2009 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 01, 2009 2:58 PM
Points: 3, 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?
Post #765568
Posted Wednesday, August 05, 2009 9:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 05, 2013 3:42 AM
Points: 10, 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
Post #765602
Posted Wednesday, August 05, 2009 9:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 01, 2009 2:58 PM
Points: 3, 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?
Post #765934
Posted Thursday, August 06, 2009 12:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, August 05, 2013 3:42 AM
Points: 10, 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
Post #765990
Posted Thursday, August 06, 2009 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 01, 2009 2:58 PM
Points: 3, 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.
Post #766236
Posted Tuesday, August 11, 2009 8:29 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 10:46 PM
Points: 764, Visits: 224
Thank you for the articles. They do shed light on what is going on behind the scenes in SQL. I find them very helpful.
Post #768658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse