situations when the #1 photo could be missing while the #2 is intactthen P.PhotoSort >= 1 predicate is not good, there has to be something else. You are absolutely correct that placing the PhotoPath into Users table is a bad idea. The real question is what does PhotoSort mean? Currently, if photo #1 could be missing while photo #2 is intact, the query will still return photo #1 unless I don't have a correct understanding of PhotoSort. Could you please let me know? As far as the queries are concerned, I am almost positive that the second query (with join) should be a better performer when compared to the first one (with cross apply), but it really depends on how your data is indexed and number of UserPhotos records per user. YOu can test both and pick the best.
No one has followed this question yet.