Show Duplicate Records

These two queries run under different schools of thought and purpose but will help in finding duplicate records.

First, if you only want to see the records that are duplicates and the amount of times they appear in the table you could run this code:

SELECT
	memberid
	,COUNT(*) AS No_Of_Occurrences
FROM
	table
GROUP BY
	memberid HAVING COUNT(*)>1
ORDER BY
	memberid

Second, if you want to see all duplicate records along with their id or other information in the table you could run this code:

SELECT
	id, memberid
FROM
	table
WHERE
	memberid
		IN
			(
				SELECT
					memberid
				FROM
					table
				GROUP BY
					memberid HAVING COUNT(*)>1
			)
ORDER BY
	memberid, id

Third

SELECT *
FROM Members HLM1
INNER JOIN (
	SELECT MemberNo
		,COUNT(*) AS dupeCount
	FROM Members
	GROUP BY MemberNo
	HAVING COUNT(*) > 1
	) HLM2 ON HLM1.MemberNo = HLM2.MemberNo
ORDER BY HLM1.MemberNo

Fourth

SELECT [id]
	,[Source]
	,[MemberNo]
	,[FirstName]
	,[MiddleName]
	,[LastName]
	,[Suffix]
	,[Email_1]
	,[Email_2]
	,[Email_3]
	,[ActiveMember]
	,ROW_Number() OVER (
		PARTITION BY [MemberNo]
		,[FirstName]
		,[MiddleName]
		,[LastName]
		,[Suffix]
		,[Email_1]
		,[Email_2] ORDER BY [MemberNo]
		) RowNumber
FROM [Members]
ORDER BY [MemberNo]