Question

Michel Yousry on Sat, 18 Feb 2012 11:26:14


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


Sponsored



Replies

Horizon_Net on Sat, 18 Feb 2012 13:20:14


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).

Michel Yousry on Sun, 19 Feb 2012 06:22:22


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

Papy Normand on Sun, 19 Feb 2012 11:25:30


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

Banketeshvar Narayan on Wed, 23 Mar 2016 17:24:45


I know that I am posting on a 4 yrs. old post but it may help other people

The maximum number of column allowed in a SQL server table is 1024 and if you use “sparse column” then this limit is 3000. But this maximum column limit having some other conditions too.

You are using 200 int columns and 12 columns of other data type. If those 12 columns are varchar/nvarchar type then you need maximum

200 (columns) X 4 (int size 4 bytes) +  12 (columns) X 24 (24 bytes pointer) =1088 bytes and in one row you can store 8060 bytes so you are safe as far as size limit is concerned. Suppose someone is using 320 varchar columns then he may fall into the problem of 8060 size limits.

If you are using nvarchar/varchar/text/ntext data type and if it is exceeding the 8060 size limit then 24 bytes pointer is used for nvarchar/varchar and 16 bytes pointer is used in case of text/ntext.

 


100

Poojasood on Fri, 02 Sep 2016 15:17:16


Hi

How can I use "Spare column" to increase the limit to 3000. I have 1300 columns in the table.

Thanks

Pooja

davidbaxterbrowne on Fri, 02 Sep 2016 16:14:01


>How can I use "Spare column" to increase the limit to 3000

Start by reading the documentation: Use Sparse Columns

David