@thom-2 Bolin,

I might be the only one but I like the title of "Unexpected View Performance" because use of the UNION operator seems to be much more prevalent in views than in most other places. It's also where you discovered the problem and so I think the title is very appropriate. Thanks for taking the time write it and nice job.

Just a suggestion, though... I'd recommend staying away from 3rd party data generators to build test data for whatever article you write simply because not everyone will have it and that will inherently limit participants. When you did provide the T-SQL to build test data, it only built one value which, again, will tend to limit participants. The only things that kept this from being a 5 star article is the absence of a decent T-SQL test data generator and that one statement that had no code to go with it. 🙂

With those thoughts in mind, here's one way to generate a shedload of the data you needed in a short time without explicit loops.

SELECT TOP 1000000

AccountNumber =

CAST(

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ RIGHT(ABS(CHECKSUM(NEWID()))%1000000+1000000,6)

AS CHAR(9))

INTO dbo.Agents2009

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

SELECT TOP 1000000

AccountNumber =

CAST(

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ CHAR(ABS(CHECKSUM(NEWID()))%26+65)

+ RIGHT(ABS(CHECKSUM(NEWID()))%1000000+1000000,6)

AS CHAR(10))

INTO dbo.Agents

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

Like I said, nice job and thanks for sharing your find with us.