Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


An Identity Crisis


An Identity Crisis

Author
Message
cs_troyk
cs_troyk
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1529 Visits: 965
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tKetsdever/anidentitycrisis.asp



G.R.Prithiviraj Kulasingham
G.R.Prithiviraj Kulasingham
SSC-Addicted
SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)SSC-Addicted (427 reputation)

Group: General Forum Members
Points: 427 Visits: 367

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.



Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/
Malcolm Leach
Malcolm Leach
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 71

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.



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
Jane Matheson-154829
Jane Matheson-154829
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 185

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.

.


Malcolm Leach
Malcolm Leach
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 71

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.



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
ubi_comp
ubi_comp
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 3
Excellent Article Troy,

I was looking for some way to get *all* the identity columns and their values. Ended up reading about SQLDMO, but suddenly your article made it simple and quick to get all the identity column names, their table names and values...

Thanks!!
Jane Matheson-154829
Jane Matheson-154829
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 185

I understand your point. But, I think you were harsh on the writer. I appreciated his point of view.


Aaron Myers
Aaron Myers
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 1
Agreed...it would have been interesting to see his schema/logic modifications and cleanup sequence. As is, the article isn't very useful.



Malcolm Leach
Malcolm Leach
SSC Journeyman
SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)SSC Journeyman (85 reputation)

Group: General Forum Members
Points: 85 Visits: 71

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



Malcolm
DB Ghost - Build, compare and synchronize from source control = Database Change Management for SQL Server
www.dbghost.com
Vic Kirkpatrick-173212
Vic Kirkpatrick-173212
Mr or Mrs. 500
Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)Mr or Mrs. 500 (501 reputation)

Group: General Forum Members
Points: 501 Visits: 76

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search