Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
4.40/5 (2 votes)
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#
Posted
Updated 26-Feb-12 10:05am
v2
Comments
Sergey Alexandrovich Kryukov 26-Feb-12 13:53pm    
I'm just curious: why would you thing ordering could help you, how?
--SA
TANicox 27-Feb-12 12:51pm    
It was only for maintain the index less diffuse.
But now I now that is bad for performance, thanks for comment

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
 
Share this answer
 
v4
Comments
Wonde Tadesse 26-Feb-12 18:32pm    
5+
Sergey Alexandrovich Kryukov 26-Feb-12 19:13pm    
Thank you, Wonde.
--SA
thatraja 28-Feb-12 13:36pm    
5!
Sergey Alexandrovich Kryukov 28-Feb-12 13:52pm    
Thank you, Raja.
--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.
 
Share this answer
 
Comments
Espen Harlinn 26-Feb-12 17:47pm    
Well answered Dave :)
Wonde Tadesse 26-Feb-12 18:31pm    
5+
Sergey Alexandrovich Kryukov 26-Feb-12 19:14pm    
All correct, a 5.
--SA
TANicox 27-Feb-12 12:49pm    
It was only for maintain the index less diffuse. Thanks for your answer
Dave Kreskowiak 27-Feb-12 16:57pm    
What does THAT mean?? What you were talking about won't affect the database indexs at all, nor will it speed up searching the database, so this still doesn't make any sense.
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
 
Share this answer
 
v2
Comments
Wonde Tadesse 26-Feb-12 18:32pm    
5+
Espen Harlinn 26-Feb-12 19:35pm    
Thank you, Wonde :)
Sergey Alexandrovich Kryukov 26-Feb-12 19:14pm    
Useful and educating information, my 5.
--SA
Espen Harlinn 26-Feb-12 19:35pm    
Thank you, Sergey :)
thatraja 28-Feb-12 13:36pm    
5!
i think you're referring to clustered indexes[^] - the data will be re-ordered according to the primary key ...

hugely inefficient for inserting, but screaming fast for sequential bulk reads
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900