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.



 

 

 

19 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. 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



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

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

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Fertility is the natural capability to produce offspring. As a measure, fertility rate is the number of offspring born per mating pair, individual or population.Human fertility depends on factors of nutrition, sexual behavior, consanguinity, culture, instinct, endocrinology, timing, economics, way of life, and emotions.Greate thinks of a fertility center for humans.

    Fertility Center in OMR

    ReplyDelete
  8. thank your valuable content.we are very thankful to you.one of the recommanded blog.which is very useful to new learners and professionals.content is very useful for hadoop learners


    Best Spring Classroom Training Institute
    Best Devops Classroom Training Institute
    Best Corejava Classroom Training Institute
    Best Oracle Classroom Training Institute
    Best Oracle Classroom Training Institute

    ReplyDelete

  9. NICE for giving a chance to share ideas for your comuty i really thanks for that great post. Still Hunting Method
    Hunting psych tips Survival Tips Travel Touring Tips

    ReplyDelete
  10. Worthful C#.Net tutorial. Appreciate a lot for taking up the pain to write such a quality content on C#.Net tutorial. Just now I watched this similar C#.Net tutorial and I think this will enhance the knowledge of other visitors for sure C#.Net online training C#.Net Online Course

    ReplyDelete
  11. This site helps to clear your all query. allahabad university BA 3rd year result
    mgsu ba 1st year result This is really worth reading. nice informative article.

    ReplyDelete
  12. Hi, Thank you for this informative blog, I have just started to learn sql online and this blog is really informative for me. Thank you for this blog!

    ReplyDelete
  13. nice sql article these is i have never seen
    Azure Data Factory course in Ameerpet
    ,Azure Data Engineer Training Online Hyderabad
    ,azure training in hyderabad ameerpet
    ,Azure Data Factory online Training in Hyderabad,
    Microsoft Azure Data Factory in hyderabad

    ReplyDelete