Posted by David Portas on 10/01/20 11:42

pb648174 wrote:
> I guess what I'm getting at is that it is a heck of a lot easier to use
> an identity to refer to the row than the long description, especially
> within the application. Are you saying not to use the identity in
> application code anywhere and not in foreign keys or just not to
> display it to the end user?

Use IDENTITY for your foreign key references if you need to. Reference
it in the application (as long as you don't persist it outside the
database). Just don't expose it to users.

Don't have IDENTITY as the ONLY key of a table.

Itzik Ben-Gan gives two alternative methods for generating sequence
numbers in the following article. They are both highly scalable
solutions and are intrinically better than IDENTITY if you DO need an
exposed sequence number. What you need to consider however, is that a
monotonically increasing number is often not a very user-friendly
solution. As Joe pointed out, many, many things have standard codes
available to you. Always look for those standards first.
http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html

If you have to design your own system of codes then don't just assign
them arbitrarily. Use some scheme that you can verify and then apply it
consistently. Create a data dictionary so that the codes are known and
can be shared between systems. The last thing you want is to implement
different keys for the same elements in different databases. Anyone
familiar with data integration projects can tell you the fantastic
value of standardising codes across your organization.

--
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]