IDENTITY discussion...again.
Check out Aaron Bertrand's blog on the bad habits of haphazardly throwing IDENTITY attribute on a numeric field and calling it a primary key at http://sqlblog.com/blogs/aaron_bertrand/archive/2010/02/08/bad-habits-to-kick-putting-an-identity-column-on-every-table.aspx. He has a pretty good chat on the topic going, and I actually have some thoughts on this as well so I might chime in and join the fun. Essentially what he's doing here is taking a middle of the road approach by saying that, OK we have IDENTITY property, but use it wisely. Just slapping an IDENTITY column on every table as the first order of business just doesn't make sense, and I completely agree. I'm actually getting away from using them if possible; maybe it's because my last couple of gigs have been JD Edwards applications, and it's really nice to 1) have natural keys, and 2) know that someone took the time to map them out. (On the other hand, I don't like how JDE uses columns when they could use rows...you get AAAAA1, AAAAA2, AAAAA3, etc., ad nauseum attributes rather than modeling them as records in a table).
I've worked on some transactional systems that do 1000-2000 transactions/second, real screamers, and I've also worked on data warehouses, currently what I am doing. To have IDENTITY on tables that load a warehouse is greatness; in a fast transactional system not so much, because you end up with a table that's 50 million rows with a PK of IDENTITY attribute on an int...what'cha gonna do with that? Those IDENTITY values don't mean anything! But, I"m also very quick to point that, hey, if it makes sense to put one on a table, then do it. I would never take a stance as some hardcore folks might (Celko for one) that they are an unnecessary evil to never, ever be used. It just doesn't make sense.
Lee
----------------------------
I wanna take you out to dinner, and then I wanna go back to my apartment and watch 'Kung Fu'. Do you ever watch 'Kung Fu'?
64665e89-8ee7-4e8e-acf1-6b79573d9f96|0|.0