Q.) What is the data type of the result of a CASE function?.

A.) Returns the highest precedence type from the set of types in resultexpressions and the optional elseresult_expression.

Q.)Can you use the CASE function to simulate dynamic ORDER BY clause? If yes, how?.

A.) Yes.

SELECT EmpID,IsFullTime FROM tblEmployee
ORDER BY 
    CASE IsFullTime WHEN 1 THEN EmpID END DESC
        ,CASE WHEN IsFullTime = 0 THEN EmpID END

Q.)Demonstrate how you can use the APPLY operator or a subquery to avoid executing same query multiple times when the query (including shredding an xml doc) is part of the CASE function.

A.) Good example is given here. http://www.projectdmx.com/tsql/rowconcatenate.aspx

1.The datatype of the case statement will be the datatype which is having the highest precedence used in all datattypes among all the branches of the case statement. Supopse, a case statement has 2 conditions, one is of integer datatype and another is of real datatype, as real datatype has highest precendence over Int, Result will be real datatype.

This will gives the order of precedence of datatypes.

http://msdn.microsoft.com/en-us/library/ms190309.aspx

2.These are 2 queries taken which will show dynamic order by clause.

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;

These 2 queries are from MSDN :)

3.2nd query in below example will show how we can avoid Executing same expression multiple times by using cross apply. How ever i didn't understand the word why u used "shredding an xmldoc"?? Is it related with question??

declare @t table
(
    Id int
)
insert into @t values (1),(2),(3),(4),(5),(6)

select ID,
case
    when ID % 2 = 0 and ID % 3 = 0 then 'Divides by both 2 & 3'
    when ID % 2 = 0  then 'Divides by 2 only'
    when ID % 3 = 0 then 'Divides by 3 only'
    else 'Not divide by 2 or 2'
end
 from @t

 select ID,
case
    when t1.divide2 = 0 and t1.divide3 = 0 then 'Divides by both 2 & 3'
    when t1.divide2 = 0  then 'Divides by 2 only'
    when t1.divide3 = 0 then 'Divides by 3 only'
    else 'Not divide by 2 or 3'
end
 from @t 
 cross apply (select ID % 2 as divide2, ID % 3 as divide3  )t1

1) Case statement always return highest precedence

http://blog.sqlauthority.com/2010/10/08/sql-server-simple-explanation-of-data-type-precedence/

2)

USE AdventureWorks
GO
DECLARE @OrderBy VARCHAR(10)
DECLARE @OrderByDirection VARCHAR(1)
SET @OrderBy = 'State' ----Other options Postal for PostalCode,
---- State for StateProvinceID, City for City
SET @OrderByDirection = 'D' ----Other options A for ascending,
---- D for descending
SELECT AddressID, City, StateProvinceID, PostalCode
FROM person.address
WHERE AddressID < 100
ORDER BY
CASE WHEN @OrderBy = 'Postal'
AND @OrderByDirection = 'D'
THEN PostalCode END DESC,
CASE WHEN @OrderBy = 'Postal'
AND @OrderByDirection != 'D'
THEN PostalCode END,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection = 'D'
THEN StateProvinceID END DESC,
CASE WHEN @OrderBy = 'State'
AND @OrderByDirection != 'D'
THEN StateProvinceID END,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection = 'D'
THEN City END DESC,
CASE WHEN @OrderBy = 'City'
AND @OrderByDirection != 'D'
THEN City END
GO

3) RamiReddy has got it right. I can not come up anything better.

1.What is the data type of the result of a CASE function?.

Returns the highest precedence type from the set of types in resultexpressions and the optional elseresult_expression ( Taken from MSDN).

2.Can you use the CASE function to simulate dynamic ORDER BY clause? If yes, how?.

--( Taken from MSDN).

SELECT BusinessEntityID, SalariedFlag

FROM HumanResources.Employee

ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC

        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;

GO

3.Demonstrate how you can use the APPLY operator or a subquery to avoid executing same query multiple times when the query (including shredding an xml doc) is part of the CASE function.

