Click here to Skip to main content
15,123,367 members
Articles / Database Development / SQL Server
Posted 18 Jul 2007


29 bookmarked

Computed Column Specification in SQL Server

Rate me:
Please Sign up or sign in to vote.
3.45/5 (29 votes)
18 Jul 2007CPOL2 min read
Computed Column Specification for populating nth column
Screenshot - Formula_Image1.gif


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


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 nth 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]))

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)
//    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:



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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Padoor Shiras
Web Developer Proteans Software Solutions Pvt Ltd.
India India
Shiras AbdulRahman Currently working with Proteans Software Solutions Bangalore.

Proteans a CAMO group company is an outsourcing company focusing on software product development and business application development on Microsoft Technology Platform. "Committed to consistently deliver high-quality software products and services through continual improvement of our knowledge and practices focused on increased customer satisfaction.

Comments and Discussions

GeneralMy vote of 5 Pin
Dhritirao's18-Apr-13 1:11
MemberDhritirao's18-Apr-13 1:11 
QuestionA bit light Pin
AndrewB-UK4-Sep-12 0:15
MemberAndrewB-UK4-Sep-12 0:15 
GeneralMy vote of 5 Pin
Dipak Y Salve29-Jun-10 21:58
MemberDipak Y Salve29-Jun-10 21:58 
QuestionIs it possible to write inline functions ? Pin
jjhhgg829-Aug-08 3:59
Memberjjhhgg829-Aug-08 3:59 
AnswerRe: Is it possible to write inline functions ? Pin
Padoor Shiras1-Sep-08 5:48
MemberPadoor Shiras1-Sep-08 5:48 
GeneralHelped me in writing a complex code as computed column function Pin
vfer_4429-Aug-08 3:50
Membervfer_4429-Aug-08 3:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.