Index Strategy - Table Design Hourly Data

Question

I am working on a financial project and I am trying to design a table with best index strategy which stores hourly data and I need faster data retrieval. Since its confidential I will explain with an example of a weather table. If I have a below table

CREATE TABLE [dbo].[WeatherM](
    [WID] [int] IDENTITY(1,1) NOT NULL,
    [CityId] [int] NOT NULL,
    [TempDate] [datetime] NOT NULL,
    [TempF] [decimal](18, 5) NOT NULL,
    [TempC] [decimal](18, 5) NOT NULL,
    [IsActive] [bit] NOT NULL,
    [IsDeleted] [bit] NOT NULL
) ON [PRIMARY]

    ALTER TABLE [dbo].[WeatherM] ADD  CONSTRAINT [DF_WeatherM_IsActive]  DEFAULT ((1)) FOR [IsActive]
    GO
    ALTER TABLE [dbo].[WeatherM] ADD  CONSTRAINT [DF_WeatherM_IsDelete]  DEFAULT ((0)) FOR [IsDeleted]
    GO

What would be the best possible indexes? I have where clause filter for CityId, TempDate and IsActive.

My usual select statement is

SELECT TOP (1000) [WID]
      ,[CityId]  -- Could join with City table and get CityName
      ,[TempDate]
      ,[TempF]  --fahrenheit
      ,[TempC]  --Celcius
      FROM [dbo].[WeatherM] WITH (NOLOCK)
  WHERE CityId = @CityId
  AND TEMPDATE BETWEEN @Date1 and @Date2
  AND IsActive = 1

What are the best possible indexes for faster results

Option 1: Create multi column clustered index with where clause column

CREATE CLUSTERED INDEX [IX_WeatherM_1] ON [dbo].[WeatherM]
(
    [CityId] ASC,
    [TempDate] ASC,
    [IsActive] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

-- Do I need any further non clustered indexes

Option 2: Create Multi column clustered index with resultset columns and one non clustered index with filter columns

CREATE CLUSTERED INDEX [IX_WeatherM_1] ON [dbo].[WeatherM]
(
    [CityId] ASC,
    [TempDate] ASC,
    [TempC] ASC,
    [TempF] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_WeatherM] ON [dbo].[WeatherM]
(
    [CityId] ASC,
    [TempDate] ASC,
    [IsActive] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Option 3: Any other?

You use indexes to support ALL of the queries against your table - not just one. Suggest you first determine how to define the [clustered index](https://stackoverflow.com/questions/2267326/how-to-choose-the-clustered-index-in-sql-server) since you can only have one. And stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). — SMor, Mar 24 '21 at 19:14
For the query you posted, I would go with option 1 and the column order CityId, IsActive, and TempDate. Indexes should specify the equality predicates first. Food for thought - do you actually use the WID in any queries? — Dan Guzman, Mar 24 '21 at 19:20
Two further points: `TempF` and `TempC` are redundant, specify one and make the other a computed column. `TOP (1000)` without `ORDER BY` is non-deterministic and could return different results each time — Charlieface, Mar 24 '21 at 21:34
Thanks for input SMor - Since there are few columns in this table, select columns are mostly Cityname,TempF. For the where clause yes there could be additional filter IsDeleted = 0 , [TempF] is not null. Thats it. But data would be huge, that is my worry — ASmith, Mar 25 '21 at 06:37
Will there be any difference in performance Option A : CREATE CLUSTERED INDEX [IX_WeatherM_1] ON [dbo].[WeatherM] ( [CityId] ASC, [TempDate] ASC, [IsActive] ASC ) Option B : Create Clustered Index on WID and then add Non clustered index on [CityId] ASC, [TempDate] ASC, [IsActive] ASC , will option A and option B behave same? — ASmith, Mar 25 '21 at 07:51

Index Strategy - Table Design Hourly Data

0 Answers0