How to add 1100 columns in SQL Server

Question

I need 1100 columns in a single table.I already have 928 columns in same table, now I need to add new 150 columns.

I know SQL Server allow maximum 1024 columns.

Now I will not create new table. If I create new table and join my require table, I need to make changes in many places and this is not possible for me. So I want to add all columns in same table(require table).I know this is so tough to execute this table but presently I need to serve this requirement.

Answer 1

Bad idea, but if you insist and your data qualifies for the 8K bytes limitation -

Wide Tables

A wide table is a table that has defined a column set. Wide tables use sparse columns to increase the total of columns that a table can have to 30,000. The number of indexes and statistics is also increased to 1,000 and 30,000, respectively. The maximum size of a wide table row is 8,019 bytes. Therefore, most of the data in any particular row should be NULL. To create or change a table into a wide table, you add a column set to the table definition. The maximum number of nonsparse columns plus computed columns in a wide table remains 1,024. By using wide tables, you can create flexible schemas within an application. You can add or drop columns whenever you want. Keep in mind that using wide tables has unique performance considerations, such as increased run-time and compile-time memory requirements. For more information, see Performance Considerations for Wide Tables.

Answer 2

How about this:

• Do store the information in two tables, one with the 950 you have and a new table with the new 150 columns. Of course these tables should have the same primary key.
• Create a view that selects all columns by joining the two tables.
• Have the necessary triggers to update the tables underlying the view if needed (on insert, update, delete).
• Use the view instead of the original table where needed. You could rename the original table to something else, and name the view with the original table name. That way you don't need to change code.

Creating a view should allow you to select up to 4,096 columns. Reference: Maximum Capacity Specifications for SQL Server.

---

Regarding wide tables. You need a very specific type of data when using wide tables. Most columns should be NULL if you go much wider than 1,024 columns (i.e. the data should be sparse). The maximum row size is the same for wide and non-wide tables.

Perhaps at some point your data is sparse enough to fit in the maximum row size... until a row is inserted that isn't. Using the view as I outlined sidesteps this and can be used for non-sparse data.

Answer 3

In essence, the question you are asking is "How do I create a table with more than 1024 columns?" The answer to this is simply, "There is no easy way to exceed that hard limit in SQL Server. You should split the data into multiple tables.".

You could use sparse columns and wide tables. However, this comes with its own set of restrictions and limitations. For example, you cannot use compressed indexes with sparse columns. While using a wide table is possible, I would advise that you consider alternate methods.

Given that you must split the data into at least two tables, there are three aspects which I think you need to consider: Normalization, performance, and backwards compatibility.

Normalization

Having hundreds of columns on a single table is a sign that your data model is not well normalized. In your question, you've not specified what type of data is in this very wide table, but I'd anticipate that it would be best to break this data into smaller logical pieces, and break the table up into many tables. Without seeing the specific table definition, I can't make any concrete suggestions, but based on experience this is surely the right move. In my experience, having a table with 1000 columns is a sign of a very bad database design.

For example, a Person table should probably not include address information within the same table. Instead, this should be split into separate Person and Address tables. Similarly, the Address table should contain only one row per address. The Person table would join to Address in a one-to-many relationship.

Performance

Having very wide tables will increase IO, exacerbating any disk IO bottlenecks. By having more data on a row, SQL Server stores fewer rows on each 8k page (in your scenario, likely only 1 row per page). Lower row-to-page density means that IO for even a simple task can be many times higher than it would be in a normalized scenario.

Additionally, if a row is wider than 8060 bytes, SQL Server will use row overflow data pages. This means that to read a single row, SQL Server can actually have to perform multiple reads for every row. Additional details on how SQL Server stores overflow data can be read here.

Backwards Compatibility

For backwards compatibility, you can create a very wide view that joins underlying tables. You can leverage INSTEAD OF triggers on the view, which allow DML to be performed on the view and actually update the underlying base table instead. This is known as vertical partitioning a table.

Short term, this method allows you to put data in multiple physical tables, but to still be accessed via a single consolidated views. You can continue to make progress in splitting up your very wide table, and still have backwards compatibility via the view.

Long-term, it is likely best to update your code base to always access the underlying tables directly, and slowly reduce/eliminate the use of very wide view.

Answer 4

I would really advise you to step back and (instead of thinking about this possible solution), look at the problem you are trying to solve. If you are exploring the limits of the model of relational databases in this way, something might be going on.

In those cases, here is how I would reason:

1. The proposed model can work, but I am not approaching my problem in the standard way. What would be this standard approach?
2. Can I stretch the standard approach a little further without breaking it down?
3. The proposed approach is not for my problem and I need another approach. Perhaps some original thought is needed.

Checking (1) is generally advisable, if anything to avoid getting lost in a maze of practical issues (because the tool being used is breaking down under the strain). Sometimes a change in perspective can avoid a lot of grief. Then, if the model really doesn't work, then I would go to (2), and then (3).

In your specific case, a table with (say) 50 columns starts getting unwieldy in a relational model and beyond it might stop making sense at all. We cannot assume that who designed and built relational database engines considered it, except perhaps in for heavy-duty implementations.

The general idea is that tables express relation between data:

• They are not unlike matrixes and if a few conditions are met, they can be easily transposed: a table of 1'000 columns and 100 lines, could become 100 columns and 1'000 lines.
• They can broken down into smaller parts, typically using "sub-tables" with foreign keys.

So we would be really curious to know what the problem is, because (as a famous Python teacher often does) we are tempted to bang our fist on the table, saying "there must be a better way"!

Answer 5

There are many times comments that if a table includes thousands of columns is due to wrong design. This is not true because at least in my case in process and power generation industry the same logical level of data consists hundreds of different type of objects which require different attributes. It's by far better to define as many columns as possible with natural use of the data rather than name them data1, data2, data3, etc. and then invent in user interphase different usage depending on the object type. Particularly this is a must with calculated numeric fields. Some of the rarely used very nonstandard data fields can be however non-descriptive and their used specified in user interphase. Using dedicated fields makes the whole application design much easier for developers and those who make queries from database. Sparse columns and wide tables are necessary when using e.g. sql server. I have also Access back-end applications where I need more than 254 columns but there I need to split data to several tables with the same key value in several tables. Makes the life more difficult and I don't like that.