SQL Server - global temporary table vs normal table

Question

I have a stored procedure which I use to load csv files to SQL Server. This will initially bulk insert the file into a global temporary table then at the end it will do:

select cast(column1 as int) [column1], column2, column3... into table from ##temptable

and then proceeds to drop the ##temptable

Before the query select into table from ##temptable I run a series of select queries on the temporary table to determine what type the columns should be (and I'm confident this works properly).

Note that having a table (either normal or temporary) before creating the final output table is essential for the stored procedure.

What I'd like to know is if I'm better off using a global temporary table or if I should use a normal table or does it make no difference?

I've seen several articles about the differences between temporary tables and table variables but I've seen nothing when it comes to comparing global temporary tables to normal tables and it's difficult for me to test this properly in my environment.

Answer 1

A normal table will cause logging in your database, consume space, and require log flush on every commit. So for temporary data, you should use a temporary table.

But you would normally use a regular (#) temporary table here, not a global (##) temporary table.

Global temporary tables are useful in the (very rare) scenario where multiple, separate sessions need access to the same temporary table. Another scenario where a global temporary table can be useful is when the temp table is created in dynamic SQL, but referenced in the calling batch, eg:

exec ('select * into ##foo from sys.objects')
select * from ##foo

But a global temporary table is not necessary to create a temp table in a stored procedure and access it in dynamic SQL, eg:

create table #foo(id int, data varchar(200))
exec ('insert into #foo(id,data) values (1,''hello'')')
select * from #foo

And when you create a regular temporary table in a stored procedure it will automatically be destroyed when the stored procedure ends, and you can run multiple instances of the same procedure without interfering with each other.

Answer 2

Is there any benefit to potentially having the data remain available? I've seen cases where, for example, it was useful to preserve the data in the staging table when the insert statement threw an unexpected error or when a particular debug flag was set because it made it much easier to troubleshoot issues and/or restart the process without reloading the file and those benefits were worth the cost of slowing down the load with additional logging. If you're doing a nightly warehouse load and loading/ processing/ validating the data in the staging table takes, say, an hour, it may be worth using a permanent table to stage the data. You might be able to save 10-15 minutes on a normal day using a temporary table that doesn't need to generate logging but it's worth it if it saves you hours debugging and re-running time when there is a problem.

If you aren't going to preserve the data in the staging table, then I'd totally concur with David that you should be using a temporary table (and probably a regular temporary table).