Hi Pradeep -

The root level is part of an index, not a place where an index is located. The root level is the single index page at the...

Meaning, of course, that you did NOT use the code.

Test your code in small, simple pieces first.

Read the documentation carefully.

-- This is what you are doing, and it won't work.

select PATINDEX('"!"', '!@#$%^&*()((*(() <>')

RETURNS 0

-- You need to use the...

It appears you are expecting patindex to return a bit.

According to

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

The return code is

"bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int."

I agree, it's not simple. I avoid solutions like this until there is no other practical choice, so I'm glad you're looking for other solutions before trying this one.

I...

How are things coming along?

Do you have a development environment where you can safely practice the concepts and examples given in the links I provided?

If you do give this a...

That's a lot of columns... which brings me back to my previous post, where I mentioned SELECT A.*, B.*...

I found a well-written post which explains this - please scroll...

Oops.... where I wrote

To your query I added

select itm.Item, itm.OrderNo ,itm.OrderLine... as rownum

I actually meant to write

To your query I added

,ROW_NUMBER() OVER (PARTITION BY ... as rownum

Thank you for providing tables and data - this takes out a lot of the guesswork.

Here is a relatively new and efficient way to solve this kind of problem.

To your...

Hi Oscar,

Thanks for providing the query plans.

Could you also provide

1) The full DDL of the view,

2) Plus the full DDL of the tables referenced by the...

diat150 (5/4/2012)

---

vicdileo (5/4/2012)

---

My suggestion for exploring indexed views is based on the query plans you provided which indicate that the indexes are being used in your query (I didn't see...

My suggestion for exploring indexed views is based on the query plans you provided which indicate that the indexes are being used in your query (I didn't see any table...

The query plans certainly concur with your statement that the first outer join is the slowest - it's reading 160 million rows via the index (IX_devicehistory).

Just to be clear -...

RE:

8) if so, then what does the query plan look like when your WHERE IN has 16,000 deviceid's?

its actually 1600+, Ive attached one for 720 devices

I don't see the attachment...

Thanks for providing the query plan and other info.

Regarding the query plan:

I don't see any table scans, so that's good.

It looks like the query plan is probably not being cached,...