I have an Indexed View question.<br /><br />Imagine a forums application, running off a SQL Server database - a bit like this one <img src='/community/emoticons/emotion-1.gif' alt='' /><br /><br />You have a Forums, Topics, and Posts table*<br /><br />When you display the forums list, you also display information on how many topics, and how many posts there in each forum. <br /><br />The way I have done this before is use sub queries in the stored procedure that gets the forums data. One sub query will count the topics, and one will count the posts. The performance is ok, but not fast - I am looking for a smarter alternative. <br /><br />I can think of a few ideas to boost performance.<br /><br />1) Maintain a TopicCount and PostCount fields in the Forums table (possibly with a trigger). This would be alot quicker for querying, but it may be replicating a solution that can already be done with indexed views. It is a little crude...<br /><br />2) Create an indexed view - this seems the most suitable option as it really provides the same performance as option 1 but with probably better insert performance. However, I cannot seem to create a view that returns an aggregate for BOTH the number of posts, and the number of topics in the same record. <br /><br />Any ideas?<br /><br />Andrew<br /><br /><br /><br /><br /><br /><br /><br /><br /><br />* Sometimes Topics and Posts might be the same table.