views:

1682

answers:

5

Is using MS SQL Identity good practice in enterprise applications? Isn't it make difficulties in creating business logic, and migrating database from one to another?

+5  A: 

Yes.

They generally works as intended, and you can use the DBCC CHECKIDENT command to manipulate and work with them.

The most common idea of an identity is to provide an ordered list of numbers on which to base a primary key.

Edit: I was wrong about the fill factor, I didn't take into account that all of the inserts would happen on one side of the B-tree.

Also, In your revised question, you asked about migrating from one DB to another:

Identities are perfectly fine as long as the migrating is a one-way replication. If you have two databases that need to replicate to each other, a UniqueIdentifier column may be your best bet.

See: http://stackoverflow.com/questions/703035/when-are-you-truly-forced-to-use-uuid-as-part-of-the-design/703070#703070 for a discussion on when to use a UUID in a database.

John Gietzen
Good post John, but I mean using it in business logic, I edited the post now
ArsenMkrt
@John Gietzen: Excellent answer however do, keep in mind that the IDENTITY data type provides ordered numbering of inserts but sequential numbers are not guaranteed. For example, if an assigned IDENTITY value is rolled back during the insert process, that IDENTITY value cannot be re-used and is skipped.
John Sansom
Adding sequential numbers to a B-tree will give a fill factor of about 50%, and pages will split (always bottom right). But you could recreate to get near 100%
Henk Holterman
@Henk Ah, good point, I was overlooking that. @John Sansom I did realize that, thanks.
John Gietzen
+8  A: 

Yes, they work very well and are reliable, and perform the best. One big benefit of using identity fields vs non, is they handle all of the complex concurrency issues of multiple callers attempting to reserve new id's. This may seem like something trivial to code but it's not.

These links below offer some interesting information about identity fields and why you should use them whenever possible.

  1. http://stackoverflow.com/questions/186369/db-to-use-identity-column-or-not
  2. http://www.codeproject.com/KB/database/AgileWareNewGuid.aspx?display=Print
  3. http://www.sqlmag.com/Article/ArticleID/48165/sql_server_48165.html
James
+5  A: 

The question is always:

What are the chances that you're realistically going to migrate from one database to another? If you're building a multi-db app it's a different story, but most apps don't ever get ported over to a new db midstream - especially when they start out with something as robust as SQL Server.

The identity construct is excellent, and there's really very few reasons why you shouldn't use it. If you're interested, I wrote a blog article on some of the common myths surrounding identity values.

The IDENTITY Property: A Much-Maligned Construct in SQL Server

Aaron Alton
+10  A: 

Personally I couldn't live without identity columns and use them everywhere however there are some reasons to think about not using them.

Origionally the main reason not to use identity columns AFAIK was due to distributed multi-database schemas (disconnected) using replication and/or various middleware components to move data. There just was no distributed synchronization machinery avaliable and therefore no reliable means to prevent collisions. This has changed significantly as SQL Server does support distributing IDs. However, their use still may not map into more complex application controlled replication schemes.

They can leak information. Account ID's, Invoice numbers, etc. If I get an invoice from you every month I can ballpark the number of invoices you send or customers you have.

I run into issues all the time with merging customer databases and all sides still wanting to keep their old account numbers. This sometimes makes me question my addiction to identity fields :)

Like most things the ultimate answer is "it depends" specifics of a given situation should necessarily hold a lot of weight in your decision.

Einstein
+1 All good points to consider.
James
+1  A: 

Good article on identities, http://www.simple-talk.com/sql/t-sql-programming/identity-columns/

IMO, migrating to another RDBMS is rarely needed these days. Even if it is needed, the best way to develop portable applications is to develop a layer of stored procedures isolating your application from proprietary features:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/02/24/writing-ansi-standard-sql-is-not-practical.aspx

AlexKuznetsov