InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 19:30:06 GMT20http://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspxAlso, table variables don't necessarily make things any faster than a Temp Table. They both start out in memory... they both write to disk (TempDB) when they get bigger than memory wants to hold.Table Variables also make it (IMHO) more difficult to troubleshoot a problem in the code because they don't persist after the run is complete in SSMS. That means you have to run the code from the beginning every time or write some code to do some skips. You don't need to do that with Temp Tables because they persist during the session..Wed, 12 Jan 2011 07:16:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspx[quote][b]tharucse (1/12/2011)[/b][hr]Thanks Gail.I just asked one person who has this practice of using temporary variables.They say that they keep it as a standard way to write sql procedures even there is no use in insert queries Because can not expect all the developers to think in an intelligent way before write queries when it comes to a particular query that may have performance issue because of parameter sniffing.:-)[/quote]That's a bad practice. The use of variables for parameter sniffing problems is a specific solution to a specific problem. By using variables everywhere you'll be hindering the optimiser and generally preventing it from finding good execution plans.Use variables for the parameters only when there really is a parameter sniffing problem and investigation has shown that it is an appropriate solution (rather than rewriting the query or specific query hints). It should not be a standard development practice[url]http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/[/url]Wed, 12 Jan 2011 05:10:46 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspxThanks Gail.I just asked one person who has this practice of using temporary variables.They say that they keep it as a standard way to write sql procedures even there is no use in insert queries Because can not expect all the developers to think in an intelligent way before write queries when it comes to a particular query that may have performance issue because of parameter sniffing.:-)Wed, 12 Jan 2011 04:35:55 GMTtharucsehttp://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspxIt doesn't. No use or benefit whatsoever.Tue, 11 Jan 2011 22:41:26 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspxIn search queries when data distribution is skewed parameter sniffing can be a problem and local variables can be used to solve it.But I really don't understand how this comes into play when we do inserts.Tue, 11 Jan 2011 20:54:23 GMTtharucsehttp://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspxThanks Gail.I'll go through it and get back to you if I have any issues.Again Thanks a lot..Tue, 11 Jan 2011 03:55:29 GMTtharucsehttp://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspxIt's used when you run into problems with parameter sniffing.[url]http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/[/url]Tue, 11 Jan 2011 02:56:36 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1045736-65-1.aspxI found that some people are using temporary variables with stored procedures just to increase the performance of the query.No particular need for temp variables there.It's like this.If we want to insert some values in a table thenCreate PROCEDURE MyStoredProcedure(@param1 INT OUTPUT,@param2 varchar(40))As DECLARE @temp1 INT SET @temp1=@param1 DECLARE @temp2 varchar(40) SET @temp2 =@param2 Insert into Table_1(Field1,Field2)values(@temp1,@param2)I just could not imagine how this will improve the performance.For me it seems in the other way.Can anyone please help me to understand this?Thanks.Tue, 11 Jan 2011 01:48:56 GMTtharucse