I have a table with at least a million records in it. These rows were created by a custom app that reads several SharePoint site collections and stores the item urls in the table. Now, since we read the site collections in a serial manner, first few thousands of rows belong to first site collection, next few thousands belong to second site collection, and so on.

I have another app that reads this table in a sequential manner. However, this way I end up sending HTTP requests to the same site collection for a longer time.

I know I could get random results from the table in my second app. But, that is not an option. I cannot change the way the second app works.

Now, the question is: How can I take all rows in the table, shuffule them and store it back in the table?

Update: SQL Server 2008 R2 is my database Server

  • Which DBMS do you use and which data type has your key column? – bernd_k Jan 14 '11 at 8:12
  • In which database? SQL Server? – CoderHawk Jan 14 '11 at 8:13
  • I edited my question. I am using SQL Server 2008 R2 – ravikanth Jan 14 '11 at 10:33

If the calling app is explicitly setting a particular order in its query (if you are running MSSQL you can check this by having a profiler session running while the app does its thing, other DMBSs will have similar logging options) then there is nothing you can do and if it isn't you can not completely guarantee any particular order.

If no explicit ORDER BY clause is given then the data will come out in an order that is officially "undefined" - it will be what-ever order the server finds most convineint. For a single table query this will most likely be the order of the primary key. In MSSQL if you have a clustered index the results will most likely come out in that order for a single table query. For multi-table queries it is even less clear cut as it depends which way around the query planner choses to go to get your results (which without explicit index hints could vary over time as the balance of data in the tables, as estimated by the index stats the server keeps, changes).

If the table has no clustered index or primary key then the data is likely to come out in an arbitrary order similiar to the order the data was inserted. In this case you could try:

SELECT * INTO temp_table FROM table_to_be_reordered
DELETE table_to_be_reordered
INSERT table_to_be_reordered SELECT * FROM temp_table ORDER BY NEWID()

or this may be faster

INSERT table_to_be_reordered SELECT * FROM temp_table ORDER BY NEWID()
DROP TABLE table_to_be_reordered
EXEC sp_rename 'temp_table', 'table_to_be_reordered'

In the above NEWID() is MSSQL's function to return a UUID and it uses random rather than sequential IDs by default - in other DMBSs you should find a similar function that you can use. Be careful with your choice of function: for instance under MSSQL the RAND() function is evaulated once per query, not once per row, so SELECT * FROM somewhere ORDER BY RAND() would not have the desited effect (you can see why by running something like SELECT RAND(), * FROM some_table).

If you are using MSSQL (your question didn't state which DBMS you are targeting) and do not already have a clustered index on the table, and either have a sufficiently random column (a UUID column for instance) or could add one without upsetting the calling app, you could create a clustered index on that which would be quicker than the SELECT INTO / DELETE / SELECT INTO above. But again: this will have no effect at all if the app is explicitly asking for the results in a particular order and may not have any effect anyway otherwise.

  • Thanks. I am using SQLServer. The calling app is not asking for the results in any order. – ravikanth Jan 14 '11 at 11:29
  • actually it will be the order of whatever index is clustered, but that is usually the primary key – BlackICE Jan 14 '11 at 20:05
  • @David. Yes, for a single table query it would be the "natural order" of the rows in the heap - which is the clustered index order if there is one. If there is no clustered index the order would be undefined unless some where/group/order clause caused another index to be used or all the fields selected from the table are in one index (in which case it is likely be more efficient to scan down the index rather than scan the heap). Once there is a join present the options are wider so the likely behaviour is harder to predict. – David Spillett Jan 14 '11 at 21:05

You don't specify which database but in Oracle you could do this by:


You will need enough space in your TEMP tablespace to cope with the sorting. Then if you wish you can rename the tables ORIG_TABLE and RAND_TABLE to swap them over. I don't think it is possible to shuffle a table "in-place".

  • Thanks. I am using SQL Server 2008 R2. So, will this work with SQL Server too? – ravikanth Jan 14 '11 at 10:34
  • Yes the same principle will work, the syntax will be different tho' – Gaius Jan 14 '11 at 11:51
  • You could do this in place by adding a column, inserting into the same table with the new column set to a value and then deleting rows with the column not set and removing the column, but I don't think it would be worth the trouble. – Leigh Riffel Jan 14 '11 at 17:04
  • +1 for a correct answer to the question even though record insert order will not guarantee select order. – Leigh Riffel Jan 14 '11 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.