(I am still around - I've just been busy with a few personal projects of late - keep asking questions if you have them).

I received a question from a reader named Andy that was a follow-up to my questions on OUTER JOINs and WHERE clauses and ON clauses.

Upon re-reading my previous entries, I determined that I got distracted talking about the old-style join syntax instead of giving a good explanation about ON vs. WHERE _semantics_ for outer joins.

select * from cc1 left outer join cc2 on (cc1.col1=cc2.col1)

drop table cc1
create table cc1(col1 int, col2 int)
insert into cc1(col1, col2) values (1, 2)
insert into cc1(col1, col2) values (2, 3)

create table cc2(col1 int, col2 int)
insert into cc2(col1, col2) values (1, 2)

select * from cc1 left outer join cc2 on (cc1.col1=cc2.col1)

col1        col2        col1        col2
----------- ----------- ----------- -----------
1           2           1           2
2           3           NULL        NULL

(2 row(s) affected)


select * from cc1 left outer join cc2 ON (1=1) WHERE (cc1.col1=cc2.col1)

col1        col2        col1        col2
----------- ----------- ----------- -----------
1           2           1           2

(1 row(s) affected)

The question boils down to "why are these two queries returning different results?" This is a very good question.

I'll explain the what, then I'll try to explain the why/how:

In the first query, the ON clause has the predicate. This logically ties the predicate to the join. Since OUTER JOINS can return rows that do not match, the predicate is used to determine what "doesn't match".

In the second case, the join part actually tells the QP to do a full cross product.  The WHERE clause is actually not bound to the OUTER join semantics at all. This is why they return different results. 

Now let's talk "why/how".  The QP represents this second query as a tree with a filter above the join.  The QP has lots of smarts in it.  One of the thing it can determine is that the filter condition (cc1.col1=cc2.col2) actually prevents NULLs from being returned since NULL = anything is UNKNOWN in three-value logic (and therefore not TRUE and therefore not returned from the WHERE clause).  Since all NULL values from the non-outer side of the join are removed, this is logically equivalent to running an inner join because all of the extra rows for non-matching rows are actually removed.

select * from cc1 INNER join cc2 ON (1=1) WHERE (cc1.col1=cc2.col1)

If you look at the query plans, you will see that the second query is actually running an inner join because of this recognition in the QP that you don't need an outer join.  (The QP can more freely reorder inner joins, so it prefers to convert outer to inner joins where possible).

I hope that gives a better explanation as to why SQL Server returns different results for this query!

Happy Querying!

Conor

 

I had a question from a reader about contradiction detection.

The basic idea is to determine that this kind of query:

SELECT * FROM TABLE WHERE col1 > 5 and col1 < 0;

...is utterly meaningless and requires no work to be done.  It will always return zero rows.  In fact, in many cases the SQL Server QP will detect cases like this and actually remove the table from the query completely.  In its place, you have this magical "constant scan" operator, which is really just an in-memory row generator.  So, you can read from it instead of from a table.

This optimization is done in many places in the SQL Server QP, and it is great because it requires no I/O, no locks, and less CPU than actually reading from a table.

The question I had from a user had to do with cases when this optimization does NOT happen.

Since the reader sent me a perfect example, I'll just copy it up here (note - I am happy to credit readers if they would like credit, but I don't post their name unless they want me to do so).

--drop table #tmp

create table #tmp (id Int Identity(1,1) Primary key, name VarChar(250))

go

insert into #tmp(name) values(NEWID())

insert into #tmp(name) values(NEWID())

go

set statistics profile on

go

-- Execution plan create a Constant Scan

select * from #tmp

where id = 1 and id = 5

go

set statistics profile off

 

GO

 

select * from #tmp  where id = 1 and id = 5
  |--Constant Scan

--drop table tmp

create table tmp (id Int Identity(1,1) Primary key, name VarChar(250))

go

insert into tmp(name) values(NEWID())

insert into tmp(name) values(NEWID())

 

go

set statistics profile on

-- Why execution plan does not create a Constant Scan for this case?

select * from tmp

where id = 1 and id = 5

go

set statistics profile off

