Tracking records in SQL

Tracking Records in SQL

There are times that you want to track the exact order that records are added to a table.  The best way to do this, is with a line number field, that automatically increments as each record is added.  This is accomplished using the IDENTITY() clause for an integer field when creating the table.

The IDENTITY() clause takes two arguments:  The starting number, and the increment number.  If you want to start at one, and increment by one for each record, then you would use IDENTITY(1,1).  To start at 1000 and increment by 10, it would be IDENTITY(1000,10).

What you will get, is that when you insert a record into the table, if you do not specify the value of the field with the identity on it, it will get automatically assigned.  The number that it will get, is the highest value that has previously been used, plus the increment amount.  Note, that if you have deleted the last record added, the identity field will still go up to the next increment amount.  So, if you add three records, then delete the third one, and add another one, the last record will go to the next increment amount from the original third record.  In other words, if you are starting at one, and incrementing by one, you would get 1, 2, 4, as your numbers.

For a very simple example, lets define a table for short notes:

  CREATE TABLE NOTES_TABLE (NOTE_LINE INT NOT NULL IDENTITY(1,1), NOTES varchar(50))

The field NOTE_LINE will automatically increment from one, by one, as we add notes.  We then issue the commands:

  INSERT INTO NOTES_TABLE (NOTES) VALUES (‘Note 1’)
  INSERT INTO NOTES_TABLE (NOTES) VALUES (‘Note 2’)
  INSERT INTO NOTES_TABLE (NOTES) VALUES (‘Note 3’)

If the table will then contain:

  NOTE_LINE  NOTES 
  ———            —————- 
  1                     Note 1 
  2                     Note 2 
  4                     Note 3  

Using a different base and increment, such as IDENTITY(1000,10), yields the following:

  NOTE_LINE  NOTES 
  ———            ————— 
  1000              Note 1 
  1010              Note 2 
  1030              Note 3

That is all that there is to implementing a simple record counter in MS-SQL.

Dave.

Leave a Reply