Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Primary key Ascending vs Descending

In Sql Server, I have a table with an Identity primary key. Often I want the latest few new records, so I grab the Top n sorted by descending the primary key. Should I define the Primary Key index as Descending, or does it make no difference? i.e. if they are in Ascending order, then can sql just as efficiently work backwards?

like image 279
peterorum Avatar asked Mar 16 '09 04:03

peterorum


People also ask

Does order of primary key matter?

Does the order of columns in a PK index matter? Yes it does. By default, the primary key constraint is enforced in SQL Server by a unique clustered index. The clustered index defines the logical order of rows in the table.

Which column should be primary key?

Primary keys must contain unique values. A primary key column cannot have NULL values. A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.

What is the best choice for a primary key?

Integer (number) data types are the best choice for primary key, followed by fixed-length character data types. SQL Server processes number data type values faster than character data type values because it converts characters to ASCII equivalent values before processing, which is an extra step.

What does descending and ascending mean?

Descending can also be thought of as climbing down the stairs of numbers starting from the highest value. Moving down the slide is descending. The opposite of descending order is known as ascending order, in which the numbers are arranged from lower value to higher value.


1 Answers

From a purely querying standpoint, it makes no difference whether your key is descending or ascending if you want to pull N most recent or N oldest records:

The internal algorithms of SQL Server can navigate equally efficiently in both directions on a single-column index, regardless of the sequence in which the keys are stored. For example, specifying DESC on a single-column index does not make queries with an ORDER BY IndexKeyCol DESC clause run faster than if ASC was specified for the index.

http://msdn.microsoft.com/en-us/library/aa933132(SQL.80).aspx

However under just about any normal circumstance, you want your primary key to be ascending and ordinally sequential to prevent fragmentation. SQL Server is optimized for physically appending new records to the end of the database file. If it needs to insert each new record at the top and push everything down, it would probably result in nearly 100% fragmentation.

like image 155
Rex M Avatar answered Sep 28 '22 18:09

Rex M