CODEWITHSUNDEEP
c#sqlguid
Villager
Villager

Reputation: 6689

GUIDs in SQL Server

I have an ASP.NET application that generates GUIDs in the code-behind via C#. These GUIDs are generated via the following:

Guid id = Guid.NewGuid();

This GUID is later stored in a SQL Server 2008 database. I also have a stored procedure that will update that record. I would like to generate a GUID in the stored procedure that is in the same format as the one generated in ASP.NET.

Can somebody please tell me how to do this?

Thank you!

Upvotes: 3

Views: 4885

Answers (6)

Vasea
Vasea

Reputation: 5333

You can use the NEWSEQUENTIALID for better indexing support, but the downside is that you can use this function only as a default value expression for your column.

You can use something like:

INSERT INTO MyTABLE (...) OUTPUT inserted.GUIDCOLUMN INTO @tableVar VALUES (...)

to access the newly generated sequential id.

Upvotes: 0

Ben
Ben

Reputation: 1216

Use NEWID() method

DECLARE @ID uniqueidentifier
SET @ID = NEWID()

Upvotes: 7

Jesse C. Slicer
Jesse C. Slicer

Reputation: 20157

If this is for a clustered index (most often a primary key), I highly recommend NEWSEQUENTIALID() (SQL Server 2005 on up) since, NEWID() will create a fragmented index in that case, being truly random.

Upvotes: 1

jason
jason

Reputation: 241601

You could use NEWID().

But, there are issues with indexing guids generated like this. Instead, you should use the comb algorithm:

CAST(CAST(NEWID() AS BINARY(10)) +
    CAST(GETDATE() AS BINARY(6)) AS UNIQUEIDENTIFIER)

Make sure you are storing these in a column of type UNIQUEIDENTIFIER and not converting them to NVARCHAR or anything of the sort.

Upvotes: 0

Justin
Justin

Reputation: 86729

My guess from the way that you have worded your question is that you are storing the GUIDs in a text (e.g. VARCHAR) field in the database - if this is the case then you should instead be using the uniqueidentifier type in which case you can use the NEWID() SQL function to generate a new GUID.

See C# guid and SQL uniqueidentifier for more detail on how to store GUIDs in an SQL Server database.

Upvotes: 0

James Hill
James Hill

Reputation: 61792

This will generate a GUID for you: SELECT NEWID()

Examples may be found here: http://msdn.microsoft.com/en-us/library/ms190348.aspx

Upvotes: 0

Related Questions