While checking for long running queries I noticed a lot of entries where the query was "CREATE FUNCTION xyz ....".
After some investigation it appears that we have a function in databaseA that is taking a contactID and looking it up to get a staffNo in a table in a table in a different database. like this:
USE DatabaseA GO CREATE funcGetStaffNo(@ContactID int) returns INT as BEGIN DECLARE @StaffNo INT SELECT @StaffNo = [Staff_Number] FROM DatabaseB.dbo.[Employees] AS ea WHERE [ea].[EmployeeID] = @ContactID RETURN @StaffNo END GO select c.ContactID, dbo.funcGetStaffNo(c.ContactID), c.ContactAddress FROM Contacts as c
Is there something in the background that actually creates the function in DatabaseB for the query to run?
I'm going to work on getting the function(s) (i expect there isnt just one case of this!) moved to the databases where the data they work on is stored in order to speed things up a bit but am interested in what is actually going on.
[EDIT:] The script I'm using that shows this up is from here: http://scarydba.wordpress.com/2009/02/18/refining-the-missing-index-data/