How to write a query which finds all circular references when a table references itself?
This calls for a recursive CTE:

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' &gt; ' + CAST(P.Id AS NVARCHAR(MAX))
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId &lt;&gt; P.Id AND C.ParentId &lt;&gt; C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId &lt;&gt; 0;


See it in action here: SQL Fiddle



Update:

Added distance to be able to exclude all self cycles (see ypercube's comment):

WITH FindRoot AS
(
    SELECT Id,ParentId, CAST(Id AS NVARCHAR(MAX)) Path, 0 Distance
    FROM dbo.MyTable

    UNION ALL

    SELECT C.Id, P.ParentId, C.Path + N' &gt; ' + CAST(P.Id AS NVARCHAR(MAX)), C.Distance + 1
    FROM dbo.MyTable P
    JOIN FindRoot C
    ON C.ParentId = P.Id AND P.ParentId &lt;&gt; P.Id AND C.ParentId &lt;&gt; C.Id
 )
SELECT *
FROM FindRoot R
WHERE R.Id = R.ParentId 
  AND R.ParentId &lt;&gt; 0
  AND R.Distance &gt; 0;


SQL Fiddle

Which one you should use depends on your requirement.