What is the Difference Between WHERE and HAVING Clauses in SQL Server

• HAVING clause can be used only with a GROUP BY clause, where as a WHERE clause can be used with constructs like SELECT, UPDATE, DELETE etc.
• HAVING clause is applied as a filter to the data / output resulting from the GROUP BY clause, where as a WHERE clause is applied to every row in the SELECT, UPDATE, DELETE etc constructs.
• In queries where both HAVING and WHERE clauses are used, WHERE clause is applied first (to every row in the SELECT statement to filter the records before they are fed to GROUP BY clause for aggregation) and then the HAVING clause is applied (to filter the aggregated result from GROUP BY clause).

Read more...

What is the Difference Between TRUNCATE & DELETE Clauses in SQL Server

• TRUNCATE is a DDL (Data Definition Language) command, whereas DELETE is a DML (Data Manipulation Language) command.
• TRUNCATE removes all the records from a table without making a log entry for individual row deletions (Does make log entries about data page deallocation etc, which can be used for ROLLBACK of a TRUNCATE command), whereas DELETE removes all or selected records (based on absence or presence of a WHERE condition) from a table by making a log entry for individual row deletion. Hence TRUNCATE is faster than DELETE.
• TRUNCATE removes all the records from a table and a WHERE clause or filter condition cannot be used with TRUNCATE, whereas DELETE can remove selected records or all records based on whether a WHERE clause (Optional) is used or not used respectively.
• TRUNCATE cannot be used on a table if it satisfies one of the following conditions:
• Table is referenced by one of more FOREIGN KEY constraints
• Table is marked / enabled for Replication
• TRUNCATE resets IDENTITY in any of the columns in a table, whereas DELETE does not reset the IDENTITY.

Read more...