Need more than 300 columns

I have a database table with 130 columns, now I need 500 columns for a
special case, but I find that the limit is 300 in sql server 2000. Can i get
around that?
Any suggestions? Why is the limit 300?
The application works fine now, I think i use something around 30-40, I
don't want to change the data base structure and the code for only one case!
Thanx in advanceThe max number of columns in SLQ server 2000 is 1024. Perhaps the GUI too you are using to add
columns poses the limitation. Also, having a large number of columns often indicates a
non-normalized database design...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Hocke" <Hocke@.discussions.microsoft.com> wrote in message
news:67BB9B5A-1396-48B9-8B7E-66C2A7A84893@.microsoft.com...
> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can i get
> around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40, I
> don't want to change the data base structure and the code for only one case!
> Thanx in advance|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234jeOeq3EHA.524@.TK2MSFTNGP09.phx.gbl...
> The max number of columns in SLQ server 2000 is 1024. Perhaps the GUI too
> you are using to add
> columns poses the limitation. Also, having a large number of columns often
> indicates a
> non-normalized database design...
>
I agree with Tibor. Is it possible to create a bitmap for those other 500
columns and reduce them to a single varbinary(2048) or something similar?
Or possibly moving them to a separate child table with a 1 to 1
relationship?
Rick Sawtell
MCT, MCSD, MCDBA|||"=?Utf-8?B?SG9ja2U=?=" <Hocke@.discussions.microsoft.com> wrote in
news:67BB9B5A-1396-48B9-8B7E-66C2A7A84893@.microsoft.com:
> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can
> i get around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40,
> I don't want to change the data base structure and the code for only
> one case!
> Thanx in advance
In addition to Tibor's comment, row size is also limited by capacity.
You could get around the problem by vertically partitioning the table
(this solution may also help increase db performance)
What error messages or problems are you getting when trying to add more
columns?|||The fact that you have 130 columns but "one case" that needs 500 columns
suggests to me that you have the wrong design. Are you sure your schema is in
at least Third Normal Form? I doubt that adding more columns is the best
solution.
--
David Portas
SQL Server MVP
--|||Taking short cuts in database design will surely come back to hunt you so be
very careful.
Normalization is the standard way to proceed in designing a database. There
are cases when you might want to demoralize your database to speed data
retrieval at the expense of data modification. You might want to take a look
at the following article if you are not planning to normalize your DB:
http://www.objectarchitects.de/ObjectArchitects/orpatterns/Performance/Denormalization/CraigMullinsGuidelines/i001fe02.htm
Sasan Saidi,
MSc. CS, MCSE, IBM Certified MQ Administrator
Senior DBA
Brascan Business Services
"Hocke" wrote:
> I have a database table with 130 columns, now I need 500 columns for a
> special case, but I find that the limit is 300 in sql server 2000. Can i get
> around that?
> Any suggestions? Why is the limit 300?
> The application works fine now, I think i use something around 30-40, I
> don't want to change the data base structure and the code for only one case!
> Thanx in advance