Systems Engineering and RDBMS

Computed Columns

Posted by decipherinfosys on April 3, 2007

As per database normalization rules, a computed columns (Column value is derived from the value(s) of some other columns) should not be stored in the schema. They should be always calculated at runtime like (Average, total etc.). But, at times, it is required to store such calculations in the schema – sometimes to help with performance issues at the time of the reads like calculating and storing whether a given date falls on a business day or on a weekend/list of holidays, storing complex mathematical calculations etc.. We ran into a situation for one of our client where we needed to store the reverse value of the one of the columns to enforce security in the system.  In this case, we decided to go for computed column since the runtime changes would have meant changes to the application and since the application was going to do reads on this column a lot.  We will show you how we have achieved this in Oracle, MS SQLServer and DB2.

Oracle does not support computed column like MS SQL Server but we can achieve similar functionality using a trigger. Using trigger we will store reverse value of col1 into col2.

Let us create the table first.

CREATE TABLE TEST_COMPUTE
(
COL1 VARCHAR2(15),
COL2 VARCHAR2(15)
);

Now let us create trigger and populate it with some data.

CREATE OR REPLACE TRIGGER TIB_TEST_COMPUTE
BEFORE INSERT ON TEST_COMPUTE
FOR EACH ROW
BEGIN

SELECT REVERSE(:NEW.COL1)
INTO :NEW.COL2
FROM DUAL;

END;
/

INSERT INTO TEST_COMPUTE(COL1) VALUES(‘DECIPHER’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘INFORMATION’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘SYSTEMS’);

In above trigger, we are using Oracle’s REVERSE function to reverse the string to store it in col2. Let us check the output now which shows col2 contains reverse value of col1.

SELECT * FROM TEST_COMPUTE;

COL1            COL2
————— —————
DECIPHER        REHPICED
INFORMATION     NOITAMROFNI
SYSTEMS         SMETSYS

MS SQL Server supports computed columns. It also has a REVERSE function, so in this case it is very straight forward to implement computed column.  Execute following SQL from query analyzer or management studio.

CREATE TABLE TEST_COMPUTE
(
COL1 VARCHAR(15),
COL2 AS REVERSE(COL1)
)
GO

Above table will populate col2 with reverse value of col1 when new data is inserted or updated in the table. Let us populate it with some data.

INSERT INTO TEST_COMPUTE(COL1) VALUES(‘DECIPHER’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘INFORMATION’);
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘SYSTEMS’);

Following SQL shows the result of affected rows.

SELECT * FROM TEST_COMPUTE;

COL1            COL2
————— —————
DECIPHER        REHPICED
INFORMATION     NOITAMROFNI
SYSTEMS         SMETSYS

SQLServer allows creation of index on computed columns. For complete list of requirements/ rules governing index creation on computed columns, please refer to BOL.

DB2 LUW also provides computed columns but computed columns are widely known as “expression generated” columns and are created using syntax ‘GENERATED ALWAYS AS’ syntax.  Unlike Oracle and SQLServer it does not have REVERSE function so we need to define one user defined function to reverse the string.

Let us first create user defined function called reverse and then we will use it in our table.

CREATE FUNCTION REVERSE(V_STR varchar(100))
RETURNS VARCHAR(100)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE V_OUTSTR VARCHAR(100);
DECLARE V_LEN INT;

SET V_OUTSTR= ”;
SET V_LEN = length(V_STR);

WHILE V_LEN > 0
DO
SET V_OUTSTR = V_OUTSTR || SUBSTR(V_STR,V_LEN,1);
SET V_LEN = V_LEN -1;
END WHILE;

RETURN V_OUTSTR;
END
@

Execute above code from command editor to create the reverse function. We will use this function to create the table with generated column. Make sure to set statement terminator as ‘@’.

One thing to remember is while creating function, please make sure that function is created with ‘CONTAINS SQL’ clause. By default function is created with ‘READS SQL DATA’ clause so if function is not created using ‘CONTAINS SQL’ clause, we will not be able to use it in GENERATED column because ‘READS SQL DATA’ is not allowed in GENERATED expression and we will run into following error.

SQL0548N  A check constraint or generated column that is defined with
“REVERSE” is invalid.  SQLSTATE=42621

Once function is created, let us create table and populate it with some data.

CREATE TABLE TEST_COMPUTE
(
COL1 VARCHAR(15),
COL2 VARCHAR(15) GENERATED ALWAYS AS (REVERSE(COL1))
)
@

INSERT INTO TEST_COMPUTE(COL1) VALUES(‘DECIPHER’)@
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘INFORMATION’)@
INSERT INTO TEST_COMPUTE(COL1) VALUES(‘SYSTEMS’)@

Let us check the result by querying against the table. Issue following command from command editor and it is followed by result.

SELECT * FROM TEST_COMPUTE@

COL1            COL2
————— —————
DECIPHER        REHPICED
INFORMATION     NOITAMROFNI
SYSTEMS         SMETSYS

3 Responses to “Computed Columns”

  1. […] is different in each one of them. For details on computed columns, look at our previous blog post […]

  2. […] name. You can read more about computed columns (virtual columns) in one of our previous blog post here. In Oracle 10g and prior releases, the only way we can achieve computed column functionality is via […]

  3. […] You can search for “computed column” and get to all those posts – the basic ones are here and here. A few days ago, a reader had asked whether we can create a computed column based on an […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: