Unlimited columns
-
Hi guys,
I am currenlty looking for a dabases that does not have a limit on the number of columns (fields) for each table....
Is there such thing?
TNT
-
Not in SQL Server. The limit is 1,024 columns per table in SQL 2000.
Greg
Greg
-
Thanks for replying Greg.
I know that MS SQL 2000 Limit is 1024.
Currently the only two options that i have thought of are:
[1] Normalise the database ( Which my boss doesnot like )
[2] span the data over many tables...
Are there anyother options?
T.
-
Heh... text file...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thats what we are trying to get way from
TNT.
-
I know of no commercial DB that gives you an unlimited number of columns: Oracle tops off at 1000, MySql can get over 3000, depending on the underlying database engine...
The APPROPRIATE thing to do, as my colleagues suggest, is to normalize and split up your database.
If not, use a few columns of "Typed XML" datatype. You have up to 2 GByte of storage for these in optimized binary format; SQL Server has facilities for querying, modifying and indexing XML.
-
Hi CodeDigger,
Many thanks for your response!
i found this http://dev.mysql.com/tech-resources/features.html which compares database...
Sysbase and FrontBase look promising!!!
Thanks to all those who responded!!!
Regards,
T.
-
i would not let a non-dba force me to use an unfamiliar product just because someone wants a lot of columns in a table...I would stick with a product i was already familiar with, could easily find community level (ie sqlservercentral) support for , and come up with a presentation solution for that non-dba instead ;
that would allow me to continue best practices, normalizing, with no learning curve for alternate systems.
if a non-dba told you he wants everything in excel, wouldn't you explain that you can store the data safely, with better referential integrity, and more securely in sql server, and give him reports in excel instead?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hi Lowell
I agree with you and i'd rather use SQL SERVER ... I have already told my boss about the benefits of keeping to SQLSERVER ...
He has now agreed to atleast try the spanning of data over mulitple tables....
wish me luck!!!
Regards,
TNT
-
Now you just have to convince him to let you design a proper database system, and allow you to normalize the data so it doesn't have to span multiple tables.
Good Luck, which ever way you end up going!
-
Now you just have to convince him to let you design a proper database system, and allow you to normalize the data so it doesn't have to span multiple tables.
Good Luck, which ever way you end up going!
-
I can think of no good reason to do such a thing.
One of the things that a lot of folks don't realize is that most DB's that do a form of "replication" can't replicate the max number of columns... I forget what it is in SQL Server 2k but think I rember a limit of something like 526 columns.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heh, sorry... was having a bit of fun, there.
In al seriousness, the folks that have been saying that an unlimited number of columns is not necessary are all talking from experience. If you don't want to use just a text file, we have to, somehow, convince your boss to stop using a database as if it were a text file.
It would be interesting to know what kind of columns your boss is expecting so we can try to help you normalize the data and, perhaps, convince your boss.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi all,
Initially customers provide our software with a raw text flat file (along with a header file) which can have huge number of fields (>1000) of different data types…
The customer doesn’t know which fields to use for his\her statistical model until some analysis is done on most of the fields …
Once the analysis is performed the user then selects the most useful fields which numbers to few hundred fields.
My problem is in the initial process, when loading the 1000s of fields!
Normalisation would result in multiple row calls per record which is not ideal so the next best thing is spanning the record over multiple tables so that a single row is returned per record.
I will initially try this with MS Access then if it works (which it will
  I will move to MS SQL… I will notify you of the outcome…
Many thanks guys,
TNT.
, could someone give me an example of why you'd need more than 1024 columns in a data table??? Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply