Тия автоинцремент (или идентити) колони не гарантират че няма да има дупки, при това големи дупки.

Иначе техниките са както ти казваш праиш си брояч и го инкрементваш. Ето няколко примера (за сайбейс/мс сял сержер) с коментар. Да речем че таблицата ти се казва invoice i ключа, който искаш да генериарш се казва inv_no.

1. Metod макс+1

declare @new_inv_no int
select @new_inv_no=max(inv_no) from invoice
insert into invoice (inv_no,....) values (@new_inv_no, ...)

Prosto, i лесно, но има проблем: може да се случи че когато юзер А а направил селекта, но още не е направил инсерта, юзер Б прави селекта и получава същия номер.
За да се избегне това трябва да имаш юник индекс в/у инж_но и да се грижиш когато инсерта бърне дупликейт кей да си вземеш нов номер (да повториш селекта)

2. Метод с отделнна таблица за ключове
правиш си отделнна таблица за генериране на ключове:
create table key_table (table_name char(30), id int)
insert into key_table values ("invoice", 0)
И след тая еднократна подготовка:
declare @new_inv_no int
begin tran get_key
update key_table set id=id+1 where table name ="invoice"
select @new_inv_no=id from key_table where table name ="invoice"
commit tran get_key
insert into invoice (inv_no,....) values (@new_inv_no, ...)

Prosto i ефикасно. Както се сещаш кий_табле може да се използва и за генерирането на ключове и на други таблици, стига тия ключове да са инт.
Обаче дупките не се избягват -- ако инсерта в инвойс се усере по каквито и да било причини и се получава дупка в номерата.
Затова ...

3. друг Метод с отделнна таблица за ключове (избягване на дупките). Идеята е като по-горе, само че генерирането на ключа и инсерта в инвойс са в същата транзакция, така че се гарантира последователни ключове:
declare @new_inv_no int
begin tran insert_row
update key_table set id=id+1 where table name ="invoice"
select @new_inv_no=id from key_table where table name ="invoice"
insert into invoice (inv_no,....) values (@new_inv_no, ...)
commit tran insert_row
Кофтито е че таблицата с ключовете е заключена за по-дълго време (докато трае инсерта в инвойс), което не е гот за перформанса ако имаш милиони транзакции.

И последно тоя чалъм с отделната таблица може да се изпрограмира, така че ключовете, които са генерирани, но не са използвани да се използват от следващите инсерти т.е. пак се гарантират последователни ключове без да се заключва таблицата за дълго време.

В заключение -- ползвай най-простия метод, който върши работа в конкр. случай.