Insert Does Not Increment Primary Key Correctly I need to figure out how to insert into this table without breaking the application that runs on it. When I run an insert statement outside the application, I select the max uid (which is the indexed primary key) and add one, then insert the data with that uid. 

The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.

Something does not make it down to the application though and it will not allow new inserts into the table through the application until I delete the rows I inserted through SQL. It gives an SQL error that the primary key customer_salesrep_uid is already taken. The application does not seem to know about the inserts I made outside of it and the increases to the uid field. There must be a counter somewhere that I need to update? The index should update by itself on insert.

Update 8/10 re comments:
Thank you all for your advice. I will try the profiler and also will try inserting in test with proper locks, with a lower uid than max, and with a much higher uid than max and see if I can glean any further clues from that.

Table Structure:

    Column_name Type    Computed    Length  Prec    Scale   Nullable    TrimTrailingBlanks  FixedLenNullInSource    Collation
    customer_salesrep_uid   int no  4   10      0       no  (n/a)   (n/a)   NULL
    company_id  varchar no  8                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    customer_id decimal no  9   19      0       no  (n/a)   (n/a)   NULL
    salesrep_id varchar no  16                  no  yes no  SQL_Latin1_General_CP1_CI_AS
    commission_percentage   decimal no  9   19      4       no  (n/a)   (n/a)   NULL
    primary_salesrep_flag   char    no  1                   no  yes no  SQL_Latin1_General_CP1_CI_AS
    row_status_flag int no  4   10      0       no  (n/a)   (n/a)   NULL
    date_created    datetime    no  8                   no  (n/a)   (n/a)   NULL
    created_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS
    date_last_modified  datetime    no  8                   no  (n/a)   (n/a)   NULL
    last_maintained_by  varchar no  255                 no  yes no  SQL_Latin1_General_CP1_CI_AS


No Identity columns

Indexes:

    index_name  index_description   index_keys
    ak_customer_salesrep    nonclustered, unique, unique key located on PRIMARY company_id, customer_id, salesrep_id
    pk_customer_salesrep    clustered, unique, primary key located on PRIMARY   customer_salesrep_uid
The application is our ERP and was purchased from a vendor. Their support does not extend to SQL help so I have no way of seeing how it is doing the insert. There are no sequences in the database at all either.


You could run a trace using SQL Profiler to catch the exact statement that your application is executing. 

Also, a slight alteration to Scott's suggestion, I wouldn't use a TABLOCK, instead, something like:

BEGIN TRANSACTION
SELECT @customer_salesrep_uid = max(customer_salesrep_uid) 
    FROM YourTable WITH (XLOCK, READPAST)...


This should reduce the risk of locks on the table.