Post reply

TABLE Data Type

  • August 12, 2004 at 3:19 pm

    #108685

    If using the TABLE data type in any SQL statement, where does the structure get built.....in memory or tempdb?

  • August 13, 2004 at 1:35 am

    #518808

    From the same web site:

    http://qa.sqlservercentral.com/columnists/dasanka/temptablesinsqlserver_printversion.asp

    Basically a structure in Memory.  BOL has some good info and tips on a TABLE variable, one of those is that its cleaned up automatically and completely.

  • August 13, 2004 at 6:24 am

    #518841

    Structure will created in the memory.This increases the performance of the query. 

  • August 13, 2004 at 6:56 am

    #518845

    This is a common misconception on table variables. Please see following site ...

    http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

    and in particular...

    Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?

    A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

  • August 13, 2004 at 6:59 am

    #518846

    You also need to be aware that the performance using a table variable can be far worse than using a #temp table or a temporary permanent table created in tempdb.

    table variables are best for small result sets

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply