How can I re-index a column using SQL Server Compact Edition?
Since you can't generate the ID artificially with a windowing function, you will need to add a step and create it as an IDENTITY field in another table.

Something like this

            --temp table to store all the new data
    CREATE TABLE #Hierarchy
    (
          HId INT IDENTITY  
        , ParentID INT
        , ListIndex INT
    )

    --distinct parentids in hierarchial model
    SELECT DISTINCT ParentID
    INTO #Parents
    FROM TemplateItem 

    --looping variables
    DECLARE 
          @loopCnt INT
        , @curParentId INT

    --set looping variables (not a fan of cursors)
    SELECT @loopCnt=COUNT(ParentID), @curParentId=-1
    FROM #Parents

    --loop through parentids and reset the ListIndex
    WHILE @loopCnt &gt; 0
    BEGIN
        --get the next ParentID
        SELECT @curParentId=MIN(ParentID)
        FROM #Parents
        WHERE ParentID &gt; @curParentId

        --temp table to store the refreshed ListIndexes by ID
        CREATE TABLE #NewIndex
        (
              NewIndex INT IDENTITY
            , Id INT
        )

        INSERT #NewIndex
            (Id)
        SELECT Id
        FROM TemplateItem
        WHERE ParentID = @curParentId
        ORDER BY ListIndex

        INSERT #Hierarchy
            (ParentID, ListIndex)
        SELECT  t.Id,
                t.ParentId,
                Sub.NewIndex - 1 AS ListIndex --&gt;Reset to 0 here

        FROM    TemplateItem t
        JOIN    #NewIndex Sub ON Sub.Id = t.Id

        --CleanUp and continue/stop loop
        DROP TABLE #NewIndex
        SET @loopCnt=@loopCnt-1
    END