Results 1 to 5 of 5
  1. #1
    bar tomas is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    5

    Are text primary keys less efficient than autonumbers?

    Hi,


    In one of my tables I have a column that is a kind of tracking number for a product. It is the primary key of the table composed of a string of numbers (date+ id product+ id person processing order, etc) that I have defined as a text, because it is too long to be declared as an integer.

    - Is a text primary key less efficient than an integer one?
    - Is it safer (for database integrity) to have a autonumber primary key?

    Thanks very much.

  2. #2
    Matrix's Avatar
    Matrix is offline Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Yes, integer primary key is always a better option. To me, it's the only option. Auto-number primary key is better than the tracking number in your project, IMO.

    The performance is better, also with auto-number primary key, there won't be any ambiguity no matter how your business logic changes, or how big you DB becomes
    in the future. I can see only benefits. Probably the only loss is a few extra lines of code.


  3. #3
    bar tomas is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    5
    Thanks a lot for your reply.
    When you say that performance is better with an Auto-number primary key, what exactly do you mean? Is it that indexing and querying is more efficient? Why is that?
    Thanks very much again for your help.

  4. #4
    Matrix's Avatar
    Matrix is offline Admin
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2005
    Posts
    517
    Integer must be faster than string for index, search and other database queries, thus the performance is better. Sorry, I don't know how to explain further.

  5. #5
    bar tomas is offline Novice
    Windows XP Access 2003
    Join Date
    Apr 2009
    Posts
    5
    Thanks a lot!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Primary Key change macro?
    By gracin in forum Access
    Replies: 0
    Last Post: 03-06-2009, 10:59 AM
  2. Select "autonumbers" not in sequence
    By jerry525 in forum Queries
    Replies: 5
    Last Post: 11-09-2008, 02:48 PM
  3. database structure:primary key debate
    By MUKUDU99 in forum Database Design
    Replies: 0
    Last Post: 08-19-2008, 12:20 AM
  4. reset primary key
    By emilylu3 in forum Access
    Replies: 1
    Last Post: 12-09-2005, 03:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums