What is the max. columns Number for sql server table?

Question

Hi all

I have a sql server table that has 212 columns 200 columns has (int) DataType

The table used by a web application

so my question is the performance going to be slow or not?

note : I have to select the complete row on form load

thanks

---

Michel Yousry

Answer 1

Hi,

here you can find the Maximum Capacity Specification for SQL Server. Like you see there's some space to the top, but you're scenario seems to be like in a big business intelligence environment.

The first recommendation from me is that you think about if you really need all the columns at the same time in your application or if you can get some data at a later point. 
The second recommendation is that you try to normalize your table, because the more columns you select at the same time the more the performance of your application will decrease (that also will more decrease if your working with a remote SQL Server - what's normally the case).

Also you should create your queries for really good performance in this scenario. Another thing to think about is to cache some of your data in your application (but remember that also caching has some limitations).

---

Best Regards. Please Mark Answered, If my solution solves your problem.

Answer 2

Unfortunately I have no other way I should load all data on the form load and the table can not be normalized more than that Ii already split the first table into 4 tables

however I think I have to start working and check the performance online

Thanks for your recommendations

---

Michel Yousry

Answer 3

Hello,

8060 bytes for a row for all columns which are not with a length defined as MAX ( char, nchar, varbinary, text, image ). For the datatypes with a length = MAX, they use a pointer ( 8 bytes i think  for each column ) which is include in the 8060 bytes limit.

Have a nice day

---

Mark Post as helpful if it provides any help.Otherwise,leave it as it is.