Trying to break a bad habit
So, I have this habit where I copy tables into tempdb...basically, b/c it's easy to do SELECT * INTO tempdb..mytable. For some reason, my boss would prefer me to DECLARE TABLE variables in my scripts instead (no one uses these except for me). I'm reluctant b/c I don't want to spell out table definition every time. Some of these tables have a lot of fields. Anyway I can use DECLARE TABLE and not have to spell out the exact table definition?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Anthony Perkins
But if you are concerned about performance you would be wise to explore the advantages of variables of type table and or explicitly declaring temporary tables.
You could be right, and he could be as well.
Here is a site that helps when deciding one over the other:
http://odetocode.com/code/365.aspx
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
Another option, is to execute a global temp table...an idea that keeps a table in the schema, but truncates when connection is severed.
Last option is a physical table that you can use as a variable. Table Definition permamently stays, but so does the instance.
Hope this helps.
Here is a site that helps when deciding one over the other:
http://odetocode.com/code/365.aspx
http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html
Another option, is to execute a global temp table...an idea that keeps a table in the schema, but truncates when connection is severed.
Last option is a physical table that you can use as a variable. Table Definition permamently stays, but so does the instance.
Hope this helps.
>>You could be right, and he could be as well. <<
But he is not using a temporary table, the author is using a table in the tempdb database. Clearly not the same animal.
But he is not using a temporary table, the author is using a table in the tempdb database. Clearly not the same animal.
It depends on the scenario. The author does not want to re-instantiate table dictionaries with every run. That changes the game. This is definately a philosophical discussion
>>This is definately a philosophical discussion <<
Actually not really I was pointing out that what the author is using is not a temporary table it is a table in the tempdb database. Nothing philosophical about that. By definition, SQL Server considers temporary tables all those tables named with a #(local) or ## (global).
Actually not really I was pointing out that what the author is using is not a temporary table it is a table in the tempdb database. Nothing philosophical about that. By definition, SQL Server considers temporary tables all those tables named with a #(local) or ## (global).