Returning random rows in a result set

March 1, 2010

Using the NEWID() function to return random rows

The NEWID function will return a GUID value, that is statistically guaranteed to be unique e.g.

If you repeatedly call this function it will return a unique GUID. You can utilise this feature to give each row a unique random value. I’ve encountered a number of occaisions where it is desiranle to return from a resultset, a random number of rows. In the past I would achieve this by returning the result set to some application code that would randomise the results and select at most N items.

The example below shows how we can return 5 random rows from the Cutsomers table (Northwind database).

Selecting 5 Random rows

Each time the SQL snippet is run, 5 random customers are returned from the table.

This is a very neat and efficient.


Object reference not set to an instance of an object. (SQLEditors)

March 24, 2007

SQL Server 2005, SQL Server Management Studio (SSMS)

When attempting to modify a table in the SSMS (right-click on the table, click “Modify table” option) I receved the error, “Object reference not set to an instance of an object. (SQLEditors)” .

This error appears to be a problem with the registry installation for the WorkStation/Client Components. I managed to repair this by removing and then reinstalling the ‘WorkStation Components’ via the Control Pannel | Add, Remove Programs.

I suspect, but cannot be sure, that this problem may have arisen due to a install of SQL Server Express after I had already installed SQL Server 2005.

The steps I followed are as follows;

You will need the SQL Server 2005 install disk to remove/install components. Also, ensure you close Visual Studio and SQL Server Management Studio if they are open.

  1. Control Panel | Add, Remove Programs.
  2. Select SQL Server 2005, Click the Change button.
  3. On the Component Selection dialog, select ‘Workstation Components’.
  4. Click the next button on the Wizard, until you come to the Change or Remove Instance dialog.
  5. Select ‘Changed Installed Components’.
  6. On the Client Components option, right-click and select ‘Entire feature will be unavailble’.
  7. Click the Next button and then click the Install Button.
  8. The components will now be removed, this will take some time.
  9. After the components have been removed, Repeat Steps 1 to 3 and click the next button on the Server Installation wizard to start the System Configuration Check dialog.
  10. You may have to reboot your machine, inspect the Actions for a Pending Reboot Required Message. Reboot if required.
  11. Click the next button on the Wizard, until you come to the Change or Remove Instance dialog.
  12. Select ‘Changed Installed Components’.
  13. On the Client Components option, select ‘Entire feature will be installed on local hard drive’.
  14. Click the Next button and then click the Install Button.
  15. The Workstation Components will now be installed. This may take some time.
  16. Installation should now be complete.