SQL Server computed column select from another table

I'm not to sure what the best way to go about this is, so i'll describe what the end goal is, and if a computed column is the answer then please help me go that route, or perhaps a better route.

I have two tables:

Orders

OrderId
PackageId
MediaSpend
TotalAdViews (Computed column)

Packages

PackageId
BaseAdViews

Each order is assigned a package that comes with say 1,000 views, you can then buy more media spend to get more views. I wanted to create a column called TotalAdViews which would add BaseAdViews + MediaSpend. From my understanding if persistance is enabled the column won't need to recalculate every time it is queried, which could help performance.

How do I get a value from another table in my computed column? Or please suggest an alternat开发者_运维知识库e way of accomplishing my goal.

I know this answer comes two years late, but just to help anyone who googles and finds this post:

It is perfectly legal to define a user-defined function and use it as the computed value. This function may contain select statements from other tables.

CREATE FUNCTION dbo.getAdViews(@packageId int)
RETURNS INT
AS
BEGIN
    declare @bav int
    select @bav = BaseAdViews from Packages where PackageId = @packageId
    RETURN @bav
END

Then in your computed column, just use the expression dbo.getSumAdViews(PackageId)+MediaSpend as so:

CREATE TABLE [dbo].[Orders](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [PackageId] [int] NOT NULL,
    [MediaSpend] [int] NULL,
    [TotalAdViews] AS dbo.getAdViews(PackageId)+MediaSpend
) ON [PRIMARY]

You won't be able to use columns from another table within a computed column expression. This is an extract from the MSDN documentation.

A computed column is computed from an expression that can use other columns in the same table.

You mentioned that your motivation for using a computed column was to increase performance. There are a lot of restrictions but an indexed view might add value here.

if it is only for display purposes,why not create a view..

select <<add other columns you need>> mediaspend+basicadviews as totaladviews
from 
orders o
join
packages p
on p.packageid=o.orderid