.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

The Power of Computed Columns

Posted By:Jean Paul       Posted Date: June 20, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Sql Server - Computed Columns usage tutorial. The Computed Columns remains to be an unused feature in Sql Server. But if efficiently used, this can give us good flexibility and speed over the traditional approach
 

The Computed Columns remains to be an unused feature in Sql Server.  But if efficiently used, this can give us good flexibility and speed over the traditional approach.

What is Computed Column?

Computed Columns are those columns whose values are not stored in the table.  Instead they will be calculated dynamically.  They will be having a formula to set and it could be a Function, Other Column Reference or a Constant Value.

In the figure below, we can see that ProjectName is a Computed Column.  The Column Properties sets the column specification to a user defined method call named GetProjectName() passing the ProjectId as argument.



Let us see how we can benefit from it.

Example

We have two tables in this example - Employee and Project.  The tables are related through the ProjectId column as shown below:



The Problem

We need to get the ProjectName of each Employee without using join statement as the join statement will create alterations in sql statement.

The Solution

Here we can introduce the computed column ProjectName which in turns calls a user defined function to get the actual project name based on the ProjectId.
The following is the User Defined Function to achieve the same:

CREATE FUNCTION [dbo].[GetProjectName]
(
  @Id int
)
RETURNS varchar(50) AS
BEGIN
  DECLARE @Result varchar(50)

SET @Result = (SELECT Name FROM Project WHERE Id=@Id)

  RETURN @Result
END

Advantages

The following advantages can be identified from the above scenario:

  • Less Overheads - While connecting with ORM tools we do not need to call specific methods to invoke the table or filter on the column. 
  • Speed Optimization on Filtering- As the calculation and filtering done on the server side only the required rows are passed over the wire.  Otherwise without computed column, the whole table will be fetched and processed thereafter filtering - this will increase the network traffic as well.

Testing It - Creating Data

After creating the necessary tables and function, we can use the following insert sqls to create the data.  (the associated sql files are attached with the article)

-- Create data in Project table
INSERT INTO [Project] ([Id], [Name]) VALUES (1, 'Project1');
INSERT INTO [Project] ([Id], [Name]) VALUES (2, 'Project2');
INSERT INTO [Project] ([Id], [Name]) VALUES (3, 'Project3');
GO

-- Create data in Employee table
INSERT INTO [Employee]([Id], [Name], [ProjectId]) VALUES (1, 'Employee1', 1);
INSERT INTO [Employee]([Id], [Name], [ProjectId]) VALUES (2, 'Employee2', 2);
INSERT INTO [Employee]([Id], [Name], [ProjectId]) VALUES (3, 'Employee3', 3);
INSERT INTO [Employee]([Id], [Name], [ProjectId]) VALUES (4, 'Employee4', 1);
INSERT INTO [Employee]([Id], [Name], [ProjectId]) VALUES (5, 'Employee5', 2);

Testing It - Selecting Data

SELECT * FROM Employee;



From the above result we can see that the ProjectName column is returning values appropriately.

Note

The example provided above is a simple scenario.  In real world, we can address more complex issues like looking up multiple tables to get the required column value.  This could be done through the same User Defined Function and associated Computed Column.


 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend