Smart choice for primary key and clustered index on a table in SQL 2005 to boost performance of selecting single record or multiple records

Question

EDIT: I have added "Slug" column to address performance issues on specific record selection.

I have following columns in my table.

Id Int - Primary key (identity, clustered by default)
Slug varchar(100)
...
EntryDate DateTime

Most of the time, I'm ordering the select statement by EntryDate like below.

Select T.Id, T.Slug, ..., T.EntryDate
From (
    Select Id, Slug, ..., EntryDate,  
        Row_Number() Over (Order By EntryDate Desc, Id Desc) AS RowNum
    From TableName
    Where ...
) As T
Where T.RowNum Between ... And ...

I'm ordering it by EntryDate and Id in case there are duplicate EntryDates.

When I'm selecting A record, I do the following.

Select Id, Slug, ..., EntryDate
From TableName
Where Slug = @slug And Year(EntryDate) = @entryYear 
    And Month(EntryDate) = @entryMonth

I have a unique key of Slug & EntryDate.

What would be a smart choice of keys and indexes in my situation? I'm facing performance issues probably because I'm ordering by a column that is not clustered indexed.

Should I have Id set as non-clustered primary key and EntryDate as clustered index?

I appreciate all your help. Thanks.

EDIT:

I haven't tried adding non-clustered index on the EntryDate. Data inserted from back-end, so performance for insert isn't a big deal for me. Also, EntryDate is not always the date when it is inserted. It can be a past date. Back-end user picks the date.

Answer 1

Based on the current table layout you want some indexes like this.

CREATE INDEX IX_YourTable_1 ON dbo.YourTable
(EntryDate, Id)
INCLUDE (SLug)
WITH (FILLFACTOR=90)

CREATE INDEX IX_YourTable_2 ON dbo.YourTable
(EntryDate, Slug)
INCLUDE (Id)
WITH (FILLFACTOR=80)

Add any other columns you are returning to the INCLUDE line.

Change your second query to something like this.

Select Id, Slug, ..., EntryDate
From TableName
Where Slug = @slug 
    AND EntryDate BETWEEN CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE) AND DATEADD(mm, 1, CAST(CAST(@EntryYear AS VARCHAR(4) + CAST(@EntryMonth AS VARCHAR(2)) + '01' AS DATE))

The way your second query is currently written the index will never be used. If you can change the Slug column to a related table it will increase your performance and decrease your storage requirements.