Best Practice: Table Primary Key

Hi gang,

I need some SOLID direction here.

I am planning to use DataSets and DataBinding in an app which will have a
few users (VB.NET / SQL Server). I need to be able to create an 'offline'
mode, where they do not have access to the WebService, and would therfore
need to store the data locally as XML, then merge into the DB later.

My issue relates to primary keys. As a former Access Guy (current app is
SQL Server 2000), I have grown accustomed to the use of Auto-Incrementing
primary keys for a table, then making relationships to this integer value.
In .NET Datasets, I know (painfully) that I would need to set the
AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to avoid
issues. This doesn't work for me here, since I need the PrimaryKeys to be
the same in the 'offline' xml file and the online DB.

I am willing to change my ways, and begin to use either 'natural' keys, or
GUID fields. I would like some people to share their experience on using
either of these is similar situations. Are there performance hits
asssociated with relating tables to VARCHAR fields as oposed to INTs? Are
there any trade-offs I need to be aware of prior to using this approach?
Should I 'never use an AutoIncrement again'? Does databinding have any
issues using a GUID as the Value of a combobox, for example?


Thanks!!

Kevin

Miha
Wed Jun 02 07:32:43 CDT 2004

Hi a,

I am not sure why AutoInc keys are not good for you?

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"a" <a@a.com> wrote in message news:OSdxJsJSEHA.3504@TK2MSFTNGP09.phx.gbl...
> Hi gang,
>
> I need some SOLID direction here.
>
> I am planning to use DataSets and DataBinding in an app which will have a
> few users (VB.NET / SQL Server). I need to be able to create an 'offline'
> mode, where they do not have access to the WebService, and would therfore
> need to store the data locally as XML, then merge into the DB later.
>
> My issue relates to primary keys. As a former Access Guy (current app is
> SQL Server 2000), I have grown accustomed to the use of Auto-Incrementing
> primary keys for a table, then making relationships to this integer value.
> In .NET Datasets, I know (painfully) that I would need to set the
> AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to
avoid
> issues. This doesn't work for me here, since I need the PrimaryKeys to be
> the same in the 'offline' xml file and the online DB.
>
> I am willing to change my ways, and begin to use either 'natural' keys, or
> GUID fields. I would like some people to share their experience on using
> either of these is similar situations. Are there performance hits
> asssociated with relating tables to VARCHAR fields as oposed to INTs? Are
> there any trade-offs I need to be aware of prior to using this approach?
> Should I 'never use an AutoIncrement again'? Does databinding have any
> issues using a GUID as the Value of a combobox, for example?
>
>
> Thanks!!
>
> Kevin
>
>

Robert
Wed Jun 02 08:02:15 CDT 2004

Hi Kevin,

what you could do is reserve like a thousand keys for each table per user
and use the AutoIncrementSeed to specify a starting point.

Robert

Cowboy
Wed Jun 02 07:43:10 CDT 2004

You have a couple of choices, as I see it.

1. Do not create keys on the client side and allow the database to solve
that when you send data back. This gets increasingly difficult when you deal
with multiple tables offline.

2. Use GUIDs, which are a bit clunkier, but guarantee uniqueness. If you
really have to have unique, this is a good method.

3. Use MSDE on the client and work up replication between client and server.
This is not the greatest offline solution, however. You could, however, make
a sync utility and add a certain amount of numbers to the MSDE database when
you attempt a sync and then have the MSDE autoincrement, but sync at the
next higher number in SQL Server. This may get difficult in some situations.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
"a" <a@a.com> wrote in message news:OSdxJsJSEHA.3504@TK2MSFTNGP09.phx.gbl...
> Hi gang,
>
> I need some SOLID direction here.
>
> I am planning to use DataSets and DataBinding in an app which will have a
> few users (VB.NET / SQL Server). I need to be able to create an 'offline'
> mode, where they do not have access to the WebService, and would therfore
> need to store the data locally as XML, then merge into the DB later.
>
> My issue relates to primary keys. As a former Access Guy (current app is
> SQL Server 2000), I have grown accustomed to the use of Auto-Incrementing
> primary keys for a table, then making relationships to this integer value.
> In .NET Datasets, I know (painfully) that I would need to set the
> AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to
avoid
> issues. This doesn't work for me here, since I need the PrimaryKeys to be
> the same in the 'offline' xml file and the online DB.
>
> I am willing to change my ways, and begin to use either 'natural' keys, or
> GUID fields. I would like some people to share their experience on using
> either of these is similar situations. Are there performance hits
> asssociated with relating tables to VARCHAR fields as oposed to INTs? Are
> there any trade-offs I need to be aware of prior to using this approach?
> Should I 'never use an AutoIncrement again'? Does databinding have any
> issues using a GUID as the Value of a combobox, for example?
>
>
> Thanks!!
>
> Kevin
>
>

a
Wed Jun 02 08:06:11 CDT 2004

Lets assume I have only one user. She adds records 1 through 10. Later,
she deletes the record with ID = 10. Tomorrow, she gets the dataset from
the server, containing ID's 1 through 9. IF I don't set the
AutoIncrementStep and AutoIncrementSeed in the dataset, when she adds a new
record to the dataset, the new record will get ID = 10. This then creates
issues on updates, where the saved record has ID = 11 in the DB.

If I fix this issue with the MS Suggestion of setting AutoIncrementStep
to -1 and AutoIncrementSeed to 0 , I avoid the Primary Key collisions.
However, I introduce a new issue. When the user goes 'offline', and saves
the Dataset to an XML file, the ID's of new records are negative. If she
then merges back to the server, the ID's will be positive, and all is well.

Wait! Is this not really an issue? Can this handle multiple users, too?

Gee, I think you are right. No Issue.

Thanks!

Kevin


