sql - 在 where 子句中使用临时表

Question

我想删除某些 (6) 个表中具有相同字段值集的许多行。我可以通过删除每个表中一个子查询的结果来做到这一点(解决方案 1)，这将是多余的，因为子查询每次都是相同的；所以我想将子查询的结果存储在临时表中，并删除表中(临时表的)每一行的值(解决方案2)。哪种解决方案更好？

第一个解决方案:

DELETE FROM dbo.SubProtocols
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM dbo.Protocols
    WHERE WorkplaceID = @WorkplaceID
)

DELETE FROM dbo.ProtocolHeaders
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM dbo.Protocols
    WHERE WorkplaceID = @WorkplaceID
)

// ...

DELETE FROM dbo.Protocols
WHERE WorkplaceID = @WorkplaceID

第二个解决方案:

DECLARE @Protocols table(ProtocolID int NOT NULL)

INSERT INTO @Protocols
SELECT ProtocolID
FROM dbo.Protocols
WHERE WorkplaceID = @WorkplaceID

DELETE FROM dbo.SubProtocols
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM @Protocols
)

DELETE FROM dbo.ProtocolHeaders
WHERE ProtocolID IN (
    SELECT ProtocolID
    FROM @Protocols
)

// ...

DELETE FROM dbo.Protocols
WHERE WorkplaceID = @WorkplaceID

是否可以在没有子查询的情况下执行解决方案 2？假设执行 WHERE ProtocolID IN @Protocols (但语法正确)？

我使用的是 Microsoft SQL Server 2005。

Answer 1

虽然您可以通过连接避免 SQL Server 中的子查询，如下所示:

delete from sp
from subprotocols sp
inner join protocols p on
    sp.protocolid = p.protocolid
    and p.workspaceid = @workspaceid

您会发现，与您的任何一种方法相比，这并没有真正为您带来任何性能。通常，对于子查询，SQL Server 2005 会将in 优化为内连接，因为它不依赖于每一行。此外，SQL Server 可能会缓存您的情况下的子查询，因此将其插入临时表很可能是不必要的。

但是，第一种方式很容易受到交易期间协议(protocol)变化的影响，而第二种方式则不会。只是需要思考的事情。