A quick stand-alone repro for the collation thing:

USE tempdb;

GO

CREATE TABLE dbo.SpaceTest

(

Data VARCHAR(4000) NOT NULL

);

GO

INSERT dbo.SpaceTest

SELECT TOP (10000)

SPACE(4000)

FROM master.sys.allocation_units A1,

master.sys.allocation_units A2,

master.sys.allocation_units A3,

master.sys.allocation_units A4;

GO

DECLARE

@Bitbucket VARCHAR(4000);

SET STATISTICS TIME ON

-- SLOW

SELECT

@Bitbucket = --dbo.fn_CleanUp_MichaelMeierruth_MKII(Data)

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Data COLLATE LATIN1_GENERAL_CI_AS,

' ',' '),

' ',' '),

' ',' '),

' ',' '),

' ',' '),

' ',' '),

' ',' ')

FROM

dbo.SpaceTest;

-- FAST

SELECT

@Bitbucket = --dbo.fn_CleanUp_MichaelMeierruth_MKII(Data)

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Data COLLATE LATIN1_GENERAL_BIN,

' ',' '),

' ',' '),

' ',' '),

' ',' '),

' ',' '),

' ',' '),

' ',' ')

FROM

dbo.SpaceTest;

SET STATISTICS TIME OFF;

DROP TABLE dbo.SpaceTest;

The other odd thing is that if I change the data type in the table from VARCHAR(4000) to CHAR(4000), the problem goes away. I'm convincing myself that this is either an optimizer bug, or some effect of the complex collation precedence rules (coercible-default and all that).

Paul