SQL Tips

I am reading joe celko’s SQL Programming style book. Few notes on chapter 6 mentioned is very good. I am sharing a few. This would be useful for code review. I am using SQL Server 2008 R2 Environment.
--STEP 1

CREATE TABLE TestTable
(Col1 INT Identity(10,5),
Col2 INT ,
Col3 INT ,
DateOfSale Datetime
)

CREATE Clustered Index CIX_TestTable_Col1 ON TestTable(Col1)
CREATE Index IX_TestTable_Date ON TestTable(DateOfSale)

--STEP 2
INSERT INTO TestTable(Col2,Col3,DateOfSale)
SELECT 50,10,Getdate()-10
GO 5000

SQL Tip #1 – Query Involving Date Columns and where indexes are present do not manipulate

As seen in below example it results in SCAN when you apply datediff filter.
Bad Query
--Index Scan
SELECT Col1 FROM TestTable
WHERE DateDiff(Day,getdate(),DateofSale)-20 > 1
Better option
--Index Seek
SELECT Col1 FROM TestTable
WHERE DateofSale > GETDATE()-20

SQL Tip #2 - Use Exists instead of IN Operator

Bad Query
SELECT * FROM TestTable
WHERE Col1 in
(Select Col2 From NewTable)
Better Query
SELECT * FROM TestTable T1
WHERE Exists
(Select 1 FROM NewTable WHERE Col2 = T1.Col1)

SQL Tip #3 - Use CASE Instead of IF-ELSE Statements

SQL Tip #4 USE IN whenever it is logically correct than using OR Condition
Below Example is a simple differentiation when to use OR and when to use IN
Example Query
SELECT * FROM TestTable
WHERE Col1 = 10
OR Col1 = 20

Use of IN
SELECT * FROM TestTable
WHERE Col1 IN (10,20)