Hmm, that still doesn't make sense.

Compare them very closely: there must be some difference somewhere ... or maybe you specified DESC on one of the existing index keys?!

In SQL Server, there is no difference.

I guess the backward compatability would be that you can still use "numeric" rather than the offical "decimal".

Yeah, reasonably close.

Probably the major difference is that the OP and myself don't want to "flatten" the Chain and Store into a single "location".

nick.mcdermaid (2/7/2013)

---

Thanks for your advice Scott.

I did change the PK to

RowNum_Start,SRC_System, SRC_Application

and also

SRC_System, SRC_Application,RowNum_Start

(Yes I know you can create a clustered index that isn't based on the primary key)

In both...

You could move the BLOB(s) to a different table; use views if you need to, to combine the original table columns and the BLOB(s), to keep current apps "happy".

So are you doing a REORG or a REBUILD?

Is it on Enterprise Edition (2008)?

You need to keep this normalized and have a separate table for every relation. You could even create an additional table to control the chain/store incidents if needed.

Here's some...

You could partition the table; if you're lucky, the older data will not even need rebuiilt, and you can greatly reduce rebuild time and overhead.

Even if it all still needs...

I strongly recommend fully qualifying all table names with the owning schema, typically "dbo" but of course could be different in your specific environment.

For example:

...

from dbo.AbstractData a

inner join dbo.AbsDrgDiagnoses b...

Granting bulkadmin doesn't actually give that person INSERT authority to any table.

Any user would still need standard GRANT INSERT on a table or a role containing that authority to insert...

Eugene Elutin (2/7/2013)

---

What is the problem with updateable...

nick.mcdermaid (2/6/2013)

---

I took an uneducated guess and

-Added a unique index to RowNum_Start

-Removed the SRC_System join (I know I know, changing the requirements is cheating )

and query time went...

No, I wouldn't use OR just because that makes a view updateable -- UPDATEs aren't typically done using views like that anyway.

AND

CASE WHEN @InfoKey = 5 THEN

CLIENT_IDENTIFIER_TYPE.Code IN (028,029,030)

ELSE

CLIENT_IDENTIFIER_TYPE.Code = @Code

END

The "THEN" and "ELSE" parts of a CASE statement must evaluate to a single value. The value can...

Is the "MS Dynamics Navision ERP" application running on the same box as SQL Server in prod?

If so, I'd lower the fixed memory for SQL considerably.

Even if not, I'd still...