Sql-server – Validate Primary Key and Index Selection

indexprimary-keysql serversql-server-2005

I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this level of table design so advice would be greatly appreciated.

The table will be queried mainly on looking for a specific transaction (TrxDate, StoreNo, RegNo and TrxNo) or summarising data by TrxDate/ StoreNo.

Do my indecies look correct? Should the UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo index be a primary key? Is it worth adding another index just to cover looking for specific transactions (same a UX but without SeqNo)?

This is going to be a key table as the database grows and I really want to avoid having to change things round once the table has grown significantly.

CREATE TABLE sfs.tbl_POS_eCommTrx
(TrxDate int NOT NULL,
    StoreNo smallint NOT NULL,
    RegNo smallint NOT NULL,
    TrxNo smallint NOT NULL,
    SeqNo tinyint NOT NULL,
    SKN int NOT NULL,
    Pcs smallint NOT NULL,
    Retail decimal(38,4) NOT NULL,
    CorrTend numeric(1,0) NOT NULL,
    OrderID int,
    UID int NOT NULL identity(1,1),
    DateStamp datetime Default(getdate())
) 

CREATE UNIQUE INDEX UX_POS_eCommTrx_TrxDate_StoreNo_RegNo_TrxNo_SeqNo 
    ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo,RegNo,TrxNo,SeqNo)

CREATE INDEX IX_POS_eCommTrx_TrxDate_StoreNo
    ON sfs.tbl_POS_eCommTrx (TrxDate,StoreNo)

Thanks in advance.

Best Answer

I wouldn't get too hung up on estimating the correct indexes ahead of time. Make the ones you think you'll need then watch the missing index DMV's after you get some data in the table.

In my experience, it's pretty difficult to correctly estimate the indexes that will have the biggest impact until you start actually querying the data, unless you know EXACTLY the queries you will use. If you DO know exactly the queries you will be using, post them so we can help you.

You will also want to view the estimated and actual execution plans for any queries you do make, looking for table scans with an eye to converting them into index seeks.

Related Question