Post reply

Table and design advice

  • 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'

  • April 29, 2010 at 8:55 pm

    #1160142

    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

  • April 30, 2010 at 12:00 am

    #1160200

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

  • April 30, 2010 at 12:16 am

    #1160212

    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

  • April 30, 2010 at 12:55 am

    #1160240

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

  • April 30, 2010 at 8:37 am

    #1160474

    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

  • April 30, 2010 at 9:26 am

    #1160522

    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.

  • April 30, 2010 at 7:04 pm

    #1160835

    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)
    Intro to Tally Tables and Functions

  • April 30, 2010 at 7:05 pm

    #1160837

    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)
    Intro to Tally Tables and Functions

  • April 30, 2010 at 7:50 pm

    #1160842

    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?

  • April 30, 2010 at 11:14 pm

    #1160863

    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)
    Intro to Tally Tables and Functions

  • May 1, 2010 at 1:04 am

    #1160877

    Thanks Jeff, Speed much improved !!

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

  • May 1, 2010 at 8:39 am

    #1160898

    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)
    Intro to Tally Tables and Functions

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

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