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
I'd like to change the parent table to be like this:
Is this possible?
For example
PARENT TABLE: Topic
-------------------------------
ID: (integer, PK)
Name: (string)
CHILD TABLE: Comment
-----------------------------------
ID: (integer, PK)
TopicId: (integer, FK)
CreatedAt: (DateTime)
Body: (string)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)Is this possible?
ASKER
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?
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?
ASKER
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?
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?
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Just what I needed. Thanks!
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