January 16, 2014 at 5:50 pm
We have a data table with 92 million rows.
It currently has 1 data field (Integer) and some descriptive fields
Some new data acquisition is now required which has 3 data points.
I have two options:
1) Add the extra fields required to handle this data.
Advantage: Minimal alterations to surrounding relationships. Disadvantage: A large increase in Table Size due to the existing rows. (This is the point I am unsure on. Will SQL Server allocate extra space for the existing rows.?)
2) Add another data table specifically for this type of data.
Advantage: Efficient use of space. Disadvantage: a large design effort to get the surrounding relationships and business logic to allow for two data tables.
I obviously prefer option 1 but I am unsure of its impact.
Any thoughts on this would be welcome.
Thanks
Bob
January 16, 2014 at 7:52 pm
ALTER TABLE ... ADD COLUMN is a meta data change to a table only. It does not change what is currently stored in the table. SQL Server tracks what version of meta data is relevant to a page (I think that is the correct granularity) of each table. When you update rows of the table to actually have data for those columns, the data page will reference the new version of the meta data.
Regardless of the version of meta data for each page, SQL returns the records as though the current version applies (it is transparent to applications using the table)
January 17, 2014 at 1:30 am
Thank you.
That clears it up nicely.
regards
Bob
January 17, 2014 at 2:02 am
happycat59 (1/16/2014)
ALTER TABLE ... ADD COLUMN is a meta data change to a table only. It does not change what is currently stored in the table. SQL Server tracks what version of meta data is relevant to a page (I think that is the correct granularity) of each table. When you update rows of the table to actually have data for those columns, the data page will reference the new version of the meta data.Regardless of the version of meta data for each page, SQL returns the records as though the current version applies (it is transparent to applications using the table)
Its safer if you ensure the new column is nullable to ensure it is a metadata change only.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply