0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professional and independent game developers

-- spool table to temp table
select *
into #Votes
from Votes

-- default index
CREATE UNIQUE CLUSTERED INDEX [UIX_Votes_Id] ON [#Votes](Id ASC)

-- added indexes

-- option 1
/*
CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId] 
   ON [#Votes](VoteTypeId ASC) 
*/

-- option 2

CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId_Include] 
   ON [#Votes](VoteTypeId ASC) 
   INCLUDE (PostId, CreationDate)


-- option 3
-- CREATE NONCLUSTERED INDEX [IX_Votes_PostId] ON [#Votes](PostId ASC)

-- option 4
/*
CREATE NONCLUSTERED INDEX [IX_Votes_CreationDate] 
    ON [#Votes](CreationDate ASC)
    INCLUDE (votetypeid)
*/

-- option 5
/*
CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId_Include] 
    ON [#Votes](PostId ASC, VoteTypeId ASC) 
    INCLUDE (CreationDate) 
*/

go
set showplan_all on
go

-- common query scenarios


select count(*) [bounties]
from #votes
where votetypeid = 8


select postid, score
from posts p
inner join #votes on postid = p.id
where votetypeid = 9


select count(*) [accepts in date range]
from #votes
where creationdate > '2020-01-01' -- and '2022-04-01'
and votetypeid = 1

fork query

edited jul 20 anonymous

created may 21 rene