Execution Plan

Question

Post reply

Execution Plan

Confusing Queries

Ten Centuries

Points: 1137
More actions
January 10, 2012 at 7:16 pm

#253141

I executed this query and checked the execution plan and it is showing segment and sequence project. What does SQL Server do in Segment and Sequence Project? I mean what happens internally to give the result.
SELECT
ROW_NUMBER() OVER (
ORDER BY (select 7/3))as columnn
FROM
( VALUES
(1),(1),(1),(1),('1'),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
) AS V (n)
Thanks...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

How about saving your actual execution plan and uploading it to this thread? That would help us a lot.

SeaQL SSC Eights! Points: 997 More actions · Answer 2

From MSDN:

Segment http://msdn.microsoft.com/en-us/library/ms180774(v=SQL.100).aspx] is a physical and a logical operator. It divides the input set into segments based on the value of one or more columns. These columns are shown as arguments in the Segment operator. The operator then outputs one segment at a time.

The Sequence Project http://msdn.microsoft.com/en-us/library/ms187041(v=SQL.100).aspx] operator adds columns to perform computations over an ordered set. It divides the input set into segments based on the value of one or more columns. The operator then outputs one segment at a time. These columns are shown as arguments in the Sequence Project operator.

Microsoft SQL Server supports four types of functions: RANK, DENSE_RANK, ROW_NUMBER, and NTILE. Sequence Project will generate plans that have a Sequence Project and generally two segments.

Sequence Project is a physical and a logical operator.

Yeah, I know, it helps a lot 😀

Jan Studenovsky

Paul White SSC Guru Points: 150467 More actions · Answer 3

nirnaykhatri (1/10/2012)
What does SQL Server do in Segment and Sequence Project? I mean what happens internally to give the result?

I wrote about the internals of Segment and Sequence Project here:

http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-and-sequence-project-iterators.aspx

You might also find these entries interesting:

http://sqlblog.com/blogs/paul_white/archive/2010/07/28/the-segment-top-query-optimisation.aspx http://sqlblog.com/blogs/paul_white/archive/2010/07/28/partitioning-and-the-common-subexpression-spool.aspx

http://sqlblog.com/blogs/paul_white/archive/2010/07/28/cool-query-plan-transformation-of-the-day.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

Lynn Pettis (1/10/2012)
How about saving your actual execution plan and uploading it to this thread? That would help us a lot.

The execution plan looks like this:

An example actual execution plan is also attached.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Confusing Queries Ten Centuries Points: 1137 More actions · Answer 5

Thanks Paul for educating me

I am new to this website.....I just wanted to know how to upload pics and stuff here just like you uploaded yesterday?

Thanks...

Paul White SSC Guru Points: 150467 More actions · Answer 6

nirnaykhatri (1/11/2012)
I am new to this website.....I just wanted to know how to upload pics and stuff here just like you uploaded yesterday?

When you are writing a post, there is an Edit Attachments button in the Post Options section below where you enter the text of your message. This allows you to create attachments quite easily, and is all most users ever need. If you want to make the image appear in-line with the post, it's a bit harder: You have to get the address of the image you attach (right click on the uploaded image) and enter that address between [ img ] and [ /img ] tags (without the spaces).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi