建立函数

     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

调用方法

 CREATE TABLE [dbo].[test](
        [id] [varchar](20) NOT NULL  Default  dbo.CreateProduct_ID(),
        [name] [varchar](10) NULL
) ON [PRIMARY]

设置默认值改进版

  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

删除函数

Drop function CreateProduct_ID

主键自增

1.新建一数据表，里面有字段id，将id设为为主键

 create table tb(id int,constraint pkid primary key (id))
 create table tb(id int primary key )

2.新建一数据表，里面有字段id，将id设为主键且自动编号

 create table tb(id int identity(1,1),constraint pkid primary key (id))
 create table tb(id int identity(1,1) primary key )

3.已经建好一数据表，里面有字段id，将id设为主键

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)