Friday, February 24, 2012

Adding Fields to a large table

Dear all
I implemented a datawarehouse for a midsized retail company on SQL Server
2005. One of their facttables is a daily stock snapshot with about 50
millions of records.
Business now want to create a new report. This report requires several
calculated values. The requirement says this values should be stored in the
warehouse.
Here is my question: When I add 3 more decimal fields (18,9) to this stock
table - how does this affect the size of the record/table when they have NULL
values or a value?
Thanks in advance for your help,
Regards,
Marc
Marc,
They will take up 9 bytes each regardless of whether or not they are null.
-- Bill
"Marc" <Roger_Rombooth@.community.nospam> wrote in message
news:CB83237B-BDE4-467F-8C4B-AD9C88D972B5@.microsoft.com...
> Dear all
> I implemented a datawarehouse for a midsized retail company on SQL Server
> 2005. One of their facttables is a daily stock snapshot with about 50
> millions of records.
> Business now want to create a new report. This report requires several
> calculated values. The requirement says this values should be stored in
> the
> warehouse.
> Here is my question: When I add 3 more decimal fields (18,9) to this stock
> table - how does this affect the size of the record/table when they have
> NULL
> values or a value?
> Thanks in advance for your help,
> Regards,
> Marc
>
|||Hello Marc,
As Bill mentioned, fixed length data will take up the bytes no matter it is
Null or not. Also, in SQL Server 2000, a NULL bitmap is always present,
even if no NULLs are allowed in any column. You may want to refer to the
following link for more details:
Poking about with DBCC PAGE (Part 1 of ?)
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/08/09/692806.aspx
If you have any further questions or comments, please feel free to let's
know. Thank you!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks Bill and Peter!
Best Regards,
Marc

No comments:

Post a Comment