O.D.A.A., Now that's a good one!

:w00t:

Should also remember that 8 years of experience does not always equate to 8 years of experience; it could be 1 year of experience 8 times.

:hehe::cool:

It also looks like you are using SQL Server 2005, so when considering indexes, also look at covered indexes using included columns. Remember there is a trade off using...

Look in BOL (Books Online) for CONTAINS.

😎

Sorry, but this doesn't quite make sense:

I need to be able to a row that contains the name of the consultant even if the main SELECT statement returns a empty...

You can put a primary key on a TVF as long as it isn't an inline TVF. We would need to see the DDL for the functions to be...

Not sure either, and with no DDL, sample data, and expected results I can't test what I have, so give this a try with no promises that it will work:

with...

You know, I didn't even notice that, but with the lack of formatting, it was easy to miss.

😎

I don't think you are going to get much better improvement. Also, it is possible a nonclustered index on [State] and [InActive:] columns may have been a better choice...

Matt was on to something else. I looked at the indexes as well, and he is correct. I think you will see an improvement if you built an...

Would you please post the execution plan for the query. Oh, and no I don't know how you should do it, as I have never tried it myself, I...

Haritha Kodali (3/4/2008)

---

Here is the view that the query is retrieving from. The earlier script is from DDL of the table that this view is using

CREATE view "MAGICSSHD"."Incident" ...

Your table DDL doesn't match your query unless I missed something in all the code.

😎

Also, what's wrong with just doing this:

SELECT

count(*)

FROM

[MAGICSSHD].[Incident] WITH (NOLOCK)

WHERE

[State:] = N'C'

AND ([InActive:] = 0)

...

Besides formating your code, we could also use the DDL of the base table, including any and all indexes you have defined.

😎