What is the data type of the result of a CASE function?

The data type will be based on the highest precedence of the resulting data type of both, the WHEN and the ELSE section. (SQL 2005: http://msdn.microsoft.com/en-us/library/ms190309%28v=SQL.90%29.aspx)

Can you use the CASE function to simulate dynamic ORDER BY clause?

Yes.

If yes, how?

There are several options including the ones already presented.

Here's another version using a variable to change the sort order (source: http://www.sqlteam.com/article/dynamic-order-by):

(Side note: the sample below requires to have data types for CompanyName, ContactName, and ContactTitle that are similar or can be implicit converted into the one with the highes precedence. See question 1.)

DECLARE @SortOrder tinyint
SET @SortOrder = 2
SELECT CompanyName,
    ContactName,
    ContactTitle
FROM Customers
ORDER BY CASE WHEN @SortOrder = 1 THEN CompanyName
    WHEN @SortOrder = 2 THEN ContactName
    ELSE ContactTitle
END

Demonstrate how you can use the APPLY operator or a subquery to avoid executing same query multiple times when the query (including shredding an xml doc) is part of the CASE function.

Like some of the previous posters, I'm having difficulties to understand the question. If you're not looking for an answer along the lines Ramireddy came up with, please provide more information.

What is the data type of the result of a CASE function?

Returns the highest precedence type from the set of types of results (including ELSE result).

Can you use the CASE function to simulate dynamic ORDER BY clause? If yes, how?

Yes, and the following example demonstrate how:

DECLARE @sort_direction BIT = 0; /* 0 for ascending order, or 1 for descending order.*/
DECLARE @sort_column TINYINT = 0; /* 0 for num column. If not then string column.*/

SELECT num, string
  FROM (VALUES(1, 'z'), (2, 'y'), (3, 'x')) AS T(num, string)
 ORDER BY CASE WHEN @sort_direction = 0
                    THEN CASE WHEN @sort_column = 0
                                   THEN RIGHT(REPLICATE('0', 38) + CAST(num AS VARCHAR(38)), 38)
                              ELSE string
                         END
          END,
          CASE WHEN @sort_direction = 1
                    THEN CASE WHEN @sort_column = 0
                                   THEN RIGHT(REPLICATE('0', 38) + CAST(num AS VARCHAR(38)), 38)
                         ELSE string
                    END
          END DESC

Demonstrate how you can use the APPLY operator or a subquery to avoid executing same query multiple times when the query (including shredding an xml doc) is part of the CASE function.

The following script demonstrate how using subquery or APPLY operator avoided multiple execution of the COUNT query:

SELECT CASE (SELECT COUNT(N) AS num_count
               FROM tsqlc_Tally) % 2
            WHEN 0 THEN 'Even'
            ELSE 'Odd'
       END AS odd_or_even;

--subquery
SELECT CASE num_count % 2
            WHEN 0 THEN 'Even'
            ELSE 'Odd'
       END AS odd_or_even
  FROM (SELECT COUNT(N) AS num_count
          FROM tsqlc_Tally) AS T;

--APPLY operator
SELECT odd_or_even
  FROM (SELECT COUNT(N) AS num_count
          FROM tsqlc_Tally) AS T
        CROSS APPLY
        (SELECT CASE num_count % 2
                     WHEN 0 THEN 'Even'
                     ELSE 'Odd'
                END AS odd_or_even) AS T2;

See execution plans to confirm this, and the same principle applies for other queries like shredding an xml doc, and the following example demonstrate this:

SELECT CASE ISDATE(x.value('(/daterow/datecol)[1]', 'nvarchar(max)'))
            WHEN 0 THEN CAST('20100101' AS DATE)
            ELSE x.value('(/daterow/datecol)[1]', 'date')
       END
  FROM (SELECT CAST('<daterow><datecol>20101016</datecol></daterow>' AS xml)) AS T(x)

--subquery
SELECT CASE ISDATE(x)
            WHEN 0 THEN CAST('20100101' AS DATE)
            ELSE CAST(x AS DATE)
       END
  FROM (SELECT x.value('(/daterow/datecol)[1]', 'nvarchar(max)')
          FROM (SELECT CAST('<daterow><datecol>20101016</datecol></daterow>' AS xml)) AS T(x)) AS T(x)

1.When you use CASE function in a query, the result will be the expression with the highest precedence. So, it's a good practice to always use the same type of expressions when using CASE function. You may want to read this blog SQL Server Case/When Data Type problems

2.Yes, case function can be used to simulate dynamic Order By. Say, you pass @OrderBy clause to the stored procedure and @OrderBy can be either 'OrderDate' or 'TotalDue'

Example:

declare @OrderBy varchar(20) 

set @OrderBy = 'TotalDue'

select top (10)  OrderDate, TotalDue
from AdventureWorks.Sales.SalesOrderHeader 
ORDER BY case when @OrderBy = 'TotalDue' then TotalDue end,
case when @OrderBy = 'OrderDate' then OrderDate end

set @OrderBy  = 'OrderDate'

select top (10)  OrderDate, TotalDue
from AdventureWorks.Sales.SalesOrderHeader 
ORDER BY case when @OrderBy = 'TotalDue' then TotalDue end,
case when @OrderBy = 'OrderDate' then OrderDate end

3.APPLY operator can be used in many situations - one of the applications is to avoid the repeating of the same statements.

Say, we want to find 10 most expensive products for each category of products but only if the word Silver is in one of the names of the products. Here is the query we can write (using APPLY operator and using this newly created column name in the WHERE expression):

Select PC.[Name] as Category, 
PSC.[Name] as SubCategory,  stuff(F.[Products],1,1,'') as [Products]
from AdventureWorks.Production.ProductCategory PC
inner join 
AdventureWorks.Production.ProductSubcategory PSC on PC.ProductCategoryID = PSC.ProductSubcategoryID 
cross apply (select top (10) '|' + [Name] from AdventureWorks.Production.Product P
where P.ProductSubcategoryID = PSC.ProductSubcategoryID order by ListPrice DESC FOR XML PATH('') ) F (Products)
where [Products] LIKE '%Silver%'

Of course, there are many other applications of the APPLY operator, say, you can check one of them in my blog:

Parsing the Address field to its individual components where I show how using multiple cross apply operators can reduce complexity of the task and solve it in a few steps.

There is also a very deep blog about CASE and Subqueries by Craig Freedman Subqueries in CASE expressions.

Here is an example of using APPLY operator to shred XML document:

declare @xml XML = '<OrdersWithTheirCopies>
 <Order OrderID="1" OrderNumber="Order #1">
  <OrderLines>
   <OrderLine OrderLineID="1" LineNumber="Line #1" ItemDescription="pipe - 100" />
   <OrderLine OrderLineID="2" LineNumber="Line #2" ItemDescription="plate 10" />
   <OrderLine OrderLineID="3" LineNumber="Line #3" ItemDescription="glass 1 mm." />
  </OrderLines>
  <OrderCopies>
   <Copy CopyNumber="1" OrderHistoryID="3">
    <OrderLineHistory OrderLineID="1" OrderLineHistoryID="8" ItemDescription="pipe - 100" />
    <OrderLineHistory OrderLineID="2" OrderLineHistoryID="9" ItemDescription="plate 10" />
    <OrderLineHistory OrderLineID="3" OrderLineHistoryID="10" ItemDescription="glass 1 mm." />
   </Copy>
   <Copy CopyNumber="2" OrderHistoryID="4">
    <OrderLineHistory OrderLineID="1" OrderLineHistoryID="11" ItemDescription="pipe - 100" />
    <OrderLineHistory OrderLineID="2" OrderLineHistoryID="12" ItemDescription="plate 10" />
    <OrderLineHistory OrderLineID="3" OrderLineHistoryID="13" ItemDescription="glass 1 mm." />
   </Copy>
   <Copy CopyNumber="3" OrderHistoryID="6">
    <OrderLineHistory OrderLineID="1" OrderLineHistoryID="16" ItemDescription="pipe - 100" />
    <OrderLineHistory OrderLineID="2" OrderLineHistoryID="17" ItemDescription="plate 10" />
    <OrderLineHistory OrderLineID="3" OrderLineHistoryID="18" ItemDescription="glass 1 mm." />
   </Copy>
  </OrderCopies>
 </Order>
 <Order OrderID="2" OrderNumber="Order #2">
  <OrderLines>
   <OrderLine OrderLineID="4" LineNumber="Line #1" ItemDescription="pipe - 200" />
   <OrderLine OrderLineID="5" LineNumber="Line #2" ItemDescription="plate 20" />
  </OrderLines>
  <OrderCopies>
   <Copy CopyNumber="1" OrderHistoryID="2">
    <OrderLineHistory OrderLineID="4" OrderLineHistoryID="6" ItemDescription="pipe - 200" />
    <OrderLineHistory OrderLineID="5" OrderLineHistoryID="7" ItemDescription="plate 20" />
   </Copy>
   <Copy CopyNumber="2" OrderHistoryID="5">
    <OrderLineHistory OrderLineID="4" OrderLineHistoryID="14" ItemDescription="pipe - 200" />
    <OrderLineHistory OrderLineID="5" OrderLineHistoryID="15" ItemDescription="plate 20" />
   </Copy>
  </OrderCopies>
 </Order>
 <Order OrderID="3" OrderNumber="Order #3">
  <OrderLines>
   <OrderLine OrderLineID="6" LineNumber="Line #1" ItemDescription="pipe - 300" />
   <OrderLine OrderLineID="7" LineNumber="Line #2" ItemDescription="plate - 30" />
   <OrderLine OrderLineID="8" LineNumber="Line #3" ItemDescription="glass - 3 mm" />
   <OrderLine OrderLineID="9" LineNumber="Line #4" ItemDescription="pipe - 330" />
   <OrderLine OrderLineID="10" LineNumber="Line #5" ItemDescription="plate - 33" />
  </OrderLines>
  <OrderCopies>
   <Copy CopyNumber="1" OrderHistoryID="1">
    <OrderLineHistory OrderLineID="6" OrderLineHistoryID="1" ItemDescription="pipe - 300" />
    <OrderLineHistory OrderLineID="7" OrderLineHistoryID="2" ItemDescription="plate - 30" />
    <OrderLineHistory OrderLineID="8" OrderLineHistoryID="3" ItemDescription="glass - 3 mm" />
    <OrderLineHistory OrderLineID="9" OrderLineHistoryID="4" ItemDescription="pipe - 330" />
    <OrderLineHistory OrderLineID="10" OrderLineHistoryID="5" ItemDescription="plate - 33" />
   </Copy>
  </OrderCopies>
 </Order>
</OrdersWithTheirCopies>'

&

SELECT
    O.x.value('@OrderID[1]', 'int') AS OrderID,
    O.x.value('@OrderNumber[1]', 'varchar(50)') AS OrderNumber
FROM
    @xml.nodes('/OrdersWithTheirCopies/Order') AS O(x);

SELECT
    C.x.value('@OrderHistoryID[1]', 'int') AS OrderHistoryID,
    O.x.value('@OrderID[1]', 'int') AS OrderID,
    C.x.value('@CopyNumber[1]', 'int') AS CopyNumber
FROM
  @xml.nodes('/OrdersWithTheirCopies/Order') AS O(x)
  CROSS APPLY
  O.x.nodes('OrderCopies/Copy') AS C(x);

SELECT
    L.x.value('@OrderLineHistoryID[1]', 'int') AS OrderLineHistoryID,
    O.x.value('@OrderID[1]', 'int') AS OrderID,
    L.x.value('@OrderLineID[1]', 'int') AS OrderLineID,
    L.x.value('@ItemDescription[1]', 'varchar(max)') AS ItemDescription
FROM
  @xml.nodes('/OrdersWithTheirCopies/Order') AS O(x)
  CROSS APPLY
  O.x.nodes('OrderCopies/Copy') AS C(x)
  CROSS APPLY
  C.x.nodes('OrderLineHistory') AS L(x);

1)What is the data type of the result of a CASE function?.

Returns the highest precedence type from the set of types in resultexpressions and the optional elseresult_expression

Reference:

CASE (Transact-SQL)

Data Type Precedence (Transact-SQL)

2) Can you use the CASE function to simulate dynamic ORDER BY clause?

Yes

If yes, how?.

The following examples uses the CASE expression in an ORDER BY clause to determine the sort order of the rows based on a given column value. In the first example, the value in the SalariedFlag column of the HumanResources.Employee table is evaluated. Employees that have the SalariedFlag set to 1 are returned in order by the EmployeeID in descending order. Employees that have the SalariedFlag set to 0 are returned in order by the EmployeeID in ascending order.

SELECT BusinessEntityID, SalariedFlag
FROM HumanResources.Employee
ORDER BY CASE SalariedFlag WHEN 1 THEN BusinessEntityID END DESC
        ,CASE WHEN SalariedFlag = 0 THEN BusinessEntityID END;
GO

In the second example, the result set is ordered by the column TerritoryName when the column CountryRegionName is equal to 'United States' and by CountryRegionName for all other rows.

SELECT BusinessEntityID, LastName, TerritoryName, CountryRegionName
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL
ORDER BY 
CASE CountryRegionName WHEN 'United States' THEN TerritoryName
         ELSE CountryRegionName END;

