创建自动生成ID的最佳做法是什么 [英] what is the best practice for creating auto generate IDs

问题描述

我必须在单表中维护我公司的购买详情，有三个购买部门参与购买并且他们在那里维护拥有'采购订单SlNo'....



因此我无法使用自动增量编号，我应该使用类似



X-001

X-002

...全部属于第一部门



Y-001

Y-002

......全部属于第二部门



Z-001

z-002

......全部属于第三部门



可以任意请帮助我并建议最佳做法？

（使用C＃）

Hi,

I have to maintain the purchase details for my company in single table, there are three Purchase dept that are involve in purchase and they are maintaining there own 'Purchase order SlNo'....

hence i cant use Auto increment number and I should have to use something like

X-001
X-002
...all belongs to only first dept

Y-001
Y-002
...all belongs to only second dept

Z-001
z-002
...all belongs to only third dept

Can any One please help me out and suggest the best practice?
(Using C#)

推荐答案

没什么大不了的。基于前缀，您只需增加后缀的值。

使用SQL Server自定义自动生成的序列 [ ^ ]

Not a big deal. Based on the prefix, you just increment value of suffix.
Custom Auto-Generated Sequences with SQL Server[^]

-- 

First Create The Tables :


Generate Student Ids :

create table  CSE
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'CSE'+'000'+convert(varchar,ID)
when 2 then 'CSE'+'000'+convert(varchar,ID)
else 'CSE'+convert(varchar,ID)
end
)

--insert into CSE default values

create table  ECE
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'ECE'+'000'+convert(varchar,ID)
when 2 then 'ECE'+'000'+convert(varchar,ID)
else 'ECE'+convert(varchar,ID)
end
)

--insert into ECE default values

create table  EEE
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'EEE'+'000'+convert(varchar,ID)
when 2 then 'EEE'+'000'+convert(varchar,ID)
else 'EEE'+convert(varchar,ID)
end
)

--insert into EEE default values

create table  IT
(
Id int identity(1,1),
--BranchName varchar(10),
Pid as case  len(Id) when 1 then 'IT'+'000'+convert(varchar,ID)
when 2 then 'IT'+'000'+convert(varchar,ID)
else 'IT'+convert(varchar,ID)
end
)

--insert into IT values('IT')

create table  MECH
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'MECH'+'000'+convert(varchar,ID)
when 2 then 'MECH'+'000'+convert(varchar,ID)
else 'MECH'+convert(varchar,ID)
end
)

--insert into MECH default values

create table  CIVIL
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'CIVIL'+'000'+convert(varchar,ID)
when 2 then 'CIVIL'+'000'+convert(varchar,ID)
else 'CIVIL'+convert(varchar,ID)
end
)

--insert into CIVIL default values

create table  PROD
(
Id int identity(1,1),
Pid as case  len(Id) when 1 then 'PROD'+'000'+convert(varchar,ID)
when 2 then 'PROD'+'000'+convert(varchar,ID)
else 'PROD'+convert(varchar,ID)
end
)


create table Temp123(StudentId varchar(10))

------------------------------------------------------------------

你可以使用具有用户定义或自定义值的计算SQL列。请参阅以下文章。



指定计算列表 [ ^ ]

SQL Server客户咨询团队 [ ^ ]

如何定义复杂的ComputedColumn in SQL Server？ [ ^ ]



希望它有所帮助。

Hi,
you can make use of Computed SQL Columns with User Defined or Custom values. refer below articles.

Specify Computed Columns in a Table[^]
SQL Server Customer Advisory Team[^]
How to define a "complicated" ComputedColumn in SQL Server?[^]

hope it helps.

这篇关于创建自动生成ID的最佳做法是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！