nadersam (10/4/2010)
NO.Locking mechanism work on row level --> page level ---> then table level.no on database level
this is lock escalation see these links
http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx
http://www.sql-server-performance.com/tips/reducing_locks_p1.aspx
nadersam (10/4/2010)
Also i tried doing the following run an insert statement and put wait for delay '00:01' in it when temporary table is still in scope, then from management studio just select properties of tempdb, i got error time out, once the insert statement finished , i could access tempdb again.
Post the error you got.
nadersam (10/4/2010)
2. My question is more about temporary table created inside trigger, how will that affect in mutli user environment.
I really didnt get this , if you talk about blocking because of one and another then dont worry . these temp tables work/created mutually exclusive to each other.and if you are talking about usage of temp table as performance point of view then it will make impact but that depends on the trigger design and amount of DML operation happening.
nadersam (10/4/2010)
4.I am asking should i drop the temporary table in the stored procedure after it finishes using it or just leave SQL handle that?.
Sql server it self handles it. but you can also drop them explicitly.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)