在添加“SET SHOWPLAN_ALL”Server MS17后，临时表引发无效的对象名称

Question

我对Server相当陌生，并被分配到优化CMS生成的一些SQL查询的任务。在我添加代码之后

SET SHOWPLAN_ALL ON
GO;

执行查询时，本地temp表# table将在“INSERT INTO”语句中抛出“无效对象名称”异常。您将看到SELECT INTO #tmpSecondLevel级别语句，然后看到INSERT INSERT #tmpSecondLevel级别语句。接下来的语句用于#tmpFirstLevel，我不确定这些语句是否与这个问题相关。

use Kentico8_2;
go
set showplan_all on;
go

-- Write revised query here.
DECLARE @ProductNodeGuid uniqueidentifier = '6F6F733D-AE4F-47DC-9BE9-52B967E9F41D'

IF OBJECT_ID('tempdb..#tmpSecondLevel') IS NOT NULL
DROP TABLE #tmpSecondLevel

IF OBJECT_ID('tempdb..#tmpFirstLevel') IS NOT NULL
DROP TABLE #tmpFirstLevel

-- Get all of the items(that go on the secondLevel) that go underneath a heading(on the firstLevel)
-- Get all of the materials that belong to this node that go on the second level
SELECT
     NodeLevel = 1
    ,NodeParentID = CASE WHEN MaterialTypeSelectByMaterial = 0 THEN M.NodeID ELSE M.NodeParentID END
    ,M.NodeID
    ,M.MaterialName
    ,MaterialImage = MV.MaterialVariantImage
    ,M.NodeAliasPath
    ,M.Published
    ,M.NodeGUID
    ,M.ClassName
    ,M.NodeOrder
INTO 
    #tmpSecondLevel
FROM 
    View_NOF_Material_Joined M
    JOIN View_NOF_Product_Joined P
        ON P.ProductMaterialNodeGuidList LIKE '%' + CONVERT(nvarchar(36), M.NodeGUID) + '%'
    JOIN View_NOF_MaterialType_Joined MT
        ON MT.NodeID = M.NodeParentID
    LEFT JOIN View_NOF_MaterialVariant_Joined MV
        ON MV.NodeParentID = M.NodeID AND MV.NodeOrder = 1 -- always the first
WHERE
    P.NodeGUID = @ProductNodeGuid AND MaterialTypeSelectByMaterial = 1

-- Get all of the material variants that belong to this node that go on the second level
INSERT INTO
    #tmpSecondLevel
SELECT
     NodeLevel = 1
    ,NodeParentID = CASE WHEN MaterialTypeSelectByMaterial = 0 THEN M.NodeID ELSE M.NodeParentID END
    ,MV.NodeID
    ,MaterialName = MV.MaterialVariantName
    ,MaterialImage = MV.MaterialVariantImage
    ,MV.NodeAliasPath
    ,MV.Published
    ,MV.NodeGUID
    ,MV.ClassName
    ,MV.NodeOrder
FROM 
    View_NOF_MaterialVariant_Joined MV
    JOIN View_NOF_Product_Joined P 
        ON P.ProductMaterialNodeGuidList LIKE '%' + CONVERT(nvarchar(36), MV.NodeGUID) + '%'
    JOIN View_NOF_Material_Joined M 
        ON M.NodeID = MV.NodeParentID
    JOIN View_NOF_MaterialType_Joined MT 
        ON MT.NodeID = M.NodeParentID
WHERE 
    P.NodeGUID = @ProductNodeGuid 
        AND MaterialTypeSelectByMaterial = 0

-- Get all of the headings( for the firstLevel) that go above a list of items(on the secondLevel)
-- Get all of the material types that are used as headings
SELECT DISTINCT
     NodeLevel = 0
    ,NodeParentID = NULL
    ,MT.NodeID
    ,MaterialName = MT.MaterialTypeName
    ,MaterialImage = ''
    ,MT.NodeAliasPath
    ,MT.Published
    ,MT.NodeGUID
    ,MT.ClassName
    ,MT.NodeOrder
INTO 
    #tmpFirstLevel
FROM 
    View_NOF_MaterialType_Joined MT
    INNER JOIN #tmpSecondLevel M 
        ON MT.NodeID = M.NodeParentID

-- Get all of the materials that are used as headings
INSERT INTO 
    #tmpFirstLevel
SELECT DISTINCT
     NodeLevel = 0
    ,NodeParentID = NULL
    ,MJ.NodeID
    ,MaterialName = MJ.MaterialName
    ,MaterialImage = ''
    ,MJ.NodeAliasPath
    ,MJ.Published
    ,MJ.NodeGUID
    ,MJ.ClassName
    ,MTJ.NodeOrder
FROM 
    View_NOF_Material_Joined MJ
    INNER JOIN View_NOF_MaterialType_Joined MTJ 
        ON MTJ.NodeID = MJ.NodeParentID
    INNER JOIN #tmpSecondLevel M 
        ON MJ.NodeID = M.NodeParentID

-- Put all of the second level items (the details) and first level items (the headings) in the same table
SELECT * FROM #tmpSecondLevel
UNION
SELECT * FROM #tmpFirstLevel
ORDER BY NodeOrder

IF OBJECT_ID('tempdb..#tmpSecondLevel') IS NOT NULL
DROP TABLE #tmpSecondLevel

IF OBJECT_ID('tempdb..#tmpFirstLevel') IS NOT NULL
DROP TABLE #tmpFirstLevel

我预测这是一个简单的问题，因为我只添加了两行代码，但帮助是非常值得赞赏的。如果您有任何关于优化这些查询的技巧，这也是非常感谢的。

提前谢谢。

Answer 1

您将得到错误，因为SET SHOWPLAN_ALL是显示估计执行计划的命令。因此，没有实际执行TSQL命令，因此从未创建#tmpSecondLevel表。所以你犯了个错误。这与在SSMS中单击“显示估计执行计划”是一样的。

您可以在这里阅读有关该命令的内容：https://learn.microsoft.com/en-us/sql/t-sql/statements/set-showplan-all-transact-sql

如果要显示实际执行，请将SHOWPLAN_ALL行替换为STATISTICS。这将在运行查询时显示实际的执行计划。