Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Unanswered: Interesting peculiarity (another anomaly?)

    Run the following:

    declare
    @s1 varchar(10), @s2 varchar(10)
    declare @t table(
    recordid int identity(1,1)not null,
    field char(1)not null)

    insert @t (field) select 'a' union select 'b'
    select @s1 = '', @s2 = ''
    select @s1 = @s1 + field +',' from @t order by 1
    select @s2 = @s2 + field +','from @t order by recordid
    select
    [Weird]=substring(@s1, 1,(datalength(@s1)-1)),
    [Not]=substring(@s2, 1,(datalength(@s2)-1))

    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Every time I use that syntax (doing string aggregation on the fly), I run into another "interesting feature" in MS-SQL. I talked with a couple of the SQL Server PMs a while back, and they had a fit...

    Speaking of fits, you should try to run the idea past Herr Celko... I imagine that would be great fun to watch as he tried to conjour enough invective to boil that concept out of his system!

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, first of all, I was the one that "attacked" a guy here for using this algorythm a while back. Not to say that I am the only opponent of relying on accuracy of it. Second, it's the ORDER BY clause that produces the difference, which is exactly what interested me. Also interesting is the fact that this behavior can be observed in bot 2K and Yukon, which tells me that the claim in BOL for both products, saying that arguments to this clause can be either fields, expressions, or numeral constants, is faulty.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •