April 7, 2011 at 12:20 am
What are the pros and cons of using IDENTITY instead of handling it on our own? I belive that IDENTITY is they way to go but my collegue this we shall handle it on out own so it would be great with your experience on what is the advantages and disdvatanges for the different approches. I know that the real answer is that it depends but it owuld be great to know when to use which approach 🙂
April 7, 2011 at 2:56 am
If you go your own route, be aware there are many ways to get it wrong and very few ways to get it right. Wrong methods will result in either duplicate values or serious contention issues and/or deadlocks.
Sounds like a 'Not Invented Here' problem. Your colleague fond of rewriting code libraries too?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2011 at 5:26 am
There are a few situations where having your own mechanism for generating an identifier can make sense (distributed computing, stuff like that), but even then you'd probably b better off using GUIDs (despite their well-defined shortcomings). I'd use IDENTITY. It works. Flat out. You don't have to do anything extra to implement it, and its use is very well documented. Why build things you don't have to?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2011 at 5:57 am
Okey so if I got it right is IDENTITY best to use and if we need to export data beetween databases we can use GUID's to handle it? Sounds like a good solution to me.
But one concern I found though... We might in the future add support for other databases than SQL Server, nothing decided but it might be implemented. Maybe IDENTITY is not so good to use then, maybe it's better with our own mechanism in that case?
April 7, 2011 at 6:24 am
identity is still exactly what you want, even if you are combining multiple databases together, in my opinion.
One way is to use ranges for the seeds for the identities for different Locations. Miami uses 1-1M, Los angels uses 1,000,001 to 2M, etc.
I'd go with a change so the primary key would simply become a two part key though, instead of just the identity...maybe a new column with a default value of LocationName
so the Pk becomes (LocationName , IdentityCol) so if you have to add the two databases together, they can maintain the data relationships.
Lowell
April 7, 2011 at 6:26 am
If you're going to port to a different RDBMS than SQL Server, you'll have much bigger issues to deal with, most likely, than a few identity columns. If that's a concern, then go with GUIDs in the first place. Those are fully portable, even if you have to start generating them in the front end and storing them as binary data. (Generating them in the application has a few advantages, as well.)
I would avoid in-house "identity" code. Even if you build it completely correctly, it'll be more work than using GUIDs would have been, and it solves the same problems they do, but with more problems in most cases.
So I'd either use IDENTITY, or GUIDs. Both have advantages and disadvantages, so you'll need to dig into the differences a bit.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2011 at 6:32 am
I agree with everyone, but just want to add an additional caution about GUIDs. They really can be problematic, so be sure, really sure, you need them before you implement them. Don't go off of some vague "someday we'll support multiple database platforms" guess. I've heard those before and seen apps sit for 10 years on one platform.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 7, 2011 at 6:35 am
The only time I use my own incrementing instead of IDENTITY is when the business demands no gaps (ie caused by failed inserts with IDENTITY)
Far away is close at hand in the images of elsewhere.
Anon.
April 8, 2011 at 12:05 pm
If I need to generate a unique sequential integer IDs for transactional records (for example sale_id or customer_id), then I'll use an identity. I call that an instance ID. However, if I'm generating IDs for use as the primary key for reference data (for example discharge_reason_id or sales_region_id), then I'll define the column as a non-identity integer and use hard coded values in my table insert script. That way I don't run the risk of assinging different ID values when the table is re-inserted or deployed to another environment. If it's reference dataset from an external source, something like Zip Code or NAICS codes for business classification, then I'll use the standard alpha-numeric code as the primary key and not assign and integer ID at all.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply