Maximum amount of columns per table

[bransom]

As I understand it, the max amount of columns for any given table in MS SQL 2000 is 1024. I can find no documentation on how this might be set to a different amount for a specific table.

I really wish I could though, because I encounter the following problem for a relatively small table:

No more columns can be added to it, and it has only 300 columns, and only one primary key, so that can be the limiting factor. There are approximately 8500 rows, and size shouldn't be an issue either, as it's sitting at about 1.1 gigs. I'm sure I'm missing something incredibly simple, but I am in no way an SQL guru. Any help or opinion is appreciated.

 

[fluteplr]

I don't suppose you could tell us how your are trying to add the column and what specific error your are getting?

Also sql server has a limit of approx 8050 bytes per record excluding text and image fields. I can't remember the exact #. You may be running into that limit.

 

[bransom]

That's the strange thing. There is no error. Enterprise Manager simply does not allow you to enter any more columns. Would a limitation on an individual record size (of which there has been no indication) prevent the addition of a new column?

Hmmm, come to think of it - maybe that's it. Feel free to add anything you feel relevant, but I could see how reducing the size of some columns could allow for new columns to be added. Thanks for the advice.

 

[Databaseguy]

Well, all I can say is your DB most likely needs to be normalized.

My inventory program main table has maybe 20 columns....

 

[bransom]

Hate to bother you for this, but do you possibly have a link for normalizing one's DB?

 

[SonOfEmidec1100]

If you look in SQL 2000 BOL it gives all sql size limitations

SQL Server Architecture
Search for 'Maximum Capacity Specifications'

 

[bransom]

strangely enough, the issue seems to be the Design Table function in Enterprise Manager. It's Win NT running SQL Server 2000, with MMC 1.2. Seems you can only add 300 columns with this thing, otherwise I've been able to add as many as I want (so far) with a simple script.

 

[MaffewW]

yes using the EM is the problem, you should be able to add up to 1024 columns using tsql. you can also make the size of a row bigger than the max allocated of 8k eg 100 x columns of varchar255 though you have to be sure that the total amount of data across all the fields will never exceed this value. Also because SQL Server allows you to have this many columns doesnt mean you should. even 300 columns is a lot and you shouldnt need to keep adding to this, you should have a look at your database structure and read about normalization
HTH

Matt

Brighton, UK

 

[tlbroadbent]

The 300 column limitation appears to be a feature of the EM table designer. It would be nice if rows could be inserted after the last row but I've not yet found a way to do that. Here is a work-around.
[ol][li]Open the table in design mode.
[li]Move to the last column.
[li]Right-click the column
[li]Select Insert Column from the menu.
Do this for as many columns as you want to add.
[li]Move to the last column, select the grid row and drag it to its original position.
[li]Fill in the design grid for the new columns.[/ol]

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm