Table and design advice

Question

Table and design advice

Digs

SSCertifiable

Points: 7333
More actions
April 29, 2010 at 4:40 pm

#218347

CREATE TABLE [dbo].[DIM_Data_OHLC](
[Symbol] [varchar](20) NOT NULL,
[Date] [datetime] NOT NULL,
[Open] [float] NOT NULL,
[High] [float] NOT NULL,
[Low] [float] NOT NULL,
[Close] [float] NOT NULL,
[Volume] [int] NOT NULL,
[AdjClose] [float] NULL,
[CreatedOn] [datetime] NULL)
go
INSERT INTO dbo].[DIM_Data_OHLC]
([Symbol],[Date],[Open],[High] ,[Low],[Close] ,[Volume],[AdjClose],[CreatedOn]
VALUES('MSFT','04/22/2010',46.63,47.17,46.37,47.06,10291300,47.06,getdate())
go
INSERT INTO dbo].[DIM_Data_OHLC]
([Symbol],[Date],[Open],[High] ,[Low],[Close] ,[Volume],[AdjClose],[CreatedOn]
VALUES('MSFT','04/23/2010',46.63,47.17,46.37,47.06,10291300,47.06,getdate())
go
INSERT INTO dbo].[DIM_Data_OHLC]
([Symbol],[Date],[Open],[High] ,[Low],[Close] ,[Volume],[AdjClose],[CreatedOn]
VALUES('AAPL','04/22/2010',46.63,47.17,46.37,47.06,10291300,47.06,getdate())
go
INSERT INTO dbo].[DIM_Data_OHLC]
([Symbol],[Date],[Open],[High] ,[Low],[Close] ,[Volume],[AdjClose],[CreatedOn]
VALUES('AAPL','04/23/2010',46.63,47.17,46.37,47.06,10291300,47.06,getdate())
go
The table holds stock price data (see field names).
I have decided to make the primary key a combined field of SYMBOL and DATE. As this will make the key unique. And not allow any duplicated to be loaded of Symbol and date combination.
Question:Are they any issues with this type of primary key in search data?
The main and only search SQL type will be:
SELECT ]Date[,[Open],[High],[Low],[Close],[Volume],[AdjClose] FROM [DIM_Data_OHLC] WHERE [Symbol] ='MSFT'
I will not be doing searchs with WHERE DATE = ??
Other options of primary key.
1) Add a Auto number, then make symbol and date unque search indexes. But I dont see the point of this.
Any holes in my approach to this table.
My ultimate wish is FAST data reading of the table by the search SQL I posted here:
SELECT ]Date[,[Open],[High],[Low],[Close],[Volume],[AdjClose] FROM [DIM_Data_OHLC] WHERE [Symbol] ='MSFT'

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

I would make sure you put a clustered index on it. I would also imagine that there should be a date comparison since that is a key part of evaluating stock worth (history and current value).

I would also change that symbol field to a smaller sized field - no need for varchar(20) unless there is a symbol that long.

Otherwise that PK combo should be fine.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Digs SSCertifiable Points: 7333 More actions · Answer 2

Thanks..

I thought a primary key was a clustered index ??

Is it not ??

if not whats the code for that type of index on a field combo ??

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Digs (4/30/2010)
Thanks..
I thought a primary key was a clustered index ??
Is it not ??
if not whats the code for that type of index on a field combo ??

The primary key is not always a clustered index. The default behavior is to create it as a clustered index - but it doesn't have to be that way.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Digs SSCertifiable Points: 7333 More actions · Answer 4

So how do I check my index type ? ( ie I have a combo field primary key), if its not there, how do I cluster this index ???

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

This is how you would create it when issuing your create table statement

CONSTRAINT [PK_TableName_Col1_Col2]

PRIMARY KEY CLUSTERED ([Col1], [Col2])

as an example.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 6

I would perform a search on "SQL server float vs decimal" to see if float will work for you. I am not an expert by any means on this subject but you may want to investigate further.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

I'd recommend NOT using a clustered index that has Symbol,Date as the columns and in that order... there will be a pot wad of page splits.

Make Symbol,Date a NON clustered PK (see Books Online for how to do that). Use something more temporal such as an IDENTITY or other date column for the clustered index on this soon to be large table.

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 8

p.s. Nice post, Digs. It's always great to see the table and some data.

--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)

Digs SSCertifiable Points: 7333 More actions · Answer 9

Jeff,

Thanks

Yes this table could be 300,000 records, and I need speed to read.

So you say

1) have identity field like ID with Auto Identity: clustered

2) Make Symbol and Date a combined colomn PK, NON clustered index

Hows the above done, I thought PK was clustered automatically? So how is a PK done NON clustered??

Sorry I have to ask for code on this index combination?

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 10

Heh... you also need speed to insert and doing it the way I suggested will help both speed to read and speed to insert.

PK's are only clustered by default. You can specify that a PK is NON Clustered. Like I said in my previous post, see Books Online for the syntax because there's a couple of ways to do it. Hint... CREATE TABLE and ALTER TABLE.

--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)

Digs SSCertifiable Points: 7333 More actions · Answer 11

Digs

SSCertifiable

Points: 7333

May 1, 2010 at 1:04 am

#1160877

Thanks Jeff, Speed much improved !!

:-):-D:-):-D

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Digs (5/1/2010)
Thanks Jeff, Speed much improved !!
:-):-D:-):-D

Thanks for the feedback, Digs. 🙂

--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)