Loading ...
Sorry, an error occurred while loading the content.
 

Generating primary keys vs database keys

Expand Messages
  • banshee858
    I posed this question on the TDD group, but perhaps it is better answered here. What experience do people have with generating primary keys manually versus
    Message 1 of 10 , Apr 12, 2005
      I posed this question on the TDD group, but perhaps it is better
      answered here. What experience do people have with generating primary
      keys manually versus allowing the database to handle this for you? To
      facilitate programmer tests, I am manually creating these keys.
      However, I was questioned about the need for this since the database
      does this for you. I am not sure how to respond.

      Carlton
    • yahoogroups@jhrothjr.com
      From: banshee858 To: agileDatabases@yahoogroups.com
      Message 2 of 10 , Apr 12, 2005
        From: "banshee858" <cnett858.at.hotmail.com@...>
        To: "agileDatabases@yahoogroups.com"
        <agileDatabases.at.yahoogroups.com@...>
        Sent: Tuesday, April 12, 2005 12:23 PM
        Subject: [agileDatabases] Generating primary keys vs database keys

        > I posed this question on the TDD group, but perhaps it is better
        > answered here. What experience do people have with generating primary
        > keys manually versus allowing the database to handle this for you? To
        > facilitate programmer tests, I am manually creating these keys.
        > However, I was questioned about the need for this since the database
        > does this for you. I am not sure how to respond.

        The FitNesse version of FIT has a mechanism to allow you to extract
        a generated key and then use it later. PyFit has the same mechanism.

        John Roth
        >
        > Carlton
        >
        >
        >
        >
        >
        >
        >
        >
        >
        > Yahoo! Groups Links
        >
        >
        >
        >
        >
        >
        >
        >
      • Crowhurst,Christian
        The biggest benefit we ve found with generating our own pks (a special kind of guid that doesn t have the performance issues normally associated with guids as
        Message 3 of 10 , Apr 13, 2005
          The biggest benefit we've found with generating our own pks (a special kind of guid that doesn't have the performance issues normally associated with guids as pks) is on transactions. In the middle tier (or the client for that matter) we can create a whole object graph that needs to be persisted or not at all without having to hit the database to get our primary keys before we can save the child records. Also we've noticed that being able to assign our own pks make working with master-detail records in a database that much easier - we don't have to insert the master record and then select the new primary key which is then used as the foreign key to insert into the detail rows. It means you have a little more freedom in deciding the order in which you lay out your dml statements.
           
          Actually, the most significant benefits we derive is actually using guids rather than say a numeric as a surrogate pk:
          - easier replication
          - not having to worry about pk clashes when merging tables in our local developer database with the production database
          - object (value based) identity and relational identity are one and the same.
           
          Christian
          -----Original Message-----
          From: banshee858 [mailto:cnett858@...]
          Sent: 12 April 2005 18:24
          To: agileDatabases@yahoogroups.com
          Subject: [agileDatabases] Generating primary keys vs database keys



          I posed this question on the TDD group, but perhaps it is better
          answered here.  What experience do people have with generating primary
          keys manually versus allowing the database to handle this for you?  To
          facilitate programmer tests, I am manually creating these keys.
          However, I was questioned about the need for this since the database
          does this for you.  I am not sure how to respond.

          Carlton








          Click here to report this email as spam.
           
           
          This e-mail, and any attachment, is confidential and is intended only for the use of the individual to which it is addressed. If you have received it in error, please delete it from your system, do not use or disclose the information in any way. The contents of this message may contain personal views which are not the views of the ECA Group, unless specifically stated.
        • Luiz Esmiralha
          Could you ellaborate on these special guids? I ve always wanted to use guids as PKs but DBAs look horrified when I mention that...
          Message 4 of 10 , Apr 13, 2005
            Could you ellaborate on these special guids? I've always wanted to use
            guids as PKs but DBAs look horrified when I mention that...

            On 4/13/05, Crowhurst,Christian <christian.crowhurst@...> wrote:
            >
            >
            > The biggest benefit we've found with generating our own pks (a special kind
            > of guid that doesn't have the performance issues normally associated with
            > guids as pks) is on transactions. In the middle tier (or the client for that
            > matter) we can create a whole object graph that needs to be persisted or not
            > at all without having to hit the database to get our primary keys before we
            > can save the child records. Also we've noticed that being able to assign our
            > own pks make working with master-detail records in a database that much
            > easier - we don't have to insert the master record and then select the new
            > primary key which is then used as the foreign key to insert into the detail
            > rows. It means you have a little more freedom in deciding the order in which
            > you lay out your dml statements.
            >
            > Actually, the most significant benefits we derive is actually using guids
            > rather than say a numeric as a surrogate pk:
            > - easier replication
            > - not having to worry about pk clashes when merging tables in our local
            > developer database with the production database
            > - object (value based) identity and relational identity are one and the
            > same.
            >
            > Christian
            >
            > -----Original Message-----
            > From: banshee858 [mailto:cnett858@...]
            > Sent: 12 April 2005 18:24
            > To: agileDatabases@yahoogroups.com
            > Subject: [agileDatabases] Generating primary keys vs database keys
            >
            >
            >
            > I posed this question on the TDD group, but perhaps it is better
            > answered here. What experience do people have with generating primary
            > keys manually versus allowing the database to handle this for you? To
            > facilitate programmer tests, I am manually creating these keys.
            > However, I was questioned about the need for this since the database
            > does this for you. I am not sure how to respond.
            >
            > Carlton
            >
            >
            >
            >
            >
            >
            >
            >
            >
            > Click here to report this email as spam.
            >
            >
            > This e-mail, and any attachment, is confidential and is intended only for
            > the use of the individual to which it is addressed. If you have received it
            > in error, please delete it from your system, do not use or disclose the
            > information in any way. The contents of this message may contain personal
            > views which are not the views of the ECA Group, unless specifically stated.
            >
            >
            > ________________________________
            > Yahoo! Groups Links
            >
            >
            > To visit your group on the web, go to:
            > http://groups.yahoo.com/group/agileDatabases/
            >
            > To unsubscribe from this group, send an email to:
            > agileDatabases-unsubscribe@yahoogroups.com
            >
            > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
          • GilderJ
            Could I echo the request for more details of these sql optimised guids? I think they would be very useful, for testing, static data set management, and
            Message 5 of 10 , Apr 20, 2005
              Could I echo the request for more details of these sql optimised guids?

              I think they would be very useful, for testing, static data set
              management, and streamlining dependent record set insertions, if
              nothing else.

              On 4/13/05, Luiz Esmiralha <esmiralha@...> wrote:
              >
              >
              > Could you ellaborate on these special guids? I've always wanted to use
              > guids as PKs but DBAs look horrified when I mention that...
              >
              > On 4/13/05, Crowhurst,Christian <christian.crowhurst@...> wrote:
              > >
              > >
              > > The biggest benefit we've found with generating our own pks (a special kind
              > > of guid that doesn't have the performance issues normally associated with
              > > guids as pks) is on transactions. In the middle tier (or the client for that
              > > matter) we can create a whole object graph that needs to be persisted or not
              > > at all without having to hit the database to get our primary keys before we
              > > can save the child records. Also we've noticed that being able to assign our
              > > own pks make working with master-detail records in a database that much
              > > easier - we don't have to insert the master record and then select the new
              > > primary key which is then used as the foreign key to insert into the detail
              > > rows. It means you have a little more freedom in deciding the order in which
              > > you lay out your dml statements.
              > >
              > > Actually, the most significant benefits we derive is actually using guids
              > > rather than say a numeric as a surrogate pk:
              > > - easier replication
              > > - not having to worry about pk clashes when merging tables in our local
              > > developer database with the production database
              > > - object (value based) identity and relational identity are one and the
              > > same.
              > >
              > > Christian
              > >
              > > -----Original Message-----
              > > From: banshee858 [mailto:cnett858@...]
              > > Sent: 12 April 2005 18:24
              > > To: agileDatabases@yahoogroups.com
              > > Subject: [agileDatabases] Generating primary keys vs database keys
              > >
              > >
              > >
              > > I posed this question on the TDD group, but perhaps it is better
              > > answered here. What experience do people have with generating primary
              > > keys manually versus allowing the database to handle this for you? To
              > > facilitate programmer tests, I am manually creating these keys.
              > > However, I was questioned about the need for this since the database
              > > does this for you. I am not sure how to respond.
              > >
              > > Carlton
              > >
              > >
              > >
              > >
              > >
              > >
              > >
              > >
              > >
              > > Click here to report this email as spam.
              > >
              > >
              > > This e-mail, and any attachment, is confidential and is intended only for
              > > the use of the individual to which it is addressed. If you have received it
              > > in error, please delete it from your system, do not use or disclose the
              > > information in any way. The contents of this message may contain personal
              > > views which are not the views of the ECA Group, unless specifically stated.
              > >
              > >
              > > ________________________________
              > > Yahoo! Groups Links
              > >
              > >
              > > To visit your group on the web, go to:
              > > http://groups.yahoo.com/group/agileDatabases/
              > >
              > > To unsubscribe from this group, send an email to:
              > > agileDatabases-unsubscribe@yahoogroups.com
              > >
              > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
              >
              > Yahoo! Groups Links
              >
              >
              >
              >
              >
            • Scott W. Ambler
              I ve posted a discussion of surrogate key strategies at: http://www.agiledata.org/essays/dataModeling101.html#AssignKeys If you really want to horrify your
              Message 6 of 10 , Apr 20, 2005
                I've posted a discussion of surrogate key strategies at:
                http://www.agiledata.org/essays/dataModeling101.html#AssignKeys

                If you really want to horrify your DBAs, get them a copy of Agile Database
                Techniques. ;-)

                - Scott


                At 02:15 PM 4/20/2005, you wrote:

                >Could I echo the request for more details of these sql optimised guids?
                >
                >I think they would be very useful, for testing, static data set
                >management, and streamlining dependent record set insertions, if
                >nothing else.
                >
                >On 4/13/05, Luiz Esmiralha <esmiralha@...> wrote:
                > >
                > >
                > > Could you ellaborate on these special guids? I've always wanted to use
                > > guids as PKs but DBAs look horrified when I mention that...
                > >
              • Vijay Eranti
                note that if you have database generate primary key, then there are issues with multi master replication of databases ... -- Vijay Eranti
                Message 7 of 10 , Apr 20, 2005
                  note that if you have database generate primary key, then there are
                  issues with multi master replication of databases



                  On 4/20/05, Scott W. Ambler <swa@...> wrote:
                  > I've posted a discussion of surrogate key strategies at:
                  > http://www.agiledata.org/essays/dataModeling101.html#AssignKeys
                  >
                  > If you really want to horrify your DBAs, get them a copy of Agile Database
                  > Techniques. ;-)
                  >
                  > - Scott
                  >
                  >
                  > At 02:15 PM 4/20/2005, you wrote:
                  >
                  > >Could I echo the request for more details of these sql optimised guids?
                  > >
                  > >I think they would be very useful, for testing, static data set
                  > >management, and streamlining dependent record set insertions, if
                  > >nothing else.
                  > >
                  > >On 4/13/05, Luiz Esmiralha <esmiralha@...> wrote:
                  > > >
                  > > >
                  > > > Could you ellaborate on these special guids? I've always wanted to use
                  > > > guids as PKs but DBAs look horrified when I mention that...
                  > > >
                  >
                  >
                  >
                  > ________________________________
                  > Yahoo! Groups Links
                  >
                  >
                  > To visit your group on the web, go to:
                  > http://groups.yahoo.com/group/agileDatabases/
                  >
                  > To unsubscribe from this group, send an email to:
                  > agileDatabases-unsubscribe@yahoogroups.com
                  >
                  > Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
                  >

                  --
                  Vijay Eranti
                • Brian Murray
                  If you re using Java here are a couple of classes that generate GUID s: http://www.activescript.co.uk/jguid.html
                  Message 8 of 10 , Apr 21, 2005
                    If you're using Java here are a couple of classes that generate GUID's:

                    http://www.activescript.co.uk/jguid.html
                    http://www.javaexchange.com/aboutRandomGUID.html

                    plus you can look at the java.rmi.dgc.VMID class included with J2SE.

                    If you use Hibernate, here's an article arguing why this is a good
                    technique when using Hibernate:

                    http://jroller.com/page/jcarreira/20041215#overcoming_the_hashcode_object_identity
                    essentially the same justification as Christian (you know the ID before
                    a new object is persisted), plus a tip to get it to work with
                    Hibernate's automatic save vs. update checking logic.

                    GilderJ wrote:

                    > Could I echo the request for more details of these sql optimised guids?
                    >
                    > I think they would be very useful, for testing, static data set
                    > management, and streamlining dependent record set insertions, if
                    > nothing else.
                    >
                    > On 4/13/05, Luiz Esmiralha <esmiralha@...> wrote:
                    > >
                    > >
                    > > Could you ellaborate on these special guids? I've always wanted to use
                    > > guids as PKs but DBAs look horrified when I mention that...
                    > >
                    > > On 4/13/05, Crowhurst,Christian <christian.crowhurst@...> wrote:
                    > > >
                    > > >
                    > > > The biggest benefit we've found with generating our own pks (a
                    > special kind
                    > > > of guid that doesn't have the performance issues normally
                    > associated with
                    > > > guids as pks) is on transactions. In the middle tier (or the
                    > client for that
                    > > > matter) we can create a whole object graph that needs to be
                    > persisted or not
                    > > > at all without having to hit the database to get our primary keys
                    > before we
                    > > > can save the child records. Also we've noticed that being able to
                    > assign our
                    > > > own pks make working with master-detail records in a database that
                    > much
                    > > > easier - we don't have to insert the master record and then select
                    > the new
                    > > > primary key which is then used as the foreign key to insert into
                    > the detail
                    > > > rows. It means you have a little more freedom in deciding the
                    > order in which
                    > > > you lay out your dml statements.
                    > > >
                    > > > Actually, the most significant benefits we derive is actually
                    > using guids
                    > > > rather than say a numeric as a surrogate pk:
                    > > > - easier replication
                    > > > - not having to worry about pk clashes when merging tables in our
                    > local
                    > > > developer database with the production database
                    > > > - object (value based) identity and relational identity are one
                    > and the
                    > > > same.
                    > > >
                    > > > Christian
                    > > >
                  • Todd Carrico
                    What performance issue are they afraid of? I did a pretty indepth test looking at GUID vs INT in SQL 2000. I so need to find the post, but the jist of the
                    Message 9 of 10 , Apr 21, 2005
                      What performance issue are they afraid of?

                      I did a pretty indepth test looking at GUID vs INT in SQL 2000.

                      I so need to find the post, but the jist of the investigation was this:
                      1. GUID's take up 4 times more space than ints.
                      2. They do not appear to add any IOs for singleton lookups.
                      3. They do not make sense in a clustered index (PK's are clustered by
                      default, so you have specify nonclustered).
                      4. Lookup's based on GUID's do not take longer than lookups on INT's.

                      I used to fear them as well. Not any more. Since they are 16 bytes, I
                      would figure that just by there very nature they would be inefficient to
                      search on. 4 times the size means you can put about 503 GUIDs in a
                      page, and about 2015 ints. Common sense would say that this would cause
                      more io.

                      The reality is different. B-Tree changes what you might expect as
                      common sense. Also, the key value makes up part of the row in the page.

                      It took an hour or so to create the test tables and cases. My advice to
                      any one that thinks GUIDS are "Horrific" is to prove it. Throw some
                      queries together and find out for yourself.

                      Another point to remember is that SQL Servers stores these as binary
                      data. Not the string representation that gets displayed. So I am
                      talking about the unuqieidetifier data type, not a varchar(36).

                      tc


                      > -----Original Message-----
                      > From: agileDatabases@yahoogroups.com
                      > [mailto:agileDatabases@yahoogroups.com] On Behalf Of GilderJ
                      > Sent: Wednesday, April 20, 2005 1:15 PM
                      > To: agileDatabases@yahoogroups.com; christian.crowhurst@...
                      > Subject: Re: [agileDatabases] Generating primary keys vs database keys
                      >
                      >
                      > Could I echo the request for more details of these sql optimised
                      guids?
                      >
                      > I think they would be very useful, for testing, static data set
                      > management, and streamlining dependent record set insertions, if
                      > nothing else.
                      >
                      > On 4/13/05, Luiz Esmiralha <esmiralha@...> wrote:
                      > >
                      > >
                      > > Could you ellaborate on these special guids? I've always wanted to
                      use
                      > > guids as PKs but DBAs look horrified when I mention that...
                      > >
                      > > On 4/13/05, Crowhurst,Christian <christian.crowhurst@...>
                      wrote:
                      > > >
                      > > >
                      > > > The biggest benefit we've found with generating our own pks (a
                      special
                      > kind
                      > > > of guid that doesn't have the performance issues normally
                      associated
                      > with
                      > > > guids as pks) is on transactions. In the middle tier (or the
                      client
                      > for that
                      > > > matter) we can create a whole object graph that needs to be
                      persisted
                      > or not
                      > > > at all without having to hit the database to get our primary keys
                      > before we
                      > > > can save the child records. Also we've noticed that being able to
                      > assign our
                      > > > own pks make working with master-detail records in a database that
                      > much
                      > > > easier - we don't have to insert the master record and then select
                      the
                      > new
                      > > > primary key which is then used as the foreign key to insert into
                      the
                      > detail
                      > > > rows. It means you have a little more freedom in deciding the
                      order in
                      > which
                      > > > you lay out your dml statements.
                      > > >
                      > > > Actually, the most significant benefits we derive is actually
                      using
                      > guids
                      > > > rather than say a numeric as a surrogate pk:
                      > > > - easier replication
                      > > > - not having to worry about pk clashes when merging tables in our
                      > local
                      > > > developer database with the production database
                      > > > - object (value based) identity and relational identity are one
                      and
                      > the
                      > > > same.
                      > > >
                      > > > Christian
                      > > >
                      > > > -----Original Message-----
                      > > > From: banshee858 [mailto:cnett858@...]
                      > > > Sent: 12 April 2005 18:24
                      > > > To: agileDatabases@yahoogroups.com
                      > > > Subject: [agileDatabases] Generating primary keys vs database keys
                      > > >
                      > > >
                      > > >
                      > > > I posed this question on the TDD group, but perhaps it is better
                      > > > answered here. What experience do people have with generating
                      primary
                      > > > keys manually versus allowing the database to handle this for you?
                      To
                      > > > facilitate programmer tests, I am manually creating these keys.
                      > > > However, I was questioned about the need for this since the
                      database
                      > > > does this for you. I am not sure how to respond.
                      > > >
                      > > > Carlton
                      > > >
                      > > >
                      > > >
                      > > >
                      > > >
                      > > >
                      > > >
                      > > >
                      > > >
                      > > > Click here to report this email as spam.
                      > > >
                      > > >
                      > > > This e-mail, and any attachment, is confidential and is intended
                      only
                      > for
                      > > > the use of the individual to which it is addressed. If you have
                      > received it
                      > > > in error, please delete it from your system, do not use or
                      disclose
                      > the
                      > > > information in any way. The contents of this message may contain
                      > personal
                      > > > views which are not the views of the ECA Group, unless
                      specifically
                      > stated.
                      > > >
                      > > >
                      > > > ________________________________
                      > > > Yahoo! Groups Links
                      > > >
                      > > >
                      > > > To visit your group on the web, go to:
                      > > > http://groups.yahoo.com/group/agileDatabases/
                      > > >
                      > > > To unsubscribe from this group, send an email to:
                      > > > agileDatabases-unsubscribe@yahoogroups.com
                      > > >
                      > > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
                      Service.
                      > >
                      > > Yahoo! Groups Links
                      > >
                      > >
                      > >
                      > >
                      > >
                      >
                      >
                      >
                      > Yahoo! Groups Links
                      >
                      >
                      >
                      >
                      >
                      >
                      >
                    • christianacca
                      Check out a rather conclusive article from one of the first (I believe) implementors of an optimised guid on a microsoft platform:
                      Message 10 of 10 , Apr 21, 2005
                        Check out a rather conclusive article from one of the first (I
                        believe) implementors of an optimised guid on a microsoft platform:

                        http://www.informit.com/articles/article.asp?p=25862

                        I believe that an implementation of this optimised guids has been
                        added to the NHibernate framework

                        Christian

                        --- In agileDatabases@yahoogroups.com, GilderJ <gilderj@g...> wrote:
                        > Could I echo the request for more details of these sql optimised
                        guids?
                        >
                        > I think they would be very useful, for testing, static data set
                        > management, and streamlining dependent record set insertions, if
                        > nothing else.
                        >
                        > On 4/13/05, Luiz Esmiralha <esmiralha@g...> wrote:
                        > >
                        > >
                        > > Could you ellaborate on these special guids? I've always wanted
                        to use
                        > > guids as PKs but DBAs look horrified when I mention that...
                        > >
                        > > On 4/13/05, Crowhurst,Christian <christian.crowhurst@e...> wrote:
                        > > >
                        > > >
                        > > > The biggest benefit we've found with generating our own pks (a
                        special kind
                        > > > of guid that doesn't have the performance issues normally
                        associated with
                        > > > guids as pks) is on transactions. In the middle tier (or the
                        client for that
                        > > > matter) we can create a whole object graph that needs to be
                        persisted or not
                        > > > at all without having to hit the database to get our primary
                        keys before we
                        > > > can save the child records. Also we've noticed that being able
                        to assign our
                        > > > own pks make working with master-detail records in a database
                        that much
                        > > > easier - we don't have to insert the master record and then
                        select the new
                        > > > primary key which is then used as the foreign key to insert
                        into the detail
                        > > > rows. It means you have a little more freedom in deciding the
                        order in which
                        > > > you lay out your dml statements.
                        > > >
                        > > > Actually, the most significant benefits we derive is actually
                        using guids
                        > > > rather than say a numeric as a surrogate pk:
                        > > > - easier replication
                        > > > - not having to worry about pk clashes when merging tables in
                        our local
                        > > > developer database with the production database
                        > > > - object (value based) identity and relational identity are one
                        and the
                        > > > same.
                        > > >
                        > > > Christian
                        > > >
                        > > > -----Original Message-----
                        > > > From: banshee858 [mailto:cnett858@h...]
                        > > > Sent: 12 April 2005 18:24
                        > > > To: agileDatabases@yahoogroups.com
                        > > > Subject: [agileDatabases] Generating primary keys vs database
                        keys
                        > > >
                        > > >
                        > > >
                        > > > I posed this question on the TDD group, but perhaps it is better
                        > > > answered here. What experience do people have with generating
                        primary
                        > > > keys manually versus allowing the database to handle this for
                        you? To
                        > > > facilitate programmer tests, I am manually creating these keys.
                        > > > However, I was questioned about the need for this since the
                        database
                        > > > does this for you. I am not sure how to respond.
                        > > >
                        > > > Carlton
                        > > >
                        > > >
                        > > >
                        > > >
                        > > >
                        > > >
                        > > >
                        > > >
                        > > >
                        > > > Click here to report this email as spam.
                        > > >
                        > > >
                        > > > This e-mail, and any attachment, is confidential and is
                        intended only for
                        > > > the use of the individual to which it is addressed. If you have
                        received it
                        > > > in error, please delete it from your system, do not use or
                        disclose the
                        > > > information in any way. The contents of this message may
                        contain personal
                        > > > views which are not the views of the ECA Group, unless
                        specifically stated.
                        > > >
                        > > >
                        > > > ________________________________
                        > > > Yahoo! Groups Links
                        > > >
                        > > >
                        > > > To visit your group on the web, go to:
                        > > > http://groups.yahoo.com/group/agileDatabases/
                        > > >
                        > > > To unsubscribe from this group, send an email to:
                        > > > agileDatabases-unsubscribe@yahoogroups.com
                        > > >
                        > > > Your use of Yahoo! Groups is subject to the Yahoo! Terms of
                        Service.
                        > >
                        > > Yahoo! Groups Links
                        > >
                        > >
                        > >
                        > >
                        > >
                      Your message has been successfully submitted and would be delivered to recipients shortly.