Note that Jeff's second solution will sort the results in the order you demonstrated in your original post. The NTILE solution would not. If you need the numbers in that order then use the second solution (where RNum = Seq%5+1) you could do so using NTILE like this.
WITH cteEnumerate AS
(
SELECT Seq = ROW_NUMBER() OVER (ORDER BY queryName)-1
,queryID
,queryName
FROM #TestTable
)
SELECT queryID
,queryName
,RNum = Seq%5+1 -- leaving here to compare to NTILE
,NTILE(5) OVER (ORDER BY Seq%5)
FROM cteEnumerate
ORDER BY Seq
I'm not recommending this, I'm just showing how you'd get the result you demonstrated using NTILE. Either way, I would recommend the second solution as it will get the job done much faster and with a lot less reads.
Lastly, I recommend never using NTILE unless you need:
1. the results sorted in the in the order that Jeff's above NTILE solution sorts the numbers AND
2. You need to partition your "tile" numbers (e.g. NTILE(N) OVER (PARTITION BY...))
If #1 is true and #2 is not (no partitioning required) then I would recommend the NTally table alternative here.
-- Itzik Ben-Gan 2001