Is it possible to set trigger priority in sql server?

Yes.
    We know trigger is event which is implicitly called on any kind of DML or DDL operation occurred. It may be interview question Suppose I have one table and I created three trigger on that table for update. So which trigger will be fired when?
    If you dont know this concept you will say it will be fiired  randomly.But we can fire it as per our requirement.
  In sql server we have  sp_settriggerorder which will help us to set triggger priority.

sp_settriggerorder 'triggername','value', 'statement_type' 

Argument :

trigger Name : Name of trigger

value  : It has three value 

First	Trigger is fired first.
Last	Trigger is fired last.
None	Trigger is fired in undefined order.

 statement_type:

 Specifies the SQL statement that fires the trigger.

like  INSERT, UPDATE, DELETE.

USE  Your_Database; 

GO 

sp_settriggerorder @triggername= 'TriggerName', @order='Type', @stmttype = 'UPDATE';

SQL query to find second maximum salary of Employee

  Hi,
   Yesterday my friend attended inteview in I B M  for database devloper
 They asked him simple question .Check it out.
           
       Tell me three ways I can get above result .....

Table  -create table Employee (id int,salary money)




1.
SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);


2.
SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee)



3.In sql server - using top  keyword
SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM employees ORDER BY salary DESC) AS emp 
 ORDER BY salary ASC

 

Is it possible to create foreign key for child table if column has composit primary key?

No, But we can make it possible.

    suppose I have Parent table
eg.
 create table parent (id int  ,value int,primary key(id,value))
   
It has two column .Composit primary key is present on it.

Composit primary : If primary  key  is combination of more than one column then it called as composit primary key.

  I have child table

e.g

create tableChild(id int  foreign key(id) references   parent (id))

Now execute above statement .You will get error.
Message :There are no primary or candidate keys in the referenced table

But we can do it By following way
 make constrain unique on that column.It will work.

script :

Error :
 begin tran 
create table parent (id int  ,value int,primary key(id,value))
create table Child(id int  foreign key(id) references   parent (id))
rollback

Correct :
 begin tran 
create table parent (id int  unique,value int,primary key(id,value))
 create table Child(id int  foreign key(id) references   parent (id))
rollback

what is deadlock and How to handle Transaction Deadlocks?

  Firstly we should know about deadlock.

Deadlocks occur when two users have locks on separate objects and each user wants a lock on the other's object. When this happens, SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

  Let us use a scenario where a transaction A attempts to update table 1 and subsequently read/update data from table 2. At the same time there is another transaction B  which is trying to update table 2, and subsequently read /update data from table 1. In this scenario, transaction X holds a lock that transaction Y needs to complete its tasks and vice versa. So in this scenario neither transaction can complete until the other transaction is release.

Transaction deadlock situation:

Transaction A:

BEGIN TRAN

UPDATE EMPLOYEE SET EMPLOYEENAME=' XYZ' WHERE EMPLOYEEID=111
WAITFOR DELAY '00:00:05'
UPDATE SALARY SET BASIC= 200 WHERE EMPLOYEEID=111
COMMIT TRAN

Transaction B:

BEGIN TRAN
UPDATE SALARY SET HRA=200 WHERE EMPLOYEEID=111
WAITFOR DELAY '00:00:05'
UPDATE EMPLOYEE SET EMPLOYEENAME='ABC' WHERE EMPLOYEEID=111
COMMIT TRAN

Result:

(1 row(s) affected)
Msg 1205, Level 13, State 45, Line 5
Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 Reason for above deadlock:

What we did is we copied these two transactions to two different query windows and run them simultaneously. Consequently what happened is that Transaction X locks and updates Employee table whereas transaction X locks and updates Salary table. After a delay of 20 ms, transaction X looks for the lock on Salary table which is already held by transaction Y and transaction Y looks for lock on Employee table which is held by transaction X. So both the transactions cannot proceed further; the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.


How deadlock is resolved:

