Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
There is a column that you can add to your tables that comes in handy from time-to-time and really it's not a real column at all. What it is, is a calculated or computed column. So let's say for example we're working with this OrderItem table, and I've got multiple entries here. I've only got 20 or so but we could have 50,000 of them that deal with ProductIDs, Quantities and UnitPrice. Or you might find it useful to have kind of a rolling total of each order item going, particularly if you've got a lot of separate quantities.
So instead of having to load this data into an application to then perform a calculation, it just might be nice if each order item entry had its own little subtotal. So we can do that of course. We could just add a regular column. I'll go into that table into Design view and I'll add a new column called Total. I'll make it a money column, save my changes, I've allowed Null so won't complain about that right now, and then I go back in and try and edit directly in SQL Server Management Studio.
One order item, which is 1 of 12.32 would be 12.32, 1 of 234 would be 234, and so on. The issue is there is nothing that's actually stopping me here from typing in 50,000, and now there is no relationship between the total and the unit price and quantity, and there really should be. And it's for this reason that in general database design, not just in SQL Server but in overall database design, you should never be creating a column that stores a separate value that can be calculated based on other columns.
So we don't want to do it this way. However, we can still get the result that we want. I am going to go back into Design, come down into the Total of money, and what I'm going to do is come down into the Column Properties and this is the part that I'm interested in, Computed Column Specification. But I am telling it, don't store this data separately. Just figure it out every time I ask for one of these rows. And it gives a place to type in a formula, which for us is very simple. It's simply Quantity * UnitPrice.
Is Persisted mean is saved No. Just leave that at the default and what that actually means is I'll save this, and there I was talking about making changes to two tables simply because I have a relationship between this one and the Product table. But when I now go into OrderItem and click Edit the Top 200 Rows I can see that total is automatically doing this totaling of the Quantity * UnitPrice. So I've got 3 of 74.12, 222.36. It shows up in this kind of a lighter brown color as does the primary key, giving us the hint that we're not supposed to directly enter in anything. In fact if we do it will be ignored.
So a computed column result is actually recalculated when we retrieve, when we ask for that data back, but it can be very useful or at least very convenient for the data in your tables.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 87122 Viewers
80 Video lessons · 136238 Viewers
59 Video lessons · 54909 Viewers
52 Video lessons · 68794 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.