Newbie question Local Temp Tables vs Global Temp Tables

I'm trying to find the distinction between Local temp tables vs. Global Temp
Tables.
Seem that Global temp tables have greater persistence when using a stored
procedure with a returning select statement.
Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible
from other network users? i.e. can user using the same stored procedures at
the same time overwrite either of these tables?
Thanks for the answers.
Stephen K. MiyasatoLocal temp tables are private to the connection created the tamp table. Glob
al temp tables are not,
they are ... global.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
> I'm trying to find the distinction between Local temp tables vs. Global Te
mp Tables.
> Seem that Global temp tables have greater persistence when using a stored
procedure with a
> returning select statement.
> Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible f
rom other network users?
> i.e. can user using the same stored procedures at the same time overwrite
either of these tables?
> Thanks for the answers.
> Stephen K. Miyasato
>|||Yes, global temp tables have greater persistence, but I've never found an
actual need to retain a temporary table beyond the context of the procedure
that created it. Have you?
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
> I'm trying to find the distinction between Local temp tables vs. Global
> Temp Tables.
> Seem that Global temp tables have greater persistence when using a stored
> procedure with a returning select statement.
> Question: Are any of the #tempLocalTable or ##tempGlobalTable accessible
> from other network users? i.e. can user using the same stored procedures
> at the same time overwrite either of these tables?
> Thanks for the answers.
> Stephen K. Miyasato
>|||I guess when I did do a stored procedure, I found that the #tempLocalTable
was not available, so I thought using he global tables would have been a
solution. Perhaps I'm doing it wrong. I ended up using regular tables but
when the stored procedure were used on different stations simultaneously, I
would get results not related to the patient.
Thanks,
Stephen K. Miyasato
"JT" <someone@.microsoft.com> wrote in message
news:ugUTNx3TGHA.1868@.TK2MSFTNGP09.phx.gbl...
> Yes, global temp tables have greater persistence, but I've never found an
> actual need to retain a temporary table beyond the context of the
> procedure that created it. Have you?
> "Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
> news:u78Xuj2TGHA.1576@.tk2msftngp13.phx.gbl...
>|||Global and permanent tables are visible to all connections so you need to
account for multi-user environments when using these for transitory data.
It's best to stick with a local temp table or table variable in those cases.
Hope this helps.
Dan Guzman
SQL Server MVP
"Stephen K. Miyasato" <miyasat@.flex.com> wrote in message
news:OiWhVq4TGHA.1160@.TK2MSFTNGP09.phx.gbl...
>I guess when I did do a stored procedure, I found that the #tempLocalTable
>was not available, so I thought using he global tables would have been a
>solution. Perhaps I'm doing it wrong. I ended up using regular tables but
>when the stored procedure were used on different stations simultaneously, I
>would get results not related to the patient.
> Thanks,
> Stephen K. Miyasato
> "JT" <someone@.microsoft.com> wrote in message
> news:ugUTNx3TGHA.1868@.TK2MSFTNGP09.phx.gbl...
>|||>> BUT, sometimes the CTE doesn't behave as expected, and it does the same l
ookup several times. <<
That is T-SQL; the DB2 implementation is much better and seems to know
when to materalize and when expand a CTE in line.
My real gripe is that T-SQL keeps only one execution plan for a
procedure. Other products keep several plans, look at the parameter
values and pick the plan that is best for that set of values. Thus, if
sex is one parameter for a query againt a Marine personnel data base,
and I pass in "male", I get the tabel scan plan, but if I pass in
"female' I get a plan with an index. I vaguely remember that DB2 can
have 16 plans per proc, but I might be wrong.