Thursday, November 15, 2012

Computed Column in SQL Server


How do you create Computed/Calculated column in SQL Server?
A computed column is computed based on an expression that can use other columns of the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators. However, the expression cannot be a sub-query.
 

Scenario: I want to create a computed column which should be calculated based on the values of other columns. For instance, Employee table has DateOfBirth column and I want to create a computed column to store the Age of an employee (in years). Another example could be to display the Grade of a student based on the marks obtained by the student.

Example 1: Employee (EmployeeID, EmployeeName, BasicSalary, DOB)
EmployeeID
EmployeeName
BasicSalary
DOB
1
Hari
5000
1983-05-05
2
John
3500
1978-10-25
3
Peter
4500
1982-02-10
4
Ravi
2500
1980-03-04
5
Max
3600
1985-09-20

Problem: Add a new computed column Age in Employee table.

Example 2: Student (StudentID, StudentName, MarksObtained)
EmployeeID
EmployeeName
MarksObtained
1
Hari
74
2
John
80
3
Peter
55
4
Ravi
34
5
Max
67

Problem: Add a new computed column Grade in Student table using following grading system:
If MarksObtained >= 75 then A
If MarksObtained >= 60 then B
If MarksObtained >= 40 then C
Else F
 




Example 1:

--------------------------------------
-- Prepare data
--------------------------------------
IF OBJECT_ID('dbo.Employee', 'U') IS NOT NULL   DROP TABLE dbo.Employee
GO

CREATE TABLE dbo.Employee (
     EmployeeID INT
    ,EmployeeName VARCHAR(30)
    ,BasicSalary SMALLINT
    ,DOB DATE
)
GO

INSERT dbo.Employee VALUES
 (1, 'Hari', 5000, '1983-05-05')
,(2, 'John', 3500, '1978-10-25')
,(3, 'Peter', 4500, '1982-02-10')
,(4, 'Ravi', 2500, '1980-03-04')
,(5, 'Max', 3600, '1985-09-20')


SELECT * FROM dbo.Employee
 








--------------------------------------
-- Add computed column
--------------------------------------
ALTER TABLE dbo.Employee
ADD Age AS DATEDIFF(YEAR, DOB, GETDATE())  

SELECT * FROM dbo.Employee



 

 
 
 


You can also explicitly define the data-type of the computed column if you want. You can use CAST/CONVERT to explicitly define the data-type.

--------------------------------------
-- Change data type of computed column
--------------------------------------
ALTER TABLE dbo.Employee DROP COLUMN Age
GO
ALTER TABLE dbo.Employee
ADD Age AS CAST(DATEDIFF(YEAR, DOB, GETDATE()) AS TINYINT)
 



 
Example 2:

--------------------------------------
-- Prepare data
--------------------------------------
IF OBJECT_ID('dbo.Student', 'U') IS NOT NULL   DROP TABLE dbo.Student
GO
CREATE TABLE dbo.Student (
     StudentID INT
    ,StudentName VARCHAR(30)
    ,MarksObtained TINYINT
)
GO
INSERT dbo.Student VALUES
 (1, 'Hari', 74)
,(2, 'John', 80)
,(3, 'Peter', 55)
,(4, 'Ravi', 34)
,(5, 'Max', 67)

SELECT * FROM dbo.Student
 
 
 
 






--------------------------------------
-- Add computed column
--------------------------------------
ALTER TABLE dbo.Student
ADD Grade AS
CASE
   WHEN MarksObtained >= 75 THEN 'A'
   WHEN MarksObtained >= 60 THEN 'B'
   WHEN MarksObtained >= 40 THEN 'C'
   ELSE 'F'
END
GO
   
 
SELECT * FROM dbo.Student
  
 









Note:
Computed columns can be used in select lists, WHERE clauses, ORDER BY clauses, or any other locations in which regular expressions can be used, with the following exceptions:
  • Computed columns used as CHECK, FOREIGN KEY, or NOT NULL constraints must be marked PERSISTED. A computed column can be used as a key column in an index or as part of any PRIMARY KEY or UNIQUE constraint if the computed column value is defined by a deterministic expression and the data type of the result is allowed in index columns.              For example, if the table has integer columns a and b, the computed column a + b can be indexed, but computed column a + DATEPART(DD, GETDATE()) cannot be indexed because the value may change in subsequent invocations. 
  • A computed column cannot be the target of an INSERT or UPDATE statement.



 

 

 

8 comments:

  1. Thank you for having taken your time to provide us with your valuable information

    relating to your stay with us.we are sincerely concerned.., Most importantly, you

    Keepit the major...
    SEO Training in Chennai

    ReplyDelete
  2. Wow amazing i saw the article with execution models you had posted. It was such informative.By explaining this type we can identify the concepts easily. So thank you for this sharing.


    Web Designing Training in Chennai Velachery

    ReplyDelete
  3. It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command .

    PPC Services in Chennai

    ReplyDelete
  4. Very nice post here and thanks for latest smartphone applications it .I always like and such a super colors of phone for these post.Excellent and very cool idea and great models and different kinds of the more information's.

    digital marketing company in india

    ReplyDelete
  5. Superb i really enjoyed very much with this article here. Really its a amazing article i had ever read. I hope it will help a lot for all. Thank you so much for this amazing posts and please keep update like this excellent article.

    Digital Marketing Company in Chennai

    ReplyDelete



  6. Very Useful information that i have found. don't stop sharing and Please keep updating us..... Thanks

    ReplyDelete
  7. A nice article here, i think that people who have grown up with the idea of using computers are showing more responsibility towards writing posts that are thoughtful, do not have grammar mistakes and pertinent to the post..
    Office Interior Designers in Coimbatore
    Office Interior Designers in Bangalore
    Office Interior Designers in Hyderabad

    ReplyDelete
  8. Thanks for posting such an interesting inforamtion, Please keep updates us and make us up to date....

    ReplyDelete