Default value of a GUID field?!

[Cowboy \(Gregory A. Beamer\)]

You can seed these values, as a GUID is a GUID (not quite true, but as long
as you are not using a CLUSTERED key on the GUID (and sequential guids (SQL
SERver 2005)). BUt, if you do not want to, alter the INSERT query to not
include the key. This can get tricky if you are running related data and
need to redisplay, so think the full set of use cases through before making
a decision or you will be altering.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************

 

[Ben Voigt]

I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
2005 Express.
this column is the primary key and its default value is "newid()" in SQL
Server.

I have generated a DataSet in C# that has this column but its default
value
is DBNull what can I do to make the default value be the return value of
this function ( or I want the Id be generated by default in the DataSet)?

You'd best let SQL generate the GUID, that's the only way you can be 100%
sure of no collisions.

 

[Marina Levit [MVP]]

I would argue that the odds of generating the same GUID on 2 different
clients is so small, that it shouldn't be a consideration. Realistically,
it's just not going to happen, although yes, it's not 100% guaranteed.

 

[Ben Voigt]

I would argue that the odds of generating the same GUID on 2 different
clients is so small, that it shouldn't be a consideration. Realistically,
it's just not going to happen, although yes, it's not 100% guaranteed.

The chance of any two GUIDs being equal are practically zero.

The chance of the one new GUID colliding in a database with millions (or
more) of records is still incredibly small.

The chance of any one of a million new GUIDs colliding with millions of
existing records, is getting to be enough to worry about, to the point where
I suspect SQL specifically checks for collision and generates a new GUID.
http://en.wikipedia.org/wiki/Birthday_paradox

True, GUIDs are 128-bits, and if you needed to add 2^32 records, you'd only
have 2^63 possible collisions, leaving you with a 2^-64 chance of collision.
But some of the bits in the GUID aren't random but tied to the specific
hardware (usually MAC address) in order to guarantee that different
workstations don't collide. So your chance of collision is now on the order
of 2^-20, or one-in-a-million. Obviously the number of records is very
important.

 

[Doug Forster]

Hello Ben

True, GUIDs are 128-bits, and if you needed to add 2^32 records, you'd
only have 2^63 possible collisions, leaving you with a 2^-64 chance of
collision. But some of the bits in the GUID aren't random but tied to the
specific hardware (usually MAC address) in order to guarantee that
different workstations don't collide. So your chance of collision is now
on the order of 2^-20, or one-in-a-million. Obviously the number of
records is very important.

Actually MS discontinued the use of MAC address from Win2000 and now uses
the Crypt random number generator to produce most of the Guid. This article
http://msdn2.microsoft.com/en-us/library/aa446557.aspx is about a Windows CE
guid generator but includes some interesting information about Guid
generation generally. In particular it looks like the chance of collision is
probably way smaller than you suggest.

Cheers
Doug Forster

 

[Cor Ligthert [MVP]]

Ben,

http://en.wikipedia.org/wiki/Birthday_paradox

Yes but how is that article as it is not about a birthday, but about the
same moment of birth in a millisecond. It is possible, but when will those
people meet each other.

Cor

 

[Leon_Amirreza]

I have a column named "ID" and its type is "uniqueidentifier" in SQL Server
2005 Express.
this column is the primary key and its default value is "newid()" in SQL
Server.

I have generated a DataSet in C# that has this column but its default value
is DBNull what can I do to make the default value be the return value of
this function ( or I want the Id be generated by default in the DataSet)?

System.Guid.NewGuid()

 

[Leon_Amirreza]

chances of collsion may be a problem but simplicity of management of dataset
and code is more important to me I would rather prefer to use something like
newid() function in default value of the field in the typed dataset. because
requires less tricks and code than for example ommiting the Primary Key or
attaching a handler to the TableNewRow and DataBound controls are aware of
the difference between default values and user Entered values in firing
their event handlers or raising Data Error Exceptions and .....

 

[Leon_Amirreza]

I have more than 5 DBs in Sql Server that each has more that 150 tables with
complex Relations that use GUID primary Keys and Foreign Keys very heavily
and I need to create data Entry Forms for them

so the smooth running of the created forms and ease of data Entry for users
while fast , robust designation of the forms are important so I heavily rely
on InBuilt bahvior of data bound controls rather than changing them or
creating user controls (besides the end users are more familiar with the
..Net Controls behaviors)

because of that some function that gengerates defualt values for the GUID
fields is the prefered way if possible so it will require less event handler
coding and coding for detection of false data Error exceptions or coding for
correcting the unwanted behaviors caused by these?!

besides where is the Data Form Generator that was in New Item window in
previous versions of VS .Net ?