Best SQL Practices

/*
**Please See Example at same Numbering Position below.
**1. Avoid selecting all fields.Select Only those which are required
**2. Avoid selecting filters in select statement
**5. Not operator Prevents From Indexing, so try to avoid it.
**6. Try to Avoid using 'Like' Operator always prefer 'In' and 'Between' Operator
**7. Functions and arithmetic applied against columns with the WHERE Clause means no chance of indexes
--. and repeated execution of the function or arithmetic.
**8. 'Union' returns distinct value where as 'Union all' returns all value including duplicate values.
**9. Avoid using complex Joins it decreases performance.
--The maximum number of tables that can be joined > 100, the practical limit is < 10.
**
*/
--1.
SELECT * FROM dbo.TablName--Bad Practice
SELECT ID,Roll_No FROM dbo.TablName--Good Practice
--2.
SELECT ID,Roll_No FROM dbo.TablName WHERE ID=2--Bad Practice
SELECT Roll_No FROM dbo.TablName WHERE ID=2--Good Practice becuse It Decreases Overhead
--3.
SELECT DISTINCT Roll_No FROM dbo.TablName --If not Necessary don't use DISTINCT it added one more step for deleting duplicate records.
--4.
--If we are Comparing 2 column keep the dataType Size same
----Because it Increases DataType Conversion Overhead Unnecessary
--e.g EmpId(varchar 50)of a Table Compare with EmpId(varchar 20) of another Table
--5.
SELECT ID,Roll_No FROM dbo.TablName WHERE ID Not Between 1 and 3--Bad Practice
SELECT ID,Roll_No FROM dbo.TablName WHERE ID '>' 3--Good Practice
--6.
--If you want to search all Name like Bikesh,Bimlesh,Bikash i.e BIM to BIK
SELECT [Name] FROM dbo.TablName WHERE [Name] LIKE 'B%'--Bad Practice
--In the above statement it will check whole data
SELECT [Name] FROM dbo.TablName WHERE [Name] Between 'Bi'and 'Bj'
--In the above statement it will check only data starting from 'BI' to 'BJ'
--7.
SELECT Id FROM dbo.TablName WHERE ID + 1= 10--Bad Practice
SELECT Id FROM dbo.TablName WHERE ID = 10-1--Good Practice
--8.
--. Wanna to check if a Table is Blank or Not
SELECT count(*) FROM dbo.TablName WHERE ID=2--Bad Practice
SELECT top 1 [Name] FROM dbo.TablName WHERE ID=2--Good Practice
--9.
--Q.1 Find all the Name of Students whose Number lies between 1 to 33 if found Than say Fail
--Q.2 Find all the Name of Students whose Number lies between 34 to 55 if found Than say IInd division
--Q.3 Find all the Name of Students whose Number lies between 55 to 100 if found Than say Ist division

--BAD Approach:
SELECT [Name] FROM dbo.TablName WHERE Number Between 1 and 33
Print 'Fail'
SELECT [Name] FROM dbo.TablName WHERE Number Between 33 and 55
Print 'IInd Division'
SELECT [Name] FROM dbo.TablName WHERE Number Between 55 and 100
Print 'Ist Division'

--GOOD Approach:
SELECT [Name]
CASE WHEN Number BETWEEN 1 AND 33
THEN Print 'Fail'
CASE WHEN Number BETWEEN 33 AND 55
THEN Print 'IInd Division'
CASE WHEN Number BETWEEN 55 AND 100
THEN Print 'Ist Division' ELSE Print 'Fail' END,
FROM TablName;