Appropriate Sql Server Index For "insert Only" Schema

We are using SQLServer 2008, and have an 'insert-only' schema for many tables. An example of the sort of table that we have is (and this is just one example): create table spotquot

Solution 1:

Insert only Index :S I thin insert only schemas are best left without any indexes but having any index on a table will hurt the performance of your insert operations.

What about creating an index on feeditem_id and effectiveDateUTC fields something like

CREATE NONCLUSTERED INDEX NIX_feeditem_id_effectiveDateUTC
ON dbo.spotquotes(feeditem_id ASC, effectiveDateUTC DESC)
GO

and now write your query something like .....

;WITH LastestRecords
 AS(
   SELECT Id 
        ,feeditem_id 
        ,value_ask
        ,value_bid
        ,effectiveDateUTC
        ,ROW_NUMBER() OVER (PARTITIONBY feeditem_id ORDERBY ffectiveDateUTC DESC) AS RN
  FROM spotquotes
  )
 SELECT Id 
        ,feeditem_id 
        ,value_ask
        ,value_bid
        ,effectiveDateUTC
 FROM LastestRecords
 WHERE RN =1

OR

Create an index as follows

CREATE NONCLUSTERED INDEX NIX_feeditem_id_Id
ON dbo.spotquotes(feeditem_id ASC, ID DESC)
GO

Query

;WITH LastestRecords
 AS(
   SELECT Id 
        ,feeditem_id 
        ,value_ask
        ,value_bid
        ,effectiveDateUTC
        ,ROW_NUMBER() OVER (PARTITIONBY feeditem_id ORDERBY Id DESC) AS RN
  FROM spotquotes
  )
 SELECT Id 
        ,feeditem_id 
        ,value_ask
        ,value_bid
        ,effectiveDateUTC
 FROM LastestRecords
 WHERE RN =1

Solution 2:

For this query:

select feeditem_id, max(id) as latest from spotquotes groupby feeditem_id

create the following nonclustered index

CREATE INDEX IX_Spotquotes_feeditem_id on spotquotes(feeditem, id)