Newid() is generating id's in sequential order.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Newid() is generating id's in sequential order.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-04 : 09:37:18

Phillip writes "Hello,

I'm having a problem with one of my SQL 7 servers service pack 3 running on NT 4 service pack 6. Newid() is generating id's in sequential order. Have you run in to this problem before? I checked your faq and performed a search and found nothing.

print NEWID() -- I ran this code 5 times.
36BA8243-D59E-11D6-81DC-00B0D0B0907F
36BA8244-D59E-11D6-81DC-00B0D0B0907F
36BA8245-D59E-11D6-81DC-00B0D0B0907F
36BA8246-D59E-11D6-81DC-00B0D0B0907F
36BA8247-D59E-11D6-81DC-00B0D0B0907F
36BA8248-D59E-11D6-81DC-00B0D0B0907F"

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-10-04 : 09:37:18

If you read our article on NEWID() at http://www.sqlteam.com/item.asp?ItemID=8747 it says that NEWID() doesn't generate random results on NT4 machines.

phillip
Starting Member

1 Post

Posted - 2002-10-04 : 18:35:42

Sorry, I should have clarified. I did read the article (but not the rewrite until now) and I disagree with the statement about SQL 7 not being able to generate a random newid(). One of my SQL 7 boxes can not generate a random newid() and my other SQL 7 boxes running the same service packs for SQL and NT4 can produce random newid's. Has anyone ran in to this and possibly found a solution? I will be ugrading in a few weeks but would like a clean solution until then.

Good SQL7:
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

AA084444-9490-4999-986B-85C704B27230
A589EA8C-928D-4EF3-B054-6AC1F09C67A6
699E546D-B9FE-4AD9-967F-F4433E2AEDB5
7E45D6A9-6529-4AD7-8528-2DFFFF96DC85
71090BF2-035C-4A7F-B11D-6F28D248B482

Bad SQL7:
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Standard Edition on Windows NT 4.0 (Build 1381: Service Pack 6)

36BA8325-D59E-11D6-81DC-00B0D0B0907F
36BA8326-D59E-11D6-81DC-00B0D0B0907F
36BA8327-D59E-11D6-81DC-00B0D0B0907F
36BA8328-D59E-11D6-81DC-00B0D0B0907F
36BA8329-D59E-11D6-81DC-00B0D0B0907F

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-04 : 18:58:56

Then it might have something to do with either the network cards or the CPU configuration of the two machines. I know that these are used in the GUID algorithm to generate the values.

It also *might* be affected by the order of installation; one machine could've had the SQL service packs applied first, then the Windows SP's, and vice versa.

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-05 : 11:17:15

Phillip - the way NT4 and win2k generate GUID's changed a lot and whilst I don't believe there was a kb article about this - its something that has been mentioned numerous times in the sql newsgroups. Please remember that NEWID() is designed to generate UNIQUE values and NOT random values. Whilst on win2k they appear random and are often suggested as a way of selecting random rows this is not what the function was designed for - the RAND() function is designed to return random values. Pleas e search google groups for "NEWID not random NT" and have a look for replies from Steve Kass and BP Margolin - there's some very interesting discussions on the randomness of the various methods and how to use RAND() effectively - in particular the fact that reseeding RAND(N) with a different value of N actually defeats the purpose and evaluates to

RAND(N) = the fractional part of 0.713573+N*1.8633E-05
(to about 5 decimal places) e.g.

use pubs
go
select left(rand(pub_id),7), LEFT(0.713573+pub_id*1.8633E-05,7)
FROM titles

[url]http://groups.google.com/groups?num=20&hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=off&q=NEWID+not+random+NT+group%3Amicrosoft.public.sqlserver.*&btnG=Google+Search[/url]

HTH
Jasper Smith

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-06 : 05:19:43

quote:

RAND(N) = the fractional part of 0.713573+N*1.8633E-05
(to about 5 decimal places) e.g.

Except that RAND(2147483563) to RAND(2147483647) and
RAND(-2147483563) to RAND(-2147483648) are all the same (and equal to RAND(0))

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-06 : 07:53:41

Another consideration is that SQL Server's query optimizer can have difficulty with the non-deterministic nature of NEWID(), as seen in this example:


CREATE TABLE Numbers (n int PRIMARY KEY)

DECLARE @n AS int
SET @n = 0
SET NOCOUNT ON
BEGIN TRANSACTION
WHILE @n < 10000
BEGIN
  INSERT INTO Numbers VALUES (@n)
  SET @n = @n + 1
END
COMMIT TRANSACTION
SET NOCOUNT OFF

GO

-- Look at the distribution of the first byte of NEWID()

-- This produces the wrong result because NEWID gets called again after the aggregation:
SELECT r, COUNT(*)
FROM (
    SELECT CAST(NEWID() AS binary(1)) AS r
    FROM Numbers
  ) AS a
GROUP BY r
ORDER BY r

-- This produces the right result:
SELECT r, COUNT(*)
FROM (
    SELECT CAST(NEWID() AS binary(1)) AS r
    FROM Numbers
    GROUP BY n
  ) AS a
GROUP BY r
ORDER BY r

(This is adapted from the thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=18896 , which also explores some different but related difficulties with RAND()).

Edited by - Arnold Fribble on 10/06/2002 07:58:48

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-06 : 08:26:36

Yeah, RAND(0) seems to be anamolous - Steve Kass mentions this in one of the posts from the sqlserver newsgroups - I certainly don't claim to be any good at maths

HTH
Jasper Smith

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-10-06 : 21:44:20

quote:

Phillip writes "Hello,

I'm having a problem with one of my SQL 7 servers service pack 3 running on NT 4 service pack 6. Newid() is generating id's in sequential order. Have you run in to this problem before? I checked your faq and performed a search and found nothing.

print NEWID() -- I ran this code 5 times.
36BA8243-D59E-11D6-81DC-00B0D0B0907F
36BA8244-D59E-11D6-81DC-00B0D0B0907F
36BA8245-D59E-11D6-81DC-00B0D0B0907F
36BA8246-D59E-11D6-81DC-00B0D0B0907F
36BA8247-D59E-11D6-81DC-00B0D0B0907F
36BA8248-D59E-11D6-81DC-00B0D0B0907F"

Stop it, you will use them all up

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources