467,116 Members | 1,196 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,116 developers. It's quick & easy.

Default value of a GUID field?!

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()

Oct 26 '06 #1
  • viewed: 18580
Share:
8 Replies
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!
*************************************************
"Leon_Amirreza" <am**************@yahoo.comwrote in message
news:eA****************@TK2MSFTNGP03.phx.gbl...
>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()

Oct 26 '06 #2

"Leon_Amirreza" <am**************@yahoo.comwrote in message
news:eA****************@TK2MSFTNGP03.phx.gbl...
>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.
System.Guid.NewGuid()

Oct 26 '06 #3
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" <rb*@nospam.nospamwrote in message
news:%2******************@TK2MSFTNGP03.phx.gbl...
>
"Leon_Amirreza" <am**************@yahoo.comwrote in message
news:eA****************@TK2MSFTNGP03.phx.gbl...
>>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.
>System.Guid.NewGuid()


Oct 26 '06 #4

"Marina Levit [MVP]" <so*****@nospam.comwrote in message
news:um****************@TK2MSFTNGP03.phx.gbl...
>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.
>
"Ben Voigt" <rb*@nospam.nospamwrote in message
news:%2******************@TK2MSFTNGP03.phx.gbl...
>>
"Leon_Amirreza" <am**************@yahoo.comwrote in message
news:eA****************@TK2MSFTNGP03.phx.gbl...
>>>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.
>>System.Guid.NewGuid()



Oct 26 '06 #5
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
Oct 26 '06 #6
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
Oct 27 '06 #7
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" <am**************@yahoo.comwrote in message
news:eA****************@TK2MSFTNGP03.phx.gbl...
>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()

Oct 29 '06 #8
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 ?
"Leon_Amirreza" <am**************@yahoo.comwrote in message
news:OA****************@TK2MSFTNGP03.phx.gbl...
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" <am**************@yahoo.comwrote in message
news:eA****************@TK2MSFTNGP03.phx.gbl...
>>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()


Oct 29 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Tom L | last post: by
2 posts views Thread by Jiho Han | last post: by
7 posts views Thread by headware | last post: by
29 posts views Thread by John Wood | last post: by
9 posts views Thread by Leon_Amirreza | last post: by
7 posts views Thread by =?Utf-8?B?Y291Z2FyaXN0aWM=?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.