Una forma muy general de hacerlo sin importar que base de datos uses sería:
1. Tener una tabla desde la que obtengas el siguiente número a asignar.
2. Crear un trigger BEFORE INSERT para la tabla en la que usas el numero consecutivo. Dicho trigger es quien se encargaría de recuperar el siguiente número, asignarselo al campo que quieres, incrementar en uno el número consecutivo y verificar si ya llegó al límite, para entonces inicializarlo en 1.
No se SQL Server, pero la esencia de la idea sería algo asi:
Código SQL
[-]
CREATE TRIGGER establece_consecutivo FOR mi_tabla
BEFORE INSERT
AS
DECLARE VARIABLE c INTEGER;
BEGIN
SELECT siguiente
FROM tabla_contador
INTO :c;
NEW.numero_consecutovo = c;
c = c+1;
IF (c = 5000) THEN
c = 1;
UPDATE tabla_contador
SET siguiente = :c;
END