Question: Some single numbers in actual business processing require automatic growth, but cannot be used instead of EG: P031106001 - the first bit P represents the purchase order, 031106 represents the date, the last three are the water number.
/ * 1: Create a test for the table structure * / create Table Tablename (Pono Char (10), B INT) GO / * 2: Create a view to get the current date, prepare the following self-character function * / create view VGetdateas Select getdate () as TodayGO / * 3: Get a single number with a custom function (due to getDate () due to custom functions, you want to use the above view) * / create function getDh () Returns Char (10) asbegin declare @ DH1 Char (10), @ DH2 Char (10) SELECT @ DH1 = Max (PONO) from Tablename Set @ DH1 = ISNULL (@ DH1, 'P000000000') SELECT @ DH2 = Left (@ DH1 , 1) Right (VARCHAR (8), TODAY, 112), 6) '001' from Vgetdate IF @ DH1> = @ DH2 Begin Set @ DH2 = Left (@ DH1, 7) Right ('000 ' cast (CAST (Right (@ DH1, 3) AS INT 1 AS VARCHAR), 3) End Return (@ DH2) endgo / * 4: Fill in DBO.GETDH () * / alter in the default value Table Tablename_1 Default (dbo.getdh ()) for Pono - / * 5: Test: * / Insert Tablename (b) Values (1) Insert TableName (b) Values (2) Select * from Tablename - Test Results PONO B ---------- ----------- P031115001 1P031115002 2 Summary: This method uses some small skills 1: use the default value to achieve single number from Single number 2: use Custom functions to get the default value of the field 3: Due to the non-deterministic functions like getDate () in the custom function, use the view to get the current date
