set statistics time on;
SELECT TOP (15) *
FROM (select comment.*,
             sourceComment.source_id as source_id,
             sourceComment.source_status_id as source_status_id,
             sourceComment.source_votes as source_votes,
             ROW_NUMBER() OVER (ORDER BY [votesDisplay] DESC, added DESC) as sortColumn 
      from SourceComment sourceComment 
        inner join Comment comment 
        on sourceComment.id=comment.Id 
      where comment.erased=0 
      and comment.added>='2008-08-01 00:00:00' 
      and comment.added<'2010-12-04 00:00:00' 
      and (1=0 or comment.category=17)) as query /* эта запись означает, что категории может не быть */
      WHERE query.sortColumn > 3500 
      ORDER BY query.sortColumn
set statistics time off

set statistics time on;
SELECT TOP (15) author, 
        sumVotes
FROM (select author, 
         SUM(votesDisplay) as sumVotes,
         ROW_NUMBER() OVER(ORDER BY SUM(votesDisplay) DESC) as sortColumn 
      from Comment comment
      where Erased = 0
      and author != 'Аноним'
      and Added >= '2008-08-18 00:00:00'
      and Added < '2010-12-05 00:00:00'
      group by author) as query 
WHERE query.sortColumn > 3500 
ORDER BY query.sortColumn
set statistics time off

/* -------- Основная таблица. ~700k записей. */
CREATE TABLE [Comment](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [erased] [bit] NOT NULL,
    [author] [nvarchar](128) NOT NULL,
    [text] [nvarchar](410) NOT NULL,
    [added] [datetime] NOT NULL,
    [category] [tinyint] NOT NULL,
    [votes] [int] NOT NULL,
    [votesDisplay] [int] NOT NULL,
    [obscene] [bit] NOT NULL,
 CONSTRAINT [PK__STATUS] PRIMARY KEY NONCLUSTERED 
(
    [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]
GO

/* -------- Вторая таблица */
CREATE TABLE [SourceComment](
    [id] [bigint] NOT NULL,
    [source_status_id] [bigint] NOT NULL,
    [source_id] [bigint] NOT NULL,
    [source_votes] [int] NOT NULL,
 CONSTRAINT [PK__SOURCESTATUS] 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]
GO


/* -------- Foreign Key со второй таблицы на основную */
ALTER TABLE [SourceComment]  WITH CHECK ADD  CONSTRAINT [FK__SOURCES_REFERENCE__STATUS] FOREIGN KEY([id])
REFERENCES [Comment] ([id])
GO
ALTER TABLE [SourceComment] CHECK CONSTRAINT [FK__SOURCES_REFERENCE__STATUS]
GO


/* --  ИНДЕКСЫ -- */

/* -------- Таблица [Comment] - кластерный индекс. */
CREATE CLUSTERED INDEX [erased_category_votesDisplay_added_author] ON [Comment] 
(
    [erased] ASC,
    [votesDisplay] DESC,
    [added] DESC,
    [author] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/* -------- Таблица [Comment] - не кластерный индекс. Нужен для запроса, который здесь не упоминается, но для полноты картины решил его привести */
CREATE NONCLUSTERED INDEX [added] ON [Comment] 
(
    [added] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

/* -------- Таблица [SourceComment] - не кластерный индекс. */
CREATE UNIQUE NONCLUSTERED INDEX [id_in_source__and__source_id_index] ON [SourceComment] 
(
    [source_status_id] DESC,
    [source_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

O>set statistics time on;
O>SELECT TOP (15) author, 
O>        sumVotes
O>FROM (select author, 
O>         SUM(votesDisplay) as sumVotes,
O>         ROW_NUMBER() OVER(ORDER BY SUM(votesDisplay) DESC) as sortColumn 
O>      from Comment comment
O>      where Erased = 0
O>      and author != 'Аноним'
O>      and Added >= '2008-08-18 00:00:00'
O>      and Added < '2010-12-05 00:00:00'
O>      group by author) as query 
O>WHERE query.sortColumn > 3500 
O>ORDER BY query.sortColumn
O>set statistics time off
O>

set statistics time on;
SELECT author, 
       sumVotes
FROM (select author, 
         SUM(votesDisplay) as sumVotes,
         ROW_NUMBER() OVER(ORDER BY SUM(votesDisplay) DESC) as sortColumn 
      from Comment comment
      where Erased = 0
      and author != 'Аноним'
      and Added >= '2008-08-18 00:00:00'
      and Added < '2010-12-05 00:00:00'
      group by author) as query 
WHERE query.sortColumn > 3500 and query.sortColumn < 3516
set statistics time off

select id from Comment where Added >= '2008-08-18 00:00:00' and Added < '2010-12-05 00:00:00'