Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

An Identity Crisis Expand / Collapse
Author
Message
Posted Wednesday, March 16, 2005 10:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:43 PM
Points: 1,306, Visits: 778
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tKetsdever/anidentitycrisis.asp


Post #168263
Posted Wednesday, March 23, 2005 2:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, March 10, 2014 9:05 AM
Points: 421, Visits: 363

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/
Post #169444
Posted Wednesday, March 23, 2005 3:34 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 1:21 AM
Points: 73, Visits: 69

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
Post #169452
Posted Wednesday, March 23, 2005 6:32 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 3:55 PM
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. 

.

 

 

 

Post #169497
Posted Wednesday, March 23, 2005 7:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 1:21 AM
Points: 73, Visits: 69

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
Post #169517
Posted Wednesday, March 23, 2005 7:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 03, 2012 8:27 AM
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!!
Post #169524
Posted Wednesday, March 23, 2005 7:21 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, December 05, 2012 3:55 PM
Points: 442, Visits: 185

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

Post #169525
Posted Wednesday, March 23, 2005 7:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 30, 2006 2:07 AM
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.


Post #169529
Posted Wednesday, March 23, 2005 7:32 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 1:21 AM
Points: 73, Visits: 69

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
Post #169532
Posted Wednesday, March 23, 2005 8:00 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2008 9:13 AM
Points: 499, 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

Post #169543
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse