asked on

CTE, table variable, or temp table pros and cons

I've written a lot of SQL over the years, but I recently ran into a situation where I wanted to select records from a SQL table based on values in a table which is local to Access. I can simply create the query in Access joining the linked table to the Access table, but I'm wondering if there isn't a more efficient method. This stemmed from an EE question in which the user indicated that they have read-only permissions on the SQL Server

This is for a report, so the recordset returned from SQL Server does not have to be updateable. With the help of a couple of Access MVPs, I found several syntax which worked in a pass-through query:

SET NOCOUNT ON 

CREATE TABLE #TempData (PO_Number nvarchar(10))
Insert into #TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * 
FROM yourTable INNER JOIN #TempData ON yourTable.FieldName = #TempDate.PO_Number

Open in new window

However, I doubt this syntax would work in a read-only connection to the server.

SET NOCOUNT ON 

declare @TempData as table (PO_Number nvarchar(10))
insert into @TempData values ('abc123'), ('xyz456'), ('mno789')

SELECT * 
FROM yourTable INNER JOIN @TempData ON yourTable.FieldName = @TempDate.PO_Number

Open in new window

and one of the MVPs' recommended a syntax of:

;WITH
cteC AS
(SELECT 'abc123' PO_Num
 UNION ALL SELECT 'xyz456'
 UNION ALL SELECT 'mno789'
)
SELECT SomeTable.*
FROM SomeTable INNER JOIN cteC ON SomeTable.PO_Number = cteC.PO_Num

Open in new window

So, what are the advantages/disadvantages of each of these?

Do any of these techniques allow you to create an index on the new table (temp, cte, or variable)?

If the tempdb method will not work with a read-only connection, can you modify the syntax of that example so that it would run with a read-only connection? Maybe with a Run As command?

I don't know what version SQL Server the OP is using, so can someone tell me what versions of SQL Server that the CTE and table variables became available.

Thanks for your assistance.

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

ste5an,

Thanks for the response. The whole point of using this techniques is to avoid the link to the Access table on the Access FE. Thanks for the input on simply adding the PK to the tempdb or table variable method.

The OP will build the pass-through query in Access with one of these options and will create the "filter table" to perform the filter operation on the server.

So, are there any advantages or disadvantages to the tempdb, CTE or table variable technique?

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

Thanks for your help guys.