Given:
WHERE (@Id Is NULL OR @Id = Table.Id)
If @Id is null: the expression evaluates to true. Does the second part @Id = Table.Id still get considered? or is it sufficient that the expression evaluates to true given that the first part is (which is the case in c#).
This is relevant because of some much more complex OR statements where it is important to know if all parts are getting evaluated.
Execution plans may not be so great with a query like that. Both will be evaluated.
Sometimes they are, sometimes they aren't. SQL Server does not guarantee boolean operator short circuit, don't rely on it for correctness. See this blog entry: Boolean Operator Short Circuit.
Complex queries that depend on @variables like this are much better written as explicit IF statements:
IF (@id IS NULL)
SELECT ... FROM ... WHERE ...
ELSE
SELECT ... FROM ... WHERE ...