Sql – Results returned from a view using linked server may vary

linked-listsqlsql-server

i have a view that is using linked server to retrieve data from a remote server in SQL Server. On each time viewing the view, the results returned are vary. For example, 1st time execution may return 100 rows of records but on 2nd time of execution, rows returned are 120 rows. Any ideas what is the cause?

Best Solution

I have witnessed odd linked-server results that are a product of non-determinism written into the SQL itself, I.e. a TOP query written without an ORDER BY clause.

This problem, for example, where the chap had multiple non-unique foreign keys coming from a table source on the left hand side of a linked-server INNER JOIN, and wanted 10 rows from a remote sub-query to the right, where the end result was restricted to 10 rows itself, when it should have been greater than 10 rows.

Should definitely give your SQL a quick eye for such curiosities.