SQL Server Performance

Parameterized query vs local variable in a query

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by sql_machine, Nov 12, 2006.

  1. sql_machine New Member

    Not exectly sure what the heck the parameterized query really means. Does it always use sp_executesql? like:

    DECLARE @UserName NVARCHAR(255)
    SET @UserName = suser_sname()
    EXEC sp_executesql N'SELECT * FROM UserLog WHERE UserName = @n',
    N'@n nvarchar(255)', @UserName
    GO

    and so this like using a sproc and is different than just using local variable in a query, like:

    DECLARE @StartOrderDate datetime
    SET @StartOrderDate = '20051131'
    SELECT * FROM Sales.SalesOrderHeader h, Sales.SalesOrderDetail d
    WHERE h.SalesOrderID = d.SalesOrderId AND h.OrderDate >=
    @StartOrderDate
  2. satya Moderator

    Check the execution plan for both the query types and with proper indexes it doesn't matter much on performancd.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  3. Adriaan New Member

    You can use sp_ExecuteSQL with parameters if you do lots of dynamic SQL, to improve SQL Server's chances of finding an execution plan hat it can reuse.

    You can call static queries the same way, but AFAIK there's no additional benefit.
  4. Madhivanan Moderator

    If you dont pass object name as parameter, why dont you avoind Dynamic sql at all?
    www.Sommarskog.se/dynamic_sql.html

    Madhivanan

    Failing to plan is Planning to fail
  5. gurucb New Member

    One more additional point would be for Both Adhoc and parameterized queries thay are space sensitive and case sensitive...

    So

    select * from <table> where <col> = 100 is different from

    select * from <table> where <col> = 100

    And thus plan will not be used..

    Please refer this good documentation on this:

    http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
  6. BikeBoy New Member

    So my question was how parameterized queries are different from just a parameter passed in a local variable....

    Is parameterized querie always supposed to use dynamic sql, be it sp_executesql or exec (@var). Basically the definition of parameterized queries.
  7. hrishi_des New Member

  8. Adriaan New Member

    Bikeboy,

    There is no formal definition of "parameterized query". You can use variables to pass values, like in a stored procedure, or you can use them to compile a dynamic query statement, for instance to use different columns.

    You can use sp_ExecuteSQL with parameters to pass filter values, regardless of whether the query statement itself is dynamic or static. (By the way, if the satement is static then you might consider making it a stored procedure.)

Share This Page