SQLServerCentral / Discuss Content Posted by Troy Ketsdever / Article Discussions / Article Discussions by Author / An Identity Crisis / Latest Posts

RE: An Identity Crisis

Douglas Osborne-456728 — Mon, 27 Oct 2008 08:49:43 GMT

Adam,He had it on his site - #18SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GOCREATE FUNCTION dbo.udf_SQL_DataTypeString ( @BaseDataType nvarchar(128) -- base name like int, numeric , @Character_Maximum_Length int , @Numeric_Precision int , @Numeric_Scale int) RETURNS nvarchar(24) -- Data type name like 'numeric (15, 3)' WITH SCHEMABINDING/** Returns a data type with full length and precision information* based on fields originally queried from * INFORMATION_SCHEMA.ROUTINES or from SQL_VARIANT_PROPERTIES. * This function is intended to help when reporting on functions * and about data.** Example:SELECT ROUTINE_NAME as [Function] , dbo.udf_SQL_DataTypeString (Data_Type , Character_Maximum_Length, Numeric_Precision, Numeric_Scale) as [Data Type] FROM information_schema.routines WHERE ROUTINE_TYPE='FUNCTION'** © Copyright 2003 Andrew Novick http://www.NovickSoftware.com* You may use this function in any of your SQL Server databases* including databases that you sell, so long as they contain * other unrelated database objects. You may not publish this * UDF either in print or electronically.* Published in T-SQL UDF of the Week Newsletter Vol 1 #18http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm****************************************************************/AS BEGINRETURN LTRIM(RTRIM(@BaseDataType)) + CASE WHEN @BaseDataType in ('char', 'varchar' , 'nvarchar', 'nchar') THEN '(' + CONVERT (varchar(4) , @Character_Maximum_Length) + ')' WHEN @BaseDataType in ('numeric', 'decimal') THEN '(' + Convert(varchar(4), @Numeric_Precision) + ' ' + convert(varchar(4), @Numeric_scale) + ')' ELSE '' -- empty string ENDENDGRANT EXEC, REFERENCES ON dbo.udf_SQL_DataTypeString to [PUBLIC]GO

RE: An Identity Crisis

Adam Machanic — Sun, 02 Apr 2006 13:32:00 GMT

Hi Andrew,I think we also need to see udf_SQL_DataTypeString to make that UDF work.

RE: An Identity Crisis

Andrew Novick — Sun, 02 Apr 2006 06:54:00 GMT

There's no need to "cheat" and use the syscolumns and sysobjects tables. While INFORMATION_SCHEMA doesn't have the information on identity columns, they OBJECTPROPERTY(<objectid>, 'TableHasIdentity') function tells you which tables have an identity column and you can use it in the WHERE clause to filter the tables from INFORMATION_SCHEMA. You can also use the COLUMNPROPERTY function to test each column.

I see that a previous post identified that also. Instead of keeping around a script, I try and code scripts into User-Defined Fucntions (UDF)s so that they're always in the database. Mine follows. You'll also need the function udf_SQL_DataTypeString from here:

http://www.novicksoftware.com/UDFofWeek/Vol1/T-SQL-UDF-Volume-1-Number-18-udf_SQL_DatatypeString.htm

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

CREATE FUNCTION [dbo].[udf_Tbl_IdentityTAB] ( @Table_Name_Pattern sysname = NULL -- Pattern for table -- names to include or NULL for all) RETURNS TABLE -- No SCHEMABINDING due to use of INFORMATION_SCHEMA/* * Returns a table of information about the identity columns* in tables including their column, data type, and the * seed, increment, and current value.** Example:select * FROM dbo.udf_Tbl_IdentityTAB(default)* History:* When Who Description* ------------- ------- -----------------------------------------* 2006-04-02 anovick Added IsMsShipped test* 2003-04-02 anovick Initial Coding** © Copyright 2002, 2006 Andrew Novick http://www.NovickSoftware.com* You may use this function in any of your SQL Server databases* including databases that you sell, so long as they contain * other unrelated database objects. You may not publish this * UDF either in print or electronically except where posted by the author.****************************************************************/AS RETURNSELECT TOP 100 PERCENT WITH TIES T.TABLE_SCHEMA , T.TABLE_NAME , COALESCE(C.COLUMN_NAME, '<not available to you>') as [COLUMN_NAME] , C.DATA_TYPE as DATA_TYPE , IDENT_SEED(T.TABLE_NAME) AS Seed , IDENT_INCR(T.TABLE_NAME) AS Increment , IDENT_CURRENT(T.TABLE_NAME) [Current_Value] FROM INFORMATION_SCHEMA.TABLES T LEFT OUTER JOIN ( -- LOJ - COLUMN SECURITY COULD LIMIT ACCESS SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME , ORDINAL_POSITION , dbo.udf_SQL_DataTypeString (DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , NUMERIC_PRECISION , NUMERIC_SCALE) AS DATA_TYPE FROM INFORMATION_SCHEMA.[COLUMNS] WHERE 1=COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME) , COLUMN_NAME , 'IsIdentity') ) C ON T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_NAME = C.TABLE_NAMEWHERE T.TABLE_TYPE = 'BASE TABLE'

