It probabely violates all the normal forms...

Still the same conclusion, you do 4 trims for nothing when none can be used and give out the same results.

<>

not sure it would work in this case.

CASE WHEN CancelDate <= GETDATE() THEN 3

WHEN CancelDate IS NULL AND CONVERT(varchar(10), PaymentEndDate, 111) <= CONVERT(varchar(10), getdate(), 111) THEN 1

ELSE 0

END

HTH. Did you change the collation only in the query or in the table DDL?

BTW you didn't answer why you are inserting xml in the db..

I think I would go with a design similar to this one :

Create table a

( PK int not...

Damn, forgot to make you check the provider, it's always a good place to check.

Index scan :

Sql server will analyse ALL the rows of the index and check if Left(.....

So 1 000 000 rows = 1 000 000 checks.

Index seek :

Sql server...

Where are you concatenating the spaces between the fields?

Also I think this version would be slower than mine as you call LTrim and ISNULL 4 times/row while I only call...

Hence my second post :

print 'SET CONCAT_NULL_YIELDS_NULL ON '

SET CONCAT_NULL_YIELDS_NULL ON

Select null + 'A'

print 'SET CONCAT_NULL_YIELDS_NULL OFF '

SET CONCAT_NULL_YIELDS_NULL OFF

Select null + 'A'

SET CONCAT_NULL_YIELDS_NULL ON

...

What's the size of the fields?

What's the size of the table (100 millions of rows of much smaller)?

Is is something like toggle field, (always 1 field filled but never both...

[EDITED]

Forgot to mention that must must have CONCAT_NULL_YIELDS_NULL ON for this query to work :

if null = null

print 'RUN CONCAT_NULL_YIELDS_NULL ON'

else

print 'you''re set'

Select ISNULL(FirstName + ' ', '') + ISNULL(middlename + ' ', '') + ISNULL(lastname + ' ', '') + ISNULL(suffix, '') as FullName from dbo.Contacts

Forgot to mention this... the first query will run faster (once fixed) because AS400 will be filtering out the results using its own indexes instead of sending out a few...

That was my point .

The most likely explanation is that AS400 doesn't understand exactly what you want to filter in the where condition. ...