Saturday, 11 June 2016

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)+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>





No comments:

Post a Comment