AND (@Table_Name_Pattern IS NULL OR T.TABLE_NAME LIKE @Table_Name_Pattern) AND 1= OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME), 'TableHasIdentity') AND NOT 1=OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME) , 'IsMsShipped')ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME

RE: An Identity Crisis

cs_troyk — Thu, 23 Mar 2006 11:23:00 GMT

Nice, Ravi!

I agree -- I don't like to query the sytem tables directly unless absolutely necessary. This is a slick solution -- obviously it didn't occur to me to check the system functions.

TroyK

RE: An Identity Crisis

Ravi Prashanth Lobo-275382 — Thu, 23 Mar 2006 05:49:00 GMT

I use the following script to get the tables having identity values....

SELECT table_name ,column_name ,IDENT_CURRENT(table_name) as Identity_value ,data_type FROM information_schema.columns WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,'IsIdentity')=1ORDER BY table_name

Why bother with system tables, when you can get the same with schema view and functions?

RE: An Identity Crisis

cs_troyk — Mon, 28 Mar 2005 11:36:00 GMT

For those still interested, I whipped up a script to get all of the maximum values for any columns within a database that are defined as integers. (It should be easy to modify if you're interested in, say, smallints or tinyints).

I'm not normally fond of cursors, but I figured it would be alright as this is a "toolbox" type of script, and not necessarily something that you would want to incorporate into a stored proc, for instance.

If anyone sees improvements that can be made, have at it.

http://www.sqlservercentral.com/scripts/contributions/1468.asp

TroyK

RE: An Identity Crisis

Adam Machanic — Thu, 24 Mar 2005 07:58:00 GMT

It wasn't a shameless plug -- I would hope that no one would ever actually use it!

RE: An Identity Crisis

Peter DeBetta — Thu, 24 Mar 2005 07:58:00 GMT

A better version of the code from my previous post

SELECT T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, IDENT_CURRENT(T.TABLE_SCHEMA + '.' + T.TABLE_NAME) [CURRENT_IDENTITY_VALUE]FROM INFORMATION_SCHEMA.TABLES AS T (NOLOCK) INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C (NOLOCK) ON C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAMEWHERE T.TABLE_TYPE = 'BASE TABLE' AND COLUMNPROPERTY (OBJECT_ID(T.TABLE_SCHEMA + '.' + T.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME

--Peter

RE: An Identity Crisis

Frank Kalis — Thu, 24 Mar 2005 07:54:00 GMT

Haha, for qualifying this as a shameless plug, you forgot to post the URL.

RE: An Identity Crisis

Adam Machanic — Thu, 24 Mar 2005 07:46:00 GMT

And if you need even MORE, you can use the large number libraries posted on my blog

RE: An Identity Crisis

Frank Kalis — Thu, 24 Mar 2005 07:43:00 GMT

... and if that still isn't enough, and you know in advance that you need to keep a row for almost every single grain of sand in the Sahara, you can do something like:

create table decimal_t( col1 decimal(38,0) identity(-99999999999999999999999999999999999999, 1))

RE: An Identity Crisis

Jane Matheson-154829 — Thu, 24 Mar 2005 06:56:00 GMT

The following way to find indentities is returning invalid results:

SELECT table_name, column_name, ordinal_position orgPostion, data_type FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) where COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity') = 1order by table_name, column_name

It returns an integer field from a view as an identity. The source of this view is a table in another database. The base table does not have an identity defined. The field is an "int not null."

You will need to filter out views.

RE: An Identity Crisis

G.R.Prithiviraj Kulasingham — Wed, 23 Mar 2005 21:01:00 GMT

My apologies... Just a few minutes back I found another area where we need to access system tables: to identify current identity values on linked server. you article is usefull in that ara, as most of the system functions won't work across servers.

RE: An Identity Crisis

cs_troyk — Wed, 23 Mar 2005 20:47:00 GMT

It's true that the problem can crop up on non-identity columns. I acknowledged that in a previous post on this thread.

However, I think most autoincrement solutions use the Identity property as a means of implementation. Therefore, the problem of an autoincrementing column overflowing the datatype's boundaries is most likely to occur on an Identity column.

I have completed and submitted a script which will grab the maximum value for all integer columns on user tables within a database. Once the script has undergone the sqlservercentral.com vetting process, I'll post the link.

TroyK

RE: An Identity Crisis

Michael Fleming — Wed, 23 Mar 2005 20:08:00 GMT

The is also a SQLServer function called COLUMNPROPERTY that you can use to find Identity colums. Try the following :

Select O.name as [Table], C.name as [Column], T.name as [Type]from sysobjects Ojoin syscolumns C on (C.id=O.id)join systypes T on (T.xtype=C.xtype) where COLUMNPROPERTY(O.id, C.name, 'IsIdentity')=1and O.type = 'U'

RE: An Identity Crisis

G.R.Prithiviraj Kulasingham — Wed, 23 Mar 2005 19:54:00 GMT

I mentioned 3 points... Unfortunately all negative points:

1. The crisis is nothing to do with Identity. It is regarding DB design. I believe all agree on that.

2. This is about the various ways you can identify identity values. Unless we have a performance issue or do are planning to modify the system entries, using system functions is the best way to retrieve the identity value. This point leads to the third argument.

3. I am sure Performance is not an issue in identifying the identity column, and current value. Is the author planning to change the values? Yes. If he is going to manipulate system tables, those information are useful. Otherwise what’s the point of accessing system tables? That is my final argument. How these system table values helped him to change the data type. Unless he specifies that, that part is not useful.

I am sorry, The article isn't useful to me.

RE: An Identity Crisis

dMacey — Wed, 23 Mar 2005 16:07:00 GMT

hmm, more than 1?

My point was meant to be that the problem initiated from bad design and planning (particularly relating to the maximum size of the chosen data type and the amount of rows produced), such assumptions probably caused the problem in the first place.

RE: An Identity Crisis

cs_troyk — Wed, 23 Mar 2005 15:53:00 GMT

dMacey;

I think you're right... the 6-9 billion may have stuck in my head because the team was joking about how maybe we should seed the value at -2^63 to get double the years. All I know is that I'm glad I'm not going to be the one responsible for converting the column to a hugeint when the time comes!

TroyK

RE: An Identity Crisis

Adam Machanic — Wed, 23 Mar 2005 15:38:00 GMT

If you're that concerned, perhaps you should use NUMERIC(38, 0) ... how many billions of years will that give you ?

RE: An Identity Crisis

dMacey — Wed, 23 Mar 2005 15:36:00 GMT

The author mentioned "the table should now have no problems for the next 6 to 9 Billion years"

if the usage of the table was to remain the same would the following not be true?

max value for signed int = ((2 ^ 32) / 2) - 1 = 2147483647

rows per year = (rowcount / monthsInOperation) * 12 = (2147483647 / 8) * 12 = 3221225471

-> rowcount chosen as max value of signed int cause it was the datatype to overflow and monthsInOperation from artical

max value of signed bigint = ((2 ^ 64) / 2) - 1 = 9.22337E+18

number of years = max value of signed bigint / rows per year = 9.22337E+18 / 3221225471 = 2863311532

a mere 2.8 Billion years (yea I know someone else will have my job by then), somewhat less than the 6 to 9 that the author claimed

If we are talking about understanding the demands on our data types and choosing them wisely it seems such calculations should be done. I can imagine the designer thinking int would be more than enough, don't do the same with again by not taking into account such simple calculations.

RE: An Identity Crisis

David Lu — Wed, 23 Mar 2005 14:31:00 GMT

I used David Bird's code and modified a bit as follows and am happy with it.

SELECT table_name, column_name, ordinal_position orgPostion, data_type FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) where COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity') = 1

RE: An Identity Crisis

Adam Machanic — Wed, 23 Mar 2005 12:01:00 GMT

Vic,Using max(colval) + 1 is probably not a good idea. If two sessions hit it simultaneously, you're going to get a collision unless you serialize access. So you need to choose between scalability and either duplicate rows or constraint violations. That's one of the main benefits that IDENTITY provides.

RE: An Identity Crisis

David Lu — Wed, 23 Mar 2005 11:56:00 GMT

Excellent! Now I know how to find that kind field quickly with 3 or more options.

In my mind, I hate to use identity 1. you must have other alternative key to ensure the uniquness of the row, otherwise, you could have a million same contents records except the identity column. 2. however, it might be useful in a situation that there are 3 or more concatenated pk. then, an identity field is ok. In summary, my max idenetity column is 34 million, still have room to grow. but to change 2300 databases from int to bigint to stop the 7days operation might be a big issue. In addition, the FK is also a issue.

Good job, Troy Ketsdever

-D

RE: An Identity Crisis

cs_troyk — Wed, 23 Mar 2005 11:01:00 GMT

Hi Malcolm;

Your comments (and everyone else who expressed similar comments) about this not being an Identity-specific problem are absolutely correct. I focused on the Identity columns in particular because I found it interesting that there were multiple ways of getting at the same piece of metadata. And now, with some of the other responses, I have a couple more ways to find Identities.

It is also correct to say that a comprehensive look at the problem of inappropriate datatype choices is beyond the scope of the article's conclusion. I can see that perhaps I should have been more general in that respect, but, hey, this is my first crack at a tech article.

So I'm interested to further discuss anyone's ideas about how this could be generalized a bit. Sticking with integers only, what would you do if you were a consultant brought in to "check the health" of an enterprise system? How would we do a comprehensive check of integer values accross all tables? How would we focus in on those tables most likely to cause problems? Is it something that could be done in a single script, or would we need to do some kind of script->code generation->script solution?

I'll give this some thought and post ideas later this evening. I look forward to seeing what others come up with, too!

TroyK

RE: An Identity Crisis

Peter DeBetta — Wed, 23 Mar 2005 09:23:00 GMT

Hear! Hear!

Microsoft recommends that you use the Information Schema views instead of the system tables when possible. To quote SQL Server 2000 Books Online: "To obtain meta data, use system stored procedures, system functions, or these system-supplied [Information Schema] views only. Querying the system tables directly may not provide accurate information if system tables are changed in future releases."

Also, I noticed that schema (object ownership) was not addressed and if a table exists with multiple owners (Bob.MyTable, Jane.MyTable, dbo.MyTable) then the IDENT_CURRENT results are inaccurate. In order to utilize the Information Schema views and account for the multiple table ownership, you need to query as follows:

SELECT T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,  IDENT_CURRENT(T.TABLE_SCHEMA + '.' + T.TABLE_NAME) [CURRENT_IDENTITY_VALUE]FROM INFORMATION_SCHEMA.TABLES AS T (NOLOCK)  INNER JOIN     (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE     FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)     GROUP BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE     HAVING MAX(COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity')) = 1) AS C  ON C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME

Later,

Peter

RE: An Identity Crisis

David Bird — Wed, 23 Mar 2005 08:10:00 GMT

I've used the COLUMNPROPERTY to Identify tables with Identity columns.

SELECT Distinct Table_Name,MAX(COLUMNPROPERTY (OBJECT_ID(Table_Name),Column_Name,'IsIdentity')) as Id FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)GROUP BY Table_Name

RE: An Identity Crisis

Vic Kirkpatrick-173212 — Wed, 23 Mar 2005 08:00:00 GMT

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

RE: An Identity Crisis

Malcolm Leach — Wed, 23 Mar 2005 07:32:00 GMT

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

RE: An Identity Crisis

Aaron Myers — Wed, 23 Mar 2005 07:30:00 GMT

Agreed...it would have been interesting to see his schema/logic modifications and cleanup sequence. As is, the article isn't very useful.

RE: An Identity Crisis

Jane Matheson-154829 — Wed, 23 Mar 2005 07:21:00 GMT

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

RE: An Identity Crisis

ubi_comp — Wed, 23 Mar 2005 07:19:00 GMT

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!!

RE: An Identity Crisis

Malcolm Leach — Wed, 23 Mar 2005 07:07:00 GMT

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.

RE: An Identity Crisis

Jane Matheson-154829 — Wed, 23 Mar 2005 06:32:00 GMT

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.

RE: An Identity Crisis

Malcolm Leach — Wed, 23 Mar 2005 03:34:00 GMT

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.

RE: An Identity Crisis

G.R.Prithiviraj Kulasingham — Wed, 23 Mar 2005 02:33:00 GMT

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.

An Identity Crisis

cs_troyk — Wed, 16 Mar 2005 22:19:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/tKetsdever/anidentitycrisis.asp