Monday, January 30, 2012

Query to find nth hieghst salry in sql

Declare @sal int
Set @sal=1    --------(what ever position you want)
Select E1.Esalary from MyEmployee E1
Where @sal=(select Count(distinct E2.Esalary) from MyEmployee E2
Where E2.Esalary >= E1.Esalary)
Go

Sunday, January 29, 2012

Differences Between Delete and Truncate commands

Differences between Delete and Truncate commands
1. DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
Truncate removes the data by De-allocating the data pages used to store the tables data. and only page deallocations are recorded in the transaction log.
2. Truncate Reset the identity of the table, where as Delete cannot reset the identity of the table.
3. Truncate table cannot logged so it cannot activate the trigger.
    Delete can logged and activate the trigger.
4. Truncate is DDL command, where as Delete is DML command.
5. Truncate cannot be rolled back by using logs, where as Delete can rolled back by using logs.
6. Truncate is faster than the Delete. Because truncate cannot log the deleted rows entry.