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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Effect of number of coulms on table space
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

79 Posts

Posted - 03/08/2013 :  09:40:25  Show Profile  Reply with Quote

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 ???

Flowing Fount of Yak Knowledge

6065 Posts

Posted - 03/08/2013 :  10:25:12  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000