Mar 1

Written by: Joel Taylor
3/1/2007

AutoNumber is a powerful tool for creating keys.  First we will discuss the default settings and some of the default limitations. Then we will discuss the more advanced features and how they can expand the performance of the AutoNumber field.  You can refer to the picture below to see an illustration of the different autoNumber options.



Default Settings

Data Type:	Long Integer
New Values:	Increment (ex. 1,2,3,...)

Advantages

• Keep the default settings if you just need a quick way to track data and would like a primary key that is easy to remember. 
• Numbers appear in the order they were entered. 
  
• Easy and quick to setup.
  

Limitations

• If the database is being used as part of a website it would be easy to guess the primary key. 
  
• If this is passed as a query string or as a form post and attacker could send this information and possibly gain access to data if the application isn't protected appropriately.
  
• You can only have two of these fields per table.
• One field can be integer data type and one can be replication id data type.
  
• This makes sense if you think about it.  All AutoNumber fields in default mode would have the same value.

Changing The New Values
If the data type is Long Integer you don't need to keep the increment mode.  Instead use "Random".  This will place any long integer in the AutoNumber field.  Increment could be like (12300, 2030900,...). 

Advantages

• Guessing the primary key is moderately difficult. (The key is sort of unique.)
  
• Data Type is still easy to use because it is an integer.

Changing The Data Type
Instead of using an integer us a "Replication Id".  This field is actually a guid field.  This is the field I would use if the data is available online. 

Advantages

• Guessing the primary key is very difficult. (The key is very unique.)

Limitations

• Data Type is actually a text field and is over 30 characters long!  May make it hard to use.  Especially if you are trying to use it as an order number.  Could you image a customer trying to type in the number into a form.

Tags: