Computed Column Specification in SQL Server

• Download CreateFunction AddTwoValues - 314 B
• Download CreateTable - 414 B

Introduction

This article will help the user to write a complex calculation in the backend (SQL SERVER) and populate the n^th column in the table. Since the code snippet is written in the backend, the performance for a complex calculation is high.

Background

I believe the users of this article have got enough knowledge in writing SQL functions. I have written a function which will take two parameters as input and add the parameter values into the result. This computed value is then inserted into the n^th column of my table where my SQL query for insertion will have only two values.

Using the Code

I have created a very basic example to show the usage of "Computed Column Specification". Initially I created a Table which was named as "AddValues" which has three columns of datatype INT, my columns were named firstvalue, secondvalue and total. My idea was to insert firstvalue and secondvalue where the thirdcolumn named total will be populated depending on the function which I have written automatically.

Let me first create a simple table with three columns as below. Execute the below in the query analyzer of SQL Server:

//
//CREATE TABLE [dbo].[AddValues](
//    [firstvalue] [int] NOT NULL,
//    [secondvalue] [int] NOT NULL,
//    [total]  AS ([dbo].[AddTwoValues]([firstvalue],[secondvalue]))
//) ON [PRIMARY]
//

The next step is to create a function which will do a calculation in-order to populate our third column. In my example, I have written a function which takes two parameters and adds the values. My function returns integer values. Users can create their own complex functions.

//
//ALTER FUNCTION [dbo].[AddTwoValues](@firstval INT,@secondval INT)
//RETURNS INT
//AS
//    BEGIN
//        DECLARE @Result INT
//        SET @Result = @firstval + @secondval
//        RETURN 
//        (
//            @Result
//        )
//    END
//

My next step is to write the function name along with the parameters which I need to pass. The user needs to select the column and write in "Computed Column Specification --> Formula". Remember the parameters are the column names, users are not supposed to use '@' symbol here.

Formula -- Displays the formula for the computed column. To edit this property, type a new formula directly.

Is Persisted -- Indicates whether the results of the formula are stored. If this property is set to No, then only the formula is stored and the values are calculated every time this column is referenced. To edit this property, click its value, expand the drop-down list, and choose another value.

Now you may execute the insert query and look at the result. You can use the simple insert query as below:

//
//INSERT INTO AddValues VALUES(1,2)
//

History

As I get queries from users, I will be updating the article.

About Proteans Software Solutions

Proteans is an outsourcing company focusing on software product development and business application development on Microsoft Technology Platform. Proteans partners with Independent Software Vendors (ISVs), System Integrators and IT teams of businesses to develop software products. Our technology focus and significant experience in software product development - designing, building, and releasing world-class, robust and scalable software products help us to reduce time-to-market, cut costs, reduce business risk and improve overall business results for our customers. Proteans expertise's in development using Microsoft .NET technologies.