26 performance tuning questions and solutions, Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Concatenation Operator, Difficult SQL Interview Questions, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Execution Plan Operator - Concatenation, Execution/Query Plan Operator - The Concatenation, Free Download SQL SERVER Interview questions, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, Looking for SQL Optimization Interview Questions, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, Query Plan Operator - Concatenation, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL Complex Queries, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Queries asked in interviews, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Questions, SQL Server, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL SERVER Interview questions for experienced, SQL SERVER Interview questions pdf, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL SERVER Tricky questions, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, SQL Tricky question, SQL Tuning Overview, SQL Tutorial, T-SQL, T-SQL Interview questions, T-SQL Programming, T-SQL Server Interview Questions, T-SQL Tutorial, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, TOP 100 SQL SERVER INTERVIEW QUESTIONS, Top 50 SQL Server Questions & Answers, Tough PL SQL Interview Questions, Tough SQL Interview Questions, Tough SQL Queries, Tough SQL Queries Interview Questions, Tough SQL Questions, Tricky SQL Interview Questions, Tricky SQL Interview Questions and answers, Tricky SQL Queries for Interview, Tricky SQL SERVER Interview Questions and answers
Execution/Query Plan Operator – The Concatenation
Today let’s talk about Concatenation query plan operator. In execution plan we may have n number of operators that were used by SQL Server to satisfy a query. These operators are called physical operators. Now there are some operators which are logical as well as physical. Concatenation is one of those operators. It is logical as well as physical.
So what Concatenation operator do, Concatenation operator receives one or more input tables(streams) and returns all the rows from each input. So you must be getting what I am talking about. Yes you are right it is used for UNION ALL.
Lets jump on the demo and check out the execution plan-
-- --Create a simple table CREATE TABLE Book ( BookID INT ,BookName VARCHAR(50) ,Edition VARCHAR(10) ) GO --Insert some values INSERT INTO Book(BookID,BookName,Edition) VALUES (100012,'SQL Server MVP Deep Dives','1st'), (167012,'SQL Server MVP Deep Dives','Second'), (132012,'SQL 2016','Third'), (100222,'SQL SERVER 2014 - Hekaton','1st') GO --Create a clustered index CREATE UNIQUE CLUSTERED INDEX Ix_BookId ON Book(BookId) GO --Lets execute the below query and check out the execution plan SELECT BookID,BookName,Edition FROM Book UNION ALL SELECT BookID,BookName,Edition FROM Book --
Textual Actual Execution Plan using SET Statistics PROFILE ON
Graphical Actual Execution Plan
The concatenation operator here receives the result of all clustered index scan and combines them together to make a single Data Set. Here SQL Server executes the plan in the order that appears in the execution plan.( In this case Top to Bottom )
I hope you have enjoyed the article. Thanks for reading !