[dba-SQLServer] Fwd: SQL Server Primary Key

Susan,
  I recommend the autonumber int as a pkid, if he estimates that he can end
up with over 4Billion entries, he should then consider a BIG int, so if
that's the case his choice column (datetime) for a pkid is unique enough,
the compromise would be for him to convert his date to yymmddhhmmssnnnnnnn
as he stated, it would give him the benefit of a faster bigint vs datetime.



-Francisco <http://twitter.com/seecoolguy>




On Wed, Sep 3, 2014 at 7:26 AM, Rocky Smolin <rockysmolin at bchacc.com> wrote:

> "Each phone record can be
> > uniquely identified by the DateTime2(7) start time of the call because
> > each record is guaranteed to be created in a different 100 nano second
> > window."
>
> That's probably true.  Probably isn't good enough for a primary key.  Use
> Autonumber.
>
> IMHO.
>
> Rocky
>
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
> Harkins
> Sent: Wednesday, September 03, 2014 7:02 AM
> To: Discussion concerning MS SQL Server
> Subject: [dba-SQLServer] Fwd: SQL Server Primary Key
>
> This is from a reader -- seriously over my head. Anyone want to offer some
> advice?
>
> Susan H.
>
>
> On Wed, Sep 3, 2014 at 4:02 AM, Phillip Smith <phillip at creamcow.com>
> wrote:
>
> > Hi Susan,
> > Just reading your post regarding using the right Primary Key. I'm
> > building a rehouse to store telephone data. Each phone record can be
> > uniquely identified by the DateTime2(7) start time of the call because
> > each record is guaranteed to be created in a different 100 nano second
> > window. There are 100 million records. The main way to view data is
> chronological order.
> > I'm trying to decide whether to use the CallStart datetime2(7) field
> > for the primary key. I can cluster on this key and join to my bridging
> > tables using this key. Or should I crate a CallId (Bigint) that
> > encodes the datetim, Maybe in yymmddhhmmssnnnnnnn format. You have
> > stated on your post that there is an overhead to using Datetime type
> > as the primary key. Is this true for my scenario?
> > Best regards
> >
> > Phillip
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>