Click here to Skip to main content
12,455,517 members (56,797 online)
Click here to Skip to main content
Add your own
alternative version

Stats

88.6K views
517 downloads
29 bookmarked
Posted

Computed Column Specification in SQL Server

, 18 Jul 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Computed Column Specification for populating nth column
Screenshot - Formula_Image1.gif

Introduction

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.

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 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]))
//) 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.

License

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

Share

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.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 5 Pin
Dhritirao's18-Apr-13 0:11
memberDhritirao's18-Apr-13 0:11 
QuestionA bit light Pin
AndrewB-UK3-Sep-12 23:15
memberAndrewB-UK3-Sep-12 23:15 
GeneralMy vote of 5 Pin
Meedipak29-Jun-10 20:58
memberMeedipak29-Jun-10 20:58 
QuestionIs it possible to write inline functions ? Pin
jjhhgg829-Aug-08 2:59
memberjjhhgg829-Aug-08 2:59 
AnswerRe: Is it possible to write inline functions ? Pin
Padoor Shiras1-Sep-08 4:48
memberPadoor Shiras1-Sep-08 4:48 
GeneralHelped me in writing a complex code as computed column function Pin
vfer_4429-Aug-08 2:50
membervfer_4429-Aug-08 2: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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160826.1 | Last Updated 18 Jul 2007
Article Copyright 2007 by Padoor Shiras
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid