Tuesday, April 12, 2011

T-SQL Tuesday #017: APPLY: It Slices! It Dices! It Does It All!

T-SQL TuesdayThis blog entry is participating in T-SQL Tuesday #017, hosted this month by Matt Velic.

You are invited to visit his blog and join the party and read more blogs participating in this month’s theme: APPLY Knowledge.

I’ve been a fan of the APPLY operator since the beginning. That’s why it’s incorporated into the name of my blog. And I've blogged about its power about a dozen times.

I don’t know how anyone lived without it before SQL2005.

Practically everyone knows that you can use it to invoke table-valued functions. That is its most obvious usage and it’s about the only way you’ll see APPLY demonstrated in 97% of the books on SQL Server (if they even mention it at all).

APPLY is a cool cat, baby!But, as you can see by the twinkle in APPLY’s eyes at the left (even behind the cool shades), he’s got other things up his sleeve.

(Okay, the goofy pointed hat and the goatee are a little much, but hey, if you want great artwork, you’re in the wrong place… Michael J. Swart or Kendra Little are the masters of illustration).

APPLY is capable of soooooo much more than just invoking TVF’s. It is incredibly versatile. It seems like it can do anything!

Let’s take a look at what you can do!



Call Table-Valued Functions!

Yawn. Okay, so this is the most common way people use APPLY. Here’s a quick demo… For each Vista credit card expiring in Jun2008, let’s get the Contact information for that card using a built-in function in AdventureWorks.

select f.FirstName
,f.LastName
,f.JobTitle
,f.ContactType
,cc.CardNumber
from Sales.CreditCard cc
join Sales.ContactCreditCard ccc on cc.CreditCardID=ccc.CreditCardID
cross apply dbo.ufnGetContactInformation(ccc.ContactID) f
where cc.ExpYear=2008
and cc.ExpMonth=6
and cc.CardType='Vista'
/*
FirstName LastName JobTitle ContactType CardNumber
--------- -------- ---------------- ------------- --------------
Peggy Justice Owner Store Contact 11119759315644
John McClane Purchasing Agent Store Contact 11119490672347
Laura Cai NULL Consumer 11112813884091
Natalie Gonzales NULL Consumer 11114369564985
Jarrod Sara NULL Consumer 11116045498593
Katherine Bailey NULL Consumer 11119100149656
Stephanie Gray NULL Consumer 11112324154556
Shawna Sharma NULL Consumer 11116413893110
Mindy Rai NULL Consumer 11115163407997
Jackson Jai NULL Consumer 11112011871602
And so on... (74 rows total)
*/
That’s very convenient, but kind of boring in the grand scheme of things. So let’s move on.

Execute SubQueries!

Why even bother to create a function when you can just create a table on the fly via a correlated subquery with APPLY?

You are only limited by your imagination.

Here’s an example…

For each store with a main office in Wisconsin, let’s look at the top 3 products (and their dollar amounts) that they bought in terms of dollars.

select c.CustomerID
,s.Name
,f.ProductID
,ProductName=p.Name
,f.PurchaseAmt
from Sales.Customer c
join Sales.Store s on c.CustomerID=s.CustomerID
join Sales.CustomerAddress ca on c.CustomerID=ca.CustomerID
join Person.Address a on ca.AddressID=a.AddressID
join Person.StateProvince sp on a.StateProvinceID=sp.StateProvinceID
cross apply (select top 3 ProductID
,PurchaseAmt=sum(LineTotal)
from Sales.SalesOrderHeader soh
join Sales.SalesOrderDetail sod on soh.SalesOrderID=sod.SalesOrderID
where CustomerID=c.CustomerID
group by ProductID
order by sum(LineTotal) desc) f
join Production.Product p on f.ProductID=p.ProductID
where ca.AddressTypeID=3 --MainOffice
and sp.StateProvinceCode='WI'
/*
CustomerID Name ProductID ProductName PurchaseAmt
---------- --------------------------- --------- -------------------------- ------------
418 Good Bike Shop 795 Road-250 Black, 52 30367.350000
418 Good Bike Shop 794 Road-250 Black, 48 24136.807500
418 Good Bike Shop 792 Road-250 Red, 58 23508.517500
453 Unique Bikes 773 Mountain-100 Silver, 44 16319.952000
453 Unique Bikes 771 Mountain-100 Silver, 38 12239.964000
453 Unique Bikes 772 Mountain-100 Silver, 42 12239.964000
543 Friendly Neighborhood Bikes 782 Mountain-200 Black, 38 9638.958000
543 Friendly Neighborhood Bikes 868 Women's Mountain Shorts, M 671.904000
543 Friendly Neighborhood Bikes 869 Women's Mountain Shorts, L 335.952000
606 Little Bicycle Supply Shop 717 HL Road Frame - Red, 62 1717.800000
606 Little Bicycle Supply Shop 838 HL Road Frame - Black, 44 858.900000
606 Little Bicycle Supply Shop 738 LL Road Frame - Black, 52 809.328000
*/
That’s pretty slick, huh?

Shred XML!

Using the .nodes() function, coupled with the .value() and .query() functions, we can use APPLY to do some cool tricks with XML.

For the first 10 JobCandidates, let’s pull information out of the Resume column, which is of type XML. We’ll get their Name and the schools (there might be more than one) that they attended, listing them in order of their graduation date.:

with xmlnamespaces 
(
'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/Resume' as ns
)
select JobCandidateID
,Name
,Education=stuff(EduList,1,2,'')
from HumanResources.JobCandidate
cross apply
Resume.nodes('/ns:Resume') F_ResumeNode(ResumeNode)
cross apply

ResumeNode
.nodes('(./ns:Name)') F_NameNode(NameNode)
cross apply

(select Name=NameNode.value('(./ns:Name.First[1])','nvarchar(50)')
+' '
+NameNode.value('(./ns:Name.Last[1])','nvarchar(50)')
) F_Name
cross apply
(select EduList=ResumeNode.query('for $p in (./ns:Education)
order by $p/ns:Edu.EndDate
return concat("; ",string($p/ns:Edu.School))'
).value('.','nvarchar(200)')
) F_Edu
where JobCandidateID<=10
/*
JobCandidateID Name Education
-------------- -------------------- ----------------------------------------------------
1 Shai Bassli Midwest State University
2 Max Benson Evergreen High School ; Everglades State College
3 Krishna Sunkammurali Western University
4 Stephen Jiang Louisiana Business College of New Orleans
5 Thierry D'Hers Université d'Aix-Marseille
6 Christian Kleinerman Lycée technique Émile Zola ; Université de Perpignan
7 Lionel Penuchot Université de Lyon
8 Peng Wu Western University
9 Shengda Yang Evergreen High School ; Soutern State College
10 Tai Yee Midwest State University
*/
As Miley Cyrus would say: That’s really cool.

Introduce New Columns!

This is probably the best use of APPLY because it makes code so much more clear.

Consider the following query, which groups the 2002 Sales by Month. That’s done by the DATEADD/DATEDIFF logic, but it has to be repeated in the GROUP BY and the SELECT and the ORDER BY:

