Schema binding for a UDF using tables from other db

Question

I have a UDF in SQL 2005 that I would need to schemabind as it is used in a view that I need an index on.

This UDF gets information from a table that is located in a different db (same server) then the one where the UDF is located.

Since it is invalid to specify table as [DBName].dbo.[Tablename], is there a way I can get the info from the table in the other db?

Answer 1

Schema binding is supposed to guarantee consistency. However consistency can not be guaranteed across two different databases, therefore schema-binding cannot be made across two different databases. In other words it's impossible to achieve.

Imagine that, for example, one database is restored to an earlier point in time - the index on the indexed view would become corrupt and queries would be returning wrong results.

Answer 2

If your UDF is in Database1, and it needs to access data from a table in Database2, all you have to do is create a view in Database1 that grabs the data you need from the table(s) in Database2. Then use this view in your UDF.

Works just fine, I used this approach many times.

Hope it helps.