database design question

Hi. I have a new OLTP database requirement, and was wondering what columns
do some of you use in your db design for primary keys, since identity
columns can't really be used for this? I was thinking of using the select
convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
Other suggestions would be appreciated. Thanks.
Alex IvascuWhy are you saying that identity cannot be used for primary keys? I would estimate that perhaps 95%
of the SQL Server population who uses surrogate keys uses identity. And indeed, identity will find
its way into the next ANSI standard.
Having said that, you might want to read Joe Celko's posts regarding identity, even though many
points are inaccurate. So, if you do that, you should also read a good response to that (one from
Tony Rogerson, for instance). You can search the archives using the link at the bottom of my URL.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> Hi. I have a new OLTP database requirement, and was wondering what columns
> do some of you use in your db design for primary keys, since identity
> columns can't really be used for this? I was thinking of using the select
> convert(int, convert(varbinary, newid())) to insert. Any drawbacks to this?
> Other suggestions would be appreciated. Thanks.
> Alex Ivascu
>
>|||Hi, Tibor
Look, I am not lawyer of Celko and I agree with you that some his posts are
inaccurate.
Just yesterday I faced the problem where I was need to update the column
that is PK with Identity property.
At beginining our business logic did not allow to do such things but as you
know, came a new requirement of production in our company.
I think we falled in these 5 % that you estimated that should be used
identity property for promary key .
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:u$ADar5lDHA.2528@.TK2MSFTNGP12.phx.gbl...
> Why are you saying that identity cannot be used for primary keys? I would
estimate that perhaps 95%
> of the SQL Server population who uses surrogate keys uses identity. And
indeed, identity will find
> its way into the next ANSI standard.
> Having said that, you might want to read Joe Celko's posts regarding
identity, even though many
> points are inaccurate. So, if you do that, you should also read a good
response to that (one from
> Tony Rogerson, for instance). You can search the archives using the link
at the bottom of my URL.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at: http://groups.google.com/groups?oi=djq&as
ugroup=microsoft.public.sqlserver
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:IH0lb.2145$e56.174@.newssvr25.news.prodigy.com...
> > Hi. I have a new OLTP database requirement, and was wondering what
columns
> > do some of you use in your db design for primary keys, since identity
> > columns can't really be used for this? I was thinking of using the
select
> > convert(int, convert(varbinary, newid())) to insert. Any drawbacks to
this?
> > Other suggestions would be appreciated. Thanks.
> >
> > Alex Ivascu
> >
> >
> >
>|||>> was wondering what columns do some of you use in your db design for
primary keys, <<
There is no "magic, one-size-fits-all, universal" answer. Designing a
database is hard work! So, for each and every individual table:
1) Is there an ISO standard?
2) Is there a national standard?
3) Is there an industry standard?
Examples: GTIN for retail, VIN for vehicles, ISBN for books and
publications.
Other questions to ask, if you have to invent a code:
1) Will a human being input it? So, does it need an easy syntax
(airport codes)? Does it need a check digit(ISBN)? What kind of check
digit algorithm?
2) How do I verifiy it in the reality represented in my data model?
Internally or with a trusted external source?
External: government tax numbers. Internal: ISO tire sizes (155R15 =155 cm width, Radial construction, 15 inch diameter; verify with a ruler
and your eyes).
I have four chapers on designing codes in DATA & DATABASES which you
might want to read.
--CELKO--
=========================== Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!