How to fix Error 1993 on Windows Server 2025 / Windows 11

by

Why This Error Happens

SQL Server Error 1993 occurs because table variables and the return table definitions of table-valued functions (TVFs) are implicitly scoped objects that do not support partition schemes or filegroup placement. The Query Optimizer and storage engine require a physical partition scheme bound to a filegroup, but table variables and TVF result sets are materialized in tempdb (or in-memory, depending on configuration) without that underlying partition metadata. This limitation is by design in SQL Server 2025 and is not caused by corruption or missing permissions — it is a structural restriction on these object types.

Step-by-Step Resolution Guide

Step 1: Identify the Offending DDL Statement

  • Review the CREATE TYPE, DECLARE @table, or CREATE FUNCTION script that triggered Error 1993.
  • Check the T-SQL for a CREATE PARTITION SCHEME or ON [PartitionSchemeName] clause applied to an index defined inside a table variable or TVF RETURNS TABLE clause.
  • Use SQL Server Management Studio (SSMS) 21 “Include Actual Execution Plan” or sys.dm_exec_sql_text to isolate the exact statement from a stored procedure or script.

Step 2: Convert the Table Variable to a Temporary or Permanent Table

  • Replace DECLARE @MyTable TABLE (...) with a session-scoped temporary table: CREATE TABLE #MyTable (...).
  • Apply the partition scheme directly to the temporary or permanent table, since temp tables stored in tempdb support filegroup and partition scheme bindings (provided tempdb has multiple filegroups configured via SSMS > Database Properties > Filegroups).
  • If the object must persist, create a permanent staging table in a user database with the partition scheme applied, then load/query against it instead of a TVF.

Step 3: Refactor the Table-Valued Function to a Multi-Statement Procedure

  • If the error originates from an inline or multi-statement TVF’s RETURNS @ReturnTable TABLE (...), redesign the logic as a stored procedure that inserts into a pre-partitioned physical table.
  • Update any calling application or reporting layer (SSRS, Power BI, .NET Entity Framework) to call EXEC dbo.usp_GetData instead of SELECT * FROM dbo.fn_GetData().
  • Validate with sys.objects and sys.partitions that the new physical table shows multiple partition_number values once populated.

Step 4: Apply the Index Without Partitioning (If Partitioning Is Not Mandatory)

  • On the original table variable or TVF, remove the ON [PartitionScheme](PartitionColumn) clause and instead use ON [PRIMARY] or leave the clause omitted entirely.
  • Re-run the DDL in SSMS or via sqlcmd -S <ServerName> -i script.sql to confirm Error 1993 no longer appears.

Alternative or Verification Steps

  • Verify the fix by executing SELECT * FROM sys.messages WHERE message_id = 1993 to confirm the error no longer triggers after rerunning the corrected script in a fresh SSMS session.
  • As an alternative, use SQL Server 2025’s native support for memory-optimized table types (CREATE TYPE ... AS TABLE with MEMORY_OPTIMIZED = ON) for high-throughput scenarios, since these avoid partitioning constraints entirely while still improving performance over disk-based table variables.