dwain.c (1/9/2013)
Alan - That's a very intriguing use of NTILE!I would recommend, however that you change the way you construct your asciichar table:
;WITH asciichar(n, c) AS (
SELECT n=64+number, CHAR(64+number)
FROM [master].dbo.spt_values Tally
WHERE [Type] = 'P' AND Number BETWEEN 1 AND 26)
SELECT *
FROM asciichar
Thank you, and thank you. I'm still just learning Windows functions (have been studying them for ~6 months now give or take.) For me it's been easy to find a uses for ROW_NUMBER , RANK and DENSE_RANK but this was a rare case where I found some use for NTILE; this was originally a dynamic SQL query.
I agree that I should have used a tally table. I've been writing CTE's for counting for awhile and can do so while sleeping. I still fumble around with the tally table and, in this case was in a hury to post my code. I just updated my query to include the tally table as you showed above: 303 reads is now 146 reads. 🙂
-- Itzik Ben-Gan 2001