>>9,999 times out of 10,000 there will be a row.
>>How can I avoid the second query here?
>>
>>
>>

>>CREATE FUNCTION dbo.GetStoreNameAndAddress           -- function name
>>(@storecode CHAR(11))                     -- input parameter name and data type
>>RETURNS CHAR(50)                          -- return parameter data type
>>AS
>>BEGIN                                -- begin body definition
>>DECLARE @StoreNameAndAddress CHAR(50)
>>IF EXISTS (select * FROM store where LEFT(storecode,11) = @storecode)
>>   SELECT @StoreNameAndAddress = ( select RTRIM(store_name) + ' ' + RTRIM(f_address) from store where LEFT(storecode,11) = @storecode)
>>ELSE
>>     SELECT @StoreNameAndAddress = 'Unknown Store'
>>RETURN @StoreNameAndAddress
>>END
>>GO
>>

>
>Don't use scalar functions.
>
>You may use
>

>select @StoreNameAndAddress = rtrim(store_name) + ' ' + ... from store where storeCode like @storeCode + '%'
>
>if @@ROWCOUNT = 0
>   select @StoreNameAndAddress = 'Unknown Store'

>
>BTW, it should return varchar and not char unless you want to save spaces.
>
>--------------------------------------
>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?