>>

>>IF OBJECT_ID('TempDB..#Temp', N'U') IS NOT NULL DROP TABLE #Temp;
>>
>>select numero into #Temp from dbo.Country where Country.Numero < 10;

>>
>>The first check with object_id will verify if the table already exists or not and will drop it if it exists.
>
>Thanks, as I understand this is window query session related. Is that correct?
>
>When doing this, is there some kind of changes that is applied to the database itself or this is memory only?

The temporary table is created in the TempDB database under unique name for the session. So, you can create temporary table with the same name in two different sessions. This is for local #temp tables. If we're talking about global temp table ##temp then this table is shared by all sessions created after the session that created that global table.

You may want to check this interesting article by Peter Larsson

http://sqlblog.com/blogs/peter_larsson/archive/2009/10/15/performance-consideration-when-using-a-table-variable.aspx

-----------------------------
BTW, quite recently we published a new Transact-SQL eBook (the second item in the News right now). Anyone interested in many good Transact-SQL articles on different topics may want to download this PDF file.

Right now we're working on another free eBook for SQL Server DB Engine. This may be even more interesting to get into bottom of many complex problems in SQL Server Database.