SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Execution Plan


Execution Plan

Author
Message
Confusing Queries
Confusing Queries
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 528
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...
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)SSC Guru (225K reputation)

Group: General Forum Members
Points: 225152 Visits: 40420
How about saving your actual execution plan and uploading it to this thread? That would help us a lot.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
SeaQL
SeaQL
SSC Eights!
SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)SSC Eights! (829 reputation)

Group: General Forum Members
Points: 829 Visits: 294
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 :-D

Jan Studenovsky
Paul White
Paul White
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80144 Visits: 11400
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.aspxhttp://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
SQLblog.com
@SQL_Kiwi
Paul White
Paul White
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80144 Visits: 11400
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
SQLblog.com
@SQL_Kiwi
Attachments
Forum.png (292 views, 8.00 KB)
ExecutionPlan.sqlplan (14 views, 4.00 KB)
Confusing Queries
Confusing Queries
Mr or Mrs. 500
Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)Mr or Mrs. 500 (553 reputation)

Group: General Forum Members
Points: 553 Visits: 528
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
Paul White
SSC Guru
SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)SSC Guru (80K reputation)

Group: General Forum Members
Points: 80144 Visits: 11400
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
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search