3

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 could 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

James Parish
  • 327
  • 1
  • 6
  • 15
  • 1
    How important is the `sequential` aspect? Can there be any gaps at all left by failed inserts? – Martin Smith Jan 26 '11 at 13:55
  • unfortunately really important - as it makes it easier to physically identy when a file is missing from the storage (after archiving they are taken in and out all the time) - obviously, if they were gaps in the numbers, you wouldn't know if a file was missing or a number wasn't allocated – James Parish Jan 26 '11 at 14:02
  • OK that completely rules out `identity` then as that doesn't guarantee this at all (even if you had 3 different tables you could still get gaps from rolled back transactions) – Martin Smith Jan 26 '11 at 14:05
  • I just noticed that you say you want to generate a new "unique identifier on the click of a get default button" - Does this mean that the generation of the number happens before you have any data to insert (e.g. to print a label or something)? If so what happens if a number is generated but never used? Also will multiple people be processing these files? – Martin Smith Jan 26 '11 at 14:17
  • @martin looking back at your `row_number` idea, rather than `order by id` can you order by the feild itself? so any file which has been asigned a number goes to the top of the list, and next time you click 'get default' you'll get the next number? not too sure if that makes sense, but i hoping the answer lies in the `row_number` realm – James Parish Jan 26 '11 at 14:20

3 Answers3

11

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.

