Search code examples
sqlsql-serverindexingdatabase-performance

SQL Server Indexing Ordering

I have a table as follows

CREATE TABLE [dbo].[VideoRecipient](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [IssueId] [bigint] NOT NULL,
    [CreatedDateTime] [datetime2](7) NOT NULL,
    [NotifiedDateTime] [datetime2](7) NULL,
    [ReceivedDateTime] [datetime2](7) NULL,
    [ReadDateTime] [datetime2](7) NULL,
    [AcknowledgedDateTime] [datetime2](7) NULL,
    [IsDeleted] [bit] NOT NULL,
    [DeletedDateTime] [datetime2](7) NULL,
 CONSTRAINT [PK_VideoRecipient] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
))

I then create an index as below

CREATE NONCLUSTERED INDEX UX_VideoRecipient_UserId_IssueId_CreatedDateTime ON [dbo].VideoRecipient ([UserId], [IssueId], [CreatedDateTime]) INCLUDE ([ReadDateTime], [ReceivedDateTime], [AcknowledgedDateTime], [NotifiedDateTime])

When I make a query to get the record via the UserId, it uses the index and it uses an index seek which is what I want. If I then make a query to get the records by the IssueId, it does an index scan which is slower. Other than create another index and specify the IssueId as the first column to index on, is there a way to make the index a seek instead of a scan?

Specifying the first column to index would seem to be more important than I originally thought!

Solution

  • An index essentially creates another version of the table that only includes the columns you've mentioned. It is then sorted in the exact order in which you indicate (in your example it's in the order of UserId->IssueId->CreatedDateTime). Since the IssueId column in your index is second it means that the values will not be in order if that is the main value you're searching on. Because of this, SQL has to perform a 'scan' of all rows in the index to find the item(s) you're searching for.

    If you intend to search on only IssueId then you'll need to adjust your index. If you plan to have multiple queries that search on the different values then you'll need different indexes.

    But please keep in mind that adding indexes to a table will slow down inserts and updates slightly since the indexes need to be updated at the same time before the change is committed. Because of this you'll want to make sure that if you're creating an index it is because you need it as opposed to "just in case".