>>>I would never use SEEK() in a VFP, SQL SELECT to lookup a value in another table, unless there is A VERY GOOD reason to do so. Just an extra join would do the trick just fine, even if it ends up a few ms slower.
>
>I use SEEK against huge lookup tables if I cannot be sure that the execution plan for theoretically elegant SQL won't involve intermediate resultsets >2gb that crash the system. As you note, sometimes there's a good reason- and static lookup tables is one of those. SET RELATION can be even better, but lets not go there. ;-)
>
JR, unless there's been significant maintenance neglect on the SQL end, how can you be comparatively less certain about optimal query performance for a SQL Server lookup, compared to a Fox SEEK against a similar sized table? Very curious.
Have you looked at the performance gains using in-memory optimized tables in SQL 2014 for static lookups?