SELECT * FROM [tmp] WHERE [id]=@1 AND [id]=@2
  |--Clustered Index Seek(OBJECT:([t1].[dbo].[tmp].[PK__tmp__3213E83F15502E78]), SEEK:([t1].[dbo].[tmp].[id]=CONVERT_IMPLICIT(int,[@1],0)),  WHERE:([t1].[dbo].[tmp].[id]=CONVERT_IMPLICIT(int,[@2],0)) ORDERED FORWARD)

So this is interesting - in one case the constant scan is done, while in the other case it is not.... hrm.  what's up with that?

Well, some of you are familiar with autoparameterization - a feature that can take unparameterized queries, try to make them parameterized, and then avoid compilation for multiple similar simple queries.  Autoparameterization is tied to the "trivial plan" feature, meaning that the optimizer feels that there isn't really a plan choice and thus going through the complete cost-based optimization process is unnecessary.

The difference you see is based on autoparameterization and whether it happens or not.  Now, the autoparameterization rules are fairly arcane at this point - they've been around, in some form, for various releases dating back to SQL 7.0.  TempDB has a different set of rules about whether a query should be autoparameterized since these tables are globally shared.  So, in the #tmp case the table isn't being autoparameterized, the rest of the logic to simplify the query is run, and this rule detects the contradiction.

In the latter case, the query IS auto-parameterized.  The optimizer doesn't see two parameters as always being a contradiction (you could pass the same value for both parameters), and then the query is not simplified.

For what it is worth, a somewhat internal knowledge of what causes a plan to be considered trivial or not can influence whether you see one plan or the other.  For example, query hints make a plan not trivial (why hint if there is no plan choice?), and as such you could do something like this:

select * from tmp
where id = 1 and id = 5 option (maxdop 1)

This will get you the constant scan in all databases.  Adding enough rows to get the cost above the threshold for parallelism (in a parallel-enabled machine + SKU of SQL) would also do it. 

Remember, trivial plan was developed before contradiction detection, so this should be viewed in that context.  It's a bit odd, but the ecosystem developed this way and you should be aware of that when developing SQL Server apps.

I enjoy the questions - keep them coming!

Thanks,
Conor

So COUNT isn't an operator, but you should view this post as a "what is this operator" kind of post since I talk about how these things work and what they mean.

One of the benefits of building database engines is that all (well, most of) the syntax rules end up stuck in your head, but I know that many of these things are completely non-obvious, so I think it's worth mentioning a few things about what each of these things mean since I see them misused in lots of queries, blogs, etc.

-- Returns the count of rows from the table at the time that the query is run in the transactional isolation mode in which the query is run (usually read committed for SQL Server)
SELECT COUNT(*) FROM Table

So that example is easy enough...

SELECT COUNT(column1) FROM Table

This is actually NOT the same query as COUNT(*), in general.  It means "count the number of non-null column1 rows". 

