建立函數(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)編號(hào)

 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)