Create Computed Column using data from another table

Question

I have a SQL Server 2008 R2 database. This database has two tables called Pictures and PictureUse.

Picture table has the following columns:

Id (int)   
PictureName (nvarchar(max)) 
CreateDate (datetime )  

PictureUse table has the following columns :

Id (int) 
Pictureid (int) 
CreateDate (datetime )  

I need to create a computed column in the Picture table which tells me that how many times this picture has been clicked.any help ?

Answer 1

You can create a user-defined function for that:

CREATE FUNCTION dbo.CountUses(@pictureId INT)
RETURNS INT
AS
  BEGIN
      RETURN
        (SELECT Count(id)
         FROM   PictureUse
         WHERE  PictureId = @PictureId)
  END 

The computed column can then be added like this:

ALTER TABLE dbo.Picture
ADD NofUses AS dbo.CountUses(Id)

However, I would rather make a view for this:

CREATE VIEW PictureView
AS
  SELECT Picture.Id,
         PictureName,
         Picture.CreateDate,
         Count(PictureUse.Id) NofUses
  FROM   Picture
         JOIN PictureUse
           ON Picture.Id = PictureUse.PictureId
  GROUP  BY Picture.Id,
            PictureName,
            Picture.CreateDate 

Answer 2

A computed column may only reference other columns in the same table. You could (as per jeroenh's answer) use a UDF, but the column won't be stored or be indexable and so it has to be recomputed every time the row is accessed.

You could create an indexed view that contains this information (if, as I suspect, it's just the count of rows from PictureUse):

CREATE VIEW dbo.PictureStats
WITH SCHEMABINDING
AS
    SELECT PictureID,COUNT_BIG(*) as Cnt from dbo.PictureUse
GO
CREATE UNIQUE CLUSTERED INDEX IC_PictureStats on dbo.PictureStats (PictureID)

Behind the scenes, SQL Server will effectively create a table that contains the results of this view, and every insert, update or delete to PictureUse will maintain this results table automatically for you.