SQL Server database design for high volume stock market price data -

i writing application store , retrieve stock market price data data inserted on daily basis. storing data each asset (stock) , of market in world. current design of tables

country table:

create table [dbo].[list_country] (     [countryid] [char](2) not null,     [name] [nvarchar](100) not null,     [currenycode] [nvarchar](5) null,     [currencyname] [nvarchar](50) null      constraint [pk_dbo.list_country]          primary key clustered ([countryid] asc) ) 

asset table:

create table [dbo].[list_asset] (     [assetid] [int] identity(1,1) not null,     [name] [nvarchar](max) not null,     [countryid] [char](2) not null,      constraint [pk_dbo.list_asset]         primary key clustered ([assetid] asc) ) 

foreign key constraint on country:

alter table [dbo].[list_asset] check  add constraint [fk_dbo.list_asset_dbo.list_country_countryid]      foreign key([countryid])     references [dbo].[list_country] ([countryid])         on delete cascade go 

stock_price table:

create table [dbo].[stock_price_data] (     [stockpricedataid] [int] identity(1,1) not null,     [assetid] [int] not null,     [pricedate] [datetime] not null,     [open] [int] not null,     [high] [int] not null,     [low] [int] not null,     [close] [int] not null,     [volume] [int] not null,      constraint [pk_dbo.stock_price_data]          primary key clustered ([stockpricedataid] asc) ) 

foreign key constraint on asset:

alter table [dbo].[stock_price_data] check  add constraint [fk_dbo.stock_price_data_dbo.list_asset_assetid]      foreign key([assetid])     references [dbo].[list_asset] ([assetid])         on delete cascade 

the concern have @ moment stock_price_data table filled high volume rows, i.e. specific market in country, there can 20,000 assets. thus, in year (260 days of trading) , potentially have 5.2 million rows each country.

the application not restrict user accessing data other default country (which setup during login).

is idea have separate table (i.e. stock_price_data_au) each country? or there better way design database above scenario?

-alan-

first of - i'd drop _data table name - overkill. if reasonably users filter data country - ie looking @ 1 country @ time i'd consider partitioning table country id - way sql server use partition elimination pick relevant data. way ease of maintenance 1 table performance if separate table per country. (i'm assuming have enterprise edition) if load works on per country basis can switch out partition , drop indexes faster loads.