I have understood that in SQL Server it is possible to create temp tables. Why would I do so and not just create a database named something like "temptabledatabase" and create temp tables to there just to be dropped seconds later? If I go with those actual temp tables (not create and drop -system), what differences do I need to make to my code of "select into - select * - drop table"?
If you create a table it is visible to all spids. Say you have an SP which can be called by many users - that couldn't create a permanent table as the next instance would get an error.
Also if a process creates a table then fails the table would be left there - the next run would get an error as it couldn't create the table.
With temp tables you do not need to code drop statements (but you can). They will be created in tempdb - there can be issues if system databases have a different collation to the user databases so try to make sure that doesn't happen.
Also look at table variables for small amounts of data.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
========================================== Cursors are useful if you don't know sql. SSIS can be used in a similar way. Beer is not cold and it isn't fizzy.
Need an opinion of temp tables
Posted - 12/05/2012 : 02:36:56
