Troubleshooting Guide: SQL Server Error 2709 — Column Cannot Be Used in Index Due to Data Access

Why This Error Happens

This error occurs when you attempt to create an index, statistics, or a partition key on a computed column that internally calls a function (scalar UDF, CLR function, or system function) flagged as performing user or system data access. SQL Server’s Query Optimizer requires that any column used in an index be deterministic and free of side effects, since indexed values must remain stable and reproducible without querying external data. The engine detects the IsDataAccess or SystemDataAccess property on the underlying function definition and blocks indexing to prevent inconsistent or stale index entries.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column and Function

• Run the following query to locate computed columns and their definitions:

  SELECT name, definition FROM sys.computed_columns WHERE object_id = OBJECT_ID('YourTableName');

• Check the referenced function using sys.sql_modules or sys.assembly_modules to confirm uses_database_data or data_access = 1.
• For CLR functions, inspect the SqlFunction attribute in Visual Studio and look for DataAccess = DataAccessKind.Read.

Step 2: Remove Data Access Dependency from the Function

• Rewrite the scalar function to eliminate any SELECT, OPENROWSET, or system catalog view references (e.g., sys.objects, sys.tables).
• Replace data-dependent logic with static T-SQL expressions, built-in deterministic functions (e.g., ABS(), DATEPART()), or pass required values in as parameters instead of querying them internally.
• For CLR functions, change the attribute to DataAccessKind.None once all internal queries are removed, then redeploy the assembly:

  ALTER ASSEMBLY YourAssemblyName FROM 'C:\Deployments\YourAssembly.dll' WITH PERMISSION_SET = SAFE;

Step 3: Mark the Computed Column as Deterministic

• Drop and recreate the computed column definition after the function fix:

  ALTER TABLE YourTableName DROP COLUMN YourComputedColumn;
  ALTER TABLE YourTableName ADD YourComputedColumn AS dbo.YourFunction(InputColumn) PERSISTED;

• Verify determinism using:

  SELECT COLUMNPROPERTY(OBJECT_ID('YourTableName'), 'YourComputedColumn', 'IsDeterministic');

• A return value of 1 confirms the column is now eligible for indexing.

Step 4: Recreate the Index or Statistics Object

• Attempt the original CREATE INDEX or CREATE STATISTICS statement again:

  CREATE INDEX IX_YourIndex ON YourTableName (YourComputedColumn);

• If using the column as a partition key, re-run the CREATE PARTITION FUNCTION script referencing the corrected column.

Alternative or Verification Steps

• Confirm the fix by querying sys.computed_columns and checking that is_deterministic = 1 and is_precise = 1 for the affected column.
• If modifying the function isn’t feasible (e.g., third-party assembly), use a regular non-computed column populated via a trigger or ETL process instead, then index that standard column directly.