You need to do further research before you change the existing index to a filtered index, because that index will no longer satisfy queries originally using it if you filter...

gbritton1 (4/16/2014)

---

Or, in that same snippet, take advantage of the fact that you initialize the variable @CompleteSetFound to 0 in the declaration. Then you can simplify to:

IF EXISTS (

...

I think this coding could theoretically perform better:

...

IF (@AnimalsFound = 1)

BEGIN

SET @CompleteSetFound =

CASE WHEN EXISTS(SELECT

1

FROM @FarmMatches ri

INNER JOIN [dbo].[Animal] ani ON ani.[FeatureID] = ri.[OverallID]

GROUP BY

FeatureID, Code

HAVING

MAX(CASE WHEN TypeName...

I suggest changing one of your existing indexes to also cover this query:

CREATE NONCLUSTERED INDEX [AI_OperatorCBGEstimate_14947]

ON OperatorCBGEstimate ( DateEndedStandard, UpdOperation )

...

I've coded checks for just Animal table groupings. Please check and see if it produces the results you want.

Edit: This is doing only full matches but partial matches could...

It's possible but it's huge overhead you should probably avoid.

You can use ROW_NUMBER() to sequentially number the data when you read it rather than having to physically update the values...

You could try something like this and see if it helps:

WITH cte_OrderProjectType AS

(

select A.Orderid, min(B.TypeID) , min(C.CTType) , MIN(D.Area)

from tableA A inner join (

select PID,...

Please add an alias to columns in the SELECT:

select ?.Orderid, min(?.TypeID) , min(?.CTType) , MIN(?.Area)

Otherwise we can't really rewrite any of the code.

What specifically does the data in the "Verdate" look like?

For example, is it 'yyyymmdd hh:mm'?

First properly tune the indexes, in particular getting the correct clustering index (hint: very often this means not clustering on an identity column). That is usually the single biggest...

Easiest way with accuracy to me is to run the code for each table create without producing any data, then script out the CREATE TABLE statement to copy into the...

Another possibility:

It also provides documentation of the FK relationships.

I suspect they wanted their application to be able to run on many different dbs. Some don't (or didn't) directly support...

Excellent! The other thing you need to include in your analysis is the "missing index" info from SQL (DMVs sys.dm_db_missing_index*).

sys.databases contains the owner of the db. That view is stored in the master db. ...

If you disable the clustered index, you've disabled the table, since the...