Alex Rivera | Logout

SQL Server Table Synonyms with Indexes

Asked 2009-09-01T14:53:44.660
10

I have multiple databases on a single instance of SQL Server 2005. I've created a synonym on one database to access a table on another database and when writing my queries, I'd like to utilize a specific index, however, when evaluating the execution plan, it doesn't appear to use it. If I write the query to access the database explicitly, it works, but I can't seem to get it to work using a synonym. For example:

select *
from testdb..testtable with (index(testindex))

|--Nested Loops(Inner Join, OUTER REFERENCES:([testdb].[dbo].[testtable].[id]))
     |--Index Scan(OBJECT:([testdb].[dbo].[testtable].[testindex]))
     |--Clustered Index Seek(OBJECT:([testdb].[dbo].[testtable].[PK_testtable]), SEEK:([testdb].[dbo].[testtable].[id]=[testdb].[dbo].[testtable].[id]) LOOKUP ORDERED FORWARD)

does not yield the same execution plan as

select *
from testdb_synonym with (index(testindex))

|--Clustered Index Scan(OBJECT:([testdb].[dbo].[testtable].[PK_testtable]))

Is this a limitation with Synonyms or is there something specific I need to do to get this to work?

Edit
Report

1 Answer

2

I tested the same thing and it seems that the query optimizer ignores that hint when done via a synonym. The details are I did a select * against an arbitrary table with an index hint to use a non-clustered index. Without the synonym, it does a bookmark lookup/nested loop join. With it, it does a table scan. Since there are no options on the create synonym syntax, I can only assume that the index hint is ignored. No details in BOL as to why. I would chalk it up as a "feature".

answered 2009-09-01T16:56:18.803

Your Answer