Alternative to multiple column join on same table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Alternative to multiple column join on same table

Author

Topic

sqlgirlatty
Starting Member

5 Posts

Posted - 2014-03-17 : 16:04:15

I'm creating a view with two columns that use the same reference table. Example -
SELECT rd.request_location_cd 'requested location', td.transfer_location_cd 'transfer location'
FROM dbo.transfers
INNER JOIN dbo.divisions_ref rd
ON t.request_location_id = rd.request_location_id
INNER JOIN dbo.divisions_ref td
ON t.transfer_location_id = td.transfer_location_id

I need to create this as an indexed view, but the index throws an error 'Cannot create index on view. The view contains a self join'

Is there a way to get the location text value from the reference table for multiple columns without joining the table twice so I can create an indexed view?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-17 : 16:06:06

Why does it need to be an indexed view? Why not just properly index the underlying tables?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sqlgirlatty
Starting Member

5 Posts

Posted - 2014-03-18 : 10:59:55

The actual view has 12 tables in the join and I hoped that indexing it would improve performance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-18 : 12:23:09

Indexed views are rarely used. Typically indexing the underlying tables is sufficient. I would take a step back and see where the bottleneck is. Check the execution plan, check the statistics io and time. Add the appropriate indexes. Compare the performance.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources