How do I autogenerate confirmation number in SQLServer?

I need to autogenerate an order confirmation number with SQL Server 2008 and display it on a webpa开发者_运维问答ge. I have set a column called confirmationnumber and assigned it a uniqueidentifier. Is this the right way to do it?

I use guids for this purpose. However, I like to have a shorter version that is a bit easier on the eyes. Here is a function that will hash the guid:

    public static string GenerateKey()
    {
        long i = 1;
        foreach (byte b in Guid.NewGuid().ToByteArray())
        {
            i *= ((int)b + 1);
        }
        return string.Format("{0:x}", i - DateTime.Now.Ticks);
    }

The result looks like this:

38f50037b1b56c97

The hash is secure and unique.

This really is more of a business question than a technical question. But while we are on it.. I think the best approach to this problem is to convert a sequential (indentity) order number to a base36 string. You end of with a nice order number that is both human friendly (think Southwest Airlines) and at the same time obfuscates the potential security issue with sequential order numbers.

just my two cents,

here is a link to base36 algorithm http://en.wikipedia.org/wiki/Base_36

A uniqueidentifier will produce GUID's. These are 32 characters in length and are not human friendly (think scenario where customer calls up with his reference number). A Guid looks like the following:

f56c41dd-7811-461a-9378-e3a2b095aafc

I would look at using an int with an identity set, this would at least be an easy to use number.

CREATE TABLE order (order_id int not null IDENTITY(1,1), customer_code varchar(50) NOT NULL);

Then insert the order:

DECLARE @order_id int;
INSERT INTO order(customer_code) VALUES('dotnethacker')
SET @order_id = SCOPE_IDENTITY()

@order_id would then contain the new inserted ID.

You can create column of uniqueidentifier type with default NEWID().

CREATE TABLE [dbo].[TestTable](
    [id] [uniqueidentifier] NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[TestTable] ADD  CONSTRAINT [DF_TestTable_id]  DEFAULT (NEWID()) FOR [id]

To catch new inserted GUID, you can use OUTPUT clause:

INSERT INTO TestTable (id)
OUTPUT inserted.id
VALUES(DEFAULT)

UPDATE: To generate code of 10 digits and letters is not a problem. Problem to guarantee uniqueness. To do this, you should to check your table if this code was really unique.

    ; with numbers as
        (SELECT ROW_NUMBER() OVER(ORDER BY o1.object_id,o2.object_id) Num,
            LEFT(REPLACE(CAST(NEWID() as nvarchar(40)),'-',''),10) Code 
FROM  sys.objects o1, sys.objects o2)
    SELECT TOP 1 Code FROM numbers where NOT EXISTS(SELECT code FROM  [Table])