none
how long will a table created by stored procedure in tempdb last ??? RRS feed

  • Domanda

  • it appears that the best solution to my actual problem is to create a group of temp tables with fixed schema then query them

    want to do it with stored procedure , except the query which may com some time later

    question is then

    if i formally create a table wt or without the prefix in tempdb  will it last

    1) until the procedure returns

    2) till next close on the DB

    3) till a fixed time (let's say next date change in machine clock  e.g)

    or may i set it as needed ??

     


    claudio cannella

    mercoledì 27 marzo 2013 16:38

Risposte

  • Hello opaklaus, 

    first of all explain what you mean with "create a table with or without prefix in tempdb".. maybe you mean with the single or double sharp?

    Basically, there are 2 kind of temporary table: local and global and their behaviour depends on which kind you are gonna create.

    The first one can be created using a single sharp (#) as the prefix of the table name, it's created under the user session and it's not affected by naming concurrency issues because SQL Server append a numerical suffix after the table and treat each call with the name stored in sysobject table. They can be called from each child procedure inside the main but not from the creator father(s). They are dropped automatically when the stored procedure end its scope or when you force drop with the classic DROP TABLE #YourTableName.

    Global temporary table are different because their life last longer than the father procedure, they can be referenced by other procedures and lasts until the last statement that use it ends its scope (after the creating session ends). You can create them using a double sharp prefix before the table name.

    CREATE TABLE #myLocalTable ( .... )
    
    CREATE TABLE ##myGlobalTable ( .... )


    Regards


    • Modificato _ Luca Gaspari mercoledì 27 marzo 2013 17:01
    • Contrassegnato come risposta opaklaus mercoledì 27 marzo 2013 20:00
    mercoledì 27 marzo 2013 16:59
  • Hi,

    there are two kind of temp tables, local and global.

    Local ones (#) are available for a user that creates them during the same connection to an instance, and they're destroyed after the user disconnects.

    Global ones (##) are visible to any users and any connection after they're created and they're destroyed when all users that are referencing them disconnects.

    If you create a table without the # or ## prefix you're making a physical table into tempdb, the table will exist until the next restart of the server (time in which the tempdb is recreated) or next drop table before the server restart.

    Hope this helps,


    Alessandro Alpi SQL Server MVP

    • Contrassegnato come risposta opaklaus mercoledì 27 marzo 2013 20:00
    • Contrassegno come risposta annullato opaklaus mercoledì 27 marzo 2013 20:04
    • Contrassegnato come risposta opaklaus mercoledì 27 marzo 2013 20:05
    mercoledì 27 marzo 2013 17:00
    Moderatore

Tutte le risposte

  • Hello opaklaus, 

    first of all explain what you mean with "create a table with or without prefix in tempdb".. maybe you mean with the single or double sharp?

    Basically, there are 2 kind of temporary table: local and global and their behaviour depends on which kind you are gonna create.

    The first one can be created using a single sharp (#) as the prefix of the table name, it's created under the user session and it's not affected by naming concurrency issues because SQL Server append a numerical suffix after the table and treat each call with the name stored in sysobject table. They can be called from each child procedure inside the main but not from the creator father(s). They are dropped automatically when the stored procedure end its scope or when you force drop with the classic DROP TABLE #YourTableName.

    Global temporary table are different because their life last longer than the father procedure, they can be referenced by other procedures and lasts until the last statement that use it ends its scope (after the creating session ends). You can create them using a double sharp prefix before the table name.

    CREATE TABLE #myLocalTable ( .... )
    
    CREATE TABLE ##myGlobalTable ( .... )


    Regards


    • Modificato _ Luca Gaspari mercoledì 27 marzo 2013 17:01
    • Contrassegnato come risposta opaklaus mercoledì 27 marzo 2013 20:00
    mercoledì 27 marzo 2013 16:59
  • Hi,

    there are two kind of temp tables, local and global.

    Local ones (#) are available for a user that creates them during the same connection to an instance, and they're destroyed after the user disconnects.

    Global ones (##) are visible to any users and any connection after they're created and they're destroyed when all users that are referencing them disconnects.

    If you create a table without the # or ## prefix you're making a physical table into tempdb, the table will exist until the next restart of the server (time in which the tempdb is recreated) or next drop table before the server restart.

    Hope this helps,


    Alessandro Alpi SQL Server MVP

    • Contrassegnato come risposta opaklaus mercoledì 27 marzo 2013 20:00
    • Contrassegno come risposta annullato opaklaus mercoledì 27 marzo 2013 20:04
    • Contrassegnato come risposta opaklaus mercoledì 27 marzo 2013 20:05
    mercoledì 27 marzo 2013 17:00
    Moderatore
  • dear luca and alessandro

    both your answer  tell the same thing and from what i understand i deduce that the solution for this problem is to create the admin work temporary as global , and the user oriented temps as fixed object [they wil be dropped at user request or after a fixed time by timed procedure]

    both posts are marked as answers

    thanks

    claudio   


    claudio cannella

    giovedì 28 marzo 2013 07:51
  • Ciao Claudio, non c'è bisogno di utilizzare l'inglese: questo è un forum in lingua italiana.....
    giovedì 28 marzo 2013 15:09
    Moderatore
  • mi è venuto solo un dubbio marginale

    queste tabelle sono create in tempdb , anche se la stp   che le genera sta in un  altro DB

    per accederle da applicativo , devo aprire una sessione su tempdb  o quella sul db che contiene la stp  è sufficiente ???


    claudio cannella

    giovedì 4 aprile 2013 12:28
  • Appena fatto un test..

    creato procedura SP_TEST che genera una tabella ##Global ed esegue una select

    select * from tempdb.sys.objects where name like '##%'

    Richiamata la procedura tramite LinkedServer2SqlServer, restituisce la tabella dalla select interna alla procedura, ergo sono create sul tempdb del server ospitante la procedura (:

    un saluto

    giovedì 4 aprile 2013 13:34
  • la mia domanda era leggermente diversa , luca.

    io scrivo una procedura diciamo in c++ o c#

    per accedere alle tabelle del db "base" e alle procedure del deb "base " devo creare un oggetto CDatabase e aprire tramite quello una sessione di SQL Client 10.0  o analogo

    quindi chiamo la stp dall'applicazione e le tabelle ##  vengono create sul tempdb

    ora per accedere le tabelle dall'applicazione posso continuare ad usare la sessione sul DB base o devo aprire una sessione sul tempdb ???

     


    claudio cannella

    giovedì 4 aprile 2013 18:40
  • Quando tu esegui una query su oggetti Global (quindi identificati dal doppio cancelletto) sql server reindirizza direttamente sul tempdb.. quindi a regola non serve istanziare un'altra sessione sql client verso il tempdb. 

    Ora non ho modo di verificare, ma (vado a memoria eh) se uno esegue una select su una global il full qualified table name ( istanza.database.schema.table ) il server risponde che la prima parte verrà ignorata in quanto le table temporanee risiedono sul tempdb.

    giovedì 4 aprile 2013 22:04