That's gonna sound harsh : but if you don't wanna code, don't be a coder. There's no shortcut in this situation so stop looking for one. You'd...

check this out :

if (object_id('test')) > 0

drop proc test

go

create proc test

as

select null

go

select Name, id, crdate, schema_ver from dbo.SysObjects where name = 'test' and xType = 'P'

go

alter proc...

I can't prove it any faster or slower. Looks pretty much the same.

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'