SQL Tran Docs
  • Overview
    • About
    • Getting started
    • Object lifecycle
    • Why is there no AI inside?
    • Performance considerations
    • Security considerations
    • Assessment
    • Unlocking projects
    • Interface walk-through
    • Translation scenarios
    • Prerequisites for Azure Marketplace deployment
  • Emulation Scenarios
    • Emulation in SQL Tran
    • SQL Server to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Cursors
        • Basic cursor loop
        • Cursor types
        • Fetch direction modes
        • Cursors in control flow
        • Nested cursors
        • Data modification cursors
        • Multiple cursors
        • Subqueries and filtering
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Computed columns
      • External tables
      • Identity columns
      • Default values
      • Sequences
      • Unsupported system objects
    • Synapse Analytics to Fabric Warehouse
      • Emulations
      • Limitations
    • SQL Server to Synapse Analytics
    • Oracle to PostgreSQL
  • Project wizard
    • Source database
    • Target database
    • Wrapping up
  • Projects
    • Project list
    • Overview
    • Workspace
    • Reports
    • Tests
    • Scratch pad
    • Settings
      • Project name
      • Mapping
      • Database connections
    • Navigation
    • Object complexity
    • Static analysis
    • Translation errors
    • Exporting and importing projects
  • Workspace
    • Object tree
    • Data lineage
    • Code
    • Actions
      • Overriding source
      • Overriding target
      • Ignoring objects
  • Tests
    • Workflow
    • Configure SQL Tran
    • Connecting to databases
      • Fabric Warehouse
      • Synapse Dedicated SQL Pool
      • Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server
    • Tables
    • Views
    • Procedures
    • Functions
    • Triggers
    • Performance tests
  • Scripter
    • About
    • Supported databases
    • SQL Server
    • Azure SQL
    • Synapse Dedicated Pool
    • Oracle
    • PostgreSQL
    • MySQL
Powered by GitBook
On this page
  • Emulation context
  • Emulation strategy
  • Code example
  • Important notes
  1. Emulation Scenarios
  2. SQL Server to Fabric Warehouse

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:

CREATE TABLE Products (
    Price decimal(10, 2),
    Tax decimal(10, 2),
    TotalPrice AS (Price + Tax)
);

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:

CREATE TABLE [HumanResources].[Products] (
	ProductID int,
	Name varchar(20),
	Descr varchar (50),
	Price decimal(10, 2),
	Tax decimal(10, 2),
	TotalPrice AS (Price + Tax),
	FullDesc AS CONCAT(Name, ' ', Descr)
);

CREATE PROCEDURE [dbo].[uspGetProducts]
	@ProductID [int]
AS
BEGIN
	SELECT *
	FROM [HumanResources].[Products] e 
	WHERE e.[ProductID] = @ProductID
END;

Fabric Warehouse (generated by SQL Tran):

CREATE TABLE [HumanResources].[Products] (
	ProductID int,
	Name varchar(20),
	Descr varchar (50),
	Price decimal(10, 2),
	Tax decimal(10, 2),
	/* SQLTRAN FIX: TotalPrice AS (Price + Tax) */
	/* SQLTRAN FIX: FullDesc AS CONCAT(Name, ' ', Descr) */
);

CREATE VIEW HumanResources.vProducts_computed
AS
SELECT *,
	(Price + Tax) AS TotalPrice,
	CONCAT(Name, ' ', Descr) AS FullDesc
FROM
	[HumanResources].[Products];
CREATE PROCEDURE [dbo].[uspGetProducts]
	@ProductID [int]
AS
BEGIN
	SELECT *
	FROM HumanResources.vProducts_computed /* SQLTRAN FIX: [HumanResources].[Products] */ e 
	WHERE e.[ProductID] = @ProductID
END;

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 or UPDATE 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.

PreviousMERGE statementsNextExternal tables

Last updated 5 days ago

Computed columns emulation in SQL Tran