find duplicates

SELECT column,COUNT(*) c FROM table
GROUP BY column HAVING COUNT(*) > 1

results show the column value and the number of duplicates for that value

Dynamic ordering without dynamic sql

DECLARE @order int
SET @order = 1
SELECT * FROM users
ORDER BY 
        CASE WHEN @order = 1 THEN [name] END DESC,
        CASE WHEN @order = 2 THEN [name] END ASC,
        CASE WHEN @order = 3 THEN [email] END DESC,
        CASE WHEN @order = 4 THEN [email] END ASC

set the @order to 1-4 to get specific order by clause, setting it to anything else give results as if no order by clause supplied

remember dynamic SQL (creating SQL in a string and then executing) can not be effectively cached so this is a much better option

Randomly ordered results in TSQL

SELECT TOP 10 userID
FROM user
ORDER BY NEWID()

or

SELECT TOP 10
        NEWID() [egg],
        userID
FROM user
ORDER BY [egg]

or

SELECT TOP 10
        NEWID(),
        userID
FROM user
ORDER BY 1

only tried the above with TSQL