Is there difference in performance when querying someting like this?
Query 1:
SELECT * FROM Customer WHERE Name=Name
Query 2:
SELECT * FROM Customer
I will use it in conditional select all
SELECT * FROM Customer
WHERE Name = CASE WHEN @QueryAll='true' THEN Name ELSE @SearchValue END
If there's no performance issue in Query 1 and 2, I think it is a short code for this one:
IF @QueryAll='true'
SELECT * FROM Customer
ELSE
SELECT * FROM Customer WHERE Name=@SearchValue
You should read Dynamic Search Conditions in T‑SQL by Erland Sommarskog.
If you use SQL Server 2008 or later, then use OPTION(RECOMPILE) and write the query like this:
SELECT *
FROM Customer
WHERE
(Name = @SearchValue OR @QueryAll='true')
OPTION (RECOMPILE);
I usually pass NULL for @SearchValue to indicate that this parameter should be ignored, rather than using separate parameter @QueryAll. In this convention the query becomes this:
SELECT *
FROM Customer
WHERE
(Name = @SearchValue OR @SearchValue IS NULL)
OPTION (RECOMPILE);
Edit
For details see the link above. In short, OPTION(RECOMPILE) instructs SQL Server to recompile execution plan of the query every time it is run and SQL Server will not cache the generated plan. Recompilation also means that values of any variables are effectively inlined into the query and optimizer knows them.
So, if @SearchValue is NULL, optimizer is smart enough to generate the plan as if the query was this:
SELECT *
FROM Customer
If @SearchValue has a non-NULL value 'abc', optimizer is smart enough to generate the plan as if the query was this:
SELECT *
FROM Customer
WHERE (Name = 'abc')
The obvious drawback of OPTION(RECOMPILE) is added overhead for recompilation (usually around few hundred milliseconds), which can be significant if you run the query very often.
NULL, so it performance is out of the question. Query3 may be unexpectedly slower than Query4, if you don't use OPTION(RECOMPILE). Actually, Query3 may also produce different result to Query4, if Name can be NULL. So, it is better not to use this style (WHERE Name=Name).The query 2 would be faster with an index on name column, and you should specify just the fields you'll need, not all of them.
For some guidance in optional parameter queries, take a look here: Sometimes the Simplest Solution Isn't the Best Solution (The Optional Parameter Problem)
* to shorten the code, by the way, thanks for the link!
Nameis nullable, the 3rd query will not return the rows whereNameis null. Also, read sql in the wild's catch-all queries article.