CODEWITHSUNDEEP
sqlsql-serverdatabase
Leifb
Leifb

Reputation: 380

Database: Should ids be sequential?

I want to use an id as a primary key for my table. In each record, I am also storing an id from an other source, but these ids are in no way sequential.

Should I add an (auto-incremented) column with a "new" id? It is very important that queries by the id are as fast as possible.

Some info:

Thanks!

Upvotes: 1

Views: 2004

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Under most circumstances, an index on the existing id should be sufficient. You can make it slightly faster by declaring it as a primary key.

From what you describe a new id is not necessary for performance. If you do add one, the table will be slightly larger, which has a (very small) negative effect on performance.

If the existing id is not numeric (or not an integer), then there might be a small gain from using a more efficient type for the index. But, your best bet is to make the existing id a primary key (although this might affect load performance).

Note: I usually prefer synthetic primary keys, so this answer is very specific to your question.

Upvotes: 2

aj-
aj-

Reputation: 7

If you are after speed I would join the two IDs together (either from the application or stored proc) and then put them in one column

Upvotes: -2

ErikL
ErikL

Reputation: 2041

As long as you are sure the supplied id's are unique, there's no need to create another (surrogate) id to use as primary key.

Upvotes: 5

Related Questions