T-SQL simple query optimization - search arguments and predicates

Once you start really trying to understand the "how's" in T-SQL, you can implement much more optimized queries. Take the following simple lesson with search predicates. 

These two queries technically will return the same results, looking for all records where the date variable is equal to the datecolumn, or the date column is null and the date variable is null.

SELECT [somecolumnname]
FROM [schema].[sometablename]
WHERE COALESCE([datecolumn],'20100101') = COALESCE(@date, '20100101')

SELECT [somecolumnname]
FROM [schema].[sometablename]
WHERE [datecolumn] = @date OR ([datecolumn] IS NULL AND @date IS NULL)

But performance wise, the first query is manipulating the table column which means the predicate isn't a search argument and SQL Server cannot efficiently use an index on the datecolumn, if one exists.

Simple rule, always attempt to find a way around using a function on your table columns in the WHERE clause. If you have to, use a CTE, temp table or table variable if you need to limit the results to search by beforehand.

Another gotcha is that even if you don't have a function on your search clause, using some evaluations aren't a search argument. Like this one:

SELECT [columnname]
FROM [schema].[tablename]
WHERE [columnname] LIKE '%8'

Not only is this not a search argument and cannot utilize indexes, but horrible performance as each row value needs to be evaluated individually to see if it ends with the number 8. 

Although WHERE [columnname] LIKE '8%' is a search argument.

Good to have a thorough grasp of T-SQL. Here's a site with good overview of this and other search argument topics. 

Happy searching!