Create a unique identifier using SQL

Is there some SQL coding which can generate a unique identifier on the click of a get default button? I'm looking for a system to number physical paper files before they are placed into storage. Problem is there are 3 offices, and each office needs a sequential number system (i.e. P001, P002, P003 and C001, C002...).

Below is the code i have so far to generate the prefix to the unique id number.

SELECT CASE WHEN ptBranch=3 THEN 'P' WHEN ptBranch=4 THEN 'A' ELSE 'C' END + CONVERT(VARCHAR(2),GETDATE(),12) FROM LAMatter WHERE ptMatter = $Matter$

The idea will be that the code cou开发者_运维问答ld generate the whole file number e.g. P110001, P110002 (where P, C or A denotes the office the file is in, and 11 denotes the year the file was placed into storage)

any pointers greatly appreciated

The SQL Server function newid() will generate a GUID.

Your SQL queries do not generate buttons or anything else. SQL is a language for querying databases, not for writing software interfaces.

I think there might have been a misunderstanding caused by the initial phrasing of the question.

I'm now envisaging that you need a process whereby each office needs to be able to generate a new guaranteed sequential number at a click of a button every time they process a file?

Any gaps in the sequence are then followed up to investigate potential missing files. Is that correct?

If so you can use something like this to generate the numbers.

CREATE TABLE Sequences
(
OfficeCode char(1) primary key,
SequenceNumber int NOT NULL DEFAULT (0)
)

INSERT INTO Sequences(OfficeCode)
SELECT 'P' UNION ALL SELECT 'C' UNION ALL SELECT 'A'

GO

CREATE PROC dbo.GetSequence
@OfficeCode char(1),
@val AS int OUTPUT,
@n as int =1
AS
UPDATE dbo.Sequences 
SET @val = SequenceNumber = SequenceNumber + @n
WHERE OfficeCode = @OfficeCode;

SET @val = @val - @n + 1; 

GO


DECLARE @val  int

EXEC dbo.GetSequence 'C', @val output
select @val

I'd use the row_number function with a creation date. if you're batch inserting then you'll also want to order by the id column in the row_number function.

declare @records table (id int identity(1,1), CreationDate datetime, Name varchar(50), Section char(1), FileID varchar(10))

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:57:49', 'abc','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:57:50', 'def','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:00', 'ghi','c'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:50', 'jkl','d'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:51', 'mno','c'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:52', 'pqr','p'

insert into @records (CreationDate, Name, Section)
select '2011-01-26 16:58:53', 'def','p'

update @records
set FileID=a.FileID
from 
(
    select id,
    Section + cast(row_number() over (partition by Section order by CreationDate, Section) as varchar(10)) as FileID
    from @records
) a
    inner join @records b
        on a.id=b.id

select * from @records