"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
news:%23HwB12JSEHA.1388@TK2MSFTNGP09.phx.gbl...
> Hi a,
>
> I am not sure why AutoInc keys are not good for you?
>
> --
> Miha Markic [MVP C#] - RightHand .NET consulting & development
> miha at rthand com
> www.rthand.com
>
> "a" <a@a.com> wrote in message
news:OSdxJsJSEHA.3504@TK2MSFTNGP09.phx.gbl...
> > Hi gang,
> >
> > I need some SOLID direction here.
> >
> > I am planning to use DataSets and DataBinding in an app which will have
a
> > few users (VB.NET / SQL Server). I need to be able to create an
'offline'
> > mode, where they do not have access to the WebService, and would
therfore
> > need to store the data locally as XML, then merge into the DB later.
> >
> > My issue relates to primary keys. As a former Access Guy (current app
is
> > SQL Server 2000), I have grown accustomed to the use of
Auto-Incrementing
> > primary keys for a table, then making relationships to this integer
value.
> > In .NET Datasets, I know (painfully) that I would need to set the
> > AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to
> avoid
> > issues. This doesn't work for me here, since I need the PrimaryKeys to
be
> > the same in the 'offline' xml file and the online DB.
> >
> > I am willing to change my ways, and begin to use either 'natural' keys,
or
> > GUID fields. I would like some people to share their experience on
using
> > either of these is similar situations. Are there performance hits
> > asssociated with relating tables to VARCHAR fields as oposed to INTs?
Are
> > there any trade-offs I need to be aware of prior to using this approach?
> > Should I 'never use an AutoIncrement again'? Does databinding have any
> > issues using a GUID as the Value of a combobox, for example?
> >
> >
> > Thanks!!
> >
> > Kevin
> >
> >
>
>

William
Wed Jun 02 08:46:34 CDT 2004

Set the AutoIncrementValue to -1. That resolves all of this b/c the db will
automatically assign the value since negative numbers aren't valid

--

W.G. Ryan, eMVP

http://forums.devbuzz.com/
http://www.knowdotnet.com/williamryan.html
http://www.msmvps.com/WilliamRyan/
http://www.devbuzz.com/content/zinc_personal_media_center_pg1.asp
"a" <a@a.com> wrote in message news:uuNVaIKSEHA.3012@tk2msftngp13.phx.gbl...
> Lets assume I have only one user. She adds records 1 through 10. Later,
> she deletes the record with ID = 10. Tomorrow, she gets the dataset from
> the server, containing ID's 1 through 9. IF I don't set the
> AutoIncrementStep and AutoIncrementSeed in the dataset, when she adds a
new
> record to the dataset, the new record will get ID = 10. This then creates
> issues on updates, where the saved record has ID = 11 in the DB.
>
> If I fix this issue with the MS Suggestion of setting AutoIncrementStep
> to -1 and AutoIncrementSeed to 0 , I avoid the Primary Key collisions.
> However, I introduce a new issue. When the user goes 'offline', and saves
> the Dataset to an XML file, the ID's of new records are negative. If she
> then merges back to the server, the ID's will be positive, and all is
well.
>
> Wait! Is this not really an issue? Can this handle multiple users, too?
>
> Gee, I think you are right. No Issue.
>
> Thanks!
>
> Kevin
>
>
> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> news:%23HwB12JSEHA.1388@TK2MSFTNGP09.phx.gbl...
> > Hi a,
> >
> > I am not sure why AutoInc keys are not good for you?
> >
> > --
> > Miha Markic [MVP C#] - RightHand .NET consulting & development
> > miha at rthand com
> > www.rthand.com
> >
> > "a" <a@a.com> wrote in message
> news:OSdxJsJSEHA.3504@TK2MSFTNGP09.phx.gbl...
> > > Hi gang,
> > >
> > > I need some SOLID direction here.
> > >
> > > I am planning to use DataSets and DataBinding in an app which will
have
> a
> > > few users (VB.NET / SQL Server). I need to be able to create an
> 'offline'
> > > mode, where they do not have access to the WebService, and would
> therfore
> > > need to store the data locally as XML, then merge into the DB later.
> > >
> > > My issue relates to primary keys. As a former Access Guy (current app
> is
> > > SQL Server 2000), I have grown accustomed to the use of
> Auto-Incrementing
> > > primary keys for a table, then making relationships to this integer
> value.
> > > In .NET Datasets, I know (painfully) that I would need to set the
> > > AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to
> > avoid
> > > issues. This doesn't work for me here, since I need the PrimaryKeys
to
> be
> > > the same in the 'offline' xml file and the online DB.
> > >
> > > I am willing to change my ways, and begin to use either 'natural'
keys,
> or
> > > GUID fields. I would like some people to share their experience on
> using
> > > either of these is similar situations. Are there performance hits
> > > asssociated with relating tables to VARCHAR fields as oposed to INTs?
> Are
> > > there any trade-offs I need to be aware of prior to using this
approach?
> > > Should I 'never use an AutoIncrement again'? Does databinding have any
> > > issues using a GUID as the Value of a combobox, for example?
> > >
> > >
> > > Thanks!!
> > >
> > > Kevin
> > >
> > >
> >
> >
>
>

Daniel
Wed Jun 02 11:18:39 CDT 2004


> 2. Use GUIDs, which are a bit clunkier, but guarantee uniqueness. If you
> really have to have unique, this is a good method.

From the documentation:
"A GUID is a 128-bit integer (16 bytes) that can be used across all
computers and networks wherever a unique identifier is required. Such an
identifier has a very low probability of being duplicated."

Keyword being low probability, it does not guarantee uniqueness. You can
either assume it does and except dataloss and/or corruption in the extremly
few cases they arent unique, or you have to handle the cases when you
generate the same key.

/Dan

Miha
Thu Jun 03 00:54:31 CDT 2004


> Wait! Is this not really an issue? Can this handle multiple users, too?
>
> Gee, I think you are right. No Issue.
>
> Thanks!

:-)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com