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 |
|
rod_kane
Starting Member
1 Post |
 Posted - 2006-08-08 : 11:49:30
|
| Hello,I have a table with a few million rows and I'd like to be able to add a column and enter random numbers into it. The end goal is to use the new column so that data can be sampled, for example, return the the top 5% of the rows based upon the new column and analyze that.Is there a very efficient way to do this?Thank you in advance,Rod |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2006-08-08 : 12:09:25
|
| You could create a new column of UNIQUEIDENTIFIER type and populate it with the NEWID() function. Alternatively, if you wanted to avoid making schema changes, you could make random selections using the TOP keyword and ordering the NEWID(). Not sure what the performance would be like though.Mark |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-08-08 : 12:10:44
|
| You may find these functions useful for generating random numbers. There is also a sample function that you can use to create samples. Random Integer, Sample, and Datetime Functions:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499CODO ERGO SUM |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2006-08-08 : 22:22:44
|
| I would definantly stay away from the NEWID Function. With a few million rows you will run into performance issues, and it seems like too much overkill for what you have requested. The part of the question that is confusing to me is that you said you want to be able to run logic queries against the column, yet have the column be random? I am assuming you do not want the column to be completly random, but rather have some logic behind its value. for this Check out the NEWSEQUENTIALID() funcion as a default, or you could always use a row_number function. |
|
|
|
|
|
|
|
|
random numbers - fast update