CODEWITHSUNDEEP
sql-server-2012
user3072048
user3072048

Reputation: 239

SQL Server : limitation of 1000 columns per table

I am saving in real-time on a second basis the prices of N different stocks into a Microsoft SQL Server 2012 database where all the data for a specific day go into a specific data table for that day.

As I want to be as quick as possible retrieving the data in real-time, the table structure is as simple as possible: with the row-names marking the time-stamp and the column names the unique names of the stocks. This set-up is simple but useful as it is important to obtain an synchronous snapshot of the data simultaneously when analysing the data.

The problems occurs when the number of stocks N exceeds 1000 as SQL Server does not allow me to create tables with more than 1000 columns.

My question is how I should best design a database in the case of N > 1000 such that the data-retrieval compromises speed as little as possible and also shifts as little database logic to the user as possible (such as keeping explicitly track of what stock belongs to which table in the case of a multi-table solution).

Any comments would be welcome

Upvotes: 1

Views: 166

Answers (1)

Ashigore
Ashigore

Reputation: 4678

Your database design is flawed, you should design your table like this:

RecordID
StockID
DateTime
Value

and have one row per recorded value per stock and collate afterwards.

Upvotes: 2

Related Questions