SQL Server computed column select from another table

Question

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 alternate way of accomplishing my goal.

Answer 1

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] 

Answer 2

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.