Troubleshooting Guide: SQL Server Error 495 – Return Table Column Type Mismatch

Why This Error Happens

This error occurs when a Table-Valued Function (TVF) or stored procedure module references a user-defined table type using a single-part name, and the underlying type definition has since changed or was ambiguously resolved at compile time. SQL Server 2025 requires unambiguous, two-part naming (schema.typename) to correctly bind the return table’s column metadata to the actual type schema. When the module was created without the schema qualifier, SQL Server may cache a stale or incorrect binding, causing a mismatch between the compiled column type and the runtime type definition, especially after a type alteration, database restore, or schema change.

Step-by-Step Resolution Guide

Step 1: Identify the Affected Module and Type

• Run the following query to locate the exact object throwing Error 495:

  SELECT OBJECT_NAME(object_id) AS ModuleName, definition 
  FROM sys.sql_modules 
  WHERE definition LIKE '%RETURNS TABLE%';

• Check the error message text carefully — it will specify the column name that has a type mismatch.
• Cross-reference the return table type against sys.table_types and sys.columns to confirm the current schema and data type.

Step 2: Script Out and Drop the Existing Module

• Use SQL Server Management Studio (SSMS) 21 or Azure Data Studio to generate a CREATE script for the affected function/procedure (right-click object > Script Object As > CREATE To).
• Execute a DROP statement for the module:

  DROP FUNCTION dbo.YourFunctionName;

• Confirm no dependent objects break by checking sys.sql_expression_dependencies before dropping.

Step 3: Recreate the Module Using Two-Part Naming

• Edit the scripted CREATE statement so the RETURNS TABLE clause explicitly references the schema-qualified type name.
• Example of correct syntax:

  CREATE FUNCTION dbo.YourFunctionName()
  RETURNS @ReturnTable TABLE 
  (
      ColumnName dbo.YourUserDefinedType
  )
  AS
  BEGIN
      -- function body
      RETURN;
  END;

• Ensure dbo.YourUserDefinedType (or the correct schema) is used instead of just YourUserDefinedType.

Step 4: Update Related Type References Server-Wide

• Search all stored procedures, functions, and views for single-part type name references:

  SELECT OBJECT_NAME(object_id), definition 
  FROM sys.sql_modules 
  WHERE definition LIKE '%YourUserDefinedType%' 
  AND definition NOT LIKE '%dbo.YourUserDefinedType%';

• Update and redeploy any modules found using single-part names to prevent recurrence.

Step 5: Clear Cached Execution Plans

• Run DBCC FREEPROCCACHE (or target the specific plan handle) to remove any stale cached plans referencing the old type binding.
• Restart the SQL Server service via Services.msc on Windows Server 2025 if the issue persists after cache clearing, ensuring no active connections are holding old metadata locks.

Alternative or Verification Steps

• Execute the function/procedure manually in SSMS and confirm it returns results without throwing Error 495; check sys.dm_exec_query_stats to verify no failed plan compilations remain.
• If the error resurfaces intermittently, consider dropping and recreating the User-Defined Table Type itself (with proper versioning/backups), then rebuild all dependent modules using fully qualified two-part names from the start.