paul.knibbs (5/30/2013)
Duncan Pryde (5/30/2013)
If the same values were put in both columns, we would still get different numbers of rows returned for all values of @x greater than 3 since ordering by iAsString would give 1,10,10,11,2,2,3,3...
However, you'd only get more rows on the first statement returned for *even* values of X greater than 3. For example, if X=5, you get 1, 2, 2, 3, 3 from the integer version, and 1, 10, 10, 11, 2, 2 from the string version, whereas if X=6, you get 1, 2, 2, 3, 3, 4, 4 on the integer and 1, 10, 10, 11, 2, 2 on the string. That's if it worked as we both thought it did, which it clearly doesn't!
That's right - for odd values you'd get more rows returned by the second statement - which is the same behaviour as in the actual question. To separate out the two things completely to focus on varchar vs int ordering we could rewrite the statement as:
DECLARE @Table TABLE
( iAsInt int,
iAsString varchar(2));
DECLARE @i int, @x int;
SET @i = 9;
WHILE @i <= 19
BEGIN
INSERT INTO @Table
VALUES (@i, CAST(@i AS varchar(2))),
(@i + 1, CAST(@i + 1 AS varchar(2)));
SET @i = @i + 1;
END
SET @x = ??? --set an integer value between 1 and 20
--statement 1
SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsInt;
--statement 2
SELECT TOP (@x) WITH TIES iAsInt, iAsString FROM @Table ORDER BY iAsString;
This will give the same "values" in both columns, leaving the ordering as the only factor in determining the number of rows returned, which will be different for all values of @x - statement 1 having one more row for even values, statement 2 having one more for odd values.