Friday 15 November 2013

SQL SERVER - Notes 5

32. What are the various ways to fetch the 3rd highest pay employee record from a table ?

-- various ways to fetch a 3rd highest salaried employee record from table

SELECT * FROM employee emp1
WHERE 3 = (SELECT COUNT(DISTINCT emp2.salary)+1 FROM employee emp2
WHERE emp2.salary > emp1.salary)

SELECT * FROM (SELECT name,salary,ROW_NUMBER() OVER(ORDER BY salary DESc) AS "ord" FROM employee ) emp
where ord = 3

;WITH cte as(SELECT TOP 3 * FROM employee ORDER BY salary DESC)

SELECT TOP 1 * FROM cte ORDER BY salary ASC


33. Query to find the lock takes place in db and specify the text which is executing currently and which sp id is hold on the process.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT      [SPID] = SP.SESSION_ID
            ,ER.REQUEST_ID
            ,ER.COMMAND
            ,[DATABASE] = DB_NAME(ER.DATABASE_ID)
            ,[USER] = LOGIN_NAME
            ,ER.BLOCKING_SESSION_ID
            ,[STATUS] = ER.STATUS
            ,[WAIT] = WAIT_TYPE
            ,OPEN_TRANSACTION_COUNT
            ,CAST('<?QUERY –'+ CHAR(13)+SUBSTRING(QT.TEXT,
            (ER.STATEMENT_START_OFFSET / 2)+1,     ((CASE ER.STATEMENT_END_OFFSET
            WHEN - 1 THEN DATALENGTH(QT.TEXT)    ELSE ER.STATEMENT_END_OFFSET
            END - ER.STATEMENT_START_OFFSET)/2) + 1)+CHAR(13)+ '-?>' AS XML) AS SQL_STATEMENT
            ,[PARENT QUERY] = QT.TEXT
            ,P.QUERY_PLAN
            ,ER.CPU_TIME
            , ER.READS
            , ER.WRITES
            , ER.LOGICAL_READS
            , ER.ROW_COUNT
            , PROGRAM = PROGRAM_NAME
            ,HOST_NAME
            ,START_TIME
FROM  SYS.DM_EXEC_REQUESTS ER
INNER JOIN SYS.DM_EXEC_SESSIONS SP
ON          ER.SESSION_ID = SP.SESSION_ID
OUTER
APPLY SYS.DM_EXEC_SQL_TEXT(ER.SQL_HANDLE)AS QT
OUTER
APPLY SYS.DM_EXEC_QUERY_PLAN(ER.PLAN_HANDLE) P
WHERE SP.IS_USER_PROCESS = 1
/* SP.SESSION_ID > 50
– IGNORE SYSTEM SPIDS. — */
AND SP.SESSION_ID NOT IN (@@SPID)
ORDER BY 1, 2



34. How to select a Record as Xml from table ?

SELECT CAST((SELECT * FROM employee FOR XML AUTO)  AS XML)FOR XML PATH('employees')   -- This query frames the table name as tag name

Output :
<employees>
  <employee id="1" NAME="danie" age="33" salary="1000000" department="Finance" />
  <employee id="2" NAME="Jim" age="23" salary="2000000" department="Technical" />
</employees>


SELECT CAST((SELECT * FROM employee FOR XML  PATH('emp')) AS XML) FOR XML PATH('employees')  -- user defined name emp is as tag name, columns as elements

Output :
<employees>
  <emp>
    <id>1</id>
    <NAME>danie</NAME>
    <age>33</age>
    <salary>1000000</salary>
    <department>Finance</department>
  </emp>
  <emp>
    <id>2</id>
    <NAME>Jim</NAME>
    <age>23</age>
    <salary>2000000</salary>
    <department>Technical</department>
  </emp
</employees>


SELECT CAST((SELECT * FROM employee FOR XML RAW('emp')) AS XML) FOR XML PATH('employees') –user defined emp tag name ,columns are in attributes

Output:
<employees>
  <emp id="1" NAME="danie" age="33" salary="1000000" department="Finance" />
  <emp id="2" NAME="Jim" age="23" salary="2000000" department="Technical" />

</employees>


35. What is Service Broker ?

     Service broker is used to execute a instance in async process , between the two different servers , two different database and in a same database using the communication channel, TCP/IP .

      Components uses are Service, Message Type, Contract , Queue. Each sender and receiver can create the all the components

      To Create Service Broker , Let we take an example that i am inserting a record from the front end which pass the data to a stored procedure and i dont need to wait for insertion because this table consists huge million records so now i can use Fire and forget method here otherwise UI get non - responsiveness

CREATE DATABASE SBDB
GO

ALTER DATABASE SBDB SET ENABLE_BROKER
GO
ALTER DATABASE SBDB SET TRUSTWORTHY ON
GO

