title: Composite Index Design (Narrow vs Wide) when searching on date range
body: <p>I have an argument with my colleague regarding the design of the composite index. If two fields are being used in the query, I reckon the more unique field should be first.</p>
  <p>Look at this example</p>
  <pre><code>CREATE TABLE [dbo].[tblPurchase](
      [PurchaseId] [int] NOT NULL,
      [ProductId] [int] NOT NULL,
      [ItemId] [int] NOT NULL,
      [PurchaseDate] [date] NOT NULL,
      [CurrencyId] [int] NOT NULL,
      ...
      ...
      ...
  CONSTRAINT [PK_tblPurchase] PRIMARY KEY CLUSTERED 
  (
      [PurchaseId] ASC,
      [ProductId] ASC,
      [ItemId] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  <p>At the moment we have 2 currencies and we have about 2 million records (note: we may add 2 new currencies in near future).</p>
  <p>I decided to create an index using <code>[PurchaseDate]-[CurrencyId]</code> as the composite index, but my colleague argue that we should flip it to use <code>[CurrencyId]-[PurchaseDate]</code> as CurrencyId is less unique. Our query is:</p>
  <pre><code>SELECT *
  FROM [tblPurchase]
  WHERE [PurchaseDate] &gt;= '2022-01-01' AND [PurchaseDate] &lt;= '2022-06-01' AND [CurrencyId] = 1
  </code></pre>
  <p>Any thought?</p>