Click here to monitor SSC
  • Av rating:
  • Total votes: 20
  • Total comments: 0
Fabiano Amorim

Showplan Operator of the Week - Concatenation

22 April 2010

Fabiano continues in his mission to describe, one week at a time, all the major Showplan Operators used by SQL Server's Query Optimiser to build the Query Plan. This week he gets the Concatenation operator .

Concatenation

Showplan Operators are used by SQL Server's Query Optimizer (QO)  to perform a particular operation within a query plan. A query plan will usually contain several of these physical operators. Each physical operation is represented in the Query Plan by an operator, and each operator is shown in the graphical execution plan by an icon. This week we’ll be featuring the concatenation showplan operator. Its behavior is quite simple; it receives one or more input streams and returns all the rows from each input stream in turn. We can see its effect whenever we use the Transact–SQL UNION ALL command.

Concatenation is a classic operator that can receive more than one input. It is both a logical and a physical operator.

Before we start to talk about concatenation, we need to understand some important points about showplan operators and execution plans.

All operators used in execution plans, implement three methods called Init(), GetNext() and Close(). Some operators can receive more than one input, so, these inputs will be processed at the Init() method. The concatenation is one example of these operators.

At the Init() method, the concatenation will initialize itself and set up any required data structures. After that, it will run the GetNext() method to read the first or the subsequent row of the input data, it runs this method until it has read all rows from the input data.

Let’s take the following query as a sample:

The following script will create a table TabTeste and populate with some garbage data.

USE tempdb

GO

CREATE TABLE TABTeste(ID   Int Identity(1,1) PRIMARY KEY,

                      Nome VarChar(250)      DEFAULT NewID())

GO

SET NOCOUNT ON

GO

INSERT INTO TABTeste DEFAULT VALUES

GO 10000

The script above will populate 10000 rows at the TabTeste table. Now let’s run one query sample to look at the execution plan.

SELECT *

  FROM TABTeste a

 INNER JOIN TABTeste b

    ON a.ID = b.ID

Graphical execution plan:

Text execution plan:

SELECT * FROM TABTeste a INNER JOIN TABTeste b ON a.ID = b.ID

 |--Merge Join(Inner Join, MERGE:([b].[ID])=([a].[ID]), RESIDUAL:([TABTeste].[ID] as [b].[ID]=[TABTeste].[ID] as [a].[ID]))
       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_] AS [b]), ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_] AS [a]), ORDERED FORWARD)

As we can see, this query is using one operator called Merge to join the tables, in the plan, the Merge operator is receiving two inputs (the table TabTeste twice).

The operator of the week, Concatenation, is a good example of an operator that receives more than one input. If, for example, we run the following query, we will see that it receives fours inputs.

SELECT * FROM TABTeste

UNION ALL

SELECT * FROM TABTeste

UNION ALL

SELECT * FROM TABTeste

UNION ALL

SELECT * FROM TABTeste

Graphical execution plan:

Text execution plan:

  |--Concatenation

       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_]))

       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_]))

       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_]))

       |--Clustered Index Scan(OBJECT:([TABTeste].[PK_]))

The  concatenation operator receives the result of all “clustered index scan”  and copies all the rows to one output calling the methods Init() and GetNext(). These methods are called to each Input.

The Query Processor will execute this plan in the order that the operators appear in the plan, the first is the top one and the last is  the end one.

That’s all folks, I see you next week with more “Showplan Operators”.

If you missed last week's thrilling Showplan Operator, The Assert, you can see it here

Fabiano Amorim

Author profile:

Fabiano is fascinated by the SQL Server Query Processor and the way it works to optimize queries, procedures and functions. He graduated as a Technical Processor from Colégio Bezerra de Menezes, SP- Brazil, and has worked for several years with SQL Server, focusing in creating Data Warehouses and optimizing T-SQL codes for many companies in Brazil and Argentina. Fabiano is a SQL Server MVP, MCP for SQL Server 2000, MCTS and MCITP Data Base Developer for SQL Server 2005 and 2008. He also is actively involved in SQL Server community though forums such as MSDN and TechNet Brazil, writes articles for Simple-Talk and SQL Server Magazine Brazil, and he also presents online Webcasts and In-Person events for Microsoft Brazil. His blog is on http://blogfabiano.com

Search for other articles by Fabiano Amorim

Rate this article:   Avg rating: from a total of 20 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.
 
Simple-Talk Database Delivery

DLM
Patterns & Practices Library

Visit our patterns and practices library to learn more about database lifecycle management.

Find out how to automate the process of building, testing and deploying your database changes to reduce risk and make rapid releases possible.

Get started

Phil Factor
Microsoft and Database Lifecycle Management (DLM): The DacPac

The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx), provides an... Read more...

 View the blog

Top Rated

Working with SQL Server data in Power BI Desktop
 What's the best way of providing self-service business intelligence (BI) to data that is held in... Read more...

Microsoft and Database Lifecycle Management (DLM): The DacPac
 The Data-Tier Application Package (DacPac), together with the Data-Tier Application Framework (DacFx),... Read more...

A Start with Automating Database Configuration Management
 For a number of reasons, it pays to have the up-to-date source of all the databases and servers that... Read more...

Archiving Hierarchical, Deleted Transactions Using XML
 When you delete a business transaction from the database, there are times when you might want to keep a... Read more...

Rollback and Recovery Troubleshooting; Challenges and Strategies
 What happens if your database deployment goes awry? Do you restore from a backup or snapshot and lose... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

Temporary Tables in SQL Server
 Temporary tables are used by every DB developer, but they're not likely to be too adventurous with... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.