Hi Paul,

Why did you post your solution here instead of e-mailing it to Adam? Don't you think it might have skewed the contest just a little bit?

Adam, do you think change blat1 to VARCHAR(5) will make this more challenging?  People may take the advantage of CHAR(5). :-)

Adam, not the length issue, is the trailing space issue.

Trailing space makes the difference.

for example:

if 'abcde' like 'a%'

print 'Y' -- returns Y

if 'abcde' like 'a  %'

print 'Yes' -- does not return Yes

So if we know the blat1 is fixed 5 characters, we can simply use the left(balt2,5) = balt1 for the join, and a simple HASH join hint will only sacn two tables once, avoid nested loop joins on two 19K rows tables(which caused 1.8M reads).

But if we change blat1 to VARCHAR(5), and truncate the trailing space to make the length vary (from 1 to 5), this will add the chalenge, hence you can not just compare the first 5 characters :-)

INSERT b1

SELECT RTRIM(LEFT(AddressLine1, 5)) AS blat1

FROM AdventureWorks.Person.Address

-- Note. you can comment out some sensitive part in my post if you think this will skew the contest.

David, that didn't make a difference, I am getting the same number of rows back

SELECT blat1,len(blat1),datalength(blat1),b1.blat1 ,left(b2.blat2,5),

len(blat2),datalength(blat2)

FROM b1

JOIN b2 ON  <very secret indeed>

len and datalength reveal that there are some rows with 5 and 4 chars

Adam, still the trailing space issue, because the minimal length in b2 is 5, not 7.

select min(len((blat2))) from b2

So when you change to 7, blat1 automatic added 2 trailing space to make it CHAR(7); that cause result difference.

I have sent my solution.

So Adam,

Do we need to resend you a new solution using CHAR(7), or do we still have a chance to win...?   :-)

Thanks,

Michelle.

Denis, it does make the difference. The len() does not count the trailing space, but they affect the like operator.

Try create the b3 table use trimmed varchar(5) as below:

(CREATE TABLE b3 (blat3 VARCHAR(5) NOT NULL)

INSERT b3

SELECT RTRIM(LEFT(AddressLine1, 5)) AS blat1

FROM AdventureWorks.Person.Address

SELECT count(*) FROM b3 inner JOIN b2 ON    b2.blat2 LIKE b3.blat3 + '%'

--Trimmed VARCHAR(5) returns 111181 rows

SELECT count(*) FROM b1 inner JOIN b2 ON    b2.blat2 LIKE b1.blat1 + '%'

-- original CAHR(5) returns 109984 rows

David,

I see I was talking about LEFT not LIKE, with LEFT it doesn't matter if it is char or varchar

Umm, Denis, I think with the answer you "spilled your beans" about your secret :-(

It's all about iteration.

So where are three best queries?  Who are the winners?

Criminy. I didn't even NOTICE the char vs. varchar part. Now my answer could be wrong. Blah.