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:

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

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.

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

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

Open in new window

and one of the MVPs' recommended a syntax of:
cteC AS
(SELECT 'abc123' PO_Num
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.
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
As far as I know, there is no read only connection. You have either only SELECT permissions or the database is set to read only. The tempdb cannot be set to read-only.

When your given samples show the correct magnitude of cardinatlity, then performance is not a problem and you don't need any index on your "filter table". So I assume the performance should be almost equal.

When your "filter table" is large, then indices can help:

CREATE TABLE #TempData (PO_Number nvarchar(10) primary key)

Open in new window

But the biggest performance problems will arise from your later JOIN in Access. When your passthrough returns many rows, than you will see "performance" problems on the Access side due to network latency cause ACE needs to retrieve the entire dataset first.

or on SQL Server 2012+

declare @TempData as table (PO_Number nvarchar(10) primary key)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCAuthor Commented:

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?
ste5anSenior DeveloperCommented:
As I alread said: there is a difference only when the filter table in the passthrough query itself is large, then a index helps. In this case using a indexed temp table or index table variable is better then the CTE approach. For only small sets all three should perform the same. And the sample with only three rows should perform also the same for all three approaches.

The only caveats from my experience: In rare cases the indexed table variable is slower than an unindexed one. And for very large filter tables use the temp table, cause the table variable is also materialized in such cases to tempdb, but the process doing that is somewhat slower than using an explict temp table.
Mark WillsTopic AdvisorCommented:
If you can connect to the server, then you can create a temporary table.

It is possible to revoke the connect permission to tempdb but doubt that would be in place unless they have an anally retentive security mastermind playing with fire. Not entirely unheard of, but extremely doubtful.

Temp tables would be best - but will be automatically dropped at end of procedure / session etc.  So beware, it will be dropped when they go out of scope. Global temp tables are visible to all current sessions.

CTE is a Common Table Expression, so you materialise the data in a select statement. Table variables are not much different in that regard, and both only exist for the scope of the batch.

For small data counts, it probably wont matter.

For large data and/or multi steps, multi statements then you need a temp table and would be my recommendation.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
Thanks for your help guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.