select Mth=datename(month
,dateadd(month
,datediff(month,'19000101',OrderDate)
,'19000101'))
,Total=sum(TotalDue)
from Sales.SalesOrderHeader
where OrderDate>='20020101'
and OrderDate<'20030101'
group by dateadd(month
,datediff(month,'19000101',OrderDate)
,'19000101')
order by dateadd(month
,datediff(month,'19000101',OrderDate)
,'19000101')
/*
Mth Total
--------- ------------
January 1605782.1915
February 3130823.0378
March 2643081.0798
April 1905833.9088
May 3758329.2949
June 2546121.9618
July 3781879.0708
August 5433609.3426
September 4242717.7166
October 2854206.7518
November 4427598.0006
December 3545522.738
*/
I don’t know about you, but I hate all that repetition, and it looks a little busy. So APPLY to the rescue:

select Mth=datename(month,FirstDayOfMth)
,Total=sum(TotalDue)
from Sales.SalesOrderHeader
cross apply
(
select FirstDayOfMth=dateadd(month
,datediff(month,'19000101',OrderDate)
,'19000101')
) F_Mth
where OrderDate>='20020101'
and OrderDate<'20030101'
group by FirstDayOfMth
order by FirstDayOfMth
/*
Mth Total
--------- ------------
January 1605782.1915
February 3130823.0378
March 2643081.0798
April 1905833.9088
May 3758329.2949
June 2546121.9618
July 3781879.0708
August 5433609.3426
September 4242717.7166
October 2854206.7518
November 4427598.0006
December 3545522.738
*/
Now isn’t that much clearer as to what’s going on? And it costs nothing at all! The query plans of both of the queries above are exactly the same!

Perform Complicated Calculations!

This is the part of APPLY that I really love. Let’s look at an example.

Let’s say that you have a table of comma-delimited lists of one or more integers:

create table #t
(
ID int identity(1,1)
,ListOfNums varchar(50)
)
insert #t
values ('279,37,972,15,175')
,('17,72')
,('672,52,19,23')
,('153,798,266,52,29')
,('77,349,14')
select * from #t
/*
ID ListOfNums
-- -----------------
1 279,37,972,15,175
2 17,72
3 672,52,19,23
4 153,798,266,52,29
5 77,349,14
*/
Your job: Pull out only the rows that have the 4th number in the list less than 50 and sort the output by the 3rd number in the list.

Easy, right? Ha ha ha ha ha ha ha hee hee hee hee hee hee ho ho ho ho haw haw giggle chuckle guffaw!

Before the APPLY operator, SQL2000 folks would have to resort to something ludicrous like this in order to accomplish this task:

select ID
,ListOfNums
from #t
where substring(ListOfNums+',,,,',charindex(',',ListOfNums+',,,,',
charindex(',',ListOfNums+',,,,',charindex(',',ListOfNums+',,,,')+1)+1)+1,
(charindex(',',ListOfNums+',,,,',charindex(',',ListOfNums+',,,,',
charindex(',',ListOfNums+',,,,',charindex(',',ListOfNums+',,,,')+1)+1)+1)-
charindex(',',ListOfNums+',,,,',charindex(',',ListOfNums+',,,,',
charindex(',',ListOfNums+',,,,')+1)+1))-1)
< 50
order by substring(ListOfNums+',,,,',charindex(',',ListOfNums+',,,,',
charindex(',',ListOfNums+',,,,')+1)+1,(charindex(',',ListOfNums+',,,,',
charindex(',',ListOfNums+',,,,',charindex(',',ListOfNums+',,,,')+1)+1)-
charindex(',',ListOfNums+',,,,',charindex(',',ListOfNums+',,,,')+1))-1)
/*
ID ListOfNums
-- -------------
2 17,72
5 77,349,14
3 672,52,19,23
1 279,37,972,15
*/
But now, through the magic of APPLY, you can have a much clearer query:

select ID
,ListOfNums
from #t
cross apply (select WorkString=ListOfNums+',,,,') F_Str
cross apply (select p1=charindex(',',WorkString)) F_P1
cross apply (select p2=charindex(',',WorkString,p1+1)) F_P2
cross apply (select p3=charindex(',',WorkString,p2+1)) F_P3
cross apply (select p4=charindex(',',WorkString,p3+1)) F_P4
cross apply (select Num3=convert(int,substring(WorkString,p2+1,p3-p2-1))
,Num4=convert(int,substring(WorkString,p3+1,p4-p3-1))) F_Nums
where Num4<50
order by Num3
/*
ID ListOfNums
-- -------------
2 17,72
5 77,349,14
3 672,52,19,23
1 279,37,972,15
*/
See how I used APPLY to write a little program of a sort? First, I added commas to the end of the column to account for possibly missing numbers in the list. Then I calculated the position of the first comma in that string (p1). Then I calculated the position of the second comma (p2), and that can only be done by using the p1 position I calculated in the previous step. I continue on getting the position of the third and fourth comma. And now that I have those, I can pull out Num3 (from between the second and third comma) and Num4 (from between the third and fourth comma). And I can now use those values in my WHERE and ORDER BY clause.

And the best part? NO COST! The above two queries are exactly the same as far as the optimizer is concerned. All those CROSS APPLYs are glommed together into a Compute Scalar operator, essentially coming up with really complicated expressions like you see in the first query. Take a look at the query plan yourself and you’ll see.

Replace the UNPIVOT operator!

Throw the UNPIVOT operator out the window… The optimizer really translates it into an APPLY operator under the hood anyway… and you can have control over NULLs and differing datatypes.

Look at the following example, which is a function that accepts a CustomerID and spits out information on the customer in a vertical fashion (note that there can be multiple contacts for a customer… this just spits out the first one… thus the ROW_NUMBER() logic):

create function VerticalMainOfficeData
(
@CustomerID int
)
returns table
as
return
with
BaseData as
(
select SeqNo=row_number() over (order by ContactType)
,Name,AddressLine1,AddressLine2,City,StateProvinceName
,PostalCode,CountryRegionName
,ContactType,ContactName,Phone,EmailAddress
,YearOpened,NumberEmployees,Specialty
,SquareFeet,Brands,AnnualSales
from AdventureWorks.Sales.vStoreWithDemographics
cross apply (select ContactName=isnull(Title+' ','')
+FirstName+' '
+isnull(MiddleName+' ','')
+LastName
+isnull(' '+Suffix,'')) F_Name
where CustomerID=@CustomerID
and AddressType='Main Office'
)
select Property,Value
from BaseData
cross apply
(values ('NAME AND ADDRESS:','')
,(' Name',Name)
,(' Address',AddressLine1)
,(' ',AddressLine2)
,(' City',City)
,(' State/Province',StateProvinceName)
,(' Postal Code',PostalCode)
,(' Country/Region',CountryRegionName)
,('','')
,('CONTACT:','')
,(' Type',ContactType)
,(' Name',ContactName)
,(' Phone',Phone)
,(' EmailAddress',EmailAddress)
,('','')
,('DEMOGRAPHIC INFO:','')
,(' Year Opened',str(YearOpened,4))
,(' Number of Employees',convert(varchar(10),NumberEmployees))
,(' Specialty',Specialty)
,(' Square Feet',convert(varchar(10),SquareFeet))
,(' Brands',Brands)
,(' Annual Sales','$'+convert(varchar(20),AnnualSales,1))) P(Property,Value)
where SeqNo=1
and Value is not null
Watch it in action:

select * from VerticalMainOfficeData(34)
/*
Property Value
--------------------- -----------------------------
NAME AND ADDRESS:
Name Cycles Wholesaler & Mfg.
Address Science Park South, Birchwood
Stanford House
City Warrington
State/Province England
Postal Code WA3 7BH
Country/Region United Kingdom

CONTACT:
Type Owner
Name Ms. Barbara J. German
Phone 1 (11) 500 555-0181
EmailAddress barbara4@adventure-works.com

DEMOGRAPHIC INFO:
Year Opened 1999
Number of Employees 15
Specialty Touring
Square Feet 21000
Brands 4+
Annual Sales $800,000.00
*/
Can UNPIVOT do that? Not in a million years. It’s toast!

