Advertisements

Archive

Archive for January, 2012

manub22 (me) on MCC 2011 awardee list

January 10, 2012 Leave a comment

Just about a year back in Feb-2011, I was awarded by “Microsoft Community Contributor” (MCC) Award for my contributions in Microsoft’s online technical community “MSDN Transact SQL” forum. Check here for more information.

This was a kind gesture from Microsoft to award people for dedicating their precious time for the community. The award includes MCC logo, badge, Safari BOL 1 year free subscription & access to some MSDN’s knowledge bank.

Microsoft has also put a list of all awardees on their MCC site with their Names and date of award, link: https://www.microsoftcommunitycontributor.com/current-awardees.aspx

And below is the snapshot of the page where my name appears:

I started following up MSDN forums at the very start of my carrier (around mid of 2006) when I was learning SQL Server. I used to put up my queries there and was very satisfied with the kind of response I used to get. I also used to browse through other questions to see what other techies are asking & are stuck upon, and sometimes try to answer those questions. This started becoming a good learning source and encouraged me to dedicate my time here. And I remember at around year 2010 Nov-Dec I was kind of addicted to this. Now whenever I get time I check the forum and answer the questions where I can add some value.

You can see my activity and contributions on my MSDN profile at following link: http://social.msdn.microsoft.com/Profile/manub22

Advertisements

Using CROSS APPLY & OUTER APPLY operators with UDFs, Derived tables & XML data

January 3, 2012 4 comments

In my previous posts CROSS APPLY vs OUTER APPLY I discussed on the same topic, but didn’t covered much on examples. Here we will see more examples on using APPLY operators, their workarounds and performance.

>> Using APPLY operator with User Define Functions:

USE [AdventureWorks]
GO

-- Create an "Inline Table-valued Function" that returns Sales Details of a Contact (Customer):
CREATE FUNCTION [dbo].[ufnGetSalesInfo](@ContactID int)
	RETURNS TABLE 
AS 
RETURN (
	SELECT SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, CustomerID, TotalDue
	FROM Sales.SalesOrderHeader
	WHERE ContactID = @ContactID
)
GO

-- We can use this function with SELECT FROM statement like we do for a table:
SELECT * FROM [dbo].[ufnGetSalesInfo](100)

Here, we cannot provide multiple IDs or any variable to make it work for multiple or dynamic Contacts. To return Sales details for multiple Customers we need to JOIN it with Contacts table, but JOINS are now allowed with UFDs, they only work with Tables/Views.

So to JOIN a UDF with a TABLE we need an APPLY operator, check examples below:

-- CROSS APPLY acts as INNER JOIN & will return only matching rows, returns 513 rows:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
CROSS APPLY [dbo].[ufnGetSalesInfo](con.ContactID) x
WHERE con.ContactID <= 100

-- OUTER APPLY acts as OUTER LEFT JOIN & will return all matching & non-matching rows, returns 529 rows:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
OUTER APPLY [dbo].[ufnGetSalesInfo](con.ContactID) x
WHERE con.ContactID <= 100


-- Final Cleanup
DROP FUNCTION [dbo].[ufnGetSalesInfo]
GO

>> Using APPLY operator with Derived tables:

[Scenario]: Pull upto 5 Sales Orders for each Contact (Customer):

This is not possible with JOINS, because the condition to SELECT max 5 Orders is applied after the JOIN is made, we need to add the condition before JOIN. Using APPLY operator we can do this, let’s see how:

-- With Derived Table:
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	x.*
FROM Person.Contact con
CROSS APPLY (SELECT TOP 5 
				SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, CustomerID, TotalDue 
			 FROM Sales.SalesOrderHeader soh
			 WHERE soh.ContactID = con.ContactID) x
WHERE con.ContactID <= 100

-- We can also write the above query by using ROW_NUMBER() function & JOINs instead of the APPLY operator:
;WITH CTE AS (
	SELECT 
		SalesOrderID, OrderDate, PurchaseOrderNumber, AccountNumber, ContactID, TotalDue,
		ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY ContactID, SalesOrderID) AS ROWNUM
		FROM Sales.SalesOrderHeader)
SELECT 
	con.ContactID, con.FirstName, con.LastName, con.EmailAddress, con.Phone,
	CTE.*
FROM Person.Contact con
JOIN CTE cte
ON cte.ContactID = con.ContactID
WHERE con.ContactID <= 100
AND cte.ROWNUM <= 5
GO

Both of these queries do the same task. So to check which one is more optimized we can check the cost of each query in the batch. So the cost of query using APPLY operator is 84% and query using ROW_NUMBER & JOIN is 16%. Hence the second approach is much more performant & should be considered.

>> Using APPLY operator with XML data:

APPLY operators can also we used with XML data when there is a need to query parent/child nodes, check my previous blog post on this topic.

2011 in review

January 1, 2012 2 comments

The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.

Here’s an excerpt:

Madison Square Garden can seat 20,000 people for a concert. This blog was viewed about 64,000 times in 2011. If it were a concert at Madison Square Garden, it would take about 3 sold-out performances for that many people to see it.

Click here to see the complete report.