create table tb(主键 int identity(1,1), col varchar(32))insert tb select 'asdf' union all select 'zxcv'select * from tbdrop table tb/* 主键 col ----------- -------------------------------- 1 asdf 2 zxcv(2 row(s) affected) */
建立主键时 设为自增量 create table 表(主键 int identity(1,1), column varchar(10))
alter table t add id int identity(1,1)
create function dbo.autoid() returns int as begin declare @id int select @id=max(id) from tb return isnull(@id,0)+1 end gocreate table tb(id int default dbo.autoid(),col int)insert into tb(col) values(10) insert into tb(col) values(20)自做自增列.
表结构不能改的话,只能用instead of insert触发器实现
自动加一主键create table T(ID int identity primary key ,Name nvarchar(20))
CREATE FUNCTION f_NextBH() RETURNS char(8) AS BEGIN RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK)) END GO--在表中应用函数 CREATE TABLE tb( BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(), col int)--插入资料 BEGIN TRAN INSERT tb(col) VALUES(1) INSERT tb(col) VALUES(2) INSERT tb(col) VALUES(3) DELETE tb WHERE col=3 INSERT tb(col) VALUES(4) INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14) COMMIT TRAN--显示结果 SELECT * FROM tb /*--结果 BH col ---------------- ----------- BH000001 1 BH000002 2 BH000003 4 BH000004 14 --*/
union all select 'zxcv'select * from tbdrop table tb/*
主键 col
----------- --------------------------------
1 asdf
2 zxcv(2 row(s) affected)
*/
create table 表(主键 int identity(1,1), column varchar(10))
alter table t
add id int identity(1,1)
returns int
as
begin
declare @id int
select @id=max(id) from tb
return isnull(@id,0)+1
end
gocreate table tb(id int default dbo.autoid(),col int)insert into tb(col) values(10)
insert into tb(col) values(20)自做自增列.
CREATE FUNCTION f_NextBH()
RETURNS char(8)
AS
BEGIN
RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))
END
GO--在表中应用函数
CREATE TABLE tb(
BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),
col int)--插入资料
BEGIN TRAN
INSERT tb(col) VALUES(1)
INSERT tb(col) VALUES(2)
INSERT tb(col) VALUES(3)
DELETE tb WHERE col=3
INSERT tb(col) VALUES(4)
INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)
COMMIT TRAN--显示结果
SELECT * FROM tb
/*--结果
BH col
---------------- -----------
BH000001 1
BH000002 2
BH000003 4
BH000004 14
--*/