Re: Why Not Allow Temp Tables?

"Ryan Gaffuri" <rgaffuri_at_cox.net> wrote in message news:1efdad5b.0309240556.55751129_at_posting.google.com... > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3f7177e2$0$15785$ed9e5944_at_reading.news.pipex.net>...
> > "(Pete Cresswell)" <x_at_y.z> wrote in message
> > news:7dvumv0n9nef438hgrfc1fea9c8ejf1sah_at_4ax.com...
> > > RE/
> > > >Personally, I think you are going to find that the person who wants
temp
> > > >tables probably is from a non-Oracle background (eg Sybase or
SQLServer)
> > > >where they are used all the time for a variety of reasons.
> > >
> > > Bingo!.... SQL Server...
> > > -----------------------
> > > PeteCresswell

> >
> > I'd expect in a lot of cases you won't even need a GTT. In MSSQL you
might
> > well wish temp tables to avoid locking issues, in Oracle you won't get
those
> > locking issues.

>
> i hear about temp tables in sql server all the time. are they
> implemented like global temp tables in oracle? im not familiar with
> the sql server architecture.

They are analogous to global temp tables - from books online

Temporary Tables
You can also create temporary tables. Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.

The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user; and they are deleted when the user disconnects from instances of Microsoft® SQL ServerT 2000. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created; and they are deleted when all users referencing the table disconnect from SQL Server.

For example, if you create a table named employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If you create a local temporary table named #employees, you are the only person who can work with the table, and it is deleted when you disconnect. If you create a global temporary table named ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it when both of you disconnect.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK