Apply operator in Sql Server

Recently in a scenario, I used Cross Apply in a query and one of my colleagues asked me why and where to use Cross Apply and what is the difference between Cross Join and Cross Apply, so I thought of publishing an article related to same.

Cross Apply clause comes under Apply Operator which was introduced in SQL Server 2005.

The Apply operator acts like a Join without the ON clause. The Apply function in a query allows you to join a table to a table-valued function in such a way that function is invoked for each row returned from the table which you can't do with Join and is the main difference between Join and Apply

Apply operator can be used in two ways : Cross and Outer.

• The Cross Apply only returns rows from the left side table if the table-valued-function returns rows.
• The Outer Apply clause returns all the rows of the left side table regardless of whether table-valued-function returns any row or not. If no row is returned by table-valued-function, the columns that the table-valued-function returns are null.

Let's take an example to understand in more detail.

First create split function before running the below snippet.

declare @tab table(Category varchar(20), item varchar(max))
insert into @tab
select 'Vegetables', 'Carrot,Tomato' union all
select 'Fruits', 'Apple,Banana,Grape' union all
select 'Beverages', null

select t.Category, s.val as item
from   @tab t
cross apply dbo.split(item, ',') s

select t.Category, s.val as item
from   @tab t
outer apply dbo.split(item, ',') s

OUTPUT

Look at the output.

• First output with Cross Apply: No row with Category "Beverages" in the output
• Second output with Outer apply: Getting a row with Category "Beverages" in the output

Reason for difference:

• Function is returning null value for Category "Beverages" because there is null value in item column for "Beverages"

Output Clause in SQL Server 2005

SQL Server 2005 introduced a new TSQL feature OUTPUT clause that allows you to retrieve data affected by insert/update/delete statements easily. The  OUTPUT clause returns the data that you've inserted or deleted from the table within the statement.

Basically OUTPUT clause has the access to magic tables (inserted and deleted) like triggers. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table  Let us take examples to understand how OUTPUT works.

First create a table Employee

CREATE TABLE Employee(
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME
)

Example 1: Displaying the data just inserted with output clause within the same insert statement.

INSERT INTO Employee
OUTPUT inserted.*
SELECT 'EMP001', 'Sandeep', '01/01/2008' UNION ALL
SELECT 'EMP002', 'Abhay', '06/01/2008'

OUTPUT

In the above example we have inserted two rows in the Employee table and with OUTPUT clause we are displaying the rows we have just inserted from the same insert statement.

Example 2: Insert the data in other table on insertion and deletion of a table

DECLARE @TAB TABLE(
    EmpId VARCHAR(10), EmpName varchar(10), DOJ DATETIME, Action CHAR(1), LogTime DATETIME
)
INSERT INTO Employee
OUTPUT inserted.*, 'I', GETDATE() INTO @TAB
SELECT 'EMP003', 'Ritesh', '09/01/2008'

DELETE Employee
OUTPUT deleted.*, 'D', GETDATE() INTO @TAB
WHERE EmpId = 'EMP003'

SELECT * FROM @TAB

OUTPUT

In the above example first we have inserted and then deleted a row in the Employee table and with OUTPUT clause we have inserted both the inserted and deleted rows in the table variable from the inserted and deleted tables respectively. 

Customize a TFS Work Item to create new Work Item

Prerequisite:

• To export or list work item types, you must be a member of the Project Administrators group or have your View project-level information permission set to Allow.
• To import work item types, you must be a member of the Team Foundation Administrators security group or the Project Administrators security group.

We can customize a Work Item Type to create a new Work Item Type. Many organizations use the term defect in place of Bug and they are comfortable to keep the same term in TFS also. We will use a tool built in TFS 2010; called WitAdmin to create a new work item type Defect from Bug work item type. We will begin with getting the definition of work item of the Bug type and modify it to create a new work item type Defect.

Steps

1. Open the Visual Studio 2010 command prompt and run it as Administrator.

2. We begin with getting the Bug work item type definition in a XML file. Enter the following command:

   Operation complete message will appear.

Now we have the Bug work item type definition in XML file, we will edit it in Notepad. 3. In Window Explorer, find Bug.xml and open it for editing.

4. It will open it in Notepad where we can edit it.

5. Change the name from Bug to Defect and save the file.

6. Enter the command:

  The work item type import has completed message will appear.

7. You may now start Visual Studio 2010, open Team Explorer and browse to the Team Project named EngageTest. Expand to the Work Items node and right click on it. Select the option New Work Item and you will find Defect appear in the list.

Customizing Work Item Workflow in TFS 2010

Prerequisites:

• Team Foundation Server 2010
• Microsoft Visual Studio 2010
• TFS Power Tools

You must be a member of the Team Foundation Administrators security group or the Project Administrators security group.

When you install TFS Power Tools you get an additional menu option called Process Editor under Tools menu in Visual Studio 2010.

The Process Editor allows us to customize work item type workflow. For example, we can customize the Bug Work Item Workflow by clicking Work Item Types and selecting Open WIT from Server.

This brings up a dialog box with the available TFS Projects. So if we want to customize Bug Work Item we expand the desired TFS Project node and select Bug work item.

Selecting Bug Work Item Type opens the work item in Visual Studio tab. Click the Workflow tab to customize the associated workflow.

The original workflow for Bug Work Item Type takes bugs from Active to Resolved and finally Closed. Additionally bug can be sent back to Active state either from Resolved or Closed.

Now we want to add another state Awaiting Approval so that when a bug resolved it should go to Closed state only after approval. We do this by adding a new State object to the workflow from the toolbox.

Double click the new State and name it “Awaiting Approval”.

To connect the Workflow states, Click the Transition Link tool in toolbox and click the Resolved state and connect it to Awaiting Approval state. Repeat the same process for Awaiting Approval state to Active state.

When two state objects have been connected by a transition link we see a new blue transition object. We can click the downward-pointing double arrow to modify transition object.

Each transition must be given a reason which explains why the workflow progressed from one state to the next.
To set the reason why the workflow goes from Awaiting approval to Accepted we double-click the transition object connecting them. This brings up the Workflow Transition dialog.

We switch to the Reasons tab to set the reason(s) the change in state can occur. Simply click New to add a new reason.

In this case we say there are two reasons a Bug item can go into the Awaiting Approval state:

• Waiting to be accepted by Client (this is default).
• Waiting to be accepted by Product Manager.

First we’ll add the default reason, when the client’s acceptance is required.

We’ll also add the second reason, when product manager’s acceptance is required.

Now we have two reasons for a Bug going into Awaiting Approval state.

Similarly, add reasons for a Bug going into Active state from Awaiting Approval.

• Rejected by Client.
• Rejected by Product Manager.

There is one more thing left. Bug can progress to Closed state once it is approved. So we need to modify the workflow that sends a Bug from Resolved to Closed.
To do so double click on the transition state that links Resolved to Closed. This opens up Workflow Transition dialog box. On Transition Detail tab select Awaiting Approval in from dropdown.

This will link Awaiting Approval state to Closed state.

You can add reason to this transition like Approved by Client or Approved by Product Manager.

Also read, related post
Customize a TFS Work Item to create new Work Item

Lambda expression in C#

In this post, I am explaining what is Lambda Expression in C# and how to use it.

A lambda expression is an anonymous function that you can use to create delegates or expression tree types. All Lambda expressions use lambda operator =>.

• The left hand side of operator specified input parameter (if any)
• Expression or statement is written on the right side of the operator

Lambda expression

x => x*x;

In the above Lambda expression, "x" on the left side is input parameter and on the right side is expression that returns the square value (x*x) of input "x".

Now lets take some example to understand more clearly how Lambda expressions are used

Example: Using Lambda Expression with List to find out if input name exists in List

List<string> listNames = new List<string>();
listNames.Add("sandeep");
listNames.Add("Abhay");
listNames.Add("Ritesh");
string result = listNames.Find(name => name.Equals("sandeep"));

Example: Using Lambda expression, with collection to find out employee details passing Employee ID

using System;
using System.Collections.Generic;

namespace ConsoleApplication1
{
    class Program
    {       
        static void Main(string[] args)
        {
            EmpDetails empDetails = new EmpDetails();
            clsEmployee empDetail = empDetails.FindEmpByID("EMP001");
            if (empDetail == null)
                Console.Write("Employee does not exist");
            else
            {
                Console.Write("Emp ID : " + empDetail.EmpId);
                Console.Write( "\nEmp Name : " + empDetail.EmpName);
                Console.Write("\nEmp DOB : " + empDetail.DOB.ToString("dd/MM/yyyy"));
            }
            Console.ReadLine();
        }

        public class clsEmployee
        {
            public string EmpId { get; set; }
            public string EmpName { get; set; }
            public DateTime DOB { get; set; }
            public DateTime DOJ { get; set; }
        }

        public class EmpDetails
        {
            //Creating object
            List<clsEmployee> listEmpCollection = new List<clsEmployee>();

            //Initializing Employee Details using constructor
            public EmpDetails()
            {
                AddEmpDetails();
            }

            //Adding Employee details in the employees listEmpCollection

            private void AddEmpDetails()

            {
                listEmpCollection.Add(new clsEmployee { EmpId = "EMP001", EmpName = "Sandeep", DOB = DateTime.Now.Date.AddYears(-28) });
                listEmpCollection.Add(new clsEmployee { EmpId = "EMP002", EmpName = "Abhay", DOB = DateTime.Now.Date.AddYears(-26) });
                listEmpCollection.Add(new clsEmployee { EmpId = "EMP003", EmpName = "Ritesh", DOB = DateTime.Now.Date.AddYears(-25) });
            }

            //Method to find the Employee details by pasing Emp Id using Lambda expression
            public clsEmployee FindEmpByID(string EmpId)
            {
                return listEmpCollection.Find(m => m.EmpId == EmpId);
            }
        }
    }
}

OUTPUT