Computed Column that contains ID of more recent child record

Using SQL Server, I'd like to create a Computer Column in a parent table that contains the ID of the most recent recod in a child table.

For example
PARENT TABLE: Topic
-------------------------------
ID: (integer, PK)
Name: (string)

CHILD TABLE: Comment
-----------------------------------
ID: (integer, PK)
TopicId: (integer, FK)
CreatedAt: (DateTime)
Body: (string)

Open in new window


I'd like to change the parent table to be like this:
PARENT TABLE: Topic
-----------------------------------
ID: (integer, PK)
Name: (string)
LatestCommentId: (Calculated Column, that's the PK of the more recent Comment/child record related to Topic/parent record)

Open in new window


Is this possible?
cdakzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
You could do that with an INSERT trigger on the child table.

Or just create a clustered key on TopicId or TopicId/CreatedAt in the comment table and resolve it at query time.  This would probably be my first choice.

Kent
0
cdakzAuthor Commented:
I don't want to do it with an INSERT trigger, because I'd rather that the parent always perform the query to be absolutely sure that it grabs the latest child.

I'm still new to clustered keys, but that seems expensive. Wouldn't the clustered key have to be re-created every time a new child record is added for that parent?
0
cdakzAuthor Commented:
Upon doing more research, I see that, by definition, Computed Columns can only use columns from the same table.

So I'm thinking that I'd need to do this with a View, but am struggling on the exact implementation. Can anybody provide the code to do this with a View?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kent OlsenData Warehouse Architect / DBACommented:
An INSERT trigger on the comments table would ensure that the last comment is recorded in the Topic table.  Depending on a lot of factors, this could also be the more expensive solution as it could force a read/write cycle on a page in the Topic table as well as the Comment table.  The overhead could also be trivial if the page is already cached.

The clustered index puts records of similar keys together.  Depending on your usage you may well want to do that anyway.  Then when you query the series of comments they are all read from disk in a single read.


Kent
0
Kent OlsenData Warehouse Architect / DBACommented:
Something like this:

SELECT *
FROM topic_table t0
INNER JOIN comment_table t1
   ON t0.id = t1.topic_id
WHERE (t0.name = 'Some Name')
  AND t1.CreatedAt = (SELECT MAX(CreateAd) from topic_table t2 where t1.topic_id = t2.topic_id)

Open in new window


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cdakzAuthor Commented:
Just what I needed. Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.