A million rows and a narrow table is not really a big table. I would expect most queries to return sub-second, unless you are pulling back the whole table to client, in which case rendering the rows in the SSMS grid is really taking the time.
Having said that, this might be a good use for indexed views ( and there aren't many! ). I don't tend to use them a lot as they can destroy INSERT / UPDATE / DELETE performance on the underlying table. However as your table is static and you have custom reporting you requirements, you can create multiple non-clustered indexes on the indexed view to service them. They also can help with aggregates. Here's my simple rig with 1 million rows which shows how indexed views with supporting indexes might work for you:
USE tempdb
GO
SET NOCOUNT ON
GO
------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.vw_table1_reports') IS NOT NULL DROP VIEW dbo.vw_table1_reports
IF OBJECT_ID('dbo.Table1') IS NOT NULL DROP TABLE dbo.Table1
GO
CREATE TABLE dbo.Table1 (
id NUMERIC PRIMARY KEY IDENTITY (1, 1),
[idUser] INT NOT NULL,
[Amount] INT NOT NULL,
[Attempts] INT NOT NULL,
[date] DATETIME NOT NULL,
[SUM_Amount] INT NOT NULL
) ON [PRIMARY]
GO
-- Add one million rows
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
CROSS JOIN master.sys.columns c2
CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.Table1 ( idUser, Amount, Attempts, date, SUM_Amount )
SELECT rn % 33 AS idUser, rn % 44 AS Amount, rn % 7 AS Attempts, DATEADD( day, rn % 300, '1 Jan 2014' ) AS [date], rn % 77 AS SUM_Amount
FROM cte
GO
-- Setup END
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-- Original Queries START
------------------------------------------------------------------------------------------------
-- Original queries
DECLARE @n INT = 10, @m INT = 33
SELECT TOP (@N) *
FROM dbo.table1
ORDER BY [SUM_Amount] DESC, [Attempts] DESC
SELECT TOP (@n) *
FROM dbo.table1
WHERE [SUM_Amount] >= @m
ORDER BY [SUM_Amount] ASC
GO
-- Original Queries END
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-- Indexed Views START
-- with supporting non-clustered indexes
------------------------------------------------------------------------------------------------
IF OBJECT_ID('dbo.vw_table1_reports') IS NOT NULL DROP VIEW dbo.vw_table1_reports
GO
CREATE VIEW dbo.vw_table1_reports
WITH SCHEMABINDING
AS
SELECT id, idUser, Amount, Attempts, [date], SUM_Amount
FROM dbo.table1
GO
CREATE UNIQUE CLUSTERED INDEX cdx_vw_table1_reports ON dbo.vw_table1_reports ( id )
GO
CREATE INDEX idx_vw_table1_reports1 ON dbo.vw_table1_reports ( [SUM_Amount] DESC, [Attempts] DESC )
INCLUDE ( idUser, Amount, [date] )
GO
CREATE INDEX idx_vw_table1_report2 ON dbo.vw_table1_reports ( [SUM_Amount] ASC )
INCLUDE ( idUser, Amount, Attempts, [date] )
GO
-- Indexed Views END
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
-- Revised Indexes START
------------------------------------------------------------------------------------------------
DECLARE @n INT = 10, @m INT = 33
-- Revised query 1
SELECT TOP (@n) *
FROM dbo.vw_table1_reports
ORDER BY [SUM_Amount] DESC, [Attempts] DESC
-- Revised query 2
SELECT TOP (@n) *
FROM dbo.vw_table1_reports
WHERE [SUM_Amount] >= @m
ORDER BY [SUM_Amount] ASC
GO
-- Revised Indexes END
------------------------------------------------------------------------------------------------
Any my results:
Note the original queries are executing in less than a second, but the revised ones are 'quicker'.
Drop and recreate these indexes if you have to reload the table.
In summary, if your queries are returning sub-second, you don't need to worry too much about indexing. If not, consider indexed views, considering the constraints around their use and possible overheads.
