SQL Server 2005, Views, Optimizing and UNION

I'm not typically a SQL View fan. I know many who are, and their arguments are all completely valid. In fact, I'm not much of a trigger or stored procedure fan, even though I clearly understand the benefits. It's just a "I like to have my code in one place" kind of thing. Crazy, but there it is.

Well, I needed a view. It was a three table UNION'ed join and....I just needed it simpler than putting all that nasty SQL in my ColdFusion objects. However, my modeling tool, PowerDesigner (PD), isn't always great with views that have computed columns. This particular view was performing too slowly, so I set about to add some indexes to it. Because of the PD problem, the indexes couldn't be built through the modeler. So I'd have to write the index code manually. As I began looking for the code samples, I found this page:
Improving Performance with SQL Server 2005 Indexed Views

One of the things I found was that if you do a UNION'ed view, and you use the same table names again, you should use different table identifiers. For example:

BAD:
SELECT * FROM Table T WHERE T.type="A"
UNION
SELECT * FROM Table T WHERE T.type="B"

GOOD:
SELECT * FROM Table Ta WHERE Ta.type="A"
UNION
SELECT * FROM Table Tb WHERE Tb.type="B"

When I changed my view's code to have this style, the speed on it more than doubled. And I hadn't even done the indexes that I was planning. I will, though...you just watch!

- Will Belden
September 4, 2007