Note: CASE can be used on UPDATE statement, SET statement, HAVING clause apart from ORDER by clause.

Source: Using CASE in an ORDER BY clause http://msdn.microsoft.com/en-us/library/ms181765.aspx

3) I could not understand the question.

1. What is the data type of the result of a CASE function?

2.Can you use the CASE function to simulate dynamic ORDER BY clause? If yes, how?

3.Demonstrate how you can use the APPLY operator or a subquery to avoid executing same query multiple times when the query (including shredding an xml doc) is part of the CASE function.

1.The data type of the result of a CASE function depends on the output values specified. If all output values are of data type varchar for instance, then the result of the function will also be varchar. However if the data types within the function are mixed, for example "CASE WHEN 1=1 THEN 'x' ELSE N'y' END" then the data type with the highest precedence is returned, in this case nvarchar (even though this CASE function will never return the nvarchar output value). As a result of this, each output value must be valid for implicit conversion to the data type of the highest precedence within the CASE function, else an error will occur.

2.The CASE function can be used in the ORDER BY clause, and therefore you can order the data depending on the conditions you set in the CASE function. The CASE function is resolved for each row in the result set, so your logic must affect each row in the correct way to produce the desired "dynamic" effect. For example:

(checks a user parameter and orders by matching employee name(s) first, then all other employees alphabetically)

