Since you are using sql server 2012 have you considered using the new feature for creating a sequence object? If in addition the application requires knowing whether this is the nth record, this of itself sounds more like something calculated/computed as needed and not something to store.

I am aware that maybe in legacy databases there had been the practice of artificially restricting a sequence by using a varchar, like in our database using suffix fields to arbitrarily limit items to 999 for a given module and type code to represent different entities ...for ex. MasterName( agency, incident_nr, suffix1, suffix2, module, type, <data fields...> ). Then you'd need to use a stored procedure in order to check if the generated sequence value already exists and remains in range and if not to reset the sequence and continue generating until a gap exists. Again, the select would need to compute the seqno to the application having ordered the items by a record's created datetime (in high volume scenarios or when you have bulk insert job, without a surrogate primary key using identity, a datetime may not be sufficient to order deterministically).

Now if your seqid is an int (number) then life's good, use the sequence object and let the application use a select or programing code to compute the item's order. If a varchar and you have no independent surrogate primary key to order by and you do not have an independent field retaining the timestamp for when the record was created, you need to make decisions for whats the best compromise for your situation.

Hope this helps.