创建重新排序索引算法 [英] Create algorithm for reorder index

问题描述

大家好

我在对SQL数据库中的主键进行重新排序时遇到问题.

每次插入或删除行时，我都希望对主键重新排序.

有人可以向我解释我该怎么做?

我在C#

Hello everyone

I have a problem reordering the primary key in a SQL Database.

I want to reorder the primary key each time you insert or delete rows.

Someone can explain to me how I can do that?

I''m using SQL 2008 and ASP.NET in C#

推荐答案

SQL中使用SQL 2008和ASP.NET，通常对关系数据库的访问不了解存储详细信息，尤其是数据的排序.存储在数据库系统中进行了内部优化，您不能以任何直接方式对其进行影响.

(间接地，可以更改某些索引选项来优化数据库，但是它与插入或删除操作无关(顺便说一句，您如何想象删除时的顺序更改?:-) );索引编制是一个完全不同的领域.索引用于优化性能，但会牺牲一些数据冗余，并且不会影响数据库操作的语义.

请参阅: http://en.wikipedia.org/wiki/Database_index [ http://www.sql-tutorial.com/sql-order-by-sql-tutorial / [^ ]，
http://www.w3schools.com/sql/sql_orderby.asp [ http://msdn.microsoft.com/en-us/library/ms188385％28v = sql.110％29.aspx [ ^ ].

—SA

SQL and generally the access to relationship database is agnostic to the storage detail, and, in particular, to the ordering of data. The storage is optimized internally in the database system, you cannot affect it in any direct way.

(Indirectly, one can change some indexing options to optimize a database, but it has nothing to do with insert or delete operations (by the way, how did you imagine change in ordering upon delete? :-)); indexing is a totally different area. Indexing is used for optimization of performance at the expense of some data redundancy and does not effect semantics of database operation.

Please see: http://en.wikipedia.org/wiki/Database_index[^].)

You can only define the ordering of the query results by using SQL ORDER BY clause, please see:
http://www.sql-tutorial.com/sql-order-by-sql-tutorial/[^],
http://www.w3schools.com/sql/sql_orderby.asp[^],
http://msdn.microsoft.com/en-us/library/ms188385%28v=sql.110%29.aspx[^].

—SA

为什么?由于数据库不在乎表中记录的顺序，因此没有意义.顺序由应用程序而不是数据库定义.

如果您试图将键值重新分配给表中的所有记录，那么这并不是对任何事情的解决方案，因此强烈建议您不要这样做，并且根本不能扩展.这是一个非常慢的操作，并且表中的记录越多，数据库的性能就越差，因为它必须在每次插入或删除操作时重写整个表.随着数据库的增长，这只会导致严重的性能下降.

您还遇到了一个问题，您必须跟踪每个单个更改，并且在任何相关表中，必须更新外键值以匹配新的主键值以保持记录相关.

换句话说，从没有任何理由对表中的键值进行重新排序.您要做的唯一一次操作就是一次数据库转换，在该转换中，您将数据从一个数据库迁移到另一个数据库，并且两者之间的模式有很大的不同.

Why?? Since databases don''t care about the ordering of records in the table, there''s no point to it. Order is defined by the application, not the database.

If you''re trying to reassign key values to all records in a table, that is not a solution to anything, is highly discouraged and does not scale at all. This is a very slow operation and the more records you have in your table, the worse your database performs as it has to rewrite the entire table on every insert or delete operation. That will only lead to MASSIVELY bad performance as the database grows.

You also run into a problem that you have to track every single change, and in any related table, you must update the foreign key value to match the new primary key value to keep the records related.

In other words, there is NEVER any reason to reorder key values in tables. The SINGLE time you would do this at all would be a database conversion where you migrate data from one database to another and the schema is vastly different between the two.

我想每次插入或删除行时，都要对主键重新排序.
如果这是必需的，则应返回并进行澄清.这很奇怪，我完全同意戴夫斯的回答.

请仔细阅读以下Wikipedia文章:
第一范式 [第二范式 [第三范式 [

I want to reorder the primary key each time you insert or delete rows.
If this is a requirement, you should go back and clarify it. It''s pretty strange and I totally agree with Daves answer.

Read the following Wikipedia articles carefully:
First normal form[^]
Second normal form[^]
Third normal form[^]

Best regards
Espen Harlinn

这篇关于创建重新排序索引算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！