Tsql, returning rows with identical column values

Given an example table 'Users', which has an int column named 'UserID' (and some arbitrary number of oth开发者_JAVA技巧er columns), what is the best way to select all rows from which UserID appears more than once?

So far I've come up with

select * from Users where UserID in 
(select UserID from Users group by UserID having COUNT(UserID) > 1)

This seems like quite an innefficient way to do this though, is there a better way?

In SQL Server 2005+ you could use this approach:

;WITH UsersNumbered AS (
  SELECT
    UserID,
    rownum = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UserID)
  FROM Users
)
SELECT u.*
FROM Users u
  INNER JOIN UsersNumbered n ON u.UserID = n.UserID AND n.rownum = 2

Provided there exists a non-clustered index on UserID, this yields a slightly worse execution plan than your approach. To make it better (actually, same as yours), you'll need to use... a subquery, however counter-intuitive it may seem:

;WITH UsersNumbered AS (
  SELECT
    UserID,
    rownum = ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY UserID)
  FROM Users
)
SELECT u.*
FROM Users u
WHERE EXISTS (
  SELECT *
  FROM UsersNumbered n
  WHERE u.UserID = n.UserID AND n.rownum = 2
);

In case of a clustered index on UserID all three solutions give the same plan.

This would do the same thing but evaluate the performance and it would likely be faster/more efficient. Of course there should be an index on this UserID column.

select u.*
from Users u
join (select UserID,count(UserID) as CUserID from Users group by UserID) u1 on u1.UserID = u.UserID
where CUserID > 1