All data types have their limits. This crisis is nothing specific to Identity columns. It is related to lack of upfront planning.
Additionally, Microsoft always recommends us to use Information_Schema views and functions to get system data. IDENT_CURRENT('table_name') will return the current identity value (if available. If the table doesn't have an identity column, the return value is NULL).
It looks like the author has done some changes to system entries to make the column BigInt. Unless he shares something on that area, this article is not useful.
I agree - the title of this article should have been 'The Importance Of Choosing Realistic Data Types When Designing Your Schema'. I'd seriously consider 're-allocating' whoever designed the table with 'int' in the first place as they clearly have no idea about the business.
The best designers/DBAs are ones that fundamentally understand what their company's business is all about because it means that they will intrinsically factor that knowledge into every stage of the design process.
Identity columns are simply an efficient way of generating unique keys and I'm a little tired of seeing them demonized when the real issues lie elsewhere.
Whether you want to argue that the DB shouldn't have been designed that way is a mute point. DBAs often have to clean up what others do and sometimes even make mistakes themselves - or least all the DBAs I know have at some point made at least 1 mistake. Things that are easy are often overlooked and I think because identity is easy, this situation is not unrealistic. I thought the article was a concise and easy to read warning to be wary of identities lurking in your DB because they are defined on integers.
.
I completely disagree - are you really suggesting that use of identity columns is somehow a greater evil that not designing your database properly?? I'm not suggesting that DBAs become flawless human beings and never make mistakes but designing a new schema or table isn't something that you do hundreds of times a day. It is something you do relatively infrequently and, because it can have large consequences down the road, should be given the appropriate time and effort to get right. I also cannot believe that you are using the ease of defining an identity as a negative point.
Would you rather that Microsoft remove the feature and just let developers hand code a method of doing it? Of course not.
The issue described is purely about the column datatype chosen not how the value is generated. The identity column is not the problem the datatype is. Simple as that.
I appreciate that we have to clean up after other people mistakes, god knows I've seen more than enough of them, but to suggest looking only at identity columns is ridiculous. What if the column is an integer and it's value is calculated by doing some MAX() + 1 function (not an approach anyone would recommend yet often employed). The issue still exists i.e. the column will run out of space to store the number and yet will never be picked up by monitoring columns which have the identity attribute defined.
This article should have concluded by recommending that DBAs simply monitor integer primary keys and the current maximum values. That way, whatever method is used to arrive at the arbitrary value, you will still pick up on a disaster waiting to happen.
I understand your point. But, I think you were harsh on the writer. I appreciated his point of view.
Sorry Jane I wasn't trying to slate the entire article per se, (honest!) - just the title and conclusion. It clearly has some useful points regarding the easy reporting of identity columns etc. and the general theme of averting disaster before it happens is laudible.
It's just that I have heard too many conversations amongst us techies where technical nuances (use of identity) detract from the real underlying issue (wrong datatype). I wish people would always attempt to understand the root causes of the many problems that plague us in our day to day jobs and not just try to firefight the symptoms.
Cure The Problem Not The Symptoms...er...Amen
The problem that the author had to fix had nothing to do with identity columns. This could have been a regular int column that got its new numbers via time-tested "max(colval) + 1" and it still would have run into the same issue. It's funny how identities have somehow gotten this bad rap. I've seen it everywhere I've gone in my career. In reality, identity columns are awesome when the DBA knows how to design them into the schema to match the business logic and expected record-load. They save a ton of index-scans (or table scans for some DBAs who use poorly designed indexes).
On the flip side, it is good the author featured a number of ways to list the identity columns in the database, which is helpful. The fix must have been easy, right? I mean, you can alter an int identity up to a bigint in one statement.
-Vic
