The World's Most Powerful Table
By Kalman Toth, M.Phil., M.Phil., MCDBA

August 20, 2005

There are lots of differences between tables and tables. Some tables store mundane, ordinary data, other tables contain exciting data. Some tables are very small, others store millions of rows. Here is a table which stores very powerful data: all the stock prices daily around the world:

USE AdventureWorks;

CREATE TABLE WorldStock (
TradingDate char(10),
StockExchange char(10),
Symbol char(8),
[Open] smallmoney,
High smallmoney,
Low smallmoney,
[Close] smallmoney,
Volume int
);

Note that we had to "bracket" Open and Close because those are keywords in Transact-SQL. We can use the following statement to populate the table:

insert WorldStock values('2009.03.23','NYSE','IBM',120.0, 121.1,119.5,120.5, 8000100)

Normally though, this table populates from stock market "feeds". Feeds are transmitted at the end of the day by companies involved in the financial data distribution business such as Bloomberg or Reuters.

To get an estimate for the size increase of the table in a year:

100 stock exchanges x 1,000 listed stocks x 240 trading days = 24 million