Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Jan 2003
    Location
    Peterborough, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using ORDER BY NEWID()

    I'm looking at using ORDER BY NEWID() in my sql statement to generate random records from a database, like this:

    "SELECT TOP 3 * FROM tblTable ORDER BY NEWID()"

    Is this an efficient method for selecting from tables with less than 5,000 rows, or can anyone suggest a better approach.

    DB is MS SQL Server 2000.

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    Wichita
    Posts
    3,880
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It seems to work ok but as for efficiency you'll have to use the performance tools to see if it's creating a burden on the server.

  • #3
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you've got less than 5,000 rows, I think pretty much anything will work, unless you've got other databases on the server that are getting hit hard.

    That isn't very much data as far as SQL Server is concerned - but it's still a good idea to do things right - so I'd definitely look at the server performance tools as Roy suggested.

    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #4
    New Coder
    Join Date
    Jan 2003
    Location
    Peterborough, UK
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks guys, this does fit with what I was thinking. Using the NEWID() may be inefficient for very large tables as the db has to generate a unique ID for each row, but as you say 5,000 is few.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •