流水好生成

--建立函數
Create Function F_GetBookID()
Returns Varchar(10)
As
Begin
Declare @S Varchar(10)
Select @S= 'BK'+Right(100000000+IsNull(Right(Max(BookID),8),0)+1,8) From BOOK
Return @S
End
GO
--建立測試環境
CREATE TABLE BOOK (
BookID  varchar(10) NOT NULL Default dbo.F_GetBookID(),
BookName           varchar(20) NOT NULL,
Constraint PK_BOOK_ID Primary key(BookID)
)
GO
--測試
Insert BOOK(BookName) Select  'aa'
Insert BOOK(BookName) Select 'bb'
Insert BOOK(BookName) Select 'cc'

Select * From BOOK
GO
--刪除測試環境
Drop Table BOOK
Drop Function F_GetBookID
--結果
/*
BookIDBookName
BK00000001aa
BK00000002bb
BK00000003cc
*/