Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

UDF to create a counter Expand / Collapse
Author
Message
Posted Friday, January 25, 2008 9:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:47 PM
Points: 7,154, Visits: 15,645
And - there's NO guarantee that it won't return the same number twice... The larger the set of numbers, the more likely it WILL duplicate the number.

Try this instead:

select top 1000 checksum(newID()),* from myTable



----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #447625
Posted Friday, January 25, 2008 10:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100
That works also.
I'll use your since!

Thanks!!
Post #447677
Posted Friday, January 25, 2008 11:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Monday, November 17, 2014 12:50 PM
Points: 13,872, Visits: 9,598
checksum doesn't guarantee unique results. It'll probably be unique, but it's not guaranteed. More likely to be unique than Rand() is, though.


- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #447693
Posted Friday, January 25, 2008 11:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100
After running several test the top 1000... will not work with the query.
The user name and date is being queried, member quired is not one of the 1000 returned therefore, no items selected.

When I run it with the udf random it works.
Post #447710
Posted Friday, January 25, 2008 11:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:47 PM
Points: 7,154, Visits: 15,645
GSquared (1/25/2008)
checksum doesn't guarantee unique results. It'll probably be unique, but it's not guaranteed. More likely to be unique than Rand() is, though.


Per BOL:


CHECKSUM satisfies the properties of a hash function: CHECKSUM applied over any two lists of expressions returns the same value if the corresponding elements of the two lists have the same type and are equal when compared using the equals (=) operator. For the purpose of this definition, NULL values of a given type are considered to compare as equal. If one of the values in the expression list changes, the checksum of the list also usually changes. However, there is a small chance that the checksum will not change.


From the way I read that it, running a checksum against unique values will return unique values. That's what a hash function does, after all: it returns a unique value that can be used to determine equality. If the hash codes are equal, then so are the values. NEWID() does in fact guarantee uniqueness, so I'm relying on that.

From testing, I've generated sets of 6 million unique numbers using that and have yet to run into a conflict.

Am I misreading that somehow? I am making some assumptions, but I thought they were pretty solid.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #447723
Posted Friday, January 25, 2008 11:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:47 PM
Points: 7,154, Visits: 15,645
Debra Owens (1/25/2008)
After running several test the top 1000... will not work with the query.
The user name and date is being queried, member quired is not one of the 1000 returned therefore, no items selected.

When I run it with the udf random it works.


Debra - the "top 1000" was there for my purposes (the test table was rather large). I just wanted to highlight the Checksum(NewID()) method....

You absolutely don't need the TOP n syntax if your case doesn't lend itself to it.


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #447734
Posted Friday, January 25, 2008 11:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 28, 2010 10:56 AM
Points: 40, Visits: 100
I removed the top 1000 and it worked.
I added abs to insure a positive number.

abs(checksum(newID()))

;)
Post #447738
Posted Wednesday, March 2, 2011 9:35 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:06 PM
Points: 133, Visits: 28
This is about 3 years too late, but it worked for me:

SELECT [Field1]
, ROW_NUMBER() OVER (ORDER BY [Field1]) AS 'Row Number'
FROM [dbo].[MyTable]



Post #1072027
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse