Using temporary table in where clause
sql, sql-server, sql-server-2005
Solution
While you can avoid the subquery in SQL Server with a join, like so:
delete from sp
from subprotocols sp
inner join protocols p on
sp.protocolid = p.protocolid
and p.workspaceid = @workspaceid
You'll find that this doesn't gain you really any performance over either of your approaches. Generally, with your subquery, SQL Server 2005 optimizes that `in` into an `inner join`, since it doesn't rely on each row. Also, SQL Server will probably cache the subquery in your case, so shoving it into a temp table is most likely unnecessary.
The first way, though, would be susceptible to changes in `Protocols` during the transactions, where the second one wouldn't. Just something to think about.
Problem
I want to delete many rows with the same set of field values in some (6) tables. I could do this by deleting the result of one subquery in every table (Solution 1), which would be redundant, because the subquery would be the same every time; so I want to store the result of the subquery in a temporary table and delete the value of each row (of the temp table) in the tables (Solution 2). Which solution is the better one? First solution: ``` 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 ``` Second Solution: ``` 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 ``` Is it possible to do solution 2 without the subquery? Say doing `WHERE ProtocolID IN @Protocols` (but syntactically correct)? I am using Microsoft SQL Server 2005.