locked
SQL server performance for tables having large number of columns RRS feed

  • Question

  • I have a requirement where i need to create a table with about 600 columns. Out of 600 columns about 200 columns will be XML columns. Other column will be of other datatypes such as int,varchar,nvarchar etc.

    Also about 50% XML column will have about 40 to 60 rows and other will have less number of rows. The entire table will have about 2000 to 3000 rows. Theoretically SQL allows maximum of 1024 columns. But what will be the performance in my scenario? Does this design will bad effect on performance?

    Also does SQL server has limitation for row size?
    Monday, November 25, 2013 1:21 PM

Answers

  • Hello,

      Having many column in table obviously will impact the performance.Please try to normalize the table and divide the tables and make it smaller table.

     Create Primary key and foreign key relationship between all table and make the table simple with less no of column.

     Having 100 tables with 60 column is far better than one table with 600 columns.

    Row Size please refer here

    http://technet.microsoft.com/en-us/library/ms143432.aspx

    Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

    • Proposed as answer by Kalman Toth Monday, November 25, 2013 8:09 PM
    • Marked as answer by Fanny Liu Tuesday, December 3, 2013 8:34 AM
    Monday, November 25, 2013 1:29 PM
  • No - it is doubtful that you "have a requirement" to create a 600 column table.  Perhaps that is the solution at which you have arrived.  Whether this design will perform adequately cannot be determined from a distance.  In particular, how you use the data will drive "performance" - something that you should be trying to determine - even if all you can do is estimate at this point . 

    Given that you have mentioned a single table, that alone suggests that your approach is not optimal.  I suggest you get some onsite help. 

    • Proposed as answer by Kalman Toth Monday, November 25, 2013 8:08 PM
    • Marked as answer by Fanny Liu Tuesday, December 3, 2013 8:34 AM
    Monday, November 25, 2013 1:51 PM

All replies

  • Hello,

      Having many column in table obviously will impact the performance.Please try to normalize the table and divide the tables and make it smaller table.

     Create Primary key and foreign key relationship between all table and make the table simple with less no of column.

     Having 100 tables with 60 column is far better than one table with 600 columns.

    Row Size please refer here

    http://technet.microsoft.com/en-us/library/ms143432.aspx

    Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

    • Proposed as answer by Kalman Toth Monday, November 25, 2013 8:09 PM
    • Marked as answer by Fanny Liu Tuesday, December 3, 2013 8:34 AM
    Monday, November 25, 2013 1:29 PM
  • No - it is doubtful that you "have a requirement" to create a 600 column table.  Perhaps that is the solution at which you have arrived.  Whether this design will perform adequately cannot be determined from a distance.  In particular, how you use the data will drive "performance" - something that you should be trying to determine - even if all you can do is estimate at this point . 

    Given that you have mentioned a single table, that alone suggests that your approach is not optimal.  I suggest you get some onsite help. 

    • Proposed as answer by Kalman Toth Monday, November 25, 2013 8:08 PM
    • Marked as answer by Fanny Liu Tuesday, December 3, 2013 8:34 AM
    Monday, November 25, 2013 1:51 PM
  • SQL works vertically, it does not work horizontally.

    It depends on the queries if there will be performance issues.

    As noted above, generally it is not a good idea to create a table with huge number of columns.

    Share more information for quick assistance.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, November 25, 2013 8:12 PM