SQL Server Performance Forum – Threads Archive

Search results with temp tables

Hi,
I have certain queries regarding SQL Server Temp Tables.
I believe teh fundas here will definitely get the quests replied.
My questions: 1. Is there any expiration period for temp table created. Can i write two procedures scuh that one procedure for creating (only if doesn’t exist) and another for retrieving the details. The point is that both the procedures will be called from entirely different areas. 2. Will be there a confict between users (Web application) while creating and inserting data itno the temp table if there are many concurrent users. 3. Is there any facility for maintaining user identification.. Such ans session variables in web applications. 4. Also will be helpfull if someone could guide me on how to use the temp table efficiently for searching and showing results in a web application. Thanks in Advance
Shibu Narayanan
Software Associates

Hi, <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 1. Is there any expiration period for temp table created. Can i write two procedures scuh that one procedure for creating (only if doesn’t exist) and another for retrieving the details. The point is that both the procedures will be called from entirely different areas.<hr height="1" noshade id="quote"></blockquote id="quote"> Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE: A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table. All other local temporary tables are dropped automatically at the end of the current session. Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 2. Will be there a confict between users (Web application) while creating and inserting data itno the temp table if there are many concurrent users.<hr height="1" noshade id="quote"></blockquote id="quote"> If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters. <blockquote id="quote">quote:<hr height="1" noshade id="quote"> 3. Is there any facility for maintaining user identification.. Such ans session variables in web applications.<hr height="1" noshade id="quote"></blockquote id="quote"> – Its possible , you can use cookies and session ., refer http://www.google.co.in/search?hl=en&q=session+variables+in+web+applications&btnG=Google+Search&meta=</a> 4. Also will be helpfull if someone could guide me on how to use the temp table efficiently for searching and showing results in a web application. – Read *create table* BOL for more And also refer following link: http://www.sql-server-performance.com/asp_sql_server.asp</a> <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

As for your first question, check this out:http://www.sommarskog.se/share_data.html —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

]]>