MS Sql 2008 index on two columns for 2D position

Question

Hi,

I have table named "Map" with structure "XPos int, YPos int, FieldType int".

The "XPos" and "YPos" are primary key. There are several tables depending on this table, referencing "XPos" and "YPos" columns.

In "Map" table users can not add or remove rows. Rarely is changed value in "FieldType" column.

The most selects looks like:

SELECT M.XPos, M.YPos, M.FieldType, N.NoteTitle, N.NoteDescription
FROM Map AS M
INNER JOIN Note AS N ON M.XPos = N.XPos AND M.YPos = N.YPos
WHERE M.XPos >= 248 AND M.XPos <= 311
      AND M.YPos >= 578 AND M.YPos <= 715

How should I index it for good select performance?. Now I have one clustered index on "XPos, YPos", but I don't think this is best way.

Answer 1

create an index on the fields your selecting, and in the included columns add the fields your filtering by.

here's an example:

CREATE NONCLUSTERED INDEX [IDX_Cover] ON [dbo].[tblPROJECTS] ( [QUOTESTATUS] ASC ) INCLUDE ( [Project_Amount], [SubCost], [VendorCost], [CustCost], [crmProjID]) 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]

this index would work well for a statement like this:

select QuoteStatus from dbo.tblProjects where Project_Amount>0 and SubCost=0 and VendorCost>0 and CustCost>0 and crmProjID=2