How can I re-index a column using SQL Server Compact Edition?

Question

We have a table TemplateItem that has a column ListIndex that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical.

The query requirement is that the column ListIndex has to start from 0 and be sequential for each parent / child(ren) relationship.

The query I came up with is as follows:

SELECT Id,
 ParentId,
 Sub.NewIndex AS ListIndex
FROM TemplateItem
JOIN ( 
 SELECT Id, 
 ROW_NUMBER() OVER 
 ( 
 PARTITION BY ParentId ORDER BY ListIndex 
 ) 
 - 1 AS NewIndex 
 FROM TemplateItem
 ) 
 AS Sub ON TemplateItem.Id = Sub.Id

^{( unnecessary bits of query removed for readability )}

This works perfectly fine in SQL Server 2008 R2.

However, I now need to use this same functionality in SQL Server Compact Edition 4.0 but, as you may already know, it will fail.

It does so because there is no ROW_NUMBER() in SQL Server Compact Edition 4.0.

How can I accomplish this same functionality?

• Michael Gardner: Sybase ASE is also missing the sequence function, so we approximate it using identity - check my answer here for a possible work around: stackoverflow.com/questions/24616967/sybase-sequence/…

Answer 1

SQL Server Compact does not support CTE or temp tables. Why not simply implement the solution in code, since the SQL Compact database runs in process anyway?

• Code Maverick: That's actually what's currently being done. I just wanted to see if there was a way to accomplish this on the SQL side. It sounds like it would be way more trouble than it's worth. Thanks.

Answer 2

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 > 0
 BEGIN
 --get the next ParentID
 SELECT @curParentId=MIN(ParentID)
 FROM #Parents
 WHERE ParentID > @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 -->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

• Steve Mangiameli: Sorry, forgot the -1 to reset the NewIndex to 0. Should be identical now to what you had.
• Code Maverick: So does your answer assume that this is to be consumed inside of a cursor or the like to get the hierarchical requirement to work?
• Code Maverick: Otherwise, hierarchical data will not work. I need each relationship to have its own set of 0 to N indexes.
• Steve Mangiameli: Gotcha, missed the hiarchial model. Yeah, you'd need to grab the parent IDs first and then loop through them for each of the children. Windowing functions would definitely make this much easier. I can get that scripted later tonight or tomorrow if you need an example. Sounds like you have the idea though.
• Steve Mangiameli: I think this is what you are looking for. Quite a bit more code, but it should approximate what you would need to do. I have to admit, this is untested since I don't have a dataset to test with.