Post reply

Global tables..

  • July 18, 2011 at 8:38 am

    #98216

    Hi guys

    I've been testing the differences between Global temp and Global variable tables. If you were to access the tables during runtime from a separate SPID, how would you see them, the global temp works without issue. But the global table variable has me puzzled. What purpose does it serve if it can’t be accessed during runtime by another SPID?? Why have a Global variable table object at all??

    A simple test: I create a Global table of each kind. While the loop is running, I attempt to query the respective table with a simple SELECT * FROM..

    /*********Global Table Variable Test***************/

    DECLARE @@Table Table (ID int, TestField Varchar(10))

    DECLARE

    @ID int

    SELECT

    @ID = 0

    WHILE

    @ID <= 10000000

    BEGIN

    INSERT INTO

    @@Table (TestField)

    VALUES('TVariableRun')

    END

    SELECT

    *

    FROM

    @@Table

    /******************Global Temp Table Test***********************/

    CREATE TABLE ##Table (ID int, TestField Varchar(10))

    DECLARE

    @ID int

    SELECT

    @ID = 0

    WHILE

    @ID <= 10000000

    BEGIN

    INSERT INTO

    ##Table (TestField)

    VALUES('TTableRun')

    END

    SELECT

    *

    FROM

    ##Table

  • July 18, 2011 at 8:46 am

    #1354270

    Global table variable don't exist.

    The clostest thing is table valued function which can be used as parameter in a sp strating in 2008 iirc.

  • July 18, 2011 at 8:47 am

    #1354273

    what's confusing you is there's no such thing as a global variable table. i know it seems a little intuitive that if two # signs make it global , it might do the same with @ signs

    unlike ##temp tables,

    A table variable is always scoped to just the connection using it. two @@ designators in a row are just taking advantage of the constructname beling allowed to have @ in it.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 18, 2011 at 9:00 am

    #1354283

    Even if it were possible, the differences between them would be essentially the same as between the others.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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