sorry people, being very silly today...

just used the REPLACE function:w00t:

thanks Laura, the coalesce statement works nicely:w00t:

i want to have one Currency and one Balance column.

where the CurrencyOld is null exclude it and include CurrencyNew

and the CurrencyNew is null exclude it and include CurrencyOld

found the problem earlier, a script released to the environment didn't have a GO or END statement. Script contained an alter proc as well as a create index, so the...

the reason why im trying to delete this index is cos our application is throwing this error in our logs...

09-Jun-2009 06:53:48 [TP-Processor31] [ERROR] [DatabaseResult] Failed to close DatabaseResult java.sql.SQLException: The...

should i try on my side?

oops, my bad... SQL Server 2005

many thanks

this is the query plan without the non clustered index which produced 9 table scans

|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[globalagg1005],0)))

|--Stream Aggregate(DEFINE:([globalagg1005]=SUM([partialagg1004])))

...

i get a count of 315000.

this is the create table script.

CREATE TABLE [dbo].[EntityCollection](

[EntityIdentifierContext] [varchar](32) NOT NULL,

[EntityIdentifier] [varchar](64) NOT NULL,

[EntityIdentifierSequence] [varchar](32) NULL,

[EntityIdentifierComposite] [varchar](128) NOT NULL,

[Data] [ntext] NULL,

[XML] [xml] NULL,

CONSTRAINT...

@christopher, yes i the non clustered index was created on a smaller column:cool:

ok i was also interested to know how many scans were taking place since i added the non clustered index

(1 row(s) affected)

Table 'EntityCollection'. Scan count 1, logical reads 2351, physical...

ill post that in a bit, just busy on something else....

its not partitioned, but i managed to sort this problem out.

i added a non-clustered index on a smaller column and voila... i now have great performance

thanks for all the advice...

ok. Scan Count - is the no of times a table is referenced in a query. i.e. How many times the table was accessed.

so with that in mind, why would...