(Now for something cool.  If you run this on a column that is non-nullable, then SQL Server converts it into the count(*) case because it is faster to run that form since it doesn't have to examine the data in each row and can instead just count rows).

SELECT COUNT(DISTINCT column1) will count the number of UNIQUE non-null column1 values.  It does not count NULL.  I don't believe SQL Server completely removes the DISTINCT operation for non-null columns in all cases.  It can in some cases.

COUNT(col) OVER (...) is a completely different beast.  It runs a count computation using the rules you've seen above but it does not collapse the rows - it adds a new column on all the rows with the computed count. 

Remember that these are semantically different operations.

Conor

I don't know about you, but groupby is one of my favorite operators.  There are a TON of interesting optimizations that a QP considers when you start adding group by into queries, especially when you have joins and then a group by.  TPC-H benchmark wars among the large database vendors are won and lost on many such optimizations.

So, if you are doing relational OLAP (ROLAP) or are otherwise running group by queries over lots of well-normalized data, then I suggest you brush up a little on your knowledge of group by - it will help you understand when queries are behaving and when they are not.

Here's the paper I tell people to read on the subject.  It's written for the person implementing a database, but anyone who can read query plans should get the basics from the paper.  It is the basis for most of the hard-core optimizations (and tricky problems) that face all query processors today. 

The basic idea is caused by understanding that an aggregate function can be split into multiple operations and done in parts.  Some parts can be done earlier in a query, saving a lot of work.  If these results can be combined later, you might be able to speed up a query by computing these partial results early and then combining them at the end of the query.  The usual savings is that you don't have to materialize the results of a join when you only care about the aggregate over some piece of it.

Most of the core aggregate functions defined in SQL can be decomposed.  If you have a set of rows {x} := concat({y}, {z}), then
SUM({x}) == SUM({y}) + SUM({z}).
COUNT == COUNT + COUNT
MAX == MAX (MAX(), MAX())
...

Not all aggregate operations can be decomposed in this manner, but many of them can. 

If you take this concept and then apply it a query with some joins:

select sum(col1) from a join b join c

Then the idea of local-global aggregation is that you can do part of the sum before joins and pass up the SUM for each group instead of all the rows from that group. 

This idea becomes more powerful when you start throwing more complex operations into a query processor, such as partitioning or parallelism.  Often, you want to perform partial aggregations on these groups to minimize the amount of data you have to send between threads or perhaps between nodes on a NUMA machine.  All of this is the fun stuff that makes databases interesting - you can

Not every aggregate can be pushed below every join - there are rules about what can and can't be done and maintain the same results from a query.  For example, you may need to consider whether the aggregate function can handle seeing additional NULL values and return the same result or not.

If you go look at the SQL CLR user-defined aggregate definition, you'll see the exposed pieces of some of this capability in the SQL Server query optimizer.  I won't spoil all of your fun, but go take a look. 

Happy Querying!

Conor

So I was playing today with the sparse column implementation in the latest SQL 2008 CTP, and I was specifically looking to see how this thing would work with the query processor.  I wanted to know how much information is exposed for the QP to make smart decisions when this functionality is used in complex queries with cost-based plan choices.  If the QP doesn't have the information, then sometimes the query plans will be sub-optimal because, well, garbage-in garbage-out.  While the SQL Server QP does a tremendous job at making complex plan choices compared to some of the other commercial alternatives, there are still limits on what the Optimizer can model in a reasonable amount of time.  As such, there are seams where the product tends to not work as well as one would hope.  This will always be true.  While I suppose that will also keep me employable, it is useful to understand those limits because it will help you know where to look or, if it's really hard, when to ask for help.

The SQL Server QP knows a couple of things about the data stored in a table in the storage engine:
1. How many physical pages it uses
2. How many rows it has in it (approximately)
3. Single-column statistics over a sample of the data
4. A basic notion of column interdependance to help in estimating queries with multiple scalar predicates.

From 1 and 2 it can derive the average row width.  That's useful for determining things like "how big will my sort be" if the query needs to sort.  That's a good thing - it leads to reasonable estimates for many choices in the QP.

So let's add sparse columns into the mix.  Sparse columns are useful for data with lots of NULLs.  Often this is a result of a non-traditional third-normal form database problem or, perhaps someone who is not a database person not really trying to make something into a database problem early enough in its lifecycle.  The point is that commercial database systems have a sweet spot around handling data sets with known (and small) sets of columns that can be stored in tables.  There is a TON of expressiveness available in query processors that manipulate this data because this format of data is better supported than other formats.

None of this really means that your problem is going to easily fit into a nice third-normal form system.  Often there are legacy or performance concerns that push an application away from that sweet spot.  Over time, various technologies have tried to bridge that gap (property tables, XML, and object-relational mappings).  Each of them have their own reasons to be, and I don't want to get into them in depth in my post.  I'm going to talk about how the QP deals with these from a modeling perspective.

I built two examples to explore how SQL Server 2008 reasons about sparse columns.  One example creates lots of traditional, nullable float columns while the other is exactly the same except that it uses the sparse attribute.

A few things I learned immediately:
1. Sparse columns don't change the maximum number of columns you can create in a table.  On the surface, this seems unfortunate, since it will limit the kinds of applications that can use the feature. 
2. It does seem to use less space per row.  This isn't hard, as the row format for SQL Server has a null bitmap and also needs 2 bytes per column to store the variable offset pointers.

create table sp1(aaa int)
create table sp2(aaa int)

declare @i int
set @i=0
while (@i < 990)
begin
declare @sql nvarchar(400);
declare @s nvarchar(20);
set @s = @i;
set @sql = 'alter table sp1 add col' + @s + ' float sparse'
exec sp_executesql @sql
set @i=@i+1
end

declare @i int
set @i=0
while (@i < 990)
begin
declare @sql nvarchar(400);
declare @s nvarchar(20);
set @s = @i;
set @sql = 'alter table sp2 add col' + @s + ' float'
exec sp_executesql @sql
set @i=@i+1
end

declare @i int
set @i=0
while @i < 20000 
begin
insert into sp1(col2) values (123.4)
set @i=@i+1
end

declare @i int
set @i=0
while @i < 20000 
begin
insert into sp2(col2) values (123.4)
set @i=@i+1
end

If we run "set statistics io on" and then run "select * from sp1" and "select * from sp2", you'd like to see some difference in IOs:

sp1:
(20000 row(s) affected)
Table 'sp1'. Scan count 1, logical reads 86, physical reads 0, read-ahead reads 80, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

sp2:
(20000 row(s) affected)
Table 'sp2'. Scan count 1, logical reads 20000, physical reads 1, read-ahead reads 19978, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Well, that's good - the sparse format on largely sparse data saves space.  We can confirm that with a quick look into the system tables:

SELECT o.name AS table_name, au.type_desc, au.used_pages
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
WHERE o.name in (N'sp1', N'sp2')

table_name                                                                                                                       type_desc                                                    used_pages
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ --------------------
sp1                                                                                                                              IN_ROW_DATA                                                  87
sp1                                                                                                                              ROW_OVERFLOW_DATA                                            0
sp2                                                                                                                              IN_ROW_DATA                                                  20001

(3 row(s) affected)

We've now confirmed that we actually do have fewer pages.  This is also good.

Now let's see how far into the QP this extends.  Does the QP model the costs for these two queries differently?

SP1 TotalSubtreeCost: 0.08824496
SP2 TotalSubtreeCost: 14.83936

And that, my friends, is a "good thing".  This means that sparse columns are going to help your complex queries when you use a table with sparse columns in it.  The easiest way to implement this is to simply ignore the new feature in the QP, and obviously someone did a good job to make sure that it was costed properly. 

I don't believe that there are additional statistical structures to tell the QP which things are on/off row.  This will show up in a small number of scenarios (similar to how LOB data can be on/off row).  This is outside of the model for how the QP reasons about plan cost, at least from what I've seen from SQL 2008 and from what was publicly said about 2005.

Thanks all,

Conor Cunningham

So I like kung-fu movies.  I don't care how bad the acting is, as long as there is some master/relative/friend who has been killed who needs avenged.  Often good movies are made this way.

In software, a good idea often comes out of getting tired of seeing bugs on a sore subject, often not even your fault.  So, in a fit of rage, I locked myself in my office for a few weeks and came up with the streaming table-valued function, since used as the proverbial duct-tape within the QP for all sorts of unforseen features.  However, it has a "walking on rice paper without leaving footprints" vibe, so I will tell you a bit about this guy ;).

