Solved

# RE : Sequential number generation

Posted on 2012-08-29
159 Views
Hi,

I am looking for SQL query which will generate the sequential number in below format

YYYYMMDDXXXXX like 2012082900001 -> XXXXX will be an sequential incremented number with no duplicates as per YYYYMMDD and no skipping of sequential number (for audit purpose) in table

What is the best approach to do so keeping in mind the performance and speed
0
Question by:vikalgupta

LVL 18

Expert Comment

SELECT RIGHT(MAX(MyField),5)+1
FROM Table
WHERE MyField LIKE CONVERT(char(8),@MyDateVar,112)+'%'

@MyDateVar may also be a date field.
0

LVL 18

Expert Comment

To ensure you always get 5 digits:
SELECT RIGHT('00000'+CAST(RIGHT(MAX(MyField),5)+1 AS varchar(6)),5)
FROM Table
WHERE MyField LIKE CONVERT(char(8),@MyDateVar,112)+'%'
0

LVL 12

Expert Comment

The following query gives you what you are looking for on daily basis:
``````SELECT CONVERT (CHAR(8) , GETDATE(), 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX([YourSequentialNumberColumn]),'0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) FROM [YourTable]
WHERE SUBSTRING([YourSequentialNumberColumn], 1, 8) = CONVERT (CHAR(8) , GETDATE(), 112)
``````
0

Author Comment

Hi Purfard,

How could i handle if 2 users simultaneously hit my table to insert order number, how can i protect users to insert same duplicate value in table.
0

LVL 18

Expert Comment

Make the query part of your insert. For example, instead of INSERT INTO Table VALUES (val1,val2,etc), turn it into INSERT INTO Table SELECT val1,val2,etc FROM Table
This way, you can use the formula to make sure that the insert will be unique.
0

Author Comment

Hi Purfard,

Duplicacy is happenning if i generate the no.s in multiple sql query window even after using the CTE to get order number. below is the query i used

declare @rownum int
set @rownum=0
while @rownum<10000
begin
with cte1(orderid)
as
(

SELECT CONVERT (CHAR(8) , GETDATE()+1, 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX(
orderid),'0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) as Orderid
FROM test
WHERE SUBSTRING(orderid, 1, 8) = CONVERT (CHAR(8) , GETDATE()+1, 112)
)
insert into TEST(orderid) select orderid from cte1
set @rownum=@rownum+1
end
0

Author Comment

Hi Purfard,

any update?
0

LVL 12

Expert Comment

Hi
I tried your query and it worked fine for me without any duplicate orderids.
As another solution I created a trigger:
``````CREATE TABLE [dbo].[Test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [char](13) NULL,
[Description] [nvarchar](max) NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TRIGGER AutoGenerateOrderID ON Test
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON ;

UPDATE  Test
SET   OrderID = ( SELECT  CONVERT (CHAR(8), DATEADD(dd, 1, GETDATE()), 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX(orderid), '0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) FROM Test WHERE SUBSTRING(OrderID, 1, 8) = CONVERT (CHAR(8), DATEADD(dd, 1, GETDATE()), 112) )
WHERE Test.ID = ( SELECT  id FROM  inserted )
END

GO
``````

To Test it:
``````INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
INSERT INTO [Test].[dbo].[Test] ([Description]) VALUES ('Test...')
GO
``````
0

Author Comment

Hi Purfard,

Please make the order id as primary key and try to execute 1000 order ids in 3 SQL query window. you will get the primary key violation error
0

LVL 12

Accepted Solution

I added TABLOCKX Lock and it worked:
``````DECLARE @rownum INT
SET @rownum = 0
WHILE @rownum < 10000
BEGIN
WITH    cte1 ( orderid )
AS ( SELECT   CONVERT (CHAR(8), GETDATE() + 1, 112) + RIGHT('00000' + CAST(CAST(SUBSTRING(ISNULL(MAX(orderid), '0'), 9, 5) AS INT) + 1 AS VARCHAR(5)), 5) AS Orderid
FROM     Test
WHERE    SUBSTRING(orderid, 1, 8) = CONVERT (CHAR(8), GETDATE() + 1, 112)
)
INSERT  INTO Test WITH (TABLOCKX ) ( orderid )
SELECT  orderid
FROM    cte1
SET @rownum = @rownum + 1
END
``````
0

Author Comment

Any other solution apart from making table lock?
0

Author Closing Comment

Thanks
0

## Featured Post

### Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.