|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Different between ##table and #table regarding performancehi
also i want to know what have more performance : a table variable or a temporary table in a stored procedure the rows in the tables are approx. 1000 thanks michel INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
http://support.microsoft.com/?kbid=305977 AMB Show quote "haenselmic" wrote: > hi > > also i want to know what have more performance : a table variable or a > temporary table in a stored procedure > the rows in the tables are approx. 1000 > > thanks michel
Show quote
"Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in message Also I have never found a valid use for an ##temp global temporary table.news:0E5B6748-F110-497E-ABE3-C9B90F6280FA@microsoft.com... > INF: Frequently Asked Questions - SQL Server 2000 - Table Variables > http://support.microsoft.com/?kbid=305977 > > > AMB > > "haenselmic" wrote: > >> hi >> >> also i want to know what have more performance : a table variable or a >> temporary table in a stored procedure >> the rows in the tables are approx. 1000 >> David I have used them a couple of times. It has been a while. From what I can
remember we had to dynamically build several sql statements, insert them into one (##temporary) table and then perform some data manipulation and retrieval from the ##table. -- Show quoteKeith Kratochvil "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:egT2TU1eGHA.5088@TK2MSFTNGP02.phx.gbl... > > "Alejandro Mesa" <AlejandroM***@discussions.microsoft.com> wrote in > message news:0E5B6748-F110-497E-ABE3-C9B90F6280FA@microsoft.com... >> INF: Frequently Asked Questions - SQL Server 2000 - Table Variables >> http://support.microsoft.com/?kbid=305977 >> >> >> AMB >> >> "haenselmic" wrote: >> >>> hi >>> >>> also i want to know what have more performance : a table variable or a >>> temporary table in a stored procedure >>> the rows in the tables are approx. 1000 >>> > > > Also I have never found a valid use for an ##temp global temporary table. > > David > "Keith Kratochvil" <sqlguy.bac***@comcast.net> wrote in message But if you didn't retrieve them in another session, a #table would have news:ey10gf1eGHA.2416@TK2MSFTNGP03.phx.gbl... >I have used them a couple of times. It has been a while. From what I can >remember we had to dynamically build several sql statements, insert them >into one (##temporary) table and then perform some data manipulation and >retrieval from the ##table. > > sufficed. David As I mentioned, it has been a while. I don't remember all the gory details,
but the global temp table seemed like the best solution (or perhaps the only solution) at the time. -- Show quoteKeith Kratochvil "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:uGhk1E2eGHA.2188@TK2MSFTNGP05.phx.gbl... > > "Keith Kratochvil" <sqlguy.bac***@comcast.net> wrote in message > news:ey10gf1eGHA.2416@TK2MSFTNGP03.phx.gbl... >>I have used them a couple of times. It has been a while. From what I can >>remember we had to dynamically build several sql statements, insert them >>into one (##temporary) table and then perform some data manipulation and >>retrieval from the ##table. >> >> > > But if you didn't retrieve them in another session, a #table would have > sufficed. > > David > Has anyone ever used global temporary tables (##temp)? They seem awfully
useless. Show quote "David Browne" <davidbaxterbrowne no potted m***@hotmail.com> wrote in message news:uGhk1E2eGHA.2188@TK2MSFTNGP05.phx.gbl... > > "Keith Kratochvil" <sqlguy.bac***@comcast.net> wrote in message > news:ey10gf1eGHA.2416@TK2MSFTNGP03.phx.gbl... >>I have used them a couple of times. It has been a while. From what I can >>remember we had to dynamically build several sql statements, insert them >>into one (##temporary) table and then perform some data manipulation and >>retrieval from the ##table. >> >> > > But if you didn't retrieve them in another session, a #table would have > sufficed. > > David > I've had to use them occassionally (mainly through lazyness, as it
wasn't necessary to design anything better) when I am using dynamic sql. Sometimes you need to return results back out of your dynamic sql, in which case the only way I've found to do it is either create a normal temporary table beforehand, then populate it in the dynamic sql (which isn't always an option), or just create a global one from within the dynamic sql, then look for that in the static stuff. A time when I think they would be useful is when you need an SQL equivalent of the .NET application cache. I haven't ever tried using them as such, but I can think of situations where it probably would help performance if you were to cache certain results into global temporary tables, then next time any procedure needs it they can check if the object exists, then just retrieve that. I've used temp tables to retrieve data from dynamic SQL as well, but never a
global temp table. For the second part, just creating a permanent table would seem like a better idea since you could eliminate the check for existence before inserting/deleting/updating or selecting to/from it every time. I don't know of any documentation that temp tables offer any performance improvements over regular tables... I guess they're useful to someone out there or they wouldn't keep them around :) Thanks Show quote "Will" <william_p***@yahoo.co.uk> wrote in message news:1148151980.104435.22640@j33g2000cwa.googlegroups.com... > I've had to use them occassionally (mainly through lazyness, as it > wasn't necessary to design anything better) when I am using dynamic > sql. Sometimes you need to return results back out of your dynamic sql, > in which case the only way I've found to do it is either create a > normal temporary table beforehand, then populate it in the dynamic sql > (which isn't always an option), or just create a global one from within > the dynamic sql, then look for that in the static stuff. > > A time when I think they would be useful is when you need an SQL > equivalent of the .NET application cache. I haven't ever tried using > them as such, but I can think of situations where it probably would > help performance if you were to cache certain results into global > temporary tables, then next time any procedure needs it they can check > if the object exists, then just retrieve that. > for using them in dynamic sql - if you have to create a result set in
your dynamic part with an unknown number of columns, how would you then interrogate this in static sql? "Will" <william_p***@yahoo.co.uk> wrote in message Create a result set with an unknown number of columns? The only time I've news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... > for using them in dynamic sql - if you have to create a result set in > your dynamic part with an unknown number of columns, how would you then > interrogate this in static sql? > ever seen that done is when creating a Pivot table using Dynamic SQL. And then the SELECT statement is dynamically built with correct columns named in it... Maybe you have an example of how a global temp table would be preferred to a regular temp table in this particular situation? Cause I just ain't seein' it. Mike C# wrote:
> "Will" <william_p***@yahoo.co.uk> wrote in message How about this one then:> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... > > for using them in dynamic sql - if you have to create a result set in > > your dynamic part with an unknown number of columns, how would you then > > interrogate this in static sql? > > > > Create a result set with an unknown number of columns? The only time I've > ever seen that done is when creating a Pivot table using Dynamic SQL. And > then the SELECT statement is dynamically built with correct columns named in > it... Maybe you have an example of how a global temp table would be > preferred to a regular temp table in this particular situation? Cause I > just ain't seein' it. http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e I was never actually able to come up with a decent solution to Helmut's problem, but my final attempt came quite close (I was hoping one of the smart people on the forum might ride in and save the day, but instead I think he got tired and went off and solved it himself). But anyway, somewhere in the mass of SQL at the end is a global temporary table, and I couldn't think of a way to turn it into just a normal one (which I actually needed to do). Cheers Will
Show quote
"Will" <william_p***@yahoo.co.uk> wrote in message All the work is done in a single session, so there's no need to use a ## news:1149266034.041506.274000@j55g2000cwa.googlegroups.com... > > Mike C# wrote: >> "Will" <william_p***@yahoo.co.uk> wrote in message >> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... >> > for using them in dynamic sql - if you have to create a result set in >> > your dynamic part with an unknown number of columns, how would you then >> > interrogate this in static sql? >> > >> >> Create a result set with an unknown number of columns? The only time >> I've >> ever seen that done is when creating a Pivot table using Dynamic SQL. >> And >> then the SELECT statement is dynamically built with correct columns named >> in >> it... Maybe you have an example of how a global temp table would be >> preferred to a regular temp table in this particular situation? Cause I >> just ain't seein' it. > > How about this one then: > > http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e > > I was never actually able to come up with a decent solution to Helmut's > problem, but my final attempt came quite close (I was hoping one of the > smart people on the forum might ride in and save the day, but instead I > think he got tired and went off and solved it himself). But anyway, > somewhere in the mass of SQL at the end is a global temporary table, > and I couldn't think of a way to turn it into just a normal one (which > I actually needed to do). > global temp table. David Now that's just not true,
Change the code to have only a local temporary table and watch it fail. David Browne wrote: Show quote > "Will" <william_p***@yahoo.co.uk> wrote in message > news:1149266034.041506.274000@j55g2000cwa.googlegroups.com... > > > > Mike C# wrote: > >> "Will" <william_p***@yahoo.co.uk> wrote in message > >> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... > >> > for using them in dynamic sql - if you have to create a result set in > >> > your dynamic part with an unknown number of columns, how would you then > >> > interrogate this in static sql? > >> > > >> > >> Create a result set with an unknown number of columns? The only time > >> I've > >> ever seen that done is when creating a Pivot table using Dynamic SQL. > >> And > >> then the SELECT statement is dynamically built with correct columns named > >> in > >> it... Maybe you have an example of how a global temp table would be > >> preferred to a regular temp table in this particular situation? Cause I > >> just ain't seein' it. > > > > How about this one then: > > > > http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e > > > > I was never actually able to come up with a decent solution to Helmut's > > problem, but my final attempt came quite close (I was hoping one of the > > smart people on the forum might ride in and save the day, but instead I > > think he got tired and went off and solved it himself). But anyway, > > somewhere in the mass of SQL at the end is a global temporary table, > > and I couldn't think of a way to turn it into just a normal one (which > > I actually needed to do). > > > > All the work is done in a single session, so there's no need to use a ## > global temp table. > > David There's no need for a global temp table to solve that problem. In fact, by
using a global temp table you seem to be limiting yourself to running the comparison against a single pair of tables at a time. If a local temp table were created outside of the dynamic SQL, it's scope would include the dynamic SQL. To wit, sp_executesql accesses the #Temp123 table here just as one would expect: CREATE TABLE #Temp123 (a INT) DECLARE @sql NVARCHAR(255) SELECT @sql = N'INSERT INTO #Temp123 (a) ' + 'SELECT 1 ' + ' UNION SELECT 2 ' + ' UNION SELECT 3 ' + ' UNION SELECT 4 ' EXEC dbo.sp_executesql @sql, N'' SELECT * FROM #Temp123 SELECT @sql = N'SELECT * ' + ' FROM #Temp123 ' EXEC dbo.sp_executesql @sql, N'' DROP TABLE #Temp123 Show quote "Will" <william_p***@yahoo.co.uk> wrote in message news:1149266034.041506.274000@j55g2000cwa.googlegroups.com... > > Mike C# wrote: >> "Will" <william_p***@yahoo.co.uk> wrote in message >> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... >> > for using them in dynamic sql - if you have to create a result set in >> > your dynamic part with an unknown number of columns, how would you then >> > interrogate this in static sql? >> > >> >> Create a result set with an unknown number of columns? The only time >> I've >> ever seen that done is when creating a Pivot table using Dynamic SQL. >> And >> then the SELECT statement is dynamically built with correct columns named >> in >> it... Maybe you have an example of how a global temp table would be >> preferred to a regular temp table in this particular situation? Cause I >> just ain't seein' it. > > How about this one then: > > http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e > > I was never actually able to come up with a decent solution to Helmut's > problem, but my final attempt came quite close (I was hoping one of the > smart people on the forum might ride in and save the day, but instead I > think he got tired and went off and solved it himself). But anyway, > somewhere in the mass of SQL at the end is a global temporary table, > and I couldn't think of a way to turn it into just a normal one (which > I actually needed to do). > > Cheers > Will > ok guys,
it has been mentioned several times, so believe me - I know that you can create tables outside of the dynamic sql, then access them within it. My point is that there are times when you need to create the table within dynamic sql because you don't at "authoring time" know what the schema is/will be. I could possibly build up a load of alter table statements with the dynamic sql instead however this would probably be quite innefficient and much harder to debug. I also know that it's preventing concurrent execution of the script, I would probably get around that by dynamically generating the name of the table (I would be able to generate and access that outside of the dynamic part). If you truly believe that you could adapt that code to not use a global temporary table - please do, then post it to the end of that thread. I'd be keen to see a better solution, but the problem isn't as trivial as it may at first appear. Until then I feel the example stands as a place where global temporary tables were needed (though probably not what they were intended for :)). Cheers Will Mike C# wrote: Show quote > There's no need for a global temp table to solve that problem. In fact, by > using a global temp table you seem to be limiting yourself to running the > comparison against a single pair of tables at a time. > > If a local temp table were created outside of the dynamic SQL, it's scope > would include the dynamic SQL. To wit, sp_executesql accesses the #Temp123 > table here just as one would expect: > > CREATE TABLE #Temp123 (a INT) > > DECLARE @sql NVARCHAR(255) > SELECT @sql = N'INSERT INTO #Temp123 (a) ' + > 'SELECT 1 ' + > ' UNION SELECT 2 ' + > ' UNION SELECT 3 ' + > ' UNION SELECT 4 ' > > EXEC dbo.sp_executesql @sql, N'' > > SELECT * > FROM #Temp123 > > SELECT @sql = N'SELECT * ' + > ' FROM #Temp123 ' > > EXEC dbo.sp_executesql @sql, N'' > > DROP TABLE #Temp123 > > > > "Will" <william_p***@yahoo.co.uk> wrote in message > news:1149266034.041506.274000@j55g2000cwa.googlegroups.com... > > > > Mike C# wrote: > >> "Will" <william_p***@yahoo.co.uk> wrote in message > >> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... > >> > for using them in dynamic sql - if you have to create a result set in > >> > your dynamic part with an unknown number of columns, how would you then > >> > interrogate this in static sql? > >> > > >> > >> Create a result set with an unknown number of columns? The only time > >> I've > >> ever seen that done is when creating a Pivot table using Dynamic SQL. > >> And > >> then the SELECT statement is dynamically built with correct columns named > >> in > >> it... Maybe you have an example of how a global temp table would be > >> preferred to a regular temp table in this particular situation? Cause I > >> just ain't seein' it. > > > > How about this one then: > > > > http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e > > > > I was never actually able to come up with a decent solution to Helmut's > > problem, but my final attempt came quite close (I was hoping one of the > > smart people on the forum might ride in and save the day, but instead I > > think he got tired and went off and solved it himself). But anyway, > > somewhere in the mass of SQL at the end is a global temporary table, > > and I couldn't think of a way to turn it into just a normal one (which > > I actually needed to do). > > > > Cheers > > Will > > The OP's question (on the link you sent) to me is a non-starter. He wants
to compare the rows of two separate tables that might or might not have the same columns in them. The columns they do share in common might or might not have the same data type, precision, etc. The end result is that you might or might not end up with error messages like this: "Server: Msg 257, Level 16, State 3, Line 1, Implicit conversion from data type smallmoney to nvarchar is not allowed. Use the CONVERT function to run this query." (Error message I received when running the code against my local box). Even with the solution you came up with you still have to hard-code the name of at least one column from each table ahead of time to make it run. The guy's description of his problem presents more questions. What if Table A is an exact duplicate Table B, but with one additional column? Does that mean every row of Table A needs to be listed as being "different" from Table B, and every row of Table B needs to be listed as being "different" from Table A? What if Table A has a column with the same name as Table B, but with a different data type? I would say that the guy's best bet would be to buy a third party tool to do it or write an application outside of SQL Server to perform all the proper checks needed to do what he wants dynamically. If he wanted to be really clever, he could add a hash column to each table that he wanted to compare and generate a separate SHA-512 hash for each row, then grab only the rows where there's no hash code to match in the other table. If you need a cursor and that much dynamic SQL smack dab in the middle of it, you might be better served on the application layer. But all this is off-topic anyway. I believe your challenge was to "adapt your code to not use a global temporary table." Here you go, no global temporary tables at all: DECLARE @TableName varchar(50) SET @TableName = 'tblA' DECLARE @DbName1 varchar(50) SET @DBName1 = 'db1' DECLARE @DbName2 varchar(50) SET @DBName2 = 'db2' --loop through each column and build up the strings for the table and the select CREATE TABLE #Cols ( TABLE_QUALIFIER sysname, TABLE_OWNER sysname, TABLE_NAME sysname, COLUMN_NAME sysname, DATA_TYPE smallint, TYPE_NAME sysname, [PRECISION] int, LENGTH int, SCALE smallint, RADIX smallint, NULLABLE smallint, REMARKS varchar(254), COLUMN_DEF nvarchar(4000), SQL_DATA_TYPE smallint, SQL_DATETIME_SUB smallint, CHAR_OCTET_LENGTH int, ORDINAL_POSITION int, IS_NULLABLE varchar(254), SS_DATA_TYPE tinyint ) INSERT INTO #Cols exec sp_columns @TableName --make a string to create the global table DECLARE @AlterTableSQL varchar(8000) SELECT @AlterTableSQL = '' CREATE TABLE #MyGodThatsBig(KeyID int IDENTITY(1,1)) --create strings to insert, select and where from DECLARE @INSERT varchar(8000) SET @Insert = 'INSERT INTO #MyGodThatsBig(' DECLARE @Select varchar(8000) SET @SELECT = ' SELECT ' DECLARE @FROM varchar(8000) SET @FROM = ' FROM ' + @DBName1 + '.dbo.' + @TableName + ' as db1 INNER JOIN ' + @DBName2 + '.dbo.'+@TableName + ' as db2 on db1.IDCol = db2.IDCol WHERE 1=2' --loop through every column DECLARE @Col sysname DECLARE @Type nvarchar(50) DECLARE Cols Cursor FOR SELECT Column_Name, CASE WHEN TYPE_NAME LIKE '%char%' THEN TYPE_NAME + '(' + CAST([PRECISION] as varchar(5)) + ')' ELSE TYPE_Name END FROM #Cols OPEN Cols FETCH NEXT FROM Cols INTO @Col, @Type WHILE @@FETCH_STATUS = 0 BEGIN PRINT @Col if(@Col <> 'IDCol') BEGIN --if it's not the id col, add it to the various statements SET @AlterTableSQL = @AlterTableSQL + ',' + dbo.fnCreateTableRow(@Col, @Type) SET @Insert = @Insert + ',' + @Col + 'df,' + @Col + 'N,'+@Col +'O' SET @Select = @Select + ',dbo.fncmp(db1.' + @col + ',db2.'+ @col + '),db1.'+@Col + ',db2.' + @Col SET @From = @From + ' OR dbo.fncmp(db1.' + @col + ',db2.' + @col + ') = 1' END FETCH NEXT FROM Cols INTO @Col, @Type END SELECT @AlterTableSQL = 'ALTER TABLE #MyGodThatsBig ADD ' + SUBSTRING(@AlterTableSQL, 2, 8000) --clean up the statements SET @INSERT = REPLACE(@INSERT,'(,','(') SET @SELECT = REPLACE(@SELECT,'SELECT ,','SELECT ') SET @Insert = @Insert + ')' --create the table and insert the differing values --SET @AlterTableSQL = @AlterTableSQL exec (@AlterTableSQL) exec (@Insert + @SELECT + @FROM) --cleanup CLOSE Cols DEALLOCATE Cols --create the result set CREATE TABLE #Result(FieldName varchar(100), OldValue varchar(100), NewValue varchar(100)) DECLARE Cols2 Cursor FOR SELECT Column_Name FROM #Cols OPEN Cols2 --- now select in the differing values in the form required DECLARE @FinalSQL varchar(8000) FETCH NEXT FROM Cols2 INTO @Col WHILE @@FETCH_STATUS = 0 BEGIN IF(@Col != 'IDCol') BEGIN SET @FinalSQL = 'INSERT INTO #Result(FieldName, OldValue,NewValue) SELECT ''' + @Col + ''',' + @Col + 'O,'+@Col+'N FROM #MyGodThatsBig WHERE ' + @Col + 'df = 0' FETCH NEXT FROM Cols2 INTO @Col exec (@FinalSQL) END FETCH NEXT FROM Cols2 INTO @Col END CLOSE Cols2 DEALLOCATE Cols2 --select the result, then go have a beer! SELECT * FROM #Result DROP TABLE #MyGodThatsBig DROP TABLE #Cols DROP TABLE #Result GO Show quote "Will" <william_p***@yahoo.co.uk> wrote in message news:1149524394.155798.220400@f6g2000cwb.googlegroups.com... > ok guys, > > it has been mentioned several times, so believe me - I know that you > can create tables outside of the dynamic sql, then access them within > it. > > My point is that there are times when you need to create the table > within dynamic sql because you don't at "authoring time" know what the > schema is/will be. I could possibly build up a load of alter table > statements with the dynamic sql instead however this would probably be > quite innefficient and much harder to debug. I also know that it's > preventing concurrent execution of the script, I would probably get > around that by dynamically generating the name of the table (I would be > able to generate and access that outside of the dynamic part). > > If you truly believe that you could adapt that code to not use a global > temporary table - please do, then post it to the end of that thread. > I'd be keen to see a better solution, but the problem isn't as trivial > as it may at first appear. Until then I feel the example stands as a > place where global temporary tables were needed (though probably not > what they were intended for :)). > > Cheers > Will > > Mike C# wrote: >> There's no need for a global temp table to solve that problem. In fact, >> by >> using a global temp table you seem to be limiting yourself to running the >> comparison against a single pair of tables at a time. >> >> If a local temp table were created outside of the dynamic SQL, it's scope >> would include the dynamic SQL. To wit, sp_executesql accesses the >> #Temp123 >> table here just as one would expect: >> >> CREATE TABLE #Temp123 (a INT) >> >> DECLARE @sql NVARCHAR(255) >> SELECT @sql = N'INSERT INTO #Temp123 (a) ' + >> 'SELECT 1 ' + >> ' UNION SELECT 2 ' + >> ' UNION SELECT 3 ' + >> ' UNION SELECT 4 ' >> >> EXEC dbo.sp_executesql @sql, N'' >> >> SELECT * >> FROM #Temp123 >> >> SELECT @sql = N'SELECT * ' + >> ' FROM #Temp123 ' >> >> EXEC dbo.sp_executesql @sql, N'' >> >> DROP TABLE #Temp123 >> >> >> >> "Will" <william_p***@yahoo.co.uk> wrote in message >> news:1149266034.041506.274000@j55g2000cwa.googlegroups.com... >> > >> > Mike C# wrote: >> >> "Will" <william_p***@yahoo.co.uk> wrote in message >> >> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... >> >> > for using them in dynamic sql - if you have to create a result set >> >> > in >> >> > your dynamic part with an unknown number of columns, how would you >> >> > then >> >> > interrogate this in static sql? >> >> > >> >> >> >> Create a result set with an unknown number of columns? The only time >> >> I've >> >> ever seen that done is when creating a Pivot table using Dynamic SQL. >> >> And >> >> then the SELECT statement is dynamically built with correct columns >> >> named >> >> in >> >> it... Maybe you have an example of how a global temp table would be >> >> preferred to a regular temp table in this particular situation? Cause >> >> I >> >> just ain't seein' it. >> > >> > How about this one then: >> > >> > http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e >> > >> > I was never actually able to come up with a decent solution to Helmut's >> > problem, but my final attempt came quite close (I was hoping one of the >> > smart people on the forum might ride in and save the day, but instead I >> > think he got tired and went off and solved it himself). But anyway, >> > somewhere in the mass of SQL at the end is a global temporary table, >> > and I couldn't think of a way to turn it into just a normal one (which >> > I actually needed to do). >> > >> > Cheers >> > Will >> > > I must say I do admire your tenacity...
The OP in the other post basically (as I understand it) just wanted to restore a previous backup (schema unchanged as an assumption) and see what data had changed. This would avoid the problems described, but as you say - this is irrelevant. As for the new way of doing it - dynamically building up alter statements. If only I'd thought of it, I could have refered to it in my previous post. Yes, it's a different way, in my opinion not a better way (I discounted this way because we were after efficiency, so creating a table then altering it was not as good as just creating it). I prefer the proposed solution of dynamically generating the table name, then dynamically destroying it. But whether it's better or not, I did not present this example as the all powerful use of global temporary tables (indeed I said it was flawed), however it is in my opinion a time when they played a part, and shows that they can have a purpose. So... The example still holds. Cheers Will P.S - out of interest, are you arguing this because you believe they don't have a purpose? or just arguing? both are fun! Mike C# wrote: Show quote > The OP's question (on the link you sent) to me is a non-starter. He wants > to compare the rows of two separate tables that might or might not have the > same columns in them. The columns they do share in common might or might > not have the same data type, precision, etc. The end result is that you > might or might not end up with error messages like this: "Server: Msg 257, > Level 16, State 3, Line 1, Implicit conversion from data type smallmoney to > nvarchar is not allowed. Use the CONVERT function to run this query." (Error > message I received when running the code against my local box). Even with > the solution you came up with you still have to hard-code the name of at > least one column from each table ahead of time to make it run. > > The guy's description of his problem presents more questions. What if Table > A is an exact duplicate Table B, but with one additional column? Does that > mean every row of Table A needs to be listed as being "different" from Table > B, and every row of Table B needs to be listed as being "different" from > Table A? What if Table A has a column with the same name as Table B, but > with a different data type? I would say that the guy's best bet would be to > buy a third party tool to do it or write an application outside of SQL > Server to perform all the proper checks needed to do what he wants > dynamically. > > If he wanted to be really clever, he could add a hash column to each table > that he wanted to compare and generate a separate SHA-512 hash for each row, > then grab only the rows where there's no hash code to match in the other > table. If you need a cursor and that much dynamic SQL smack dab in the > middle of it, you might be better served on the application layer. > > But all this is off-topic anyway. I believe your challenge was to "adapt > your code to not use a global temporary table." Here you go, no global > temporary tables at all: > > DECLARE @TableName varchar(50) > SET @TableName = 'tblA' > DECLARE @DbName1 varchar(50) > SET @DBName1 = 'db1' > > DECLARE @DbName2 varchar(50) > SET @DBName2 = 'db2' > --loop through each column and build up the strings for the table and the > select > CREATE TABLE #Cols > ( > TABLE_QUALIFIER sysname, > TABLE_OWNER sysname, > TABLE_NAME sysname, > COLUMN_NAME sysname, > DATA_TYPE smallint, > TYPE_NAME sysname, > [PRECISION] int, > LENGTH int, > SCALE smallint, > RADIX smallint, > NULLABLE smallint, > REMARKS varchar(254), > COLUMN_DEF nvarchar(4000), > SQL_DATA_TYPE smallint, > SQL_DATETIME_SUB smallint, > CHAR_OCTET_LENGTH int, > ORDINAL_POSITION int, > IS_NULLABLE varchar(254), > SS_DATA_TYPE tinyint > ) > INSERT INTO #Cols > exec sp_columns @TableName > > --make a string to create the global table > DECLARE @AlterTableSQL varchar(8000) > SELECT @AlterTableSQL = '' > CREATE TABLE #MyGodThatsBig(KeyID int IDENTITY(1,1)) > > --create strings to insert, select and where from > DECLARE @INSERT varchar(8000) > SET @Insert = 'INSERT INTO #MyGodThatsBig(' > DECLARE @Select varchar(8000) > SET @SELECT = ' SELECT ' > DECLARE @FROM varchar(8000) > SET @FROM = ' FROM ' + @DBName1 + '.dbo.' + @TableName + ' as db1 INNER JOIN > ' + > @DBName2 + '.dbo.'+@TableName + ' as db2 on db1.IDCol = db2.IDCol WHERE > 1=2' > > --loop through every column > DECLARE @Col sysname > DECLARE @Type nvarchar(50) > DECLARE Cols Cursor > FOR > SELECT Column_Name, CASE WHEN TYPE_NAME LIKE '%char%' THEN TYPE_NAME + > '(' + CAST([PRECISION] as varchar(5)) + ')' ELSE TYPE_Name END > FROM #Cols > > OPEN Cols > > FETCH NEXT FROM Cols INTO @Col, @Type > > WHILE @@FETCH_STATUS = 0 > BEGIN > PRINT @Col > if(@Col <> 'IDCol') > BEGIN > --if it's not the id col, add it to the various statements > SET @AlterTableSQL = @AlterTableSQL + ',' + > dbo.fnCreateTableRow(@Col, @Type) > SET @Insert = @Insert + ',' + @Col + 'df,' + @Col + 'N,'+@Col +'O' > SET @Select = @Select + ',dbo.fncmp(db1.' + @col + ',db2.'+ @col + > '),db1.'+@Col + ',db2.' + @Col > SET @From = @From + ' OR dbo.fncmp(db1.' + @col + ',db2.' + > @col + ') = 1' > END > > FETCH NEXT FROM Cols INTO @Col, @Type > END > > SELECT @AlterTableSQL = 'ALTER TABLE #MyGodThatsBig ADD ' + > SUBSTRING(@AlterTableSQL, 2, 8000) > > --clean up the statements > SET @INSERT = REPLACE(@INSERT,'(,','(') > SET @SELECT = REPLACE(@SELECT,'SELECT ,','SELECT ') > SET @Insert = @Insert + ')' > > --create the table and insert the differing values > > --SET @AlterTableSQL = @AlterTableSQL > exec (@AlterTableSQL) > exec (@Insert + @SELECT + @FROM) > > --cleanup > CLOSE Cols > DEALLOCATE Cols > > --create the result set > CREATE TABLE #Result(FieldName varchar(100), OldValue varchar(100), NewValue > varchar(100)) > > DECLARE Cols2 Cursor > FOR > SELECT Column_Name > FROM #Cols > > OPEN Cols2 > --- now select in the differing values in the form required > DECLARE @FinalSQL varchar(8000) > FETCH NEXT FROM Cols2 INTO @Col > WHILE @@FETCH_STATUS = 0 > BEGIN > IF(@Col != 'IDCol') > BEGIN > SET @FinalSQL = 'INSERT INTO #Result(FieldName, > OldValue,NewValue) SELECT ''' + > @Col + ''',' + @Col + 'O,'+@Col+'N FROM #MyGodThatsBig WHERE ' + > @Col + 'df = 0' > FETCH NEXT FROM Cols2 INTO @Col > exec (@FinalSQL) > END > FETCH NEXT FROM Cols2 INTO @Col > END > > CLOSE Cols2 > DEALLOCATE Cols2 > > --select the result, then go have a beer! > SELECT * FROM #Result > DROP TABLE #MyGodThatsBig > DROP TABLE #Cols > DROP TABLE #Result > GO > > > "Will" <william_p***@yahoo.co.uk> wrote in message > news:1149524394.155798.220400@f6g2000cwb.googlegroups.com... > > ok guys, > > > > it has been mentioned several times, so believe me - I know that you > > can create tables outside of the dynamic sql, then access them within > > it. > > > > My point is that there are times when you need to create the table > > within dynamic sql because you don't at "authoring time" know what the > > schema is/will be. I could possibly build up a load of alter table > > statements with the dynamic sql instead however this would probably be > > quite innefficient and much harder to debug. I also know that it's > > preventing concurrent execution of the script, I would probably get > > around that by dynamically generating the name of the table (I would be > > able to generate and access that outside of the dynamic part). > > > > If you truly believe that you could adapt that code to not use a global > > temporary table - please do, then post it to the end of that thread. > > I'd be keen to see a better solution, but the problem isn't as trivial > > as it may at first appear. Until then I feel the example stands as a > > place where global temporary tables were needed (though probably not > > what they were intended for :)). > > > > Cheers > > Will > > > > Mike C# wrote: > >> There's no need for a global temp table to solve that problem. In fact, > >> by > >> using a global temp table you seem to be limiting yourself to running the > >> comparison against a single pair of tables at a time. > >> > >> If a local temp table were created outside of the dynamic SQL, it's scope > >> would include the dynamic SQL. To wit, sp_executesql accesses the > >> #Temp123 > >> table here just as one would expect: > >> > >> CREATE TABLE #Temp123 (a INT) > >> > >> DECLARE @sql NVARCHAR(255) > >> SELECT @sql = N'INSERT INTO #Temp123 (a) ' + > >> 'SELECT 1 ' + > >> ' UNION SELECT 2 ' + > >> ' UNION SELECT 3 ' + > >> ' UNION SELECT 4 ' > >> > >> EXEC dbo.sp_executesql @sql, N'' > >> > >> SELECT * > >> FROM #Temp123 > >> > >> SELECT @sql = N'SELECT * ' + > >> ' FROM #Temp123 ' > >> > >> EXEC dbo.sp_executesql @sql, N'' > >> > >> DROP TABLE #Temp123 > >> > >> > >> > >> "Will" <william_p***@yahoo.co.uk> wrote in message > >> news:1149266034.041506.274000@j55g2000cwa.googlegroups.com... > >> > > >> > Mike C# wrote: > >> >> "Will" <william_p***@yahoo.co.uk> wrote in message > >> >> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... > >> >> > for using them in dynamic sql - if you have to create a result set > >> >> > in > >> >> > your dynamic part with an unknown number of columns, how would you > >> >> > then > >> >> > interrogate this in static sql? > >> >> > > >> >> > >> >> Create a result set with an unknown number of columns? The only time > >> >> I've > >> >> ever seen that done is when creating a Pivot table using Dynamic SQL. > >> >> And > >> >> then the SELECT statement is dynamically built with correct columns > >> >> named > >> >> in > >> >> it... Maybe you have an example of how a global temp table would be > >> >> preferred to a regular temp table in this particular situation? Cause > >> >> I > >> >> just ain't seein' it. > >> > > >> > How about this one then: > >> > > >> > http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e > >> > > >> > I was never actually able to come up with a decent solution to Helmut's > >> > problem, but my final attempt came quite close (I was hoping one of the > >> > smart people on the forum might ride in and save the day, but instead I > >> > think he got tired and went off and solved it himself). But anyway, > >> > somewhere in the mass of SQL at the end is a global temporary table, > >> > and I couldn't think of a way to turn it into just a normal one (which > >> > I actually needed to do). > >> > > >> > Cheers > >> > Will > >> > > > LOL. It was a contrived example, but just as a demonstration of using a
regular temp table. You can likewise strike the "##" from the code and make it a regular table without changing much of anything else in your code. I don't believe global temporary tables serve a useful purpose. They're like a hybrid between temporary tables and regular tables, and I still haven't seen any benefits that make me want to use them over one of the other two in a given situation. It's about as useless as an appendix and I suspect MS probably keeps it around so that developers who have written code using it won't complain about having to go back and strike "##" from their code. Your example is the closest I've seen to an example where it might be useful, but there are inherent limits that could make a global temporary table undesirable there as well. If the OP over there tried to run the same code against two sets of tables simultaneously, for instance. In that case your code will have some problems since it won't be able to create the global temp table again, since it already exists. If running it multiple times simultaneously is not an issue, then there's no advantage over a regular table. In fact, as far as I can tell the only advantage the global temporary table has over a regular table in your example is that it will automatically be dropped out of existence if: 1) your code is interrupted by a "catastrophic" error or the user cancels it and 2) the connection is closed; i.e., the user closes out QA. Other than that, you might as well be using a regular table with a statement at the top to check for the table's existence to drop it beforehand if necessary. In fact, if the user is going to use the same connection in QA to run this code on several tables, it might not be a bad idea to check for and drop the global temporary table at the beginning, since it won't go away until it hits a DROP TABLE or the connection is closed. Show quote "Will" <william_p***@yahoo.co.uk> wrote in message news:1149545962.259170.236890@g10g2000cwb.googlegroups.com... >I must say I do admire your tenacity... > > The OP in the other post basically (as I understand it) just wanted to > restore a previous backup (schema unchanged as an assumption) and see > what data had changed. This would avoid the problems described, but as > you say - this is irrelevant. > > As for the new way of doing it - dynamically building up alter > statements. If only I'd thought of it, I could have refered to it in my > previous post. Yes, it's a different way, in my opinion not a better > way (I discounted this way because we were after efficiency, so > creating a table then altering it was not as good as just creating it). > I prefer the proposed solution of dynamically generating the table > name, then dynamically destroying it. > > But whether it's better or not, I did not present this example as the > all powerful use of global temporary tables (indeed I said it was > flawed), however it is in my opinion a time when they played a part, > and shows that they can have a purpose. So... The example still holds. > > Cheers > Will > > P.S - out of interest, are you arguing this because you believe they > don't have a purpose? or just arguing? both are fun! > > Mike C# wrote: >> The OP's question (on the link you sent) to me is a non-starter. He >> wants >> to compare the rows of two separate tables that might or might not have >> the >> same columns in them. The columns they do share in common might or might >> not have the same data type, precision, etc. The end result is that you >> might or might not end up with error messages like this: "Server: Msg >> 257, >> Level 16, State 3, Line 1, Implicit conversion from data type smallmoney >> to >> nvarchar is not allowed. Use the CONVERT function to run this query." >> (Error >> message I received when running the code against my local box). Even >> with >> the solution you came up with you still have to hard-code the name of at >> least one column from each table ahead of time to make it run. >> >> The guy's description of his problem presents more questions. What if >> Table >> A is an exact duplicate Table B, but with one additional column? Does >> that >> mean every row of Table A needs to be listed as being "different" from >> Table >> B, and every row of Table B needs to be listed as being "different" from >> Table A? What if Table A has a column with the same name as Table B, but >> with a different data type? I would say that the guy's best bet would be >> to >> buy a third party tool to do it or write an application outside of SQL >> Server to perform all the proper checks needed to do what he wants >> dynamically. >> >> If he wanted to be really clever, he could add a hash column to each >> table >> that he wanted to compare and generate a separate SHA-512 hash for each >> row, >> then grab only the rows where there's no hash code to match in the other >> table. If you need a cursor and that much dynamic SQL smack dab in the >> middle of it, you might be better served on the application layer. >> >> But all this is off-topic anyway. I believe your challenge was to "adapt >> your code to not use a global temporary table." Here you go, no global >> temporary tables at all: >> >> DECLARE @TableName varchar(50) >> SET @TableName = 'tblA' >> DECLARE @DbName1 varchar(50) >> SET @DBName1 = 'db1' >> >> DECLARE @DbName2 varchar(50) >> SET @DBName2 = 'db2' >> --loop through each column and build up the strings for the table and the >> select >> CREATE TABLE #Cols >> ( >> TABLE_QUALIFIER sysname, >> TABLE_OWNER sysname, >> TABLE_NAME sysname, >> COLUMN_NAME sysname, >> DATA_TYPE smallint, >> TYPE_NAME sysname, >> [PRECISION] int, >> LENGTH int, >> SCALE smallint, >> RADIX smallint, >> NULLABLE smallint, >> REMARKS varchar(254), >> COLUMN_DEF nvarchar(4000), >> SQL_DATA_TYPE smallint, >> SQL_DATETIME_SUB smallint, >> CHAR_OCTET_LENGTH int, >> ORDINAL_POSITION int, >> IS_NULLABLE varchar(254), >> SS_DATA_TYPE tinyint >> ) >> INSERT INTO #Cols >> exec sp_columns @TableName >> >> --make a string to create the global table >> DECLARE @AlterTableSQL varchar(8000) >> SELECT @AlterTableSQL = '' >> CREATE TABLE #MyGodThatsBig(KeyID int IDENTITY(1,1)) >> >> --create strings to insert, select and where from >> DECLARE @INSERT varchar(8000) >> SET @Insert = 'INSERT INTO #MyGodThatsBig(' >> DECLARE @Select varchar(8000) >> SET @SELECT = ' SELECT ' >> DECLARE @FROM varchar(8000) >> SET @FROM = ' FROM ' + @DBName1 + '.dbo.' + @TableName + ' as db1 INNER >> JOIN >> ' + >> @DBName2 + '.dbo.'+@TableName + ' as db2 on db1.IDCol = db2.IDCol WHERE >> 1=2' >> >> --loop through every column >> DECLARE @Col sysname >> DECLARE @Type nvarchar(50) >> DECLARE Cols Cursor >> FOR >> SELECT Column_Name, CASE WHEN TYPE_NAME LIKE '%char%' THEN TYPE_NAME + >> '(' + CAST([PRECISION] as varchar(5)) + ')' ELSE TYPE_Name END >> FROM #Cols >> >> OPEN Cols >> >> FETCH NEXT FROM Cols INTO @Col, @Type >> >> WHILE @@FETCH_STATUS = 0 >> BEGIN >> PRINT @Col >> if(@Col <> 'IDCol') >> BEGIN >> --if it's not the id col, add it to the various statements >> SET @AlterTableSQL = @AlterTableSQL + ',' + >> dbo.fnCreateTableRow(@Col, @Type) >> SET @Insert = @Insert + ',' + @Col + 'df,' + @Col + 'N,'+@Col >> +'O' >> SET @Select = @Select + ',dbo.fncmp(db1.' + @col + ',db2.'+ @col >> + >> '),db1.'+@Col + ',db2.' + @Col >> SET @From = @From + ' OR dbo.fncmp(db1.' + @col + ',db2.' >> + >> @col + ') = 1' >> END >> >> FETCH NEXT FROM Cols INTO @Col, @Type >> END >> >> SELECT @AlterTableSQL = 'ALTER TABLE #MyGodThatsBig ADD ' + >> SUBSTRING(@AlterTableSQL, 2, 8000) >> >> --clean up the statements >> SET @INSERT = REPLACE(@INSERT,'(,','(') >> SET @SELECT = REPLACE(@SELECT,'SELECT ,','SELECT ') >> SET @Insert = @Insert + ')' >> >> --create the table and insert the differing values >> >> --SET @AlterTableSQL = @AlterTableSQL >> exec (@AlterTableSQL) >> exec (@Insert + @SELECT + @FROM) >> >> --cleanup >> CLOSE Cols >> DEALLOCATE Cols >> >> --create the result set >> CREATE TABLE #Result(FieldName varchar(100), OldValue varchar(100), >> NewValue >> varchar(100)) >> >> DECLARE Cols2 Cursor >> FOR >> SELECT Column_Name >> FROM #Cols >> >> OPEN Cols2 >> --- now select in the differing values in the form required >> DECLARE @FinalSQL varchar(8000) >> FETCH NEXT FROM Cols2 INTO @Col >> WHILE @@FETCH_STATUS = 0 >> BEGIN >> IF(@Col != 'IDCol') >> BEGIN >> SET @FinalSQL = 'INSERT INTO #Result(FieldName, >> OldValue,NewValue) SELECT ''' + >> @Col + ''',' + @Col + 'O,'+@Col+'N FROM #MyGodThatsBig WHERE ' + >> @Col + 'df = 0' >> FETCH NEXT FROM Cols2 INTO @Col >> exec (@FinalSQL) >> END >> FETCH NEXT FROM Cols2 INTO @Col >> END >> >> CLOSE Cols2 >> DEALLOCATE Cols2 >> >> --select the result, then go have a beer! >> SELECT * FROM #Result >> DROP TABLE #MyGodThatsBig >> DROP TABLE #Cols >> DROP TABLE #Result >> GO >> >> >> "Will" <william_p***@yahoo.co.uk> wrote in message >> news:1149524394.155798.220400@f6g2000cwb.googlegroups.com... >> > ok guys, >> > >> > it has been mentioned several times, so believe me - I know that you >> > can create tables outside of the dynamic sql, then access them within >> > it. >> > >> > My point is that there are times when you need to create the table >> > within dynamic sql because you don't at "authoring time" know what the >> > schema is/will be. I could possibly build up a load of alter table >> > statements with the dynamic sql instead however this would probably be >> > quite innefficient and much harder to debug. I also know that it's >> > preventing concurrent execution of the script, I would probably get >> > around that by dynamically generating the name of the table (I would be >> > able to generate and access that outside of the dynamic part). >> > >> > If you truly believe that you could adapt that code to not use a global >> > temporary table - please do, then post it to the end of that thread. >> > I'd be keen to see a better solution, but the problem isn't as trivial >> > as it may at first appear. Until then I feel the example stands as a >> > place where global temporary tables were needed (though probably not >> > what they were intended for :)). >> > >> > Cheers >> > Will >> > >> > Mike C# wrote: >> >> There's no need for a global temp table to solve that problem. In >> >> fact, >> >> by >> >> using a global temp table you seem to be limiting yourself to running >> >> the >> >> comparison against a single pair of tables at a time. >> >> >> >> If a local temp table were created outside of the dynamic SQL, it's >> >> scope >> >> would include the dynamic SQL. To wit, sp_executesql accesses the >> >> #Temp123 >> >> table here just as one would expect: >> >> >> >> CREATE TABLE #Temp123 (a INT) >> >> >> >> DECLARE @sql NVARCHAR(255) >> >> SELECT @sql = N'INSERT INTO #Temp123 (a) ' + >> >> 'SELECT 1 ' + >> >> ' UNION SELECT 2 ' + >> >> ' UNION SELECT 3 ' + >> >> ' UNION SELECT 4 ' >> >> >> >> EXEC dbo.sp_executesql @sql, N'' >> >> >> >> SELECT * >> >> FROM #Temp123 >> >> >> >> SELECT @sql = N'SELECT * ' + >> >> ' FROM #Temp123 ' >> >> >> >> EXEC dbo.sp_executesql @sql, N'' >> >> >> >> DROP TABLE #Temp123 >> >> >> >> >> >> >> >> "Will" <william_p***@yahoo.co.uk> wrote in message >> >> news:1149266034.041506.274000@j55g2000cwa.googlegroups.com... >> >> > >> >> > Mike C# wrote: >> >> >> "Will" <william_p***@yahoo.co.uk> wrote in message >> >> >> news:1148241390.987248.51840@i39g2000cwa.googlegroups.com... >> >> >> > for using them in dynamic sql - if you have to create a result >> >> >> > set >> >> >> > in >> >> >> > your dynamic part with an unknown number of columns, how would >> >> >> > you >> >> >> > then >> >> >> > interrogate this in static sql? >> >> >> > >> >> >> >> >> >> Create a result set with an unknown number of columns? The only >> >> >> time >> >> >> I've >> >> >> ever seen that done is when creating a Pivot table using Dynamic >> >> >> SQL. >> >> >> And >> >> >> then the SELECT statement is dynamically built with correct columns >> >> >> named >> >> >> in >> >> >> it... Maybe you have an example of how a global temp table would >> >> >> be >> >> >> preferred to a regular temp table in this particular situation? >> >> >> Cause >> >> >> I >> >> >> just ain't seein' it. >> >> > >> >> > How about this one then: >> >> > >> >> > http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/9aba4adb37b41544/4f72501dbabdb33e?lnk=st&q=&rnum=16&hl=en#4f72501dbabdb33e >> >> > >> >> > I was never actually able to come up with a decent solution to >> >> > Helmut's >> >> > problem, but my final attempt came quite close (I was hoping one of >> >> > the >> >> > smart people on the forum might ride in and save the day, but >> >> > instead I >> >> > think he got tired and went off and solved it himself). But anyway, >> >> > somewhere in the mass of SQL at the end is a global temporary table, >> >> > and I couldn't think of a way to turn it into just a normal one >> >> > (which >> >> > I actually needed to do). >> >> > >> >> > Cheers >> >> > Will >> >> > >> > >
Show quote
"Will" <william_p***@yahoo.co.uk> wrote in message They are not suitable for caching since they don't have a well-defined news:1148151980.104435.22640@j33g2000cwa.googlegroups.com... > I've had to use them occassionally (mainly through lazyness, as it > wasn't necessary to design anything better) when I am using dynamic > sql. Sometimes you need to return results back out of your dynamic sql, > in which case the only way I've found to do it is either create a > normal temporary table beforehand, then populate it in the dynamic sql > (which isn't always an option), or just create a global one from within > the dynamic sql, then look for that in the static stuff. > > A time when I think they would be useful is when you need an SQL > equivalent of the .NET application cache. I haven't ever tried using > them as such, but I can think of situations where it probably would > help performance if you were to cache certain results into global > temporary tables, then next time any procedure needs it they can check > if the object exists, then just retrieve that. > lifetime. A regular table, or a regular table in TempDB would need to be used. David The .NET application cache does not have a well defined lifetime
either. The advantage I can see (and like I said, I've never actually tried this out) is that by using a global temporary table as a cache, when the object is not required the resources are released, whereas by creating a permenant table you then have to manage the cleanup of the resources yourself. Managing this is not necessarily a bad thing, just extra work and another opportunity for error. I just find myself seeing parrallels between the .NET application cache and global temporary tables. It's just my view, but I suspect at some point in the future I'll find a valid use for global temporary tables, when I do, I'll post it :) Will (william_p***@yahoo.co.uk) writes:
> The advantage I can see (and like I said, I've never actually tried The problem here is that in modern application the model is connect,> this out) is that by using a global temporary table as a cache, when > the object is not required the resources are released, whereas by > creating a permenant table you then have to manage the cleanup of the > resources yourself. Managing this is not necessarily a bad thing, just > extra work and another opportunity for error. I just find myself seeing > parrallels between the .NET application cache and global temporary > tables. run query, disconnect. A global temp table goes away when the process that created it exits. (It may be delayed if another process is running a query against it.) Of course, in a modern application, the disconnect is not for real, thanks to application pooling, so nothing actually happens in the server at disconnect. But on reconnect, the API issues sp_reset_connection, and this is when the global temp demises. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx You'll generally get better performance from in-memory temporary tables as
long as they are fairly small. SQL Server will push the memory table to tempdb in certain situations such as row count or available memory. There are some limitations on indexing with in-memory temporary tables. BOL has a good section on temporary tables and explains better than I can without plagairism <g>. ## temporary tables are 'global' temporary tables and are cleaned up when sql server recycles tempdb. # temporary tables are local to the current batch and are removed when the batch has completed. -TIm Show quote "haenselmic" <haensel***@discussions.microsoft.com> wrote in message news:273D07B3-0236-43DC-AEF6-8F6D25681AAA@microsoft.com... > hi > > also i want to know what have more performance : a table variable or a > temporary table in a stored procedure > the rows in the tables are approx. 1000 > > thanks michel Global temporary tables are cleaned up as follows (according to BOL):
"Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended." I didn't think that tempdb recycled to those rules, I thought it was recreated upon startup only. Cheers Will "Tim Dot NoSpam" <Tim.NoSpam@hughes.net> wrote in message #table temp tables live for the life of the connection, not the batch. news:%23Il8tY1eGHA.1208@TK2MSFTNGP02.phx.gbl... > You'll generally get better performance from in-memory temporary tables as > long as they are fairly small. SQL Server will push the memory table to > tempdb in certain situations such as row count or available memory. There > are some limitations on indexing with in-memory temporary tables. BOL has > a good section on temporary tables and explains better than I can without > plagairism <g>. > > ## temporary tables are 'global' temporary tables and are cleaned up when > sql server recycles tempdb. # temporary tables are local to the current > batch and are removed when the batch has completed. > There is an exeption for #temp tables created inside stored procedures. They are automatically dropped after the stored procedure is invoked. David  |
|||||||||||||||||||||||
Other interesting topics