Difference between Delete and Truncate in SQL?

Delete	Truncate
Delete is DML (Data Manipulation Language) command.	Truncate is DDL (data Definition Language) command.
Delete statement is executed using a row lock.	Truncate table always lock the table and page but not each row.
We can specify in where clause.	We cannot specify in where clause.
It delete specified data if where condition exists.	It deletes all the data of table.
Delete activates a trigger because the operation is logged individually.	Truncate cannot activate a trigger because operation does not log individually row delete.
Rollback is possible in Delete.	Rollback is not possible in Truncate.
Slower then Truncate because it keep log.	Faster in performance, Don’t keep logs.

Difference between Primary Key and Unique key in SQL?

Primary key	Unique key
Primary key cannot have NULL value.	Unique key may have a NULL value.
Each table can have only one primary key.	Each table can have more than one unique key.
By default primary key is clustered index.	By default unique key is Non- clustered index.
Primary key can be related with another table’s as a Foreign Key.	Unique key cannot be related with another table’s as a Foreign Key.
We can generate ID automatically with the help of auto increment field.	Unique key is not support auto increment field.

Difference between Union and Union ALL in SQL?

Union	Union ALL
Union only selects distinct values.	Union all select all values in the table.
Union removes duplicate rows.	Union all doesn’t removes duplicate rows.
Output is in sorted order so that it is faster.	Output is not sorted order.

Difference between Where and Having clause?

WHERE	HAVING
Where can be used other then select statement also.	Having is used only with the select statement.
Where applies to each and every single row.	Having applies to summarized rows (with using Group By).
In where clause the data that fetched from memory according to condition.	In having, The completed data firstly fetched and then separated according to condition.
Where is use before group by.	Having is used to impose condition on group by function and use.

How to Find the last inserted record without using MAX / Top clause in SQL( Query).

1 : -
        Select *  From TABLENAME  Where  ID = IDENT_CURRENT('TABLENAME')

2 : -
        Set Rowcount 1
        Select * from TABLENAME   order by ID desc

How to find second highest salary using Ranking Function (DENSE_RANK()) in SQL?

Table Name : SalaryDetails

ID	Salary
1	1000
2	500
3	750
4	500
5	350
6	750

SELECT a.Name,a.salary
FROM  (   SELECT Name,salary,DENSE_RANK() over (ORDER BY salary desc) AS Rank FROM salaryDetails  )  as a where Rank = 2 

How to find second highest salary in SQL using MAX?

Table Name : SalaryDetails

ID	Salary
1	1000
2	500
3	750
4	500
5	350
6	750

                Select max(salary) as salary from salaryDetails where salary not in (select top (1) salary from salaryDetails order by salary desc)