I've been hard at work on the commenting system for weblog posts in the last day and am nearly finished. 
 
The goal was to create a hierarchical structure that would allow for people to post comments, reply to comments, and correctly display them in a hierarchical manner.  I had worked with such hierarchical structures before within SQL Server 2000, but was happy to see that SQL Server 2005 makes such work much easier.
 
Here's a simplistic view of the comments table that I used:
 
 commentID
  commentText  userID  commentParentID
 1  Parent Comment
  testUser  NULL
 2  Child Comment
 testUser2
 1
 3  Child Comment 2
  testUser3
 2
 
As you can see from the above table, commentID of 1 is a parent comment.  CommentID of 2's parent is 1.  CommentID of 3's parent is 2.  The structure is easy to implement, but querying the results so that they come back in the proper order allowing for comment nesting was somewhat of a learning experience as SQL Server 2005 does things a little bit differently than 2000.
 
At any rate, the SQL query looks like this:
 

WITH Hierarchy
(
[id], [commentDate], [commentAuthor], [commentAuthorID], [commentTitle],
[commentText], [commentParentID], [commentModifiedDate], [postID], [ipAddress], [active]
,[modifiedBy], [modifiedByUserID], [modifiedByIPAddress], HLevel, theOrder
)

AS
(
SELECT
[id], [commentDate], [commentAuthor], [commentAuthorID], [commentTitle],
[commentText], [commentParentID], [commentModifiedDate], [postID], [ipAddress], [active]
,[modifiedBy], [modifiedByUserID], [modifiedByIPAddress], 0 as HLevel,
CAST(ROW_NUMBER() OVER (ORDER BY [id]) AS VARCHAR(255))
FROM [tbl_Comments]
WHERE
[commentParentID] is NULL

UNION ALL

SELECT
SubComment.[id], SubComment.[commentDate], SubComment.[commentAuthor],
SubComment.[commentAuthorID], SubComment.[commentTitle],
SubComment.[commentText], SubComment.[commentParentID], SubComment.[commentModifiedDate],
SubComment.[postID], SubComment.[ipAddress], SubComment.[active], SubComment.[modifiedBy],
SubComment.[modifiedByUserID],
SubComment.[modifiedByIPAddress],
HLevel + 1,
CAST([theOrder] + '/' + CAST(ROW_NUMBER() OVER (ORDER BY SubComment.[id]) AS VARCHAR(255)) AS VARCHAR(255))
FROM [tbl_Comments] SubComment
INNER JOIN Hierarchy ParentComment
ON SubComment.[commentParentID] = ParentComment.[id]
)

SELECT
[id], [commentDate], [commentAuthor], [commentAuthorID],
[commentTitle],
[commentText], [commentParentID], [commentModifiedDate], [postID], [ipAddress], [active],
[modifiedBy], [modifiedByUserID], [modifiedByIPAddress], [commentParentID],
HLevel, theOrder
FROM Hierarchy
WHERE
[postID] = COALESCE(@postID, [postID])
AND [id] = COALESCE(@id, [id])
AND [active] = COALESCE(@active, [active])
ORDER BY
[theOrder]