Local Variables vs. Parameterized Stored Procedures in SQL Server (Video)

Local variables don’t behave like true parameters in stored procedures in SQL Server. Join Kendra to explore why local variables are so tricky in this free 10 minute video.

Previous Post
Ten Ways to Tell if Your SQL Server is a Clown Car
Next Post
Five Interview Questions to Ask SQL Server Developers

20 Comments. Leave new

  • Is this a matter of local variables being treated differently, or is it really the optimization of the stored procedure that causes different behavior? Parameter Sniffing is a well known issue which causes performance differences based on the initial parameter used when a stored procedure is compiled versus the value used on subsequent executions.

    In my limited testing. when the value used in the initial stored procedure execution/compilation is the same as the value used with a local variable, then the execution plans are the same. When this becomes a major issue then I add the WITH RECOMPILE option to the stored procedure to limit the differences found with parameter sniffing.

    Your thoughts?

    • Kendra Little
      March 16, 2015 4:50 pm

      It’s specific to local variables that aren’t real parameters. For example, in the code below a local variable is declared inside a stored procedure. That local variable will be “anonymized” / not sniffed. This isn’t a common usage, but it does happen (it’s the old homegrown version for the “optimize for unknown” query hint before it existed).

      create procedure dbo.foo
      AS

      declare @localvar int
      set @localvar = 1

      select *
      from dbo.posts
      where posttypeid=@localvar
      GO

      • Thanks for the clarification.

        Next time I need to watch the video AFTER having the first cup of coffee of the day.

  • Not sure if this article was your inspiration but I’d read it a little while back and it helped greatly with your (excellent as usual) content. I’d wondered why someone would put a default value in the procedure to start with. In any ase, it can’t hurt to share it.
    http://michaeljswart.com/2015/02/when-parameter-sniffing-caused-deadlocks/

  • This was very interesting, something I honestly had no idea occurs. So this same phenomenon occurs when using sp_executesql with parameters as well (they are treated as local variables)?

    • Kendra Little
      March 16, 2015 4:47 pm

      If you use parameterized dynamic SQL with sp_executesql, the parameters act like they do in a stored procedure.

  • Wow the Temp Stored procedure is a great idea! Usually I would just recreate the proc with a different name. What versions of SQL Server is this feature available on?

    Really informative stuff as usual 🙂
    Also who painted those bird silhouettes on the wall and door? Completely unrelated, but they had my attention for most of the video 🙂

  • Well I learned two important things today. I’m going to use that feature soon and those stickers are awesome. Thanks!

  • Thanks! I didn’t know you could do temporary procedures like that.

  • Very helpful. I wonder why this is? I can’t think of advantages of not treating variables like parameters. Any ideas why variables are not “sniffed into” like parameters?

    Sort of makes debugging a long sproc painful, because trying to troubleshoot the middle of a long sproc by hardcoding some local variables into it means you’re not getting the same execution plan as the parameterized version.

    But now I know, I guess!

    • Kendra Little
      March 18, 2015 9:52 am

      When you build a query optimizer, there’s a lot of hard choices to make. I don’t actually think the behavior is bad, personally, just a bit confusing when you’re first working with it.

  • Hi – Liked your post – perhaps you should mention that local variables will not use histograms
    (from Microsoft SQLServer 2014 – Query Tuning and Optimization – Benjamin Nevarez)

    • Yep, exactly Leon! What I show in the video is that it uses the “density vector” instead. Another way to say that is “it doesn’t use the histogram” (which is below the density vector in that part of the video). Great point, though– it’s definitely useful to say it both ways for people who already know about the histogram.

  • The video is no longer working. Is this just me?

  • […] Local Variables vs. Parameterized Stored Procedures in SQL Server […]

Menu
{"cart_token":"","hash":"","cart_data":""}