Make JOINs Extinct!
`
Okay, this is really kind of a joke, but really, think about it… What is a JOIN? For each row in the first table, I want to JOIN it somehow with a row or rows in the second table. That sounds like an APPLY type of thing, doesn’t it? Well it is!

Look at the following traditional JOIN query, which finds all the Accessories that are Yellow, Blue, White:

select SubCategoryName=s.Name 
,p.ProductID
,ProductName=p.Name
,p.Color
from Production.ProductSubCategory s
join Production.Product p on s.ProductSubcategoryID=p.ProductSubcategoryID
where s.ProductCategoryID=3 --Accessories
and p.Color in ('Yellow','Blue','White')
order by SubCategoryName
,p.ProductID
/*
SubCategoryName ProductID ProductName Color
--------------- --------- ------------------------------- ------
Jerseys 881 Short-Sleeve Classic Jersey, S Yellow
Jerseys 882 Short-Sleeve Classic Jersey, M Yellow
Jerseys 883 Short-Sleeve Classic Jersey, L Yellow
Jerseys 884 Short-Sleeve Classic Jersey, XL Yellow
Socks 709 Mountain Bike Socks, M White
Socks 710 Mountain Bike Socks, L White
Socks 874 Racing Socks, M White
Socks 875 Racing Socks, L White
Vests 864 Classic Vest, S Blue
Vests 865 Classic Vest, M Blue
Vests 866 Classic Vest, L Blue
*/
You can replace that JOIN with a CROSS APPLY:

select SubCategoryName=s.Name 
,p.ProductID
,ProductName=p.Name
,p.Color
from Production.ProductSubCategory s
cross apply (select *
from Production.Product
where ProductSubcategoryID=s.ProductSubcategoryID) p
where s.ProductCategoryID=3 --Accessories
and p.Color in ('Yellow','Blue','White')
order by SubCategoryName
,p.ProductID
/*
SubCategoryName ProductID ProductName Color
--------------- --------- ------------------------------- ------
Jerseys 881 Short-Sleeve Classic Jersey, S Yellow
Jerseys 882 Short-Sleeve Classic Jersey, M Yellow
Jerseys 883 Short-Sleeve Classic Jersey, L Yellow
Jerseys 884 Short-Sleeve Classic Jersey, XL Yellow
Socks 709 Mountain Bike Socks, M White
Socks 710 Mountain Bike Socks, L White
Socks 874 Racing Socks, M White
Socks 875 Racing Socks, L White
Vests 864 Classic Vest, S Blue
Vests 865 Classic Vest, M Blue
Vests 866 Classic Vest, L Blue
*/
The query plans for both of those are exactly the same!

And LEFT JOINs can be replaced by OUTER APPLYs!

Today UNPIVOTs and JOINs… Tomorrow the world! Bwu hu hu ha ha ha ha haaaaaaa (Diabolical laughter).

Do it ALL!

For my final example, I’ll do ALL of the above (except for the JOIN replacement, which was just kind of a joke/trick anyway).

In doing the examples above, my query cache got populated with the text and plans of the queries I executed. We will look in the cache for the CROSS APPLY(TOP 3) query that was in the Execute SubQueries! section above, shred its query plan, looking for the operators, figure out their percentage cost, and list them in descending order of that cost. For Scans and Seeks and Joins, we will show the table, column and/or index used. And it will be presented in a vertical manner.

Note that the challenge here is finding the cost of each operator… it is not stored in the plan. Each operator has a Total Subtree Cost, but that is the cost of the operator itself PLUS the Subtree Costs of each of its immediate children operators. So for each operator, I had to find its children, total up their Subtree Costs and subtract that from the Subtree Cost of the operator to get the Individual Cost of the operator. This is done in the CROSS APPLY of the OperatorCosts CTE.

Hopefully the comments are self-explanatory:

with xmlnamespaces 
(
default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan'
)
,
OperatorData as
(
select ParentNodeID
,NodeID
,OperatorDesc
,ScanSchema,ScanTable,ScanIndex
,LoopSchema,LoopTable,LoopColumn
,HashSchema,HashTable,HashColumn
,SubTreeCost
from sys.dm_exec_query_stats qs
cross apply
--Get the Query Text
sys.dm_exec_sql_text(qs.sql_handle) qt
cross apply
--Get the Query Plan
sys.dm_exec_query_plan(qs.plan_handle) qp
cross apply
--Get the RelOp nodes from the Plan
qp.query_plan.nodes('//RelOp') F_RelNodes(RelNode)
cross apply
--Pull out the various attributes from the RelOp Node
--And also extract the ParentNodeID of the operator
(select ParentNodeID=RelNode.value('(../../@NodeId)','int')
,NodeID=RelNode.value('(./@NodeId)','int')
,LogicalOp=RelNode.value('(./@LogicalOp)','varchar(50)')
,PhysicalOp=RelNode.value('(./@PhysicalOp)','varchar(50)')
,SubTreeCost=RelNode.value('(./@EstimatedTotalSubtreeCost)','float')
) F_OpInfo
cross apply
--Make a nice description out of the Operator
(select OperatorDesc=case
when LogicalOp=PhysicalOp
then PhysicalOp
else PhysicalOp+' ('+LogicalOp+')'
end
) F_OpDesc
outer apply
--Get child nodes having to do with a Scan/Seek
--Note that OUTER APPLY is used since there may not
--be any child nodes of this type
RelNode.nodes('(./IndexScan[1]/Object[1])')
F_ScanNode
(ScanNode)
outer apply
--And pull out their Table/Index information
(select ScanSchema=ScanNode.value('(./@Schema)','varchar(50)')
,ScanTable=ScanNode.value('(./@Table)','varchar(50)')
,ScanIndex=ScanNode.value('(./@Index)','varchar(100)')
) F_ScanInfo
outer apply
--Get child nodes having to do with Nested Loops
--Note that OUTER APPLY is used since there may not
--be any child nodes of this type
RelNode.nodes('(./NestedLoops[1]/OuterReferences[1]/ColumnReference[1])')
F_LoopNode
(LoopNode)
outer apply
--And pull out their Table/Column information
(select LoopSchema=LoopNode.value('(./@Schema)','varchar(50)')
,LoopTable=LoopNode.value('(./@Table)','varchar(50)')
,LoopColumn=LoopNode.value('(./@Column)','varchar(50)')
) F_LoopInfo
outer apply
--Get child nodes having to do with Hash Joins
--Note that OUTER APPLY is used since there may not
--be any child nodes of this type
RelNode.nodes('(./Hash[1]/HashKeysBuild[1]/ColumnReference[1])')
F_HashNode
(HashNode)
outer apply
--And pull out their Table/Column information
(select HashSchema=HashNode.value('(./@Schema)','varchar(50)')
,HashTable=HashNode.value('(./@Table)','varchar(50)')
,HashColumn=HashNode.value('(./@Column)','varchar(50)')
) F_HashInfo
where qt.text like '%select top 3 ProductID%'
and qt.text not like '%with xmlnamespaces%' --Exclude this query
)
,
OperatorCosts as
(
--Calculate the Individual Costs by subtracting each Operator's
--SubTreeCost minus its immediate children's SubTreeCosts
select NodeID
,OperatorDesc
,ScanSchema,ScanTable,ScanIndex
,LoopSchema,LoopTable,LoopColumn
,HashSchema,HashTable,HashColumn
,OperatorCost=convert(numeric(16,8),SubTreeCost-ChildrenSubTreeCost)
from OperatorData o
cross apply
--Calculate the sum of the SubTreeCosts of the immediate children
(select ChildrenSubTreeCost=isnull(sum(SubTreeCost),0)
from OperatorData
where ParentNodeID=o.NodeID) F_ChildCost
)
,
CostPercents as
(
--Calculate the CostPercent using a window function
select NodeID
,OperatorDesc
,ScanSchema,ScanTable,ScanIndex
,LoopSchema,LoopTable,LoopColumn
,HashSchema,HashTable,HashColumn
,CostPercent=convert(numeric(5,1),100*OperatorCost/sum(OperatorCost) over ())
from OperatorCosts
)
select Information
from CostPercents
cross apply
--UNPIVOT the information into a vertical presentation
(values ('NodeID '+convert(varchar(5),NodeID)
+' ('+convert(varchar(10),CostPercent)+'%):')
,(' '+OperatorDesc)
,(' Table: '+ScanSchema+'.'+ScanTable)
,(' Index: '+ScanIndex)
,(' Table: '+LoopSchema+'.'+LoopTable)
,(' Column: '+LoopColumn)
,(' Table: '+HashSchema+'.'+HashTable)
,(' Column: '+HashColumn)) P(Information)
where Information is not null --Eliminate NULL rows
order by CostPercent desc
/*
Information
----------------------------------------------------------------
NodeID 13 (19.6%):
Sort (TopN Sort)
NodeID 15 (19.6%):
Sort
NodeID 9 (18.2%):
Clustered Index Scan
Table: [Sales].[CustomerAddress]
Index: [PK_CustomerAddress_CustomerID_AddressID]
NodeID 11 (14.5%):
Clustered Index Seek
Table: [Sales].[Store]
Index: [PK_Store_CustomerID]
NodeID 20 (8.7%):
Clustered Index Seek
Table: [Sales].[SalesOrderDetail]
Index: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]
NodeID 5 (5.8%):
Hash Match (Inner Join)
Table: [Person].[Address]
Column: AddressID
NodeID 12 (4.9%):
Clustered Index Seek
Table: [Sales].[Customer]
Index: [PK_Customer_CustomerID]
NodeID 18 (4.7%):
Index Seek
Table: [Sales].[SalesOrderHeader]
Index: [IX_SalesOrderHeader_CustomerID]
NodeID 30 (2.3%):
Clustered Index Seek
Table: [Production].[Product]
Index: [PK_Product_ProductID]
NodeID 7 (0.6%):
Index Seek
Table: [Person].[StateProvince]
Index: [AK_StateProvince_StateProvinceCode_CountryRegionCode]
NodeID 8 (0.6%):
Index Seek
Table: [Person].[Address]
Index: [IX_Address_StateProvinceID]
NodeID 6 (0.2%):
Nested Loops (Inner Join)
Table: [Person].[StateProvince]
Column: StateProvinceID
NodeID 3 (0.2%):
Nested Loops (Inner Join)
Table: [Sales].[CustomerAddress]
Column: CustomerID
NodeID 0 (0.0%):
Nested Loops (Inner Join)
Table: [Sales].[SalesOrderDetail]
Column: ProductID
NodeID 1 (0.0%):
Nested Loops (Inner Join)
Table: [Sales].[Customer]
Column: CustomerID
NodeID 2 (0.0%):
Nested Loops (Inner Join)
Table: [Sales].[CustomerAddress]
Column: CustomerID
NodeID 19 (0.0%):
Compute Scalar
NodeID 16 (0.0%):
Compute Scalar
NodeID 17 (0.0%):
Nested Loops (Inner Join)
Table: [Sales].[SalesOrderHeader]
Column: SalesOrderID
NodeID 14 (0.0%):
Stream Aggregate (Aggregate)
*/
Is that, like, waaaaay cool, or what?!

I hope I’ve convinced you how powerful the APPLY operator can be. I couldn’t live without it.

150 comments:

  1. Wow! This post is easily in my top 10 (which is saying a lot)

    A few thoughts.
    * I may host a lunch and learn based on this post.
    * Yes it's waaaaay cool.
    * Thanks for the illustrations plug.
    * I've been standing at the window poised to chuck UNPIVOT out for a while now. Thanks for the go ahead.
    * I appreciate the focus on query plans.

    Good job Brad, keep it up.

    ReplyDelete
  2. @Michael and @Adam:

    Wow! Thanks, guys! Last week I had intended to just write a quick simple throwaway post for T-SQL Tuesday. But "simple throwaway" is not part of my nature, so as usually happens, I ended up really going for it.

    @Michael: Can you give more detail on the "lunch and learn"? Sounds interesting.

    --Brad

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. A lunch and learn is an internal company only-thing. :-(
    What I'd like to do is give a talk to my colleagues during lunch hour on a particular topic. (Teach someone to fish etc...)

    And I'd like to do one on APPLY. I like your enumeration of all the things APPLY can do. And the examples are cool. So I'd like to present it to folks (with your permission and my attribution of course)

    ReplyDelete
  5. @Michael: Oh gosh, you don't need my permission... present away! The whole "lunch and learn" concept sounds like fun!

    ReplyDelete
  6. Great post Brad. I've already referenced it several times when thinking about using APPLY, have a feeling I'll come back to it a lot.
    Thanks!

    ReplyDelete
  7. Very informative and wonderful examples, thanks

    ReplyDelete
  8. Good work. Good examples which I can't think of !!

    ReplyDelete
  9. Great Teaching!!

    ReplyDelete
  10. is it unusual to see the power of Cross Apply without understanding all of the examples? I am making my way through these examples, but not without my mind being exceedingly stretched. Thanks for sharing an example of SQL mastery.

    ReplyDelete
  11. GREAT ARTICLE BRAD (and I don't normally shout)! This has really opened my eyes to the CROSS APPLY operator. Still pertinent and relevant after all these years and versions of SQL. FWIW, here is an updated version of the VerticalMainOfficeData updated to run in AdventureWorks2012 databases (the formatting maybe a little munged in the comment):

    create function VerticalMainOfficeData
    (
    @BusinessEntityID int
    )
    returns table
    as
    return
    with BaseData as
    (
    select SeqNo=row_number() over (order by ContactType)
    ,swc.Name,AddressLine1,AddressLine2,City,StateProvinceName
    ,PostalCode,CountryRegionName
    ,ContactType,ContactName,PhoneNumber,EmailAddress
    ,YearOpened,NumberEmployees,Specialty
    ,SquareFeet,Brands,AnnualSales
    from Sales.vStoreWithDemographics swd
    inner join Sales.vStoreWithContacts swc
    on swc.BusinessEntityID = swd.BusinessEntityID
    inner join Sales.vStoreWithAddresses swa
    on swa.BusinessEntityID = swd.BusinessEntityID
    cross apply (select ContactName=isnull(Title+' ','')
    +FirstName+' '
    +isnull(MiddleName+' ','')
    +LastName
    +isnull(' '+Suffix,'')) F_Name
    where swd.BusinessEntityID=@BusinessEntityID
    and AddressType='Main Office'
    )
    select Property,Value
    from BaseData
    cross apply
    (values ('NAME AND ADDRESS:','')
    ,(' Name',Name)
    ,(' Address',AddressLine1)
    ,(' ',AddressLine2)
    ,(' City',City)
    ,(' State/Province',StateProvinceName)
    ,(' Postal Code',PostalCode)
    ,(' Country/Region',CountryRegionName)
    ,('','')
    ,('CONTACT:','')
    ,(' Type',ContactType)
    ,(' Name',ContactName)
    ,(' PhoneNumber',PhoneNumber)
    ,(' EmailAddress',EmailAddress)
    ,('','')
    ,('DEMOGRAPHIC INFO:','')
    ,(' Year Opened',str(YearOpened,4))
    ,(' Number of Employees',convert(varchar(10),NumberEmployees))
    ,(' Specialty',Specialty)
    ,(' Square Feet',convert(varchar(10),SquareFeet))
    ,(' Brands',Brands)
    ,(' Annual Sales','$'+convert(varchar(20),AnnualSales,1))) P(Property,Value)
    where SeqNo=1
    and Value is not null

    ReplyDelete
  12. Thanks for the feedback (and the shouting)! :)

    Glad you found it all useful. And thanks for the update.

    ReplyDelete
  13. Good post. Thanks..Helps me lot :)

    ReplyDelete

  14. I feel satisfied to read your blog, you have been delivering a useful & unique information to our vision even you have explained the concept as deep clean without having any uncertainty, keep blogging. SQL server dba Online Training

    ReplyDelete
  15. Awesome post! Thank you so much for nice explanations and great examples!

    ReplyDelete

  16. Learning databases is not that difficult but not for the humanities. As for me, people who are able to study databases are unlikely to be able to write such articles as article about marketing, such people would rather buy homework on humanitarian subjects.

    ReplyDelete
  17. Databases are a subject area that people with a technical mindset can do. That is, for those who are not able to do humanitarian tasks and buy them from the service that is advertised in the article about top chemists and about service, which helps with chemistry homework, but are able to program, study mathematics, databases, and so on.

    ReplyDelete
  18. It is not as difficult to gain knowledge of databases as it is of programming languages. Of course, you still need to have a technical mindset. A person who does not use the services that are advertised in the article with tips how to write good essay fast is most likely a humanist and does not study the database.

    ReplyDelete
  19. Good day! Do yyou use Twitter? I'd like to follow
    you iff that would be ok. I'm absolutrly enjoying
    your blog and look forward to new updates.

    ReplyDelete
  20. We are a top rated marketing assignment help Online service here with experts specializing in a wide range of disciplines ensuring you get the assignments that score maximum grades.

    ReplyDelete
  21. A dissertation assignment help is a long academic essay written after gaining in-depth knowledge of a subject using formal research. Thus Dissertation as a form of academic writing differ from other type of academic assessments and is a real test of capabilities of a student. In this form of academic assessment, leaner is expected to take full responsibility of his own learning starting from choosing the topic, selecting the method of study and concluding the outcomes in the end.

    ReplyDelete
  22. This system empowers the accountant to dissect the past measurements and figures and reports the exchanges. Alongside this, the accountant's goal is to assess the financial presentation of the endeavor. assignment provider

    ReplyDelete
  23. I am really feel glad to read out published post on this blog. Apart from this, I am still waiting other post for escalating my knowledge power. In the time scarcity situation, you can leverage from Assignment Help Online service to secure highest grade.

    ReplyDelete
  24. Hey There, I am Sofia Carter. WPS Pin is more like a secret code for safety purposes while travelling. WPS Pin, a unique 8- digit number, is used for establishing a wireless connection of the printer with the router. It is generated by the printer as a secret code. It is used for communication between your devices. Only a wireless router can be used as a device to communicate with their printer using the WPS pin. The major advantage of generating a WPS pin is that it can avoid any form of intrusion from unknown users. Usually, the WPS pin is found on the printer screen.

    ReplyDelete
  25. Getting Stuck? Take Aid To Get My Money Back From Cash App
    If you feel frustrated by not being capable of applying for a refund operation, you should be relaxed as the procedure of doing the same is quite simple and straightforward. However, you can seek a real-time assistance to Get My Money Back From Cash App from the geeks.

    ReplyDelete
  26. Can I Leverage Cash App Without A Bank Account And Cater To My Requirements?
    Do you want to take full advantages of the Cash App Without A Bank Account? For that, you have to first set up an account on your Cash App. Besides, if you are looking to have a flawless experience on your Cash App account, you have to add your bank to your account.

    ReplyDelete
  27. How to Unlock Cash App Account to Enjoy All Cash App features?
    If you are looking for some troubleshooting assistance to Unlock Cash App Account you have to be associated with the team of customer care executives. To get in touch with them, you have to make a call at the helpline phone number which will connect you to them in a couple of seconds.

    ReplyDelete
  28. How To Activate Cash App Card If You Don’t Have Information?
    For the purpose of fetching the required details and information, you have to understandHow To Activate Cash App Card. So, you can visit the official help page where you will come to know all possible modes of activating your Cash App card with optimum ease.So, rather than wandering, take necessary actions!

    ReplyDelete
  29. Those who’ve just joined the cash app want to know-What countries support cash App. If you also have the same query to ask then contact the professionals for more help. For this, you should be dialing the cash app support number for reliable solutions.

    ReplyDelete
  30. Have you been looking for the Zelle customer service support, than you have arrived at the right destination. At this destination you will find the customer service help from zelle, and if you found some issues in using zelle, then you can resolve them by just a click on the given link.

    ReplyDelete
  31. Great post ! This was actually what i was looking for and i am glad to came here!
    Pega Training
    Denodo Training
    Sitecore Training
    AEM Training

    ReplyDelete
  32. This is such a great resource that you are providing and you give it away for free.twitch.tv activate I love seeing blog that understand the value of providing a quality resource for free.
    twitch.tv activate

    ReplyDelete


  33. office.com/setup is the web URL where you can redeem Office product key and download, install, and activate Office setup. Microsoft Office Setup is a complete suite of Microsoft limit programming. It joins a variety of jobs, servers, and affiliations like PowerPoint, Excel, and Word. It includes Outlook, Publisher, OneNote, and Access. www.office.com/setup is the best and cost effective Microsoft office setup support for your office setup installations for home and office users.

    ReplyDelete
  34. Hbomax is the go-to organization for most people to watch recordings today.
    Hbomax.com/tvsignin Whether you need to watch instructional exercises, movies, or even web series, Hbomax has it and as a result it is the most standard video medium and continuous site to date. Hbomax.com tvsignin

    ReplyDelete
  35. Are you looking for the steps to make at twitch tv activate ? Then you are in the right place. In recent times, it has been the most viewed gaming channel. So, let us move forward to see the requirements to get into this channel.
    twitch.tv activate
    twitch.tv/activate

    ReplyDelete
  36. HBO MAX lets you watch movies, exclusive tv shows and other videos at one place but it only works when it is activated. It is necessary for the activation purpose that you need to enter hbomax com tvsignin activation code which is a 6 digit in alphanumeric code. hbomax/tvsignin is an American OTT video streaming system that gives films, television shows, and sports on consumer demand. Get all the details about the hbomax.com/tvsignin Enter Code site in this article and stream HBO Max shows on your tv.

    ReplyDelete
  37. Magnificent website you have got here. It really worth surfing through, keep up the good work and thanks for sharing such an elucidates reads... check ku post utme past question online

    ReplyDelete
  38. Do you want to clarify Can You Transfer Money from Green Dot to Cash App
    or you can’t make payment into it? There can be possibility but you must link your green dot debit card to your Cash App account. If you are unable to do the same, you should take help from the specialists to fix it out in no time.

    ReplyDelete
  39. How to Increase cash app withdrawal limit
    ? Well, that’s a common question of every cash app user. If you wish to get the right solution of the technical woes then dial the cash app helpline number any time of the day.

    ReplyDelete
  40. Great work. Do you want help with case study assignment help? sourceessay.com will be ideal place to explore numerous blog on different subjects. Essay rewriter Washington

    ReplyDelete

  41. Are you looking for the steps to make at twitch tv activate ? Then you are in the right place. In recent times, it has been the most viewed gaming channel. So, let us move forward to see the requirements to get into this channel.Perhaps Twitch is a boon to the gaming community, encouraging young people to interact and develop new gaming techniques.Sometimes after watching these videos, the game starts to play and not a gamer. Hence, it also expands the possibilities for future generations to explore and create more games and create videos

    ReplyDelete
  42. I’m really happy to say it was an interesting post to read. I learned new information from your article, you are doing a great job. Continue
    www twitch tv activate

    ReplyDelete
  43. hbomax/tvsignin lets you stream content on three different devices online, which means you can share it with family and friends. By contrast, Netflix lets you watch on multiple screens if you pay for the Standard (2 screens) or Premium plan (4 screens).
    hbo max tvsignin

    ReplyDelete
  44. This is because when you log into the HBO Max app on a Smart TV or other device, your device displays an 8-digit code. This is the HBO Max activation code that is used to log into the HBO Max service through the link Hbomax com tvsignin.hbo max tv sign in

    ReplyDelete
  45. Hey, I am so thrilled I found your blog, I am here now and could just like to say thank for a tremendous post and all round interesting website. Please do keep up the great work. I cannot be without visiting your blog again and again. Feel free to visit my website; 먹튀검증

    ReplyDelete
  46. Your content is nothing short of brilliant in many ways. I think this is engaging and eye-opening material. Thank you so much for caring about your content and your readers Feel free to visit my website; 바카라사이트

    ReplyDelete
  47. Very good message. I stumbled across your blog and wanted to say that I really enjoyed reading your articles. Anyway, I will subscribe to your feed and hope you post again soon. Feel free to visit my website; 바카라사이트

    ReplyDelete
  48. You should be a part of a contest for one of the highest quality sites online. I will highly recommend this website! Feel free to visit my website; 토토사이트

    ReplyDelete
  49. Wow, superb weblog structure! How long have you been blogging for? you make running a blog look easy. The whole look of your site is great, let alone the content material! Feel free to visit my website; 바카라사이트

    ReplyDelete
  50. Garmin express is an application that is used to manage, set up, and register the Garmin devices. It is helpful for users in installing all the software and map updates. Whenever new updates are available, it gives you notifications at the same time. Users can save their desired maps and locations with garmin.com express and can view the data on the navigation device. So, Garmin.com/express ensures quick and precise navigation.

    ReplyDelete
  51. In a class, when the subject is pretty hard, the professor finds time to explain to everyone present. But if the topic seems easier, and an individual student faces problems handling the topic, the teacher will not always help. Students sometimes feel shy to ask for such explanations. This, in turn, harms the career of the student. The student needs to learn the topic, and at the same time, he needs to finish the assignment too! Hence, the student goes for java assignment help and spends the time learning the subject by heart. When the professors cannot explain the portions individually, the students need to take help from online teaching platforms or from the assignment service providers to understand a certain topic!

    ReplyDelete
  52. Short Bedtime English Stories is a free app that gathers a lot of free english stories collection, and the interface design is simple. After opening, you can select the storybook you want to read, quickly scan at least more than 30 categories, and there are Short Stories ,
    Animal Stories, Classic Stories, Bedtime Stories, Kid Stories, and English Stories .

    ReplyDelete
    Replies

    1. akapparels Let ShirtSpace’s collection of wholesale clothing provide the perfect blank canvas for the design you’ll share with the world! Royal Apparel Is a Unique Blank Apparel Supplier and Manufacturer.

      25

      Delete
  53. This article is an appealing wealth of informative data that is interesting and well-written. I commend your hard work on this and thank you for this information. You’ve got what it takes to get attention.
    꽁머니

    ReplyDelete
  54. You wrote an informative post and it is very helpful and informative. Therefore, I would like to thank you for putting so much effort into writing this article. Also, see The 6 Benefits of Touch Typing blog. In this blog, I have written about the most useful benefits of touch typing.

    ReplyDelete
  55. I feel very grateful that I read this. It is very helpful and very informative and I really learned a lot from it. 카지노사이트

    ReplyDelete
  56. I just visit your website and I found it very informative. Please share more content just like that. I want to read more content so please upload more articles. 토토사이트

    ReplyDelete
  57. It is very well written, and your points are well-expressed. I request you warmly, please, don’t ever stop writing. 온라인카지노

    ReplyDelete
  58. Excellent information Providing by your Article thank you for taking the time to share with us such a nice article. 스포츠토토

    ReplyDelete
  59. I really like what you guys tend to be up too. This kind of clever work and exposure! Keep up the amazing works guys I’ve incorporated you guys to our blogroll. 카지노사이트탑

    ReplyDelete
  60. Great article I enjoy reading your thoughts. More power to your website. Please visit mine too 카지노사이트

    ReplyDelete
  61. Good site you have here.. It’s hard to find quality writing like yours these days. I honestly appreciate individuals like you! 온라인카지노

    ReplyDelete
  62. It's amazing that you have that kind of idea writing this article. Thank you for valuable article. 바둑이사이트넷

    ReplyDelete
  63. I do agree with all of the ideas you’ve presented in your post.
    바카라사이트

    ReplyDelete
  64. I will recommend your website to everyone. You have a very good gloss. Write more high-quality articles. I support you.
    온라인카지노

    ReplyDelete
  65. Simply desire to say your article is as amazing. The clearness on your post is simply spectacular and that i could assume you are a professional on this subject.
    토토

    ReplyDelete
  66. Hard to ignore such an amazing article like this. You really amazed me with your writing talent. Thank you for sharing again.
    온라인카지노

    ReplyDelete
  67. This is what i was looking for thak you for sharing this amazing post. keep on posting these kind of nice post. 스포츠토토

    ReplyDelete
  68. Unbelievable!! The problem I was thinking about was solved. 카지노사이트 You are really awesome.

    ReplyDelete
  69. Great Blog.Visit this blog for more information
    hbomax com tvsignin code

    ReplyDelete
  70. As a best SEO company in Dwarka, Delhi. We Provide search engine optimization services to boost your business online presence and graw your sales.
    Get contact details and address of Renovation Services, Renovation Works firms and ... Customoised Home Renovation Services, In Delhi Ncr, Negotiable.
    Use the Blink Home Monitor app to check in on what's happening at home from anywhere at any time. The app connects your home to your phone in HD video
    Buy indoor plants online near dwarka, new delhi - Birthright offers indoor plants for home online in India. At our online nursery, We have a team of talented brand shapers who have the capability, passion and knowledge to deal with clients.
    You Found MyPCWala Online at Electronic Shop Near Me in India - A Shop For your favourite Hariom Electronics to get exciting offers on Electronics products. Get exchange offers, cashbacks and more.

    ReplyDelete
  71. Oh Wow! Great blog I must say. The thing I like most about your blog is the catchy words that you choose. I am also here to provide information about Roadrunner email problems and settings. If you or your colleague need any help regarding rr email then you may take information from us.

    ReplyDelete
  72. Curry Leaves er en super autentisk madoplevelse i der indiske køkken. Vi blev bedt om at smage på indisk mad Brøndby maden undervejs, så styrken (stærk ret) blev, som vi ønskede. Jeg er imponeret og spiser der gerne igen.

    ReplyDelete
  73. check out the assignment answers online here. You will find answers of all your assignment from our side. You can also post your own questions for your answer’s assistance. We will help you out as best we can.

    ReplyDelete
  74. Your blog is amazing, one of the most beautiful blogs I have seen. Thank you for this wonderful article.
    Also, follow the best porn topics through the following titles:
    صور نيك
    قصص سكس محارم
    سكس حيوانات

    ReplyDelete
  75. 123.hp.com/setup, télécharger l'installation et la configuration de l'imprimante HP, établir une connexion WLAN avec Windows et Mac 123 Guide d'installation de l'imprimante HP.
    123.hp.com /
    123.hp.com/setup /

    ReplyDelete
  76. Hilfe beim schnellen Einrichten finden, 123.hp.com/setup /

    Alle Dienste für den 123 HP Drucker werden bestmöglich bereitgestellt hp drucker installieren /

    größtes Engagement und Aufrichtigkeit für unsere Arbeit, alle Druckerservices für Benutzer auf außergewöhnliche Weise bereitzustellen 123.hp.com/Laserjet /

    123 HP Drucker werden bestmöglich bereitgestellt. 123hpcom bietet allen Benutzern mit größter Sorgfalt die besten druckerbasierten Dienste.123.hp.com/Officejet /

    Der Grund, warum Sie unsere 123 PS bevorzugen sollten,123.hp.com/envy /

    dass Ihr Drucker eingerichtet wurde und mit extremer Geschwindigkeit arbeitet, die mit keinem anderen Dienst als mit unseren HP Printer Diensten möglich ist.123.hp.com/Ojpro /

    die Einrichtung und Installation des Druckers 123.hp.com

    ReplyDelete
  77. Visit ij.start.canon | ij.start canon and find out the best way to download Canon printer drivers.Canon printers are ideal for every situation wherever you need a document, paper, or photo print or even if you wish to scan, fax, and do more.

    Once you are done with the driver setup via
    canon.com/ijsetup , you will have to insert a pile of pages into the printer tray for printing the documents. But it is important to know how to correctly load the pages into the tray for avoiding all the printing errors.

    ReplyDelete
  78. Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on Coffee Culture Around the World.
    Hello your post is very helpful, thanks for wonderful information sharing. left breast
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on tongue scraper
    Hello your post is very helpful, thanks for wonderful information sharing. blogger outreach services
    Hello your post is very helpful, thanks for wonderful information sharing. thick toenail
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on High da guest post
    Hello your post is very helpful, thanks for wonderful information sharing. Women's Unstitched Summer Lawn Collection
    Hello your post is very helpful, thanks for wonderful information sharing. When you go about Find information and tips on back pain doctor in lahore
    Hello your post is very helpful, thanks for wonderful information sharing. photographers in lahore

    ReplyDelete
  79. Ij start canon and find out the best way to download Canon printer drivers.
    Ij.start.cannon | Ij.start.canon | canon.com/ijsetup

    ReplyDelete
  80. The majority of people believe that hiring writing pros is outrageously expensive. However, because the majority of consumers are students on a restricted budget, such services are extremely affordable. As a result, everyone in need of academic aid can afford the services. If you want high-quality help with paper, engage a professional writer from a legitimate and reputable writing service, and you will never be sorry.

    ReplyDelete
  81. We offer you pay someone to do my course by Do My Classes Now services to the customers at pocket friendly rates. We have designed our pricing plans in such a way that every student could afford. So stop worrying about the budget. Give us a call and we will try to accommodate you in the best possible

    ReplyDelete
  82. If you are battling to structure your paper for Australian Law Assignment, you will find the definite answer at our Australian Law Assignment Help platform. Great Assignment Helper provides you the 360degree support required to develop premium quality assignments worthy of fetching excellent grades. Our experts are very qualified and highly experienced dedicated academicians renowned for their command over the subject. Despite being the best service provider out there, our prices are always reasonably cheap and affordable.

    ReplyDelete
  83. Why is there so much good information? l keep that in mind. 메이저사이트

    ReplyDelete
  84. I ve known about this place for a while and I ve been visiting. There are still only good words and they are helpful. 먹튀검증업체

    ReplyDelete
  85. I spend a lot of time on this blog to learn a lot of good information. 메이저사이트

    ReplyDelete
  86. Just want to say your article is as surprising. The clarity in your post is just cool and i can assume you’re an expert on this subject. Well with your permission allow me to grab your RSS feed to keep up to date with forthcoming post. Thanks a million and please continue the enjoyable work.
    english stories

    ReplyDelete
  87. Remarkable! Its in fact amazing post, Really looking forward to read more 안전사이트

    ReplyDelete
  88. Nice knowledge gaining article. This post is really the best on this valuable topic 안전사이트

    ReplyDelete
  89. Via a company news release, Nadiya Attard, Chief Commercial Officer for Relax Gaming, commented on the new partnership, saying they are “delighted” to make their “first appearance” in the Serbian market with Meridian Gaming. 바카라사이트

    ReplyDelete
  90. BIIT is one of the well known Computer institute in east delhi. You can get the well teachers at a reasonable price. We also provides mock test to all students. You can also call to 9311441524 for more query.

    ReplyDelete
  91. CEA Aviation gives shocking quality and work areas in nations like Canada, USA, New Zealand, Australia and UK to the degree that Commercial DGCA Ground Classes in India
    abroad and offers foundation at an irrelevant expense Flight Academy in New Zealand.we consider everything undertaking to give Pilot planning in delhi for this organising utilise the best tutor.

    ReplyDelete
  92. Thank you for sharing. Please visit our website to learn more about our great material -Best spoken english classes in laxmi nagar
    We prepare "IELTS" and "OEt" and provide computer and digital marketing instruction; please visit or contact us for more information.
    For Query you can contact on this number = 91 9667-728-146

    ReplyDelete
  93. I think it's a website with a lot of good contents. I think I accumulate a lot of knowledge.
    Please come to my website and give me a lot of advice.
    It's my website.

    피망머니상

    ReplyDelete
  94. Hey friend, it is very well written article, thank you for the valuable and useful information you provide in this post. Keep up the good work! FYI, shih tzu hair products , Citi Double Cash Credit Card Review, The book you wish your parents had read book pdf download,My daily routine paragraph in english

    ReplyDelete
  95. IELTS is a thorough test that evaluates all four language skills: hearing, reading, writing, and speaking. Cambridge English Academy provides you with relevant and gifted IELTS data to help you prepare for the test. Visit learn more about the IELTS exam plan, go to ielts coaching in east delhi

    ReplyDelete
  96. Our Law Assignment Help expert is ready to complete your requirement. They have the sure assurance to take over your subject requirement and provide the same solution as you’re ever expected.

    ReplyDelete
  97. Wow, this is really interesting reading. I am glad I found this and got to read it. Great job on this content.I like it.
    사설토토

    ReplyDelete
  98. Canon drivers fully support and assists for all compatible products for all Window versions. We offer the required data to configure, utilize and install your Canon products on your Windows PC.canon is completely safe and secure. ij.start.canon |
    ij.start.cannon

    ReplyDelete
  99. ij scan utility is recognized as useful for shortening workflows
    ij scan utility download is one of the programs you’ll definitely have to get if you’re using a certain Canon printer or scanner model.

    ReplyDelete
  100. canon printer provides an answer for all type of canon printer problems by which you can undoubtedly figure out how to set up from canon and improve insight. ij.start.canon | canon.com/ijsetup

    ReplyDelete
  101. The setup process for every Canon model is almost similar, however the download through https //ij.start.cannon or http //ij.start.cannon and installation process may differ.Depending on your requirement, it offers a type printer including PIXMA, SELPHY, MAXIFY, etc. canon.com/ijsetup Some factors need to be in mind while choosing an inkjet printer for you. Later, you can easily set up your Canon printer through drivers from ij.start.cannon wireless connection, USB, and a few components. Https //ij.start.cannon

    ReplyDelete
  102. 메이저사이트April 3, 2022 at 1:51 AM

    Your skill is great. I am so grateful that I am able to do a lot of work thanks to your technology.메이저사이트 I hope you keep improving this technology.


    ReplyDelete
  103. Great Blog you write here. I visited your blog for the first time and seriously now I am a fan of your writing. Great and informative blog you posted here. Really Appreciable. Just like you, I am also here to promote my Online Academic writing website. If you interested or if you have to know about online Assignment help or how online assignment service, then feel free to contact our experts. We are here to help you, so always feel free to contact us.

    ReplyDelete
  104. Follow cricut.com/setup and it is the online portal that allows you to set up your Cricut machine. A most advanced Cricut machine is best in cutting shapes, text, and photos. It doesn’t only make cuts on card stock or vinyl but adhesive foil, faux leather, balsa wood, and more. So how do you start your circuit machine? You have to download Cricut setup from cricut.come/setup then connect your machine and software to get started.

    ReplyDelete
  105. essaypro reviews
    EssayPro is a writing service for students who are having trouble with their assignments. Their writers have a high reputation, and the best part about EssayPro is that all of their writers are native English speakers, so you can rest assured that your paper's content will be free of grammatical errors.

    ReplyDelete
  106. Essaypro login
    EssayPro is a writing service for students who are having trouble with their assignments. Their writers have a high reputation, and the best part about EssayPro is that all of their writers are native English speakers, so you can rest assured that your paper's content will be free of grammatical errors.

    ReplyDelete
  107. pseudophed
    Pseudoephedrine is a medicine that can be found in syrup form. It is available over the counter for the relief of nasal congestion associated with the common cold. However, it is also used as the main ingredient in the illegal making of meth. This blog takes a look at pseudoephedrine syrup and its use to ensure that you can make an informed decision on your next purchase.

    ReplyDelete
  108. You made some good points there. I did a Google search about the topic and found most people will believe your blog. 먹튀검증

    ReplyDelete
  109. Ask us to do or Hire online Finance Assignment Help experts for your finance assignments, projects and homework solutions at affordable prices. Tips provided by our experts to students at the time of writing finance assignment - Know about the main areas of the topic.

    ReplyDelete
  110. Thanks for sharing such great information with us. Very nice and well-explained article. Very fantastic piece of content I see here on your blog, you have put out very vital tips and information for your readers. Thanks for sharing. approved school fee schedule for ksu returning students

    ReplyDelete
  111. Hitech is the most seasoned and understudy's no.1 decision organization. They nearly prepared 2 lakh+ understudies with refreshed devices and procedures who are doing best in their specific regions. The fundamental courses we give are versatile fixing, PC fixing, OCA fixing, and EMMC fixing. Anyone can sign up for these courses; there is no age bar, no requirement for higher capabilities. If you have any desire to create your profession in the innovation business you can select with us, for more data you can call us at:
    9212411411

    Visit Here :- Mobile Repairing Course In Delhi

    ReplyDelete
  112. hbomax tv sign in
    STEPS TO INSTALL HBO MAX TV ON YOUR STREAMING DEVICE?
    First you need to Switch on your device on which you like to watch HBO max like your your smart Phone, laptop, Firestick or any other streaming device.
    Now go to the play Store and login with your gmail id and download HBO Max App or you may also try top open hbomax.com/tvsignin in any browser and check download options.
    Once you install the app, open it and click on the activation button, now copy the code.
    Now you have to visit hbomax.com/tvsignin and login in your account. If you dont have create an new account.
    Go and fill the basic details asking at the time of signup.
    Select your streaming device and your cable provider.
    Now enter the code you have copied earlier when asked for the activation code.
    Finally, your account is avtive and you can watch any stuff you like from different categories

    ReplyDelete
  113. PG SLOT ลุ้นเครดิตฟรี เครดิตฟรี เราเชื่อว่าสาวกสล็อตออนไลน์ หากได้ยินคำว่า “เครดิตฟรี นั้นฟรีจริงหรือไม่ ขอตอบตรงนี้เลยว่า pg slot ฟรีจริงๆไม่มีเงื่อนไขใดๆทั้งสิ้น

    ReplyDelete
  114. Youre so right. Im there with you. Your weblog is definitely worth a read if anyone comes throughout it. Im lucky I did because now Ive received a whole new view of this. 메이저사이트추천

    ReplyDelete
  115. Which is some inspirational stuff. Never knew that opinions might be this varied. Thank you for all the enthusiasm to provide such helpful information here.바카라사이트 It helped me a lot. If you have time, I hope you come to my site and share your opinions. Have a nice day.

    ReplyDelete
  116. Your blog contains lot of beneficial contents, it’s a very notable blog with well-written article piece, very impressive as it contains information that is unique to its own. It is really worth surfing through, thanks for sharing. aau-ekpoma school fees schedule for students

    ReplyDelete
  117. 메이저사이트June 4, 2022 at 1:50 AM

    Thank you for this wonderful post! It has long been extremely helpful. 메이저사이트I wish that you will carry on posting your knowledge with us.


    ReplyDelete
  118. Hello, I'm happy to see some great articles on your site. Would you like to come to my site later? My site also has posts, comments and communities similar to yours. Please visit and take a look 메이저놀이터

    ReplyDelete
  119. Want to play a good online slot game and meet all international standards from abroad Come and meet the main website like PGSLOT pgslot168game that has many online slots games to play in a lot, can play for real or try to play without having to pay any fees to play. Slots from this camp are ready with lots of prizes to win.

    ReplyDelete
  120. Great post,
    Thanks to share lots of information with us.
    Starter Kits,vapers can find the smok starter kit,premium kits and dead rabbit r tank and Artery Adapter and smok nord x kit here,whatever you are the beginner user or experience vapor,all of them will great prince
    vapeciga.uk

    ReplyDelete
  121. Placing an order with us guarantees to you that the dissertation you need will be done in an appropriate manner. We can help you deliver the best service according to your needs and requirements. Our team of professionals delivers online dissertation writing service  every time with their experience so that you can be sure that we are delivering accurate information or material which is free from any grammatical error or spelling mistakes.

    ReplyDelete

  122. Order Carton Box in Lahore Custom printed boxes are the most effective approach to improving your company’s brand image, attracting attention, and making it easier for customers to distinguish your items.
    Plain boxes are no longer sufficient; make your packing stand out with custom packaging boxes and have a professional look by including your business logo, name, brand slogan, and website into stunning colourful designs.custom weed boxes

    ReplyDelete