Dan Grossman
  • 51,866
  • 10
  • 112
  • 101
  • thanks Dan, but i was hopign for an incramental unquie identifier (i.e. 0001, 0002, 0003) is that at all possible? – James Parish Jan 26 '11 at 10:19
  • Yes, create an identity column, which will automatically generate incrementing identifiers without you writing any code. – Dan Grossman Jan 26 '11 at 10:21
  • Searching with Google or the search box on this site would've answered your question. Here's the Microsoft SQL Server documentation: http://msdn.microsoft.com/en-us/library/bb545450.aspx – Dan Grossman Jan 26 '11 at 10:25
  • one last question - if you put a prefix to the Identify (to denote which office the file is in) would the idenfier property generate like P001, P002, P003 in one office and C001, C002 in another - or woudl you get some along the lines of P001, C002, P003? suppose i'm asking does the identity property recogise (or can it be made to recognise) a prefix? – James Parish Jan 26 '11 at 10:33
  • 5
    It won't do that for you, and you should never do this. Every column value should be atomic -- it contains one and only one piece of information. Which office the file belongs to is a separate piece of information and goes in its own column. If you want to DISPLAY "P0001" in your application, you can write the code in your application to combine the office column and the identity column. You don't STORE those two distinct pieces of data as one. – Dan Grossman Jan 26 '11 at 10:34
  • i'm completely in agreement with you - but the purpose of this feild is to assign an archiving number to a file - the current system is for example P110001 (P denotes the office, 11 the year, 0001 the unique number for the file) - we have a couple of offices, and the cateloging of files for archive needs to be sequential in both – James Parish Jan 26 '11 at 10:37
  • @James- In that case `identity` probably isn't what you are looking for. I think `sequences` might help here but they are not available until SQL Server 2011 until then you'll need to bodge together your own solution. This is quite straightforward using `max` but if being sequential is a must then you need to block all concurrent inserts both to prevent race conditions and also to avoid gaps in the sequence should a transaction rollback. – Martin Smith Jan 26 '11 at 10:47
  • @James You can DERIVE that archiving number from the actual data you need to store (office name, date archived/inserted, sequential unique ID). Just concatenate them together in your application. Having these pieces of data atomic, as they should be, allows you to do things like query by office, count how many records were archived in a given month across all offices, and retrieve the most recent file while making full use of indexes and not having to tear apart and parse a string in each query. – Dan Grossman Jan 26 '11 at 10:51
  • @Martin - sorry, you've lost me - still new to sql – James Parish Jan 26 '11 at 10:52
  • just to add `row_number() over (partition by office, year order by id)` could be of help here (RE: Deriving the archive numbers). In order for the derived number to be stable you would need to ensure no deletes were ever done. – Martin Smith Jan 26 '11 at 10:53
  • @Dan - i see youre point, but wouldn't require two unique ID generates running simulatiniously? one for office A and one for B to ensure sequential number in both? – James Parish Jan 26 '11 at 10:55
  • It is sequential in both offices, just with gaps. To avoid gaps he'd have to generate his own identifiers, but he'd be doing that anyway with the proposed mash-everything-into-a-string column. – Dan Grossman Jan 26 '11 at 10:57
  • Cheers guys - i'll try testing it out and let you know how things turn out - thanks for the help :) – James Parish Jan 26 '11 at 11:15
  • @Martin - been testing out row_number, but i can't seem to get the value to save to the feild which generates the number... any ideas? – James Parish Jan 26 '11 at 12:03
  • @James - I presume Dan's idea was that it shouldn't be saved at all though I can certainly see there are advantages to saving it (if you are retrieving individual records then having to scan other rows in order to continually recalculate the derived number is probably less than ideal). You can put the `row_number` calculation in a cte and `update` the cte. – Martin Smith Jan 26 '11 at 12:29
  • @James - By the way can you push back at all on the requirement that the numbering be sequential within each group? Does this really matter? It is a pain to implement especially if you are not that familiar with SQL. It's far easier just to have one `identity` column and use that as your numeric code across all groups. – Martin Smith Jan 26 '11 at 12:39
  • @martin - ok, got the number to save, but its always number 1 - this is because i save the data in a feild, close that record and then alter the same feild in a different record - because i'm not compiling a list there is only ever one row (does that make sense?) i know this maybe bad sql practise, but i'm looking for a feild that when i click the 'get default' button pulls up the next file number and saves it to that feild (again, does that make sense?) – James Parish Jan 26 '11 at 12:45
  • @Martin - can you have more than one identity column, one for each office? Then depending on the office, it pulls through the next number needed? – James Parish Jan 26 '11 at 13:32
  • @James - No you can only have 1 identity column per table. TBH there is no particularly good way of doing what you want to do in current versions of SQL Server. If it was me I would try and get the requirement changed first. – Martin Smith Jan 26 '11 at 13:41
  • @James - The only way to have one per office is to have a separate table per office. – JNK Jan 26 '11 at 14:02
  • @JNK - that won't be possible - the software developers have set the 'Branch' feild in a table which can't be altered (expect by them at a huge fee) – James Parish Jan 26 '11 at 14:07
  • @James - How do inserts happen to this table? Do you have full control over that? – Martin Smith Jan 26 '11 at 14:10
  • @James - There's nothing stopping you from using a view or something else to join them... – JNK Jan 26 '11 at 14:16
  • @Martin - sorry Martin, you've lost me again (as i said somewhere along the lines, quite new and teaching myself sql as i go along) what do you mean by inserts? there is one table which i can write new feilds to if thats what you mean, but it doesnt contain the feilds needed `partition` – James Parish Jan 26 '11 at 14:37
  • ok - here is the `row_number` code i've got so far (though sadly it continues to return 1 every time) `SELECT row_number() over (PARTITION BY ptBranch, DATEPART(yy, getdate()) ORDER BY (SELECT ArchiveAsText FROM MatterDataDef)) FROM LAMatter WHERE ptMatter = $Matter$` – James Parish Jan 26 '11 at 14:40
0

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • first, confirming what i need (and i think we are signing from the same hymn sheet now) when a file needs to go into archive, each office will click a button and be given the next archive number sequentually (this is then written onto the file, and it's placed in storage)... occasionally fiels come back out of storage, but we reserve the gap it came out of so we know its missing (should someone else request it)... does that make sense? are we in agreement? As for code, i don't have the first idea of how to implement that into my system sorry, beyond my current understanding – James Parish Jan 26 '11 at 14:53
  • i've had a play around, but sadly the database wont let me see much more that its custom feild builder. the level of SQL i can see and alter within the builder is the same as found in the inital question. – James Parish Jan 26 '11 at 15:26
0

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
DForck42
  • 19,789
  • 13
  • 59
  • 84