Blog: Converting Oracle virtual columns to Microsoft SQL Server

In Oracle, you can specify virtual columns in a table definition. Oracle Database doesn’t store these virtual columns on the disk. Instead, the values of Oracle virtual columns are always calculated automatically. Oracle uses a set of expressions or functions to derive the values in the virtual column based on the values of other columns.

SQL Server does have computed columns on a table that provides support similar to Oracle’s virtual columns. By default, SQL Server does not store these columns in the table unless you mark them as persisted. Making the calculated column persisted is an optimization in SQL Server that Oracle doesn’t have so that SQL Server doesn’t always have to compute the value at runtime.

The problem

When migrating your Oracle database to Microsoft SQL Server, you need to convert virtual columns to computed columns. However, this is not the case if you use the SQL Server Migration Assistant (SSMA). By default, SSMA converts Oracle virtual columns as DEFAULT expression. Long story short, this approach will result in an error and the converted code will not work on SQL Server.

The problem is that in SQL Server, you can not use the table columns in the DEFAULT expression. In the default expression you can use one of the following values:

  • A constant value or NULL
  • A scalar function (either a system, user-defined, or CLR function)

So, when you try to synchronize the converted code with SQL Server database, you get the following error:
Convert Oracle Virtual Columns to Microsoft SQL Server using SSMA

Original Oracle code

Let us consider the following example. The source Oracle script includes 2 virtual columns and looks as follows:

1
2
3
4
5
6
7
8
9
10
CREATE TABLE TEST_ORG_ORACLE_SQL.T_VIRTUAL_COLS (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  virt_salary1     AS (ROUND(salary*(1+comm1/100),2)),
  virt_salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT pk_t_virtual_cols PRIMARY KEY (id));

Converted SQL Server script using SSMA

SSMA converts the original virtual columns to DEFAULT functions. However, SQL Server can not execute these functions as they use other columns of the table. So, the converted code looks as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE [dbo].[T_VIRTUAL_COLS]
(  [ID] FLOAT(53)  NOT NULL,
   [FIRST_NAME] VARCHAR(10)  NULL,
   [LAST_NAME] VARCHAR(10)  NULL,
   [SALARY] NUMERIC(9, 2)  NULL,
   [COMM1] NUMERIC(3, 0)  NULL,
   [COMM2] NUMERIC(3, 0)  NULL,
   [VIRT_SALARY1] FLOAT(53)  NULL,
   [VIRT_SALARY2] FLOAT(53)  NULL)
WITH (DATA_COMPRESSION = NONE)
GO

ALTER TABLE [dbo].[T_VIRTUAL_COLS] ADD CONSTRAINT [PK_T_VIRTUAL_COLS]
   PRIMARY KEY CLUSTERED ([ID] ASC)
GO

ALTER TABLE  [dbo].[T_VIRTUAL_COLS]
 ADD DEFAULT round(SALARY * (1 + COMM1 / 100), 2) FOR [VIRT_SALARY1]
GO

ALTER TABLE  [dbo].[T_VIRTUAL_COLS]
 ADD DEFAULT round(SALARY * (1 + COMM2 / 100), 2) FOR [VIRT_SALARY2]
GO

Correcting the generated script using Computed Columns

As I mentioned above, you should normally use computed columns in SQL Server as the equivalent of Oracle virtual columns. So, you need to manually update the converted code by removing the DEFAULT expression and adding the definition of computed columns. So, the updated SQL Server code will look as follows:

1
2
3
4
5
6
7
8
9
CREATE TABLE [dbo].[T_VIRTUAL_COLS]
(  [ID] FLOAT(53)  NOT NULL,
   [FIRST_NAME] VARCHAR(10)  NULL,
   [LAST_NAME] VARCHAR(10)  NULL,
   [SALARY] NUMERIC(9, 2)  NULL,
   [COMM1] NUMERIC(3, 0)  NULL,
   [COMM2] NUMERIC(3, 0)  NULL,
   [VIRT_SALARY1] AS round(SALARY * (1 + COMM1 / 100), 2),
   [VIRT_SALARY2] AS round(SALARY * (1 + COMM2 / 100), 2)

So, you need to use caution as well as the above-mentioned best practices when converting Oracle tables with virtual columns to Microsoft SQL Server.