1

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.

  • Are the "series of SELECT queries" run from the same session context or a different session? – George.Palacios Sep 16 at 8:29
  • what is the volume of data that you are loading through BCP? – Learning_DBAdmin Sep 16 at 8:42
  • @George.Palacios it's from the same session – Anonymous Sep 16 at 9:27
  • @Learning_DBAdmin I'm loading through bulk insert and the volume of data varies, could be anything from a 25kb file to a 17GB file – Anonymous Sep 16 at 9:28
  • 1
    What problem you are trying to solve ? Global vs temp table (session level) - differ in how they are available - global level (can be accessed by different sessions) vs the former one is available at the session level only. Depending on the version of sql server, you can use Hekaton (inmemory) tables for data loading with schema only option if you use physical staging table. – Kin Shah Sep 16 at 14:06
5

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.

  • 1
    a global temporary table is necessary here as this stored procure will use that table both in dynamic SQL and normal SQL. – Anonymous Sep 17 at 6:39
  • 1
    Added clarification for that case. – David Browne - Microsoft Sep 17 at 13:31
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.