CREATE TABLE EMPLOYEE ( ID INT IDENTITY(1,1),NAME VARCHAR(40),AGE INT,SALARY INT)

/* Drop script */
IF EXISTS(SELECT '1' FROM sys.services WHERE name = N'//SBMessage/TargetService')
BEGIN
      DROP SERVICE [//SBMessage/TargetService]
END


IF EXISTS(SELECT '1' FROM sys.services WHERE name = '//SBMessage/InitService')
BEGIN
      DROP SERVICE [//SBMessage/InitService]   
END

IF EXISTS(SELECT '1' FROM sys.service_queues WHERE name=N'receiveQueue')
BEGIN
      DROP QUEUE receiveQueue
END

IF EXISTS(SELECT '1' FROM sys.service_contracts WHERE name = N'//SBMessage/Contract')
BEGIN
      DROP CONTRACT [//SBMessage/Contract]
END

IF EXISTS(SELECT '1' FROM sys.service_message_types WHERE name = N'//SBMessage/Reply')
BEGIN
      DROP MESSAGE TYPE [//SBMessage/Reply];
END

IF EXISTS(SELECT '1' FROM sys.service_queues WHERE name=N'senderQueue')
BEGIN
      DROP QUEUE senderQueue
END

IF EXISTS(SELECT '1' FROM sys.service_message_types WHERE name = N'//SBMessage/Request')
BEGIN
      DROP MESSAGE TYPE [//SBMessage/Request];
END

/* Sender script */
/* Message Type */
CREATE MESSAGE TYPE [//SBMessage/Request] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML
Go

/* Create a Queue for sender */
CREATE QUEUE senderQueue
GO

/* Receiver Script */
/* Message Type */
CREATE MESSAGE TYPE [//SBMessage/Reply] AUTHORIZATION [dbo] VALIDATION = WELL_FORMED_XML
GO


/* Create a Contract */
CREATE CONTRACT [//SBMessage/Contract]
(
[//SBMessage/Request] SENT BY INITIATOR,
[//SBMessage/Reply] SENT BY TARGET
);
GO

/* Create a Queue for Reciever */
CREATE QUEUE receiveQueue
WITH STATUS = ON, RETENTION = OFF, ACTIVATION
(
PROCEDURE_NAME = dbo.insertemployee,
MAX_QUEUE_READERS = 10,
STATUS = ON,
EXECUTE AS SELF
)
GO

/* Create a Service script for sender */
CREATE SERVICE [//SBMessage/InitService] ON QUEUE senderQueue([//SBMessage/Contract]);
Go


/* Create a Service Script for Reciever */
CREATE SERVICE [//SBMessage/TargetService] ON QUEUE receiveQueue([//SBMessage/Contract]);
GO

IF EXISTS(SELECT '1' FROM sys.objects WHERE name = N'SendEmployeeRecord')
BEGIN
      DROP PROCEDURE SendEmployeeRecord;
END
Go

CREATE PROCEDURE SendEmployeeRecord(@name VARCHAR(30),@age INT,@salary INT)
AS
BEGIN

DECLARE @empxml XML
DECLARE @handle UNIQUEIDENTIFIER;

SELECT @empxml = '<employee><name>'+@name+'</name><age>'+CAST(@age AS VARCHAR(3))+'</age><salary>'+CAST(@salary AS VARCHAR(100))+'</salary></employee>'

-- Begin the handle
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE [//SBMessage/InitService]
TO SERVICE N'//SBMessage/TargetService'
ON CONTRACT [//SBMessage/Contract]
WITH ENCRYPTION = OFF ;

--Begin the conversation
SEND ON CONVERSATION @handle MESSAGE TYPE [//SBMessage/Request] (@empxml);

END

IF EXISTS(SELECT '1' FROM sys.objects WHERE name = N'insertemployee')
BEGIN
  DROP PROCEDURE insertemployee
END
Go


ALTER PROC dbo.insertemployee
AS
BEGIN
           
      DECLARE @contentxml XML
      DECLARE @conversationhandle UNIQUEIDENTIFIER
     
      WAITFOR (RECEIVE TOP(1) @contentxml = message_body,
                                                @conversationhandle = conversation_handle
                                           FROM receivequeue),TIMEOUT 5000
      IF @contentxml IS NOT NULL
      BEGIN
            INSERT INTO employee(name,age,salary)
            SELECT
               @contentxml.value('(employee/name)[1]','varchar(40)'),
               @contentxml.value('(employee/age)[1]','int'),
               @contentxml.value('(employee/salary)[1]','int')
        
      END              
     
      END CONVERSATION @conversationhandle
      WITH CLEANUP
     
END


---- Testing the Record
EXEC SendEmployeeRecordjgh’,23,4500000
SELECT * FROM employee





No comments:

Post a Comment