I have following queries:

```
DECLARE @application_number CHAR(8)= '37832904';
SELECT
    la.LEASE_NUMBER AS lease_number,
    la.[LEASE_APPLICATION] AS application_number,
    tnu.[FOLLOWUP_CODE] AS note_type_code -- catch codes not in codes table
FROM [dbo].[lease_applications] la
LEFT JOIN [dbo].tickler_notes_uniq tnu ON tnu.[ACCOUNT_NUMBER] = la.[ACCOUNT_NUMBER]
WHERE la.LEASE_APPLICATION = @application_number
      OR @application_number IS NULL;

SELECT
    la.LEASE_NUMBER AS lease_number,
    la.[LEASE_APPLICATION] AS application_number,
    tnu.[FOLLOWUP_CODE] AS note_type_code -- catch codes not in codes table
FROM [dbo].[lease_applications] la
LEFT JOIN [dbo].tickler_notes_uniq tnu ON tnu.[ACCOUNT_NUMBER] = la.[ACCOUNT_NUMBER]
WHERE la.LEASE_APPLICATION = @application_number; 

```

The only difference between these 2 queries is that I've added checking for the variable if it is NULL or not. 

The execution plans of these queries are:
[![enter image description here](https://i.stack.imgur.com/Gq8i6.png)](https://i.stack.imgur.com/Gq8i6.png)

You can find graphical plan [here](https://www.brentozar.com/pastetheplan/?id=S1L2dX6K-)

So the question is. Why the plans are so different? 

UPDATE:

The actual execution plan of the first query can be found [here](https://www.brentozar.com/pastetheplan/?id=Sypw74pYW)

OPTION(RECOMPILE) changed the actual execution plan to the good one. However the downside of that is that my main goal was to create the TVF with these params and then everybody who uses that function is supposed to provide that option.

It is also worth to mention that my main goal is to create TVF with 2 params. Each of it might be null and might be not but at least 1 of them is supposed to be NOT NULL. These params are more or less equal, they are just different keys in the 2 tables that would give the same result anyway (the same number of rows and so on). That's why I wanted to do something like

```
WHERE (col1 = @param1 OR @param1 IS NULL) AND (col2 = @param2 OR @param2 IS NULL) AND (@param1 IS NOT NULL or @param2 IS NOT NULL)

```

So, basically I am not interested in ALL records at all