ORDER BY

         CASE WHEN EmployeeName LIKE @myvar+'%' THEN 0 ELSE 1 END

        ,EmployeeName

3.I presume this question is referring to a subquery within the SELECT list (as part of a CASE function), e.g.

SELECT
                 EmployeeID
                ,CASE WHEN       (SELECT fieldXML.exist('/Employee[@ID=sql:column("EmployeeID")][1]')
                                                                 FROM xmlfiles
                                                                 WHERE Employees.xmldocID = xmlfiles.xmldocID) = 1
                                THEN     (SELECT fieldXML.value('/Employee[@ID=sql:column("EmployeeID")][1]/Name[1]','varchar(100)')
                                                                 FROM xmlfiles
                                                                 WHERE Employees.xmldocID = xmlfiles.xmldocID)
                                ELSE '' END AS EmpName
FROM Employees

In the above example, the CASE function and therefore the subquery is executing for every row of the Employees table, and shredding the xml document to find each employee's name based on their ID, if the ID exists in the XML document.

The multiple executions come from the SELECT statement being executed within the outer SELECT list. To reduce the executions, ideally down to one, we can move the inner SELECT statement(s) to become subqueries or part of an APPLY statement. The move must be to the FROM clause to join to the driver table (Employees in this example), e.g.

SELECT
    Emp.EmployeeID,
    xrec.Name AS EmpName
