March 10, 2004 at 7:05 am
I have a small issue, but I cannot find a way out of it - any assistance with this would be great.
We are implenting indexed views for a 'data warehouse' type arrangement. The problem is that many of our existing views incorporate self-joins which prevent me from creating indexes on them. These views have been developed by non-SQL developers using the Design View feature of EM.
Do I have alternatives to self-joins to achieve the same results?
Thanks in advance for any info/help.
March 15, 2004 at 8:00 am
This was removed by the editor as SPAM
March 16, 2004 at 11:01 am
I like self joins, they often get better performance than the alternative. But having said that the alternative is usually a sub query. For example:
the self join
SELECT e1.LastName, e1.FirstName
FROM dbo.Employees e1, dbo.Employees e2
WHERE e1.City = e2.City
AND e2.LastName="Davolio"
AND e2.FirstName="Nancy"
can be written like:
SELECT LastName, FirstName
FROM dbo.Employees
WHERE City in
( SELECT City
FROM Employees
WHERE LastName="Davolio"
AND FirstName="Nancy" )
However the self join performs better. If you want to create an indexed view though you are out of luck. A view created using either one of these is not indexable. I can't think of way to translate a slf join into a indexable view.
Francis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply