Robust unique identifiers?

Hi there ... after much research i still can't quite find the right
answer to this problem, so here goes.
I'm trying to find a simple, robust and scalable way to manage unique
identifiers in my database system. The system generates paper forms
containing the UIDs and sends them to customers, so maintaining
integrity of the numbers is critical.
The system needs to be non-DBMS-specific, needs to be able to run in
multiple instances simultaneously (although this is only a
hypothetical, later requirement) and needs to be as robust as possible
throughout DB restores, general maintenance and against rogue DBAs
resetting IDENTITYs and the like.
I've been looking at generating a unique key using a hashed combination
of the server ID and a low-level timestamp, which is fine except that
it generates enormous numbers. Customers need to be able to quote the
numbers easily to call centre staff, so I'm looking for something
ideally no more than about 12-16 digits. Is there a quality random
value generator that will give numbers as short as this? Or, is there a
suitably robust way of using SQL IDENTITYs to achieve what I need?
Many thanks for any help.
Pete.Allocate the first digit (or more if you need) to identify the server
instance - 1 number per server. The rest can just be an incrementing count.
Won't that meet your requirements?
David Portas
SQL Server MVP
--

Robust unique identifiers?

Hi there ... after much research i still can't quite find the right
answer to this problem, so here goes.
I'm trying to find a simple, robust and scalable way to manage unique
identifiers in my database system. The system generates paper forms
containing the UIDs and sends them to customers, so maintaining
integrity of the numbers is critical.
The system needs to be non-DBMS-specific, needs to be able to run in
multiple instances simultaneously (although this is only a
hypothetical, later requirement) and needs to be as robust as possible
throughout DB restores, general maintenance and against rogue DBAs
resetting IDENTITYs and the like.
I've been looking at generating a unique key using a hashed combination
of the server ID and a low-level timestamp, which is fine except that
it generates enormous numbers. Customers need to be able to quote the
numbers easily to call centre staff, so I'm looking for something
ideally no more than about 12-16 digits. Is there a quality random
value generator that will give numbers as short as this? Or, is there a
suitably robust way of using SQL IDENTITYs to achieve what I need?
Many thanks for any help.
Pete.Allocate the first digit (or more if you need) to identify the server
instance - 1 number per server. The rest can just be an incrementing count.
Won't that meet your requirements?
David Portas
SQL Server MVP
--

Robust unique identifiers?

Hi there ... after much research i still can't quite find the right
answer to this problem, so here goes.
I'm trying to find a simple, robust and scalable way to manage unique
identifiers in my database system. The system generates paper forms
containing the UIDs and sends them to customers, so maintaining
integrity of the numbers is critical.
The system needs to be non-DBMS-specific, needs to be able to run in
multiple instances simultaneously (although this is only a
hypothetical, later requirement) and needs to be as robust as possible
throughout DB restores, general maintenance and against rogue DBAs
resetting IDENTITYs and the like.
I've been looking at generating a unique key using a hashed combination
of the server ID and a low-level timestamp, which is fine except that
it generates enormous numbers. Customers need to be able to quote the
numbers easily to call centre staff, so I'm looking for something
ideally no more than about 12-16 digits. Is there a quality random
value generator that will give numbers as short as this? Or, is there a
suitably robust way of using SQL IDENTITYs to achieve what I need?
Many thanks for any help.
Pete.Allocate the first digit (or more if you need) to identify the server
instance - 1 number per server. The rest can just be an incrementing count.
Won't that meet your requirements?
--
David Portas
SQL Server MVP
--

Robust unique identifiers?

Hi there ... after much research i still can't quite find the right
answer to this problem, so here goes.
I'm trying to find a simple, robust and scalable way to manage unique
identifiers in my database system. The system generates paper forms
containing the UIDs and sends them to customers, so maintaining
integrity of the numbers is critical.
The system needs to be non-DBMS-specific, needs to be able to run in
multiple instances simultaneously (although this is only a
hypothetical, later requirement) and needs to be as robust as possible
throughout DB restores, general maintenance and against rogue DBAs
resetting IDENTITYs and the like.
I've been looking at generating a unique key using a hashed combination
of the server ID and a low-level timestamp, which is fine except that
it generates enormous numbers. Customers need to be able to quote the
numbers easily to call centre staff, so I'm looking for something
ideally no more than about 12-16 digits. Is there a quality random
value generator that will give numbers as short as this? Or, is there a
suitably robust way of using SQL IDENTITYs to achieve what I need?
Many thanks for any help.
Pete.
Allocate the first digit (or more if you need) to identify the server
instance - 1 number per server. The rest can just be an incrementing count.
Won't that meet your requirements?
David Portas
SQL Server MVP
sql

Reuse of Recursive Queries

I have a recursive query which I use to retrieve a set of identifiers. Something like:

WITH Set (Id)
AS (/* recursive code */)
SELECT Item FROM MyTable T
WHERE T.ItemId = ANY (SELECT Id FROM Set)

What I would like to achieve is to be able to reuse the code in bold in another procedure without duplicating the code (ie. reuse the query building up the set).

What I've done is to create a user-defined function which returns a TABLE parameter. So now, I have something like:

SELECT Item FROM MyTable T
WHERE T.ItemId = ANY(SELECT Id FROM MyFunction())

My question: is this the righ way of doing it? Does the use of a function incurs any relevant performance cost?

Thanks for advice.TVF would be fine. Another option is to convert it into a view.|||If you are making it as a TVF, then make sure it is inline otherwise you will have performance issues.