(I did the QO part for this operator and a chunk of the architecture for it - there were others who worked on it too, however).

So a query operator takes rows in the bottom and spits rows out the top (yes, none of the exposed query plan graphs are drawn this way, but that's how people talk about it ;).  Some operators take more than one input - most of the ones with 2 inputs are called "joins".  Some can take an arbitrary number of inputs (usually called UNIONs).  Some take parameters (Compute Scalar).  most don't take parameters, however.

After you've done the 10 or so common operators in a query processor, there's a lot of specialty problems that don't really fit well into those operators.  So people end up trying all sorts of crazy paths to get the data they need out to customers.  Exuberence can often cause a few bugs, however, so eventually something needed to be done.

So, the "Streaming Table Valued Function" (STVF) is a relational operator (takes in rows and passes out rows).  In this case, it takes in some number of scalar parameters (like any good function) and spits out zero or more rows.  The beauty of this operator is that anyone can program anything to get returned, as long as they return back rows - it's like a user-defined operator for internal folks to write features for customers.  Amazingly, there are a lot of these, and you can see them in Dynamic Management Views (amongst others).

So, when you are looking at the plans in the plan cache or some other DMV, you are using this nifty operator:

SELECT usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 1 
ORDER BY usecounts DESC;

(actually you are using it _twice_ in this case, as there are two different sys.dm* references).

  |--Nested Loops(Inner Join, OUTER REFERENCES:(SYSDMEXECCACHEDPLANS.[plan_handle]))
       |--Sort(ORDER BY:(SYSDMEXECCACHEDPLANS.[usecounts] DESC))
       |    |--Filter(WHERE:(SYSDMEXECCACHEDPLANS.[usecounts]>(1)))
       |         |--Table-valued function
       |--Table-valued function

In this plan, you can see that rows are pulled from one TVF, are filtered, then sorted, then passed into the second TVF.

The "OUTER REFERENCE" is the key that the loops join is passing something from the inner side to the outer side (remember in the crazy syntax used here, the first row below the Nested Loops is the "inner" and the second line is the "outer".  So, the Sort/Filter/TVF is the "inner" in this case.

These things differ from the traditional TVFs that you could create in SQL Server as a user.  Those were actually backed by temporary tables in many cases, and this made it a pretty heavy-weight solution if you just want to pass a few rows into the server.  _Streaming_ sounds a lot better than that, you must admit.

Many operators in a QP are streaming - filter is, as is a table scan.  However a sort operator is called a "stop-and-go" operator because it generally consumes all of its input rows before producing the first output row.  Streaming TVFs would fall into the "not stop-and-go" category.

This operator is used heavily starting in SQL 2005 in lots and lots of places.  It is not really something you can directly reference (there is no exposed syntax for this operator), and as such it requires a bit of hunting to track down the various places it gets used.

I'll leave it as an exercise for you guys to find a few non-DMV places that use STVFs.  Any TVF that takes arguments is likely an STVF.  So go look at those query plans, folks!  First one who tells me a non-DMV/DMF STVF feature gets a star next to their name! 

I also know that there is at least one new feature in SQL Server 2008 that uses the STVF.

Happy querying!

Conor Cunningham

So there's all sorts of legacy behaviors in SQL Server that will keep me employable for years and years because it didn't make sense when Microsoft started shipping SQL Server and there's too many people to change it now ;).

RAND() is one of those things that behaves differently in the various commercial database vendors, and it takes some time to grok what's happening.

Let's say that I want to get a random set of rows from a table - that seems like a nice thing to do, right?

So I go write:

SELECT * FROM MyTable WHERE RAND() > 0.5

This seems like a great first attempt to get about half the rows back, assuming a good pseudo-random number implementation.  However, you'll notice that if you run this, the behavior isn't quite what might be expected (at least the way I've described it in this example).

So when one executes this query, you'll notice that sometimes you get all rows from your table and other times you'll get none.  But wait - functions are run per-row, right?  Well, no, at least not in all cases.  The complete details are beyond a single blog posting, but for largely historical reasons the RAND() function is actually executed per query, not per row.  That means that, logically, you get the query from the plan cache, get it ready to run, and then run RAND() once, caching the value.  Then, each and every row uses that cached value when evaluating any scalar logic. 

So, for this example, either that initial call to RAND() returned a value greater than .5 or it didn't.  As such, you get all or no rows only.

SQL Server added TABLESAMPLE as a mechanism to address part of this need - retreiving a sample from a table.  However, it isn't arbitrarily composable, and the semantics used in the default system implementation in SQL 2005 are actually intended to be used for statistics generation - a slightly different goal than quality sampling.

But wait - a lot of functions DO execute per row.  How can I tell which is which?  Well, I am not sure that there is a publicly exposed way, other than trial and error, to determine this.  I need to go do some more homework, as my memory is, well, random.

Even worse, some function computations are moved around in query trees, and so the actual number of executions may not be what you think as "per row".  For example, if you execute a filter before/after the scalar logic, then the results may be different.  Put another way, if you use an index seek vs. a table scan, how many times do you expect any old function to be executed?

It's enough to make your head spin! 

The basic rules are that, as long as the optimizer knows about the semantic issues, it will try very hard to not move around functions who have different semantics when executed different numbers of times.

So, be careful with your functions, built-in, user-defined, or whatever exotic form gets dreamed up next.  There's a lot going on under the covers, and knowing a few details can help you more deeply understand what a query is doing.


Thanks,

Conor Cunningham

I previously blogged about the sequence project operator, which I helped architect/develop while working on the SQL Server QP (here).  In that same post, I promised to tell you about the Segment operator in more detail as well.

Segment is largely a "helper" operator in SQL Server.  It does very little and there isn't a direct way to create one.  They are often used to help facilitate more complex operations.

Every operator in SQL Server's QP takes rows of input and produces rows of output.  Some have multiple children (joins), while others are just unary.  Segment is a unary operator that takes rows of input and determines if some set of columns have changed from the previous row.  Usually, the input on those columns are ordered or grouped to make this meaningful.  An extra column is created, like in a compute scalar operation, which records if the value has changed.  That computed value is then used to drive other physical operators.

You can see what columns are considered in each segment by looking at showplan_xml (from the ranking query in my previous post):

                <RelOp NodeId="1" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1000" EstimateIO="0" EstimateCPU="2e-005" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0328114" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
                  <OutputList>
                    <ColumnReference Database="[t1]" Schema="[dbo]" Table="[z]" Column="col1" />
                    <ColumnReference Database="[t1]" Schema="[dbo]" Table="[z]" Column="col2" />
                    <ColumnReference Column="Segment1005" />
                    <ColumnReference Column="Segment1006" />
                  </OutputList>
                  <Segment>
                    <GroupBy>
                      <ColumnReference Database="[t1]" Schema="[dbo]" Table="[z]" Column="col2" />
                    </GroupBy>
                    <SegmentColumn>
                      <ColumnReference Column="Segment1006" />
                    </SegmentColumn>

The grouping columns are listed, and you can see these "Segment100x" columns that get fabricated along the way.  These are not surfaced outside the query, so you can't see the values of this directly.  However, you can see if the grouping columns have changed.

So, in this plan we have two segment columns on different grouping column sets, both of these passed into the sequence project (you can only actually see that they stop being propogated in the showplan_xml, but the QP is usually smart enough to not pass things along unnecessarily, so that usually means that the buck stops where it stops for a reason :).

Remember, all of the operators can change from release to release.  I don't believe any of this changed substantally from 2005 to 2008 based on what I can see in the plans.

That concludes our daily episode of the operator of the day.  You guys have a good weekend.  I am enjoying warmth and sunshine in Austin in February, which I can tell you is a substantial change from my life while in Seattle.

So I'll be posting explanations for each query operator in the output query trees that you can see through "set showplan_text on" in SQL Server.  I'm hopeful that this will give you the tools to better read the query plans being generated by the QP when evaluating your system.

So I'll start with one that I added into the optimizer - the "sequence project".

create table z(col1 int identity, col2 int default rand()*10000)

declare @i int 
set @i=0
while @i < 1000
begin
insert into z default values
set @i=@i+1
end

select RANK() over(order by col2), * from z

If you look at the graphical showplan you get an output plan that looks a bit funny.  A Sequence Project is a special form of projection (the thing that evaluates the scalars in the SELECT list for each row).  By default, a regular project in SQL Server does not preserve nor guarantee any sort properties in the physical plan.  As a result, ranking functions put in regular projects would not preserve order in all cases deterministically.  People seem to complain when the query results are incorrect.

Enter the sequence project....This little beauty does do scalar computations, but they are generally limited to a very small number of operations that require order for one reason or another.  In the case of ranking functions, an order is required as part of the syntax, and you will see that this particular plan needs a sort since there are no indexes on the base table to satisfy a sort request on col2.  (I'll talk about segments in a future post, but for now ignore them and assume that they are part of the sequence project when looking at this plan).

The problem with sequence projects are that while they preserve ordering semantics, they interfere with a lot of the other optimization logic in the QP.  For example, these can interfere with index matching for predicates above the sequence project in a query tree.  Operators that block other optimizations tend to make optimizer developers very unhappy and sometimes elicit violent reactions!  So, be careful when proposing "opaque" operators to the next query optimization developer you see ;).

So, SQL Server has a small number of operations that in previous releases of the product were "incorrectly" put into regular projects that are now in sequence projects in the current versions of the product.  (By "incorrectly", I mean that there were potential optimizations that could be run that would break their semantics, not that there were actively executing plans at a customer site that were incorrect.  In optimizer terminology, they model was not correctly preserving the semantics even though there were no rules that exposed this fault).

So, the challenge for you is think of some places _beyond sequence functions_ (RANK, DENSE_RANK, NTILE, ROW_NUMBER, ...) that use the sequence projection operator.  If you mail them to me with plans, I may be able to tell you more about what is happening for each of them.

Happy hunting!

Conor Cunningham