How to use string variable for SQL Server index hint?

Question

I have a SQL Server 2012 database table with geometry columns and there is a single Spatial index on this table.

I have a SQL query which I run to do some geospatial analysis (intersect/contains) and the performance of query execution differs a lot when forcing to use the spatial index and not (which according to this answer depends on the size of the table I use). My tables are just 1-2K of polys and 1-2K of points.

So, I use WITH (INDEX(S7_idx)) in the SQL query where the S7_idx is the name of my Spatial index.

However, when I will execute this SQL query on another database that has the same table, the Spatial index might have another name. So, I don't want to hard-code the index name. I think it would be great if I could retrieve the index value dynamically as I can be sure that there is only one Spatial index on the table.

select name from sys.indexes where object_id = (select object_id from sys.objects where name = 'TableName') AND type_desc = 'SPATIAL'

The result:

name S7_idx

Great. Now I want to use this value instead of hard-coded index name in the WITH statement. How do I do this?

I think I cannot use dynamic SQL (with EXECUTE sp_executesql) inside my WITH (INDEX(...)) statement.

Answer 1

Using hints are never recommended unless you have a well experienced DBA suggested it. Keeping the statistics up to data will solve so many issues.

If you are sure about the hint used, then you need to use dynamic query

DECLARE @sql VARCHAR(8000)= ''

SET @sql = 'SELECT *
FROM TableName
WITH (INDEX('
           + (SELECT NAME
              FROM   sys.indexes
              WHERE  object_id = (SELECT object_id
                                  FROM   sys.objects
                                  WHERE  NAME = 'TableName')
                     AND type_desc = 'SPATIAL')
           + '))'

PRINT @sql

EXEC(@sql) 

Note : Above query considers in your table there is only one index with type_desc = 'SPATIAL'