SQL Server Performance

Top and newid()

Discussion in 'T-SQL Performance Tuning for Developers' started by Madhivanan, Oct 8, 2007.

  1. Madhivanan Moderator

    I try to generate three digit random numbers

    select distinct number/power(10,len(number)-3) as number from
    (
    select top 100000 cast(abs(checksum(newid())) as bigint) as number
    from sysobjects s1 cross join sysobjects s2
    ) as t
    order by 1
    Which works fine
    If I dont use the top operator, the result does bring exactly the three digit numbers
    What am I missing?
  2. FrankKalis Moderator

    Sorry, but I am not sure, I fully understand your point here. Can you explain a bit more?
  3. Madhivanan Moderator

    [quote user="FrankKalis"]
    Sorry, but I am not sure, I fully understand your point here. Can you explain a bit more?
    [/quote]
    The query should give only 3 digit numbers. When I use Top it gives, otherwise it doesnt
  4. FrankKalis Moderator

    Hm, from time to time I receive a division by zero error, but apart from this I cannot reproduce your issue. Which version have you tried that on?
  5. MohammedU New Member

    I was able to repro the problem....looks like it issue in sql 2000...it works fine in sql 2005.
    You need to use TOP command in SQL 2000 but not in SQL 2005.
  6. FrankKalis Moderator

    I suspected this to be a 2000 issue, but wanted to make sure. I guess MS just changed the internal algorithm between these versions
  7. Madhivanan Moderator

    [quote user="FrankKalis"]
    I suspected this to be a 2000 issue, but wanted to make sure. I guess MS just changed the internal algorithm between these versions
    [/quote]
    Does it mean that 2000 has bug in this case?
  8. FrankKalis Moderator

    I'm not sure if I would call it a bug. Just a different implementation.
  9. Madhivanan Moderator

    [quote user="FrankKalis"]
    I'm not sure if I would call it a bug. Just a different implementation.
    [/quote]
    Thanks Frank [:)]
    It seems some of the things work differently in both the versions
  10. FrankKalis Moderator

    Indeed they do. You might want to use the private newsgroup to gain deeper insight. [:)]
  11. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>Indeed they do. You might want to use the private newsgroup to gain deeper insight. <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"></P><P>[/quote]</P><P>Thanks Frank. I would do [:)]</P>
  12. Madhivanan Moderator

    [quote user="FrankKalis"]
    Hm, from time to time I receive a division by zero error, but apart from this I cannot reproduce your issue. Which version have you tried that on?
    [/quote]
    Yes I tested it with SQL Server 2000. I was surprised why it gave different result when not using TOP

Share This Page