Execution plan question

• Tac11

  SSCertifiable

  Points: 6976

  More actions

  October 4, 2018 at 9:16 am

  #364561

  Hi Gurus,
  I would appreciate if  someone explain to me this:
  I ran against AdventureWorks2012:
  SELECT a.AddressID
  FROM Person.Address AS a
  WHERE a.City = N'Seattle';

  Then ran below to see query plan:
  WITH XMLNAMESPACES
  (
  DEFAULT
  'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
  )
  SELECT deqp.query_plan.value(
     '(//StmtSimple)[1]/@ParameterizedPlanHandle', 'nvarchar(64)')
     AS ParameterizedPlanHandle,
     deqp.query_plan.value(
     '(//StmtSimple)[1]/@ParameterizedText', 'nvarchar(max)')
     AS ParameterizedText,
     deqp.query_plan,
     decp.cacheobjtype,
     decp.objtype,
     decp.plan_handle,
     dest.[text],
     decp.refcounts,
     decp.usecounts
  FROM sys.dm_exec_cached_plans AS decp
  CROSS APPLY sys.dm_exec_sql_text(decp.plan_handle) AS dest
  CROSS APPLY sys.dm_exec_query_plan(decp.plan_handle) AS deqp
  WHERE dest.[text] LIKE N'%Address%'

  In 'parameterizedText' column I saw:
  (@1 nvarchar(4000))SELECT [a].[AddressID] FROM [Person].[Address] [a] WHERE [a].[City]=@1

  My concern is on nvarchar(4000) because when I checked the parameter passed "City" 's databtype was (nvarchar(30)!!!

  I am wondering why in result "nvarchar 4000" NOT nvarchar 30"?

  Thanks