>>Since scalar functions don't play well in SQL Server, I suggest to incorporate it into your main query using OUTER APPLY, then you would be able to use ISNULL function to get the 'Unknown Store'
>
>This worked.
>However, the response was still slow.
>The store table had a non-clustered index on the storecode column.
>I changed it to a clustered index and it flies now.
>Why is there such a difference in performance between the two types on index?

Is storecode is a good candidate for such index? What kind of entries do you have?

Compare the execution plans between the 2 queries. The clustered index seek is the best for the query execution.