Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Effect of number of coulms on table space

Author  Topic 

Kimi86
Yak Posting Veteran

79 Posts

Posted - 2013-03-08 : 09:40:25

I have some analysis to do for which I was testing the space occupied by table as the no of coumns grow on it. The test results are below

for 50 columns and around 100000 rows the space occupied by the table was 33 GB. the data typed were a mix of decimal int and varchar

for 100 coumns with columns structure duplicated and similar data the space occupied was 84 GB

for 150 coumns with columns structure triplicated and similar data the space occupied was 112 GB


Does this look reasonable ???


TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-08 : 10:25:12
Books online offers precise formulas for estimating table size. However, your post raises several red flags:

application design - do the number of columns in your table actually "grow" over time? Typically this is not a good design. Again, generally speaking, you want to move towards narrow tables with lots of rows. You can usually add another attribute (column) that would qualify what type of row that represents rather than a new set of columns.


database design - large number of columns CAN indicate a poorly designed model. And of course a bad model will lead to awkward t-sql and performance problems.

But that link will answer your question as to estimating table size.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -