Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Not sure why TrxDate is an int. –  ETL Apr 17 '13 at 0:44
    
I had only set it as [int] as other tables created in the database by others use that data type for the date (format YYYYMMDD, though you probably realised that) and I wasn't sure if issues would arise if I changed it for this table, if you could advise? –  Simon1979 Apr 17 '13 at 0:56
    
Apologies, amended to tag as sql-server, 2005 to be exact –  Simon1979 Apr 17 '13 at 5:31
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.