Computed columns
Emulation context
In SQL Server, tables can define computed columns, which are virtual columns whose values are derived from expressions based on other columns in the same row. These columns are defined using the AS (<expression>)
syntax and may optionally be marked as PERSISTED
. Computed columns can be read in queries but are not directly writable.
For example:
In Microsoft Fabric Warehouse, computed columns are not supported in table definitions. They cannot be declared using AS
, and there is no equivalent construct for inline virtual columns. As a result, computed columns must be emulated during migration.
Emulation strategy
SQL Tran emulates computed columns by removing them from the table definition and reintroducing them through a view. The emulation process includes the following steps:
Commenting out computed columns in the translated
CREATE TABLE
statement using/* SQLTRAN FIX */
.Generating a companion view with the suffix
_computed
, which:Selects all base columns using
SELECT *
Adds the computed expressions as derived columns
Rewriting all code that references the original table to instead reference the generated view.
Preserving the names and expressions of computed columns within the view to maintain query compatibility.
Supporting computed columns based on basic expressions and scalar user-defined functions (UDFs), when supported by Fabric.
Treating
PERSISTED
as a non-operative annotation (ignored but preserved in comments).
This transformation enables queries and procedures to behave as if computed columns still exist, even though they are now derived dynamically via a view.
Code example
SQL Server:
Fabric Warehouse (generated by SQL Tran):
Important notes
View-based emulation: All computed columns are removed from the table and moved into a view that exposes them as derived expressions. This ensures that downstream queries remain compatible without needing code changes.
Module rewrites: SQL Tran rewrites stored procedures, views, or scripts to reference the computed-column view instead of the base table, even if the computed columns are not directly used in the query.
Expression dependencies: Computed columns that reference other computed columns are carried over without resolving dependencies. This results in invalid SQL because Fabric does not allow referencing aliases within the same
SELECT
list. SQL Tran does not inline the expression, so the generated view will fail to compile unless manually corrected.Scalar UDFs in expressions: Computed columns that call scalar user-defined functions are preserved in the view if the function is available. However, SQL Tran does not currently validate UDF compatibility with Fabric or emit warnings.
CLR-based computed logic: Computed columns that depend on CLR functions are not supported. SQL Tran does not remove or flag these expressions, and no view is created, which may result in runtime errors in Fabric.
Invalid DML rewrite:
INSERT
orUPDATE
statements that target computed columns are rewritten against the view and retain invalid column references. These cases require manual correction. Application code may need to be modified to write directly to the base table instead of the view.Index loss: Indexed computed columns, including those marked as
PERSISTED
, are not preserved. Manual reimplementation using Fabric-compatible indexing strategies may be required for performance-critical workloads.
Last updated