Create temporary table resident in the RAM memory

Hello to the sql server expert,
Pls advise, if there is a way to create a temporary table, resident in the RAM memory to accelerate calculation (save I/O access)?
Thank you,
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.

You can use a table variable, and it will stay in memory so long as it doesn't grow too large.

declare @table TABLE
fld1 int

insert into @table
select 1

select * from @table

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
You can also use a standard temp table - the data will stay in memory in the same circumstances as a TABLE variable...typically if there isn't a large amount of data.

Typically, you'd go for TABLE variable if the number of records is low....but they still reside in tempdb as a normal temp table does. However, TABLE variables do have limitations that a temp table doesn't - i.e. SELECT INTO... and INSERT...EXEC... statements cant be used to populate a table variable (sql 2005 you can INSERT..EXEC..). Also, you can't explicitly create indexes on TABLE variables which may be handy to improve performance depending on your exact scenario.
Ah yes...BUT sql server creates statistics for temp tables...not table there is a touch more overhead in creating them.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ygndAuthor Commented:
Thank you,
How can I make the table variable from the sql environment known in the VBA environment where my main loop is?
Thats a new question....
ygndAuthor Commented:
I lunch a new question
Pls see if you can help.
Thank you
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 2005

From novice to tech pro — start learning today.