FROM Employees AS Emp
    LEFT OUTER JOIN
    (SELECT xmlfiles.xmldocID,
        e.value('@ID[1]','int') AS EmployeeID,
        e.value('/Name[1]','varchar(100)') AS Name
    FROM xmlfiles
    	CROSS APPLY fieldXML.nodes('/Employee') x(e)) AS xrec
    	ON Emp.xmldocID = xrec.xmldocID
    	AND Emp.EmployeeID = xrec.EmployeeID

The above example makes use of both a subquery and the APPLY function, by expanding out the nodes of the XML and then joining on matching IDs with the driver table. This ensures that the XML query is not run for each row of the Employees table.

There are many ways to re-write queries to make use of subqueries, the APPLY function or other methods in order to reduce executions and reads for performance gains. The example above is only one method.

What is the data type of the result of a CASE function?

Quoting Books Online (and practically everyone that has answered this already :) ):

Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

An important piece of information that is imperative to appropriately applying this principal to reality is described on the Data Type Precedence information from Books Online (emphasis added):

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

What does this mean? Basically, if the lower data type will not implicitly convert to the higher, a runtime error will be returned. Consider the following example that will return such error:

select
    case
        when 1=1 then 'a'
        else 1
    end

Can you use the CASE function to simulate dynamic ORDER BY clause? If yes, how?

Indeed, the CASE function is quite capable of simulating a dynamic ORDER BY clause. Consider the following (note: this will only work in 2008):

declare @sort int = 2;

select
    num
    ,chr
from (values(1,'d'),(2, 'c'),(3, 'b'),(4, 'a')) t(num, chr)
order by
    case @sort
        when 2 then chr
        else cast(num as char(1))
    end

Again, it's important to note that the resulting data types be able to implicitly convert without error. In this case, I chose to cast the integer typed column as a character as the sorting would still be accurate.

Demonstrate how you can use the APPLY operator or a subquery to avoid executing same query multiple times when the query (including shredding an xml doc) is part of the CASE function.

Assuming that I've understood what we're after here, the following should provide an adequate example:

with T(id, steps) as
(
    select
        *
    from (values(1, cast('<root>
      <location locationid="10">
         <step>1</step>
         <step>2</step>
      </location>
      <location locationid="20">
         <step>2</step>
         <step>3</step>
      </location>
      <location locationid="30">
         <step>3</step>
         <step>4</step>
      </location>
    </root>' as xml))) tbl(id, steps)
)
select
    case f.a
        when 10 then 'Dallas'
        when 20 then 'New York City'
        when 30 then 'Orlando'
        else 'Unknown'
    end
from T
cross apply steps.nodes('root/Location') as T2(Loc)
cross apply (select T2.Loc.value('@LocationID', 'int')) f(a)

You'll notice that we had to use CROSS APPLY to traverse until we got to the value for which we were after (in this case, the LocationID attribute). If we had not, the CASE function causes an execution of the .value method for each option. Below is the query plan:

The inefficient alternative would have looked something like this:

with T(id, steps) as
(
    select
        *
    from (values(1, cast('<root>
      <location locationid="10">
         <step>1</step>
         <step>2</step>
      </location>
      <location locationid="20">
         <step>2</step>
         <step>3</step>
      </location>
      <location locationid="30">
         <step>3</step>
         <step>4</step>
      </location>
    </root>' as xml))) tbl(id, steps)
)
select
    case T2.Loc.value('@LocationID', 'int')
        when 10 then 'Dallas'
        when 20 then 'New York City'
        when 30 then 'Orlando'
        else 'Unknown'
    end
from T
cross apply steps.nodes('root/Location') as T2(Loc)

Very subtle difference, but the query plan nearly says it all. It should be noted that for every additional option on the CASE funtion, this query plan will grow by another execution of the .value method. However, the former query plan remains exactly the same.