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.



 

 

 

26 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
  9. Its very useful to me. Wonderful blog.. Thanks for sharing informative Post.

    Installment loans
    Payday loans
    Title loans

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

    ReplyDelete
  11. 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
  12. I have really happy to these reading your post. This product control and maintenance of our health.The daily routine can assist you weight lose quickly and safely.My life is completely reworked once I followed this diet.I feeling nice concerning myself.

    Herbalife in Chennai
    Herbalife Nutrition Products
    Nutrition centers in Chennai
    Weight Loss in Chennai
    Weight Gain in Chennai

    ReplyDelete
  13. BIG DATA Technologies provides you with a state of the art software which combines modern GPU technology (Graphic Processing Units) with the best practices in today’s Big Data platforms, providing up to 100x faster insights from data.
    Bigdata Training in Chennai OMR

    ReplyDelete
  14. Testers can build, enhance, and maintain scripts to regression test their mobile applications. Hands-on instruction is provided for those who want to explore the power of using Appium. The course covers content from installation to execution and reporting . The focus is on the practical application of Appium to resolve common mobile automated testing challenges. This course focuses on getting started with Appium.
    course/appiumtraininginsholinganallur/

    ReplyDelete
  15. Worthful Sql server tutorial. Appreciate a lot for taking up the pain to write such a quality content on SQL server tutorial. Just now I watched this similar
    Sql Server tutorial and I think this will enhance the knowledge of other visitors for sureSql Server Online Training

    ReplyDelete
  16. 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

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

  18. Thank you for such a sweet tutorial - all this time later, I've found it and love the end result. I appreciate the time you spent sharing your skills.
    travel trekking tips
    see the link Tent Camping 101 Exploring Smithriver

    ReplyDelete
  19. Thanks For Sharing The Information The Information Shared Is Very Valuable Please Keep Updating Us Time Just Went On Reading The article sql server Online Course

    ReplyDelete
  20. 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