CREATE TABLE #people
(
id INT,
name VARCHAR(32)
)

A temporary table is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions. The data in this #temp table (in fact, the table itself) is visible only to the current scope (usually a stored procedure, or a set of nested stored procedures). The table gets cleared up automatically when the current procedure goes out of scope, but you should manually clean up the data when you're done with it:

DROP TABLE #people

This will be better on resources ("release early") than if you let the system clean up *after* the current session finishes the rest of its work and goes out of scope.

A common use of #temp tables is to summarize/compact/reorganize data coming from another stored procedure. So, take this example, which pares down the results of the system procedure sp_who2 into only the SPID, Status, and HostName of *active* processes that are *not* part of the regular operation of the system:

CREATE TABLE #sp_who3
(
SPID INT,
Status VARCHAR(32) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(32) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(14) NULL,
ProgramName VARCHAR(32) NULL,
SPID2 INT
)

INSERT #sp_who3 EXEC sp_who2 'active'

SELECT SPID, Status, HostName FROM #sp_who3
WHERE spid > 15

DROP TABLE #sp_who3

One of the main benefits of using a #temp table, as opposed to a permanent table, is the reduction in the amount of locking required (since the current user is the only user accessing the table), and also there is much less logging involved. (You could also increase this performance by placing tempdb on a separate drive.)

One minor problem with #temp tables is that, because of the session-specific identifier that is tacked onto the name, the name you give it is limited to 116 characters, including the # sign (while other table types are limited to 128). If you try, you will see this:

Server: Msg 193, Level 15, State 1, Line 1
The object or column name starting with '#' is too long. The maximum length is 116 characters.

Hopefully this won't be a limitation in your environment, because I can't imagine a table name that long being useful or manageable.

Another potential problem with #temp tables is that, if you enter a transaction and use a #temp table, and then cancel without ever issuing a ROLLBACK or COMMIT, you could be causing unnecessary locks in tempdb.