建立函數(shù)
Create function CreateProduct_ID()
Returns Varchar(16)
As
Begin
Declare @S Varchar(100)
declare @id varchar(20)
declare @date varchar(8)
select @date=convert(varchar,getdate(),112)
declare @count int
select @count=count(*) from test where substring(id,1,8)=@date
if @count=0
begin
select @S = @date+'0001'
end
else
begin
select @id=cast(cast(substring(max(id),9,4) as int)+1 as varchar) from test where substring(id,1,8)=@date
select @S = @date+right('000'+cast(cast(@id as int) as varchar),4)
end
Return @S
End
Go
調(diào)用方法
CREATE TABLE [dbo].[test](
[id] [varchar](20) NOT NULL Default dbo.CreateProduct_ID(),
[name] [varchar](10) NULL
) ON [PRIMARY]
設(shè)置默認(rèn)值改進(jìn)版
Create function CreateProduct_ID2()
Returns Varchar(30)
As
Begin
Declare @S Varchar(100)
declare @id varchar(20)
set @id = (CONVERT([bigint],replace(replace(replace(replace(CONVERT([nvarchar](100),CONVERT([nvarchar](100),getdate(),(121)),0),':',''),'-',''),' ',''),'.',''),0))
select @S = 'HJ'+ @id
Return @S
End
Go
刪除函數(shù)
Drop function CreateProduct_ID
主鍵自增
1.新建一數(shù)據(jù)表，里面有字段id，將id設(shè)為為主鍵
create table tb(id int,constraint pkid primary key (id))
create table tb(id int primary key )
2.新建一數(shù)據(jù)表，里面有字段id富弦，將id設(shè)為主鍵且自動(dòng)編號
create table tb(id int identity(1,1),constraint pkid primary key (id))
create table tb(id int identity(1,1) primary key )
3.已經(jīng)建好一數(shù)據(jù)表沟娱，里面有字段id氛驮，將id設(shè)為主鍵
alter table tb alter column id int not null
alter table tb add constraint pkid primary key (id)
4.刪除主鍵
Declare @Pk varChar(100);
Select @Pk=Name from sysobjects where Parent_Obj=OBJECT_ID('tb') and xtype='PK';
if @Pk is not null
exec('Alter table tb Drop '+ @Pk)