| Author |
Topic |
|
csecharith
Starting Member
21 Posts |
 Posted - 2008-02-12 : 00:50:27
|
| Hi,I am using SQL Server2005 global temporary table to handle certain requirements. But I am facing some issues when using them.....My doubt is; why should we use temporary tables instead of permanent tables...Here is my scenario, I want to create a view by retrieving data from various tables. But using a view is not a proper solution to my scenario as I want to send certain inputs. So I went to temporary table solution and now I have another set of issues. I am thinking to use permanent table instead of temporary table, if so I'll have to drop and create that permanent table rarely.Can you see any issue with my new solution?Thank you! |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-12 : 23:13:59
|
| Didn't get it. What's your problem with temp table? |
 |
|
|
csecharith
Starting Member
21 Posts |
Posted - 2008-02-13 : 01:15:26
|
quote:
Originally posted by rmiao
Didn't get it. What's your problem with temp table?
I am creating a global temporary table and I want to run three queries on that table. Sometimes it says that the table is not exists. So I am doing all these things (creating the temp table and running queries) in an explicit transaction. Then it works fine, but as I am retrieving a lot of data, the database becomes very slow during that process(within the transaction). So I am thinking to use permanent table instead of temporary tables. I'll drop the table after doing my tasks. Looking forward to hearing your comments....Thank you very much. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-02-13 : 01:21:53
|
Maybe you can enlighten us how are you using the global temp table ? What are you trying to achieve here ?Is the information in the global temp table going to be used by other processes ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-13 : 02:00:02
|
Global temp tables (## prefix) are automatically dropped when last referencing connection ends.Much like local temp table (# prefix) are dropped when current connection ends.Are yuo by any chance using dynamic sql in your code described above?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
csecharith
Starting Member
21 Posts |
Posted - 2008-02-13 : 02:15:19
|
quote:
Originally posted by khtan
Maybe you can enlighten us how are you using the global temp table ? What are you trying to achieve here ?Is the information in the global temp table going to be used by other processes ?
KH[spoiler]Time is always against us[/spoiler]
Ya, I first I create a global temp table and run certain queries with new connections. then it loses the table, so I make all these things as a transaction. But then the database becomes inefficient. |
|
|
|
csecharith
Starting Member
21 Posts |
Posted - 2008-02-13 : 02:16:15
|
quote:
Originally posted by Peso
Global temp tables (## prefix) are automatically dropped when last referencing connection ends.Much like local temp table (# prefix) are dropped when current connection ends.Are yuo by any chance using dynamic sql in your code described above?
E 12°55'05.25"N 56°04'39.16"
Ya, there is a dynamic sql in my code. |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2008-02-13 : 06:59:36
|
quote:
Ya, I first I create a global temp table and run certain queries with new connections. then it loses the table, so I make all these things as a transaction. But then the database becomes inefficient.
that does not sound like you need a global temp table at all. A local temp table is sufficient for you need.
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-02-13 : 09:54:00
|
| maybe you should explain your business process in better detail. a better solution can be then found if it exists._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-02-13 : 12:22:56
|
I'm not sure the view, or just a query in the proc, is not viable either. I can't see why you require these results in a table at all.Heh - I've just seen that I'm about to reiterate spirit1. What the heck - I will:What are your business requirements?  |
|
|
|
|
Why tempory tables......