Can SQL Server use a spatial index and another index at the same time and be efficient?

Question

Let's say we have a table that contains two terrain datasets defined by TerrainModelID like below:

 CREATE TABLE [dbo].[DigitalTerrain](
 [DigitalTerrainID] [uniqueidentifier] NOT NULL,
 [Longitude] [float] NULL,
 [Latitude] [float] NULL,
 [ZCoordinate] [float] NULL,
 [Tile] [nvarchar](10) NULL,
 [TerrainModelID] [uniqueidentifier] NULL,
 [Location] [geography] NULL,
 CONSTRAINT [PK__DigitalTerrainID] PRIMARY KEY CLUSTERED(DigitalTerrainID))

I have a spatial index, Location_IX and also an index on the TerrainModelID column. In a query like the below is having the two datasets in the same table going to cause the spatial index issues/slowdowns?

SELECT 
 Location, 
 ZCoordinate, 
 TerrainModelID
FROM TerrainData.dbo.DigitalTerrain WITH(INDEX(Location_IX) NOLOCK) 
WHERE @line.STDistance(DigitalTerrain.Location) < (@margin) AND TerrainModelID = @TerModID

Is it still able to filter out the terrain model dataset we want and use the spatial index efficiently? Or should we differ the design or query here? Should this have been split into two tables, one for each terrain dataset?

Answer 1

mickyt : With only 2 models in your dataset the spatial index will be more effective than an index on model id. It will still do an index seek on the model id if required. However if you start adding more and more models to the table, you may get to a tipping point.

I tested on a table with 1,000,000 points and 99 models. Using the model index only was quicker and more efficient than forcing the spatial. With 9 models over the 1,000,000, forcing the spatial index was quicker and more efficient.