Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212

    Unanswered: Pro & Cons of using a @variable as table type

    I just notice someone using a @ variable to be declared as a temp table
    in use for an example to be shown right here in DBforum.


    e.g.

    Code:
    declare @temtable table (column1 varchar)

    I didn't known it before this. I'm just wondering. Is it a wise practive for doing temp tables this way? Does in give better or poorer performance?

    How much can it load into this "virtual temptable" ? What measure of capacity is it attach to? RAM memory?

    Please give u'r comments if you know a tip or two that is useful for a person like me.

    Thanks.
    Last edited by Patrick Chua; 10-13-03 at 10:41.
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Table variables are preferred over temporary tables because they can be stored in cache (so they are faster) and they automatically go out of scope when your script completes.

    I use table variable almost exclusively.

    Their big drawback is that they are scope-specific, so for dynamic SQL it is often necessary to use temporary tables instead.

    Also, you cannot create a table variable that is available to all users, as you can with temporary tables.

    blindman

  3. #3
    Join Date
    Jun 2003
    Location
    cape town, south africa
    Posts
    102

    Re: Pro & Cons of using a @variable as table type

    some limitations:

    you cannot use them for certain updating methods (if you need to perform complex updates on their data) and they cannot be
    used as a repository for the results of a procedure exec...
    ie. you cannot say :
    insert into @table
    exec procname

    for everything else though, definitly recommended

    Des

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by blindman
    Table variables are preferred over temporary tables because they can be stored in cache (so they are faster) and they automatically go out of scope when your script completes.

    I use table variable almost exclusively.

    Their big drawback is that they are scope-specific, so for dynamic SQL it is often necessary to use temporary tables instead.

    Also, you cannot create a table variable that is available to all users, as you can with temporary tables.

    blindman
    And the answer is....

    (I like this one)...It Depends...

    Table variables are stored in memory...not tempdb


    Wouldn't think 1 million rows in to a table variable is a good idea...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •