Write a sample query for self join?

Ans: Select e1.ename, e2.empid from emp e1, emp e2 where e1.empid=e2.mgrid;

Can we change the index of primary key on table?

Ans: No

How to change the name of the table or stored procedure in sql?

Ans: sp_rename oldtablename newtablename

For changing the column name

Sp_rename  ‘tablename.[Oldcolumnname]’,’newcolumnname’,’Column’

Ex:sp_rename ‘tblemp.first’,’namechange’,’Column’

How to find out which index is defined on table?

Ans: sp_helpindex tablename

What are difference between truncate and delete?

Ans: 1) Delete keep the lock over each row where Truncate keeps the lock on table not on all the row.
2) Counter of the Identity column is reset in Truncate where it is not reset in Delete.
3) Trigger is not fired in Truncate where as trigger is fired in Delete.

4) In TRUNCATE we cannot rollback.

5) In DELETE we can rollback

What is Collation?

Ans: Collation refers to a set of rules that determine how the data is sorted and compared.

What is the difference between Primary key and unique key?

Ans: Primary key does not allow the null values but unique key allows one null value.

Primary key will create clustered index on column but unique key will create non-clustered index by default.