32. What are the various ways to fetch the 3rd highest pay employee record from a table ?
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.
34. How to select a Record as Xml from table ?
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
-- 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
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
SendEmployeeRecord ‘jgh’,23,4500000
SELECT * FROM employee
No comments:
Post a Comment