如何保留SqlCe DB中索引的连续性？ [英] How can I mantain the continuity of indexes in a SqlCe DB?

问题描述

大家好，



我有一个SqlCe 4.0数据库，我想保持数据库中索引的连续性，即使删除了一行。



我有一个具有自动增量标识的列。



IE

我有三行：

 ID |名称| 
 0 John 
 1 Jimmy 
 2 Natalie 

如果我想删除Jimmy，另一个行必须成为：

 ID |名称| 
 0 John 
 1 Natalie 

而不是



<前lang =text> ID |名称|
0 John
2 Natalie

因为它会是。



是否有与SqlCe 4.0兼容的查询来完成此结果？



谢谢!!



Jymmy097



PS：每当我必须这样做时，我手动删除Id，然后使用数据库设计器重新生成它。我想，这应该相当于ALTER TABLE，但是设计师告诉我ALTER TABLE与SqlCe不兼容...

解决方案

这不是一个有价值的目标。你永远不应该关心ID是什么，你应该从不改变它们。你真的试图完成什么？





如果你只是想要一个用户友好的订购为了显示你可以做如下的事情：

创建表y（RN INTEGER IDENTITY，ID INTEGER，Name NText）
 INSERT INTO y（ID，Name）SELECT ID，Name FROM x ORDER BY ID 
 SELECT * FROM y 
 DROP TABLE y 

在完整的Sql Server中我会使用ROW_NUMBER函数，但CE没有它。它也没有临时表。甚至不是SELECT / INTO。

Hi everybody,

I have a SqlCe 4.0 DB and I want to maintain the continuity of indexes in the DB even if a row is deleted.

I have a column with an auto-increment identity.

I.E.
I have three rows:

ID        | Name     |
0           John
1           Jimmy
2           Natalie

If I want to delete Jimmy, the other rows has to become:

ID        | Name     |
0           John
1           Natalie

and NOT

ID        | Name     |
0           John
2           Natalie

as it will be.

Is there a query compatible with SqlCe 4.0 which accomplishes this result?

Thanks!!

Jymmy097

PS: whenever I have to do this, I manually delete Id and, then, regen it by using a DB designer. This, I suppose, should be the equivalent of an ALTER TABLE, but the designer tells me that ALTER TABLE is not compatible with SqlCe...

解决方案

That is not a worthwhile goal. You should never care what the IDs are and you should never change them. What are you really trying to accomplish?


If you simply want to have a user-friendly ordering for display you could do something like the following:

CREATE TABLE y ( RN INTEGER IDENTITY , ID INTEGER , Name NText )
INSERT INTO y ( ID , Name ) SELECT ID , Name FROM x ORDER BY ID
SELECT * FROM y
DROP TABLE y

In full Sql Server I'd use the ROW_NUMBER function, but CE doesn't have it. Nor does it have temporary tables. Not even SELECT/INTO.

这篇关于如何保留SqlCe DB中索引的连续性？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！