Interview Question - Find distinct values without using distinct

I have appeared for an interview @ several companies and have observed that few questions pops up every now and then. These questions aren't tricky but still have an ability to throw otherwise brilliant T-SQL scripters of the hook. Thus it is better to prepare for them in advance.

One such question is How can you find the distinct values from a column of a table, (big pause ....wait for it ..) without using DISTINCT ? 

Now, why would you want to do that when Microsoft has been generous and has given us DISTINCT clause to use in such situation. Well the answer is because you want that job so either answer it or ....

Given below are the ways that are @ top of my head right now

• Row_Number()
• Group BY
• Intersect
• Except
• Union

DECLARE @TAB TABLE
(
ID TINYINT
)

INSERT INTO @TAB
SELECT 1 AS ID UNION ALL
SELECT 3 UNION ALL
SELECT 45 UNION ALL
SELECT 76 UNION ALL
SELECT 28 UNION ALL
SELECT 45 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 1 UNION ALL
SELECT 3

SELECT * FROM @TAB


;WITH CTE AS
(
SELECT ID, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) ROWNUM
FROM @TAB
)
SELECT ID FROM CTE WHERE ROWNUM = 1

SELECT ID FROM @TAB
GROUP BY ID

SELECT ID FROM @TAB
INTERSECT
SELECT ID FROM @TAB

SELECT ID FROM @TAB
EXCEPT
SELECT 100 -- SOME VALUE THAT ISN'T PRESENT IN ID COLUMN

SELECT ID FROM @TAB
UNION
SELECT ID FROM @TAB