Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.

Here's my theory from the query I've just optimised, through a process of trial and error and reading the MSDN documentation.

The Query

DECLARE @pic_id int
SET pic_id = 1

SELECT ROW_NUMBER() OVER (ORDER BY pic_date desc) AS row_num, *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND (hidden = 0 OR pic_id = @pic_id)

The Index

CREATE NONCLUSTERED INDEX [IX_tbl_pics] ON [dbo].[tbl_pics] 
(
    [map_id] ASC,
    [deleted] ASC,
    [pic_date] DESC
)
INCLUDE ( [hidden], [pic_id] )

There is also a PK index on pic_id

The Theory

The key columns are so, because they are used in either a WHERE clause (but not used in a OR situation) or and ORDER BY.

The nonkey (INCLUDE) columns as so, because they get used in the WHERE, but because they are used in an OR scenario they can't (can't = won't improve performance) be a key column.

Are these presumptions correct? If not, what am I missing?

Thanks!

You are asking the query optimizer to produce a plan that can answer the query:

SELECT *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND hidden = 0;

The rest is fluff (including the OR pic_id = @pic_id). This will be a table scan, guaranteed, because of the low selectivity of the predicates involved (I'm sure deleted and hidden are 0/1, and map_ip I doubt it has any significant impact). The only predicate that could save the query is pic_id = @pic_id but by placing it in an OR condition you killed it's chance. No secondary index can help it, realistically. The addition of ROW_NUMBER will ad a sort, most likely, but the real damage is the scan.

This is a lost cause. Come up with realistic requirements.

StackExchange Database Administrators Q#22688, answer score: 8