T-SQL

How to get random rows from a table?

Did you ever think how to get random rows from a table?Let this article back and try to find a solution on your own. It is not so simple task, isn’t it?Almost sure you thought to JOIN the table with itself, and you might found a reasonable solution.One way to do this would be to:

  1. create a column in the table, put NewId() as its default value (i.e. creates a unique value of type uniqueidentifier.)
  2. sort the table by this column,
  3. then take first x rows, using TOP clause ORDER BY sorts the rows in the table. Combined with the TOP keyword, it ensures that only the first n items in the resultset, as sorted according to the ORDER BY clause, appears in the results. Since you don't have a random column in your table, you might use directly NewId() function, which generates a random GUID, and sorted by that.

SELECT TOP x * FROM MyTable ORDER BY NewId()

How to show all connected users in SQL Server

The simplest way to list all users and processes is to use the system stored procedure sp_who. To list all active users:

sp_who 'active'

To list details about a particular user:

sp_who 'login-name'

How to check if SQL Server Agent is running

Here it is the T-SQL code snippet:

SELECT program_name, login_time, loginame, cmd, hostname
FROM master.dbo.sysprocesses
WHERE program_name = N'SQLAgent - Generic Refresher'

Pages

Subscribe to RSS - T-SQL