You can use two dense_rank here..

select *,rank_within_key - rank_within_key_and_product + 1 as counting from (

select key1, date, product_id, dense_rank() over (partition by key1 ,product_id order by date) as rank_within_key_and_product,dense_rank() over...

How much memory SQl server will be using depends only on your configuration of Min and Max Server memory configuration parameter. By default it will use as much memory as...

You can use two dense_rank here..

select *,rank_within_key - rank_within_key_and_product + 1 as counting from (

select key1, date, product_id, dense_rank() over (partition by key1 ,product_id order by date) as rank_within_key_and_product,dense_rank()...

select key1, date, product_id, dense_rank() over (partition by key1 order by product_id, date) as product_id_sw

from table2 order by key1, date;

take out the date from the order by in over. That...

If i got your question right then you can use following..

with cte as

(select <your cols>,CASE WHEN COL_1 = 25 AND COL_2 <> 30 THEN 1

ELSE 0

END as DOHFLAG

)

select * from...

120 million rows are quite a huge number. Have you ever thought of parttioning the table?

say e.g. you have around 8 parttiton Then you could start say 8 session with...

You're still just optimizing for logical I/O, not the whole cost of executing the extra statements. Compare that overhead to 150 microseconds for the recompile.

I would like to know that...

I am not saying use this in each and every procedure you have.It is a specific solutio for specific problem and in no way should be abused.

Optimizers are becoming better...

I am not saying that it will make your future tuning easy or something like that. You have to keep looking for whether you query is still performaning better than...

The overhead of the querying two extra tables is just 3 logical IO's.5 compilation a minute is not a big overhead in isolation but when you have other processes...

I have put it there as well.I guess it will be published in next few days. I have not put this method to test whether people will like it or...

I do not want to fight optimizer.I want to help optimizer where he can not fight on his own.

It is like "Optimize For" hint.Just helping optimizer to generate the plan...

None of these Optimize for wont work when you want different plan based on your criteria. Once a plan is generated then that plan will be used unless it is...

What is other better way to remove parameter sniffing aprt from the recompile.I would like to know. This s only when you have skewed distribution.When optimizer is not doing what...

The MAXDOP is to control parllelism degree but you can not have this value more than configured value.If you will have MAXDOP value more than configured value then it will...