如何使用字符串变量进行SQL Server索引提示？

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

我有一个带有几何列的SQL Server 2012数据库表，并且该表上有一个Spatial索引。

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.

我有一个SQL查询，我运行它来做一些地理空间分析（交叉/包含）和强制使用空间索引时查询执行的性能差异很大（根据这个答案取决于表的大小我使用）。我的桌子只有1-2K的多边形和1-2K的点数。

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

因此，我在SQL查询中使用WITH（INDEX（S7_idx）），其中S7_idx是我的Spatial索引的名称。

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.

但是，当我将在具有相同表的另一个数据库上执行此SQL查询时，Spatial索引可能具有另一个名称。所以，我不想硬编码索引名称。我认为如果我可以动态检索索引值会很好，因为我可以确定表上只有一个Spatial索引。

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

从sys.indexes中选择名称，其中object_id =（从sys.objects中选择object_id，其中name ='TableName'）AND type_desc ='SPATIAL'

The result:

结果：

name S7_idx

名称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?

大。现在我想在WITH语句中使用此值而不是硬编码索引名称。我该怎么做呢？

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

我想我不能在我的WITH（INDEX（...））语句中使用动态SQL（带有EXECUTE sp_executesql）。

1 个解决方案

#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.

除非您有经验丰富的DBA建议，否则不建议使用提示。保持统计数据可以解决许多问题。

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'

注意：上面的查询在您的表中考虑只有一个索引，其中type_desc ='SPATIAL'

#1