Sunday, January 25, 2009

I upgraded to SQL Server 2005 Enterprise Edition today in order to use indexed views to implement better performance for my web site. Basically to short cut the multiple joins it requires to denormalize my web site data into something interesting. After upgrading, I was disappointed to learn that I couldn't use indexed views to help my performance. Indexed views are severly limited you can't have any outer joins, use other views, or do unions. So for my data, and almost any highly normalized data that you want join together to make it appear more denormalized you can't use indexed views. By indexed views I mean clustered unique indexed views that you can only get in enterprise edition SQL server. These views rewrite the data into a cluster and update when the underlying table updates. As long as the table doesn't bind to another with an outer join, or you want to stack views together you can achieve some performance gains. Just for a minute, after laboring 2-3 hours to get my index to create on my view and never seceeding, I thought about going to look at the Oracle documentation. It is always really frustrating working with SQL Server and discover that it has holes in the full feature set that you really want.
{6230289B-5BEE-409e-932A-2F01FA407A92}