August 10, 2011 at 11:29 am
thompson 44810 (8/10/2011)
...Our quandary is can a temp variable be created and can that be used to pull data from?
We are trying to do this without creating a separate table.
...
AND
thompson 44810 (8/10/2011)
...Therefore if we use the temp variable that is create within the statement and are able to pull from the same statement we would use less overhead.
...
1. You need to read about what is temp table and table variables. You may find that there is no much difference in the way how they are created by SQL Server...
Just in case example how table variable can be created (you already have one for how you can get data out of it) and Yes both statements "create" and "select from" can be placed into one "process" (I wonder what you call a "process")
DECLARE @TableVariable TABLE (col1 int)
INSERT @TableVariable SELECT 1
SELECT * FROM @TableVariable
2. Here is interesting, now you are talking about creating and extracting data from table variable in the "SAME STATEMENT"
The answer is: NO YOU CANNOT DO IT WITH TABLE VARIABLE,
bad news is: YOU CANNOT DO IT WITH ANY TABLE.
Sorry...
May be what you are trying to ask is: What is faster?
I use table variables for reletively small datasets and I don't need perform any filtering on it (eg. using joins to filter it) later in the process. In all other cases I do use temp tables.
Unlike table variable, you can create and populate temp table without previous declaration from result of select query in one statement (using SELECT ... INTO ...) and it is the fastest way to populate table in SQLServer. There are also many other benefits in using temp tables over table variables (tempdb should be properly configured to feel them:-D)...
I guess, no one can understand clearly what you are trying to ask...
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply