Create temporary table resident in the RAM memory

Posted on 2008-11-14
Last Modified: 2012-05-05
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,
Question by:ygnd
    LVL 60

    Accepted Solution

    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
    LVL 23

    Assisted Solution

    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.
    LVL 60

    Expert Comment

    Ah yes...BUT sql server creates statistics for temp tables...not table there is a touch more overhead in creating them.

    Author Comment

    Thank you,
    How can I make the table variable from the sql environment known in the VBA environment where my main loop is?
    LVL 60

    Expert Comment

    Thats a new question....

    Author Closing Comment

    I lunch a new question
    Pls see if you can help.
    Thank you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now