CODEWITHSUNDEEP
sql-serverindexing
Zsolt
Zsolt

Reputation: 345

SQL Server what indexes to create

I have a simple table:

CREATE TABLE DocModHistory
  [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
  [DocID] [int] NOT NULL,
  [BranchID] [int] NOT NULL,
  [UserID] [int] NOT NULL,
  [InsDate] [datetime] NOT NULL,
  [Type] [int] NOT NULL,
CONSTRAINT [PK_DocModHistory] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and I have two select queries:

SELECT dh.BranchID, MAX(dh.ID) as MaxID FROM DocModHistory dh WHERE dh.UserID = @p_UserID GROUP BY dh.BranchID

and

SELECT dh.DocID, MAX(dh.ID) as MaxID FROM DocModHistory dh WHERE dh.UserID = @p_UserID GROUP BY dh.DocID

Could you tell me please what indexes should I create? Shall I create individual indexes for UserID, BranchID, DocID, or do I need multi-column indexes?

Thank you!

Upvotes: 0

Views: 43

Answers (1)

Adriaan Stander
Adriaan Stander

Reputation: 166396

Create an index for UserID with Included columns BranchID, ID and DocID

So something like

CREATE INDEX IX_UserID ON DocModHistory (UserID) INCLUDE (BranchID, ID ,DocID);

Upvotes: 1

Related Questions