CODEWITHSUNDEEP
c#sql.netsql-serversql-server-2012
Tiele Declercq
Tiele Declercq

Reputation: 2130

Need advise in choosing the right SQL indexes

I'm re-doing my 'ticket app' in C# 4.5 / SQL2012 using Entity Framework 6.0. The previous development was made in a hurry without any extra indexes. The app works fine but I want v2 to be 'as it should be'. Before i go any further I'm in need of 'pad on the shoulder' and the words --yea.. ur doin' a'right-- ☺

In other applications my primary key was always my ID (int) column which happened to be clustered as it seems to be the default for SQL. Afterwards I just added an unclustered column 'when i felt like it'.

So in short.. a table ticket with an id, type (incident or request), status (open, on-hold, solved,..), subject, etc.. a table assignee with an id and a name a table junctionTicketAssignee with ticketId and assigneeId

ticket & assignee relationship is many to many. MOST of the time i will just fetch a ticket or assignee by it's ID. On the ticket overview, by default tickets are listed with status 'open' & 'on-hold' sorted by date (desc) assigned to the current assignee.

Therefor I now have the following indexes:

in 'ticket'
PK_ticket = PRIMARY KEY NON-CLUSTERED (ticket.id ASC)
IX_ticket = UNIQUE CLUSTERED (ticket.id ASC, ticket.dateCreate DESC, status ASC, type ASC)
IX_type = NON-UNIQUE NON-CLUSTERED (ticket.type)
IX_status = NON-UNIQUE NON-CLUSTERED (ticket.status)

in 'assignee'
PK_assignee = PRIMARY KEY CLUSTERED (assignee.id ASC)

in 'junctionTicketAssignee'
PK_junctionTicketAssignee = PRIMARY KEY CLUSTERED (ticketId ASC, assigneeId ASC)
FK_Assignee = PRIMARY KEY assignee.id -> FOREIGN KEY junctionTicketAssignee.assigneeId
FK_Ticket = PRIMARY KEY ticket.id -> FOREIGN KEY junctionTicketAssignee.ticketId

I've put this together with my basic knowledge on indexes. Am I on the right track?

Upvotes: 0

Views: 72

Answers (1)

SteveB
SteveB

Reputation: 1514

There are lots of different ways to work out if your indexing strategy is correct.

First of all find an article on index on the internet and read up on the different kinds of indexes available.

Then run some of your queries with the actual execution plan on and have a look at the output. Then find an article on execution plans and give that a quick scan.

Sometime the execution plan will hint at missing indexes and try and prod you in the right direction.

Have fun. And yes - you're on the right track.

Upvotes: 1

Related Questions