Question and Answers in Sql Server - Part 9

In This post Let we see some important question and answers in Sql Server, which is a continuation of previous post.



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>