asked on

Maimum Number of Columns in a Table ??

Hi all
I want to know that what is the maximum number of COLUMNS that a table in a MS-SQL server have..?

ps. I need to have 500 columns in a table..is it possible ?
Regards
Khacharn

nigelrivett

Yes, up to 1024.

nigelrivett

p.s. Max rowsize = 8,060 bytes. Can use text/ntext for more.

khacharn

ASKER

Are You sure i can i have around 1024 columns..What if i want more then 1024 columns..??
Regards
Khacharn
i have Ms-SQL 7

nigelrivett

Max columns in a table for sql server 7 is 1024. You will (or at least should) get an error if you try to add more.

Try this

set nocount on
create table #a (s varchar(100))
declare @i int
select @i = 0
while @i < 1025
begin
select @i = @i + 1
insert #a select 'c' + convert(varchar(10),@i) + ' int,'
end
select * from #a
drop table #a

copy the output to a query window and put the create table statements round it and run it to see what happens.

nigelrivett

If you want more then put an ID on the table and create another table linking the columns using that ID.
Or do something like putting XML (or any sort of column identifiers and text) into a text field.

jboyd

500 columns??? You may want to rethink you database design. If you explain what you are trying to store, maybe the group can assist you with a better design.

cgoca

yeah, I agree with jboyd.
that doesn't sounds right.

khacharn

ASKER

Well OK

I have an Application which fetches ONLINE STOCK PRICES from somewhere.
THE PROBLEM is that i need to store all the values i fetch somewhere as i need to make charts and hence i need all the values of a particular STOCK.
Normally the values come after every one minute and the market is open for 6 hours so that makes ..
60 * 6 = 360 values which i need to store somehow and somwhere.
So Could you guys suggest some other solution ?
Eagerly waiting for Responses and i wouldn't mind transferring more points for a feasible solution.
Please help ASAP
Regards
Khacharn

wqw

well,

you do not need exactly 360 columns here but precisely 4!!!

create table Stocks (
id int identity,
date datetime,
stock varchar(20),
quote real)

HTH,

</wqw>

ASKER CERTIFIED SOLUTION

nigelrivett

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

khacharn

ASKER

Dear
Your design is Good but what if say i have 500 companies then the second table will rows Equal to 500 * 360 = 180000 which is a very big number...thats what i feel..and i may be DEAD WRONG..

And the Design that i have will at any time have only 500 rows..

Please Comment ..i need help..:-)
Regards
khacharn

nigelrivett

SQL Server is good at accessing small rows (it's called indexing and to do with the page size). 180,000 rows is actually quite small in database terms, small enough that you shouldn't have to worry too much about query efficiency.

create table Stock
(
StockID int,
Description varchar(100)
)
go
create index ix on Stock
(StockID)
go

create table Company
(
CompanyID int,
Description varchar(100)
)
go
create index ix on Stock
(StockID)
go

Create table StockPrice
(
CompanyID int ,
StockID int ,
InsertDate DateTime ,
Amount decimal(28,4)
)
go
create index ix on StockPrice
(CompanyID, StockID, InsertDate)
go

MuadDib

khacharn,

you should take the solution by nigelrivett, SQL-Server 4 (which is a quite old thing)
easily handles more than 1.000.000 big (>10 varchar fields) rows per table. 180.000, indexed, integer - this is quite a few!
So don't worry about performance.

Paul

cgoca

khacharn does have point,
180000 is only for one day,
yes that is not a big number for SQL,
but what if we need store a month, a year?
then the number will go up a lot.
would it still be ok in that situation?
what about create a table for each company?

nigelrivett

In this case decide what needs to be stored.
Each day update summary tables with data that needs to be reported. Archive the daily data to static tables if need be. Keep the on-line accessed tables small and you shouldn't have any problems.

danblake

To do long-term time analysis.

Use the OLAP Services.

These can also be used to design your core tables.

This way you will be able to splice the data by time frame. Expect all data to be returned < 5sec if using OLAP.

dan.

Moondancer

It's time to clean up this topic area and that means taking care of this question. Your options at this point are:

1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.

2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

3. Delete the question because it is of no value to you or to anyone else. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

If you elect for option 2 or 3, all you need to do is post right here as a comment, and I will take care of the rest. We also request that you review any other open questions you might have and deal with them as necessary.

PLEASE DO NOT AWARD THE POINTS TO ME.

____________________________________________

Hi Experts:

In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question. Likewise, you can also suggest that I PAQ or delete the question.

Experts, please do not add further "answer" information to this question. I will be back in about one week to finalize this question.

Thank you everyone.

Moondancer - Community Support Moderator @ Experts Exchange

Netminder

Netminder

Force/accepted by

Netminder
Community Support Moderator
Experts Exchange