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 ?

score 31 · Accepted Answer · edited Nov 21 '12 at 08:31

31

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

edited Nov 21 '12 at 08:31

Martin Smith

438,706
87
741
845

answered Nov 21 '12 at 08:11

jeroenh

26,362
10
73
104

i tried your answer but its giving error when i am creating `function` and `Computed column` – Smartboy Nov 21 '12 at 08:26
should i create a column first or a function ? – Smartboy Nov 21 '12 at 08:27
1

@Smartboy `CREATE FUNCTION dbo.CountUses(INT @pictureId)` should be `CREATE FUNCTION dbo.CountUses(@pictureId INT)`. Also the function is missing `BEGIN .. END` and a `RETURN` – Martin Smith Nov 21 '12 at 08:28
1

@MartinSmith Can you fix the whole function its still giving error – Smartboy Nov 21 '12 at 08:31
thanks @MartinSmith - I didn't have a SQL parser at hand so I wrote the code from the top of my head :-) – jeroenh Nov 21 '12 at 08:36
@MartinSmith I created the function and column in table but its giving 1 on all Pictures `( NofUses = 1 )` in all picture rows , whats the problem ? – Smartboy Nov 21 '12 at 08:37
its ok @jeroenh , it happens :-) – Smartboy Nov 21 '12 at 08:37
@Smartboy - Don't know. Looks like it should work to me. Do those pictures have more than one entry in `PictureUse`? – Martin Smith Nov 21 '12 at 08:40
yes , `PictureUse` have almost 90,000 plus entries and i saw now that for few pictures `PictureUse` have 35 or 38 rows ... – Smartboy Nov 21 '12 at 08:42
1

Check your GROUP BY - are use grouping by Picture.CreateDate or by PictureUse.CreateDate? – jeroenh Nov 21 '12 at 09:16

score 11 · Answer 2 · edited May 23 '17 at 11:54

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.

+1 this is much better than an UDF when it comes to reads, the big caveat is that it serializes writes due to the lock on the count row. — Remus Rusanu, Nov 21 '12 at 08:56

score 0 · Answer 3 · answered Nov 21 '12 at 08:15

you dont have to add the computed column to your table, because, after its updated,if the original table data got changed, then data become inconsistent , you can always use this select statement to get the column count, or create it as a view

select p.id,count(*) as count 
from Picture P
join PictureUse  U
on p.id=u.Pictureid 
group by p.id

score -1 · Answer 4 · answered Nov 21 '12 at 08:26

-1

This will work

SELECT  P.id
        ,P.PictureName 
        ,COUNT(P.id) as [Count] 
        FROM Picture P
        INNER JOIN PictureUse PU 
        ON P.id=PU.Pictureid 
        GROUP BY P.id,P.PictureName

answered Nov 21 '12 at 08:26

Ranish

877
2
10
30

score -2 · Answer 5 · answered Nov 21 '12 at 08:12

-2

try this

select count(distict pictureid) from pictureuse 
 inner join picture on picture.id=pictureuse.pictureid

answered Nov 21 '12 at 08:12

SRIRAM

1,888
2
17
17

Create Computed Column using data from another table

5 Answers5

Linked