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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-09-07 : 09:57:51
|
| alaam writes "Hello everyone,I'd like to select randomly 100 records from a tables within certian condition and after search I used the NEWID() ,but it takes long time to process and excute my querey.would you please explain what this NEWID() exactly doing and why it takes too long ?ThanksAlaa M" |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-09-07 : 10:42:27
|
| It just allocates a random-ish value to each row.It means reading and sorting the whole table if you do as select *tryselect *from tblwhere id in (select top 100 id from tbl order by newid())==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-07 : 10:46:51
|
| >>where id in (select top 100 id from tbl order by newid())won't this take just as long?Be One with the OptimizerTG |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-07 : 10:53:23
|
| NewID() creates a new uniqueidentifier value. Uniqueidentifiers are non-sequential and roughly randomly distributed, so they make a good poor-man's randomizer. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-09-07 : 10:55:46
|
| I don't know how you would select random records without doing some sort of scan. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2006-09-07 : 11:05:25
|
| I suppose if you need a truly random selection of 100 rows from a huge table, you could do something like generate enough random integers (at least 100) between the min(id) and max(id) assuming your id is an integer and there is an index on that column, then select the top 100 from your table joined to a table of your generated IDs. If you have gaps in your ID values then you may need to either generate enough to get 100 matches or check for the existance as you're generating them.Be One with the OptimizerTG |
|
|
|
|
|
|
newid()