Posted by David Portas on 03/12/06 16:48

Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> > Users are concerned with information. Data (how the information is
> > represented) is primarily the concern of database professionals. As a
> > database designer you have a choice because the same information can
> > always be modelled with natural keys or without. You can argue that the
> > developer may lack the time, the resources or the authority to redesign
> > his database. He may even be unable to analyse his business problem
> > well enough to identify a suitable key with a high degree of
> > confidence. However, those constraints are not problems we can solve in
> > a newsgroup. They are project management problems rather than technical
> > ones.
>
> Thus, it is not the right thing to say in a newsgroup that you should
> not use IDENTITY. Maybe it was a poor database design. Maybe there were
> time constraints in the project. Whatever, it's not our business.

I didn't say don't use IDENTITY. I said it shouldn't be the only key.
That's good advice in any situation as I think the OP's problem
demonstrates. IDENTITY is still useful as a surrogate key and I'm not
against that.

> Say that you are importing a file from some less good source. The file may
> contain lines that are true duplicates. Still we way want to import that
> file in whole, if nothing else because we think SQL is the best tool to
> find the duplicates. You still need some way to identify the lines. This
> can be achieved in several ways, whereof IDENTITY is one.

That's true. It is possible to solve this in an integration layer
before the data reaches the database however. For obvious reasons a
staging table without a natural key isn't a good place to do any
processing other than data cleansing. In the example posted in this
thread the data is being inserted from another SQL table so there's no
reason to suppose there shouldn't be a candidate key. Of course DDL
would have helped.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]