The user can choose which process should stop to allow another process to continue. SQL Server automatically chooses the process to terminate which is running completes the circular chain of locks. Sometime, it chooses the process running for a shorter period than another process. But it is recommended that we should provide a solution for handling deadlocks by finding the problem in our query code and then modify our processing to avoid deadlock situations.

Let us rewrite our transaction query.

Transaction A:

RETRY:
BEGIN TRAN
BEGIN TRY
      UPDATE EMPLOYEE SET EMPLOYEENAME='XYZ' WHERE EMPLOYEEID=111
      WAITFOR DELAY '00:00:10'
      UPDATE SALARY SET BASIC= 100 WHERE EMPLOYEEID=111
      COMMIT TRAN
END TRY
BEGIN CATCH
      PRINT 'Rollback Transaction'
      ROLLBACK TRANSACTION
      IF ERROR_NUMBER() =1205 -- DEADLOCK NUMBER
      BEGIN
            WAITFOR DELAY '00:00:00.05'
            GOTO RETRY
      ENDEND CATCH

Transaction B:

RETRY:
BEGIN TRAN
BEGIN TRY
      UPDATE SALARY SET HRA=200 WHERE EMPLOYEEID=111
      WAITFOR DELAY '00:00:10'
      UPDATE EMPLOYEE SET EMPLOYEENAME='ABC' WHERE EMPLOYEEID=111
      COMMIT TRAN
END TRY
BEGIN CATCH
      PRINT 'Rollback Transaction'
      ROLLBACK TRANSACTION
      IF ERROR_NUMBER()=1205
      BEGIN
            WAITFOR DELAY '00:00:00.05'
            GOTO RETRY
      END
END CATCH

Result:

If we run these two Trans statement at the same time, we will get the result below.

(1 row(s) affected)
Rollback Transaction

(1 row(s) affected)

(1 row(s) affected)

  In this way we can avoid deadlock in transaction.

What is index hint?

  I can ask this question another way

  suppose I have one table and 20 indexes are  on the table. But i want to force on query to use  index which i think it will give fast result .
  So is it possible ?

Ans - Yes. Through index hint it is possible.
     
   I think you got answer.Index hint means  forcing  query to use specified index not the index selected by query optimiser.

e,g

create table Movie(name varchar(100),id int identity(1,1))

create nonclustered index Movie_Name_I on Movie(name)

select * from name  with (index(Movie_Name_I ,nolock)) where name='xyz'

What is transaction ?

                               A transaction is one or more actions that are defined as a single unit of work. In the Relational Database Management System (RDBMS) world they also comply with ACID properties:

Atomic(ity) - The principle that each transaction is 'all-or-nothing', i.e. it either succeeds or it fails, regardless of external factors such as power loss or corruption. On failure or success, the database is left in either the state in which it was in prior to the transaction or a new valid state. The transaction becomes an indivisible unit.


Consistency - The principle that the database executes transactions in a consistent manner, obeying all rules (constraints). For example, consider the following table:



CREATE TABLE dbo.MyTestTable (
 ColA SMALLINT,
 CONSTRAINT uq_ColA UNIQUE )



Now consider the following valid transactions that will leave the database in a consistent state:
• INSERT INTO dbo.MyTestTable VALUES (1)
• INSERT INTO dbo.MyTestTable VALUES (9)
But the following statements, if executed and allowed to modify data, will leave the database in an inconsistent state, since they violate some constraint (or allowed datatype) of the defined table. Hence an error is returned:
• INSERT INTO dbo.MyTestTable VALUES ('Hello')
• INSERT INTO dbo.MyTestTable VALUES (3),(3),(3)


Isolation - This property means that each transaction is executed in isolation from others, and that concurrent transactions do not affect the transaction. This property level is variable, and as this article will discuss, SQL Server has five levels of transaction isolation depending on the requirements of the database.


Durability - This property means that the data written to the database is durable, i.e. it is guaranteed to be in storage and will not arbitrarily be lost, changed or overwritten unless specifically requested. More formally, it means that once a transaction is committed, no event can 'un-commit' the transaction - it is written and cannot be changed retrospectively unless by another transaction.

What is the difference between a clustered and a nonclustered index?

A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.