The method of SQL server data table field Custom Auto increment data format

Time:2020-2-18

In this paper, an example is given to illustrate the method of SQL server data table field self defining and auto increasing data format. To share with you for your reference, as follows:

--Modify the field companyid Custom Auto increment constraint in the data table sys company
ALTER TABLE [dbo].[SYS_Company]
Add Constraint DF_SYS_Company_CompanyId
DEFAULT ([dbo].[f_PrimaryCode_SYS_Company]()) FOR [CompanyId]
--Go
--Delete constraint
Alter table SYS_LogInfo Drop Constraint DF_SYS_Company_CompanyId
--Create a user-defined autoincrement constraint for the field companyid in the data table sys company (for example: cy00000001, length is bit, the first two bits are user-defined number, and the last bit is serial number)
Create function [dbo].[f_PrimaryCode_SYS_Company]()
Returns Char(10)
As
Begin
  RETURN(SELECT 'CY'+RIGHT(100000001+ISNULL(RIGHT(MAX(CompanyId),8),0),8) FROM SYS_Company WITH(XLOCK,PAGLOCK))
End

The effect is as shown in the figure:

I hope that this article will be helpful for you to design SQL Server database program.