SQL Tuning

SQL Tuning or SQL Optimization

source: count(*) vs count(column-name) - which is more correct? [duplicate]

• COUNT(*) counts all rows
• COUNT(column) counts non-NULLs only
• COUNT(1) is the same as COUNT(*) because 1 is a non-null expressions

COUNT(star) vs COUNT(column) vs. COUNT(DISTINCT) vs. COUNT(col) OVER(…)

SQL SERVER – Solution – Puzzle – SELECT * vs SELECT COUNT(*)

COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?

1.Actual column names than Select *
2.Instead of HAVING(use when required only): use  subject != 'Science'  
   note: HAVING clause used to filter rows after all rows are selected.
3.Minimize subqueries in main query.
select name from employee where (salary, age) = (select max(salary),max(age) from employee_details)
and dept = 'Electronics';
4.Use EXIST(efficient filter criteria in main query), IN(slowest performance, efficient when criteria in subquery) 
and table joins appropriately. 
5.Use EXISTS:instead of DISTINCT when using joins when table having 1:M.
6.Use UNION ALL instead of UNION.