It just run fine on my machione I have 2008R2 as well.

Try to search all the '.' and check if there is any issue specially while copying and pasting.It is...

do not use sp_ as prefix for your procedures. SQL server considers these as special procedures

and search for these in all databases and then executes it.That is why it is...

Did you use the set options properly while creating the table,as well as indexed view and whiel querying the data?

Read in the end of the below whitepaper.

http://msdn.microsoft.com/en-us/library/cc917715.aspx

May be the login default database is the same in which the proc is defined.

Is the name start with sp_ something like ssytem procs?

Just awesome.. The cost of the query is 1.8 billion and the estimated rows at one point are 417 trillion.

How do you expect this query to run fast?

You...

I copied the script and paste in my ssms and it worked fine. I am putting it back here..It should not cause any issues. I took out the comments and...

drop table tbl_intrate

go

create table tbl_intrate(schm_id int,instno int,roi float)

go

insert into tbl_intrate

values

(1, 1, 20),

(1, 4, 22),

(1, 8, 25),

(1, 10, 26),

(1, 12, 28 )

go

select * from tbl_intrate

go

drop proc myproc

go

create proc myproc

(

@instno...

Please post the ddl, and the data insertion script.

I think before dropping the indexes it is better to diable index first. It is equivalent to drop index in the terms that index is not maintained ,it is not...

As you mentioned already distinct will sort your output(in case it doesnt use the hash distinct) and then will remove duplicates.To sort the data you need memory and how costly...

--Try something like this.

declare @dt datetime

set @dt = '20120705'

declare @dt datetime

set @dt = '20120705'

select dateadd(wk,-52,case when @@datefirst = 7 then

...

Gullimeel (7/5/2012)

--------------------------------------------------------------------------------

-- ntext does not support CHARINDEX

But it could very well use the patindex

The third parameter of CHARINDEX makes it very useful for trivial string splitting. How would you...

But it could very well use the patindex 🙂

There is no hard and fast rule of 30% cost of the sort. It can be anything. It is relative to the overall batch and thus doesnt signify anything apart...

It is a kind of bug as reported here. Even if you have created the index and index is not selected then it will show missing index stats.

https://connect.microsoft.com/SQLServer/feedback/details/400578/query-plan-missing-index-recommendation-doesnt-check-if-an-index-actually-exists

Now the issue...