What is the best method to generate a Custom Auto-Generated Sequence with SQL Server?
Here is my requirement:
My application is hosted on 10 independent servers which are not interconnected.
Each instance should generate a sequence number, and all these data will be merged to all databases using SQL Server's merge replication.
The sequence generated should be unique across all the servers.
To achieve this, I have created a function to prefix a server id to the sequence number and used this as a computed column. But when I add data, I am getting the following exception:
"Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
Here is my table and function. Kindly suggest a better method.
create table Customers ( CID int identity not null primary key, CustomerName varchar(50) ) ALTER function [dbo].[NextCustomerNumber]() returns bigint as begin declare @lastval bigint declare @serverId int declare @newval bigint set @lastval = (select max(CustomerNumber) from Customers) set @serverId= 1 -- Server ID pulls from some settings set @newval=CONVERT(bigint, (CAST(@serverId as varchar(2))+CONVERT( varchar(10),(RIGHT(@lastval,(LEN(@lastval)-LEN(@serverId)))+1)))) return @newval end Alter table Customers add CustomerNumber as dbo.NextCustomerNumber()