An Identity Crisis
-
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
-
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.
-
If you're that concerned, perhaps you should use NUMERIC(38, 0) ... how many billions of years will that give you ?
--
Adam Machanic
whoisactive -
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
-
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.
-
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.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
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 O
join syscolumns C on (C.id=O.id)
join systypes T on (T.xtype=C.xtype)
where COLUMNPROPERTY(O.id, C.name, 'IsIdentity')=1
and O.type = 'U'
-
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
-
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.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
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') = 1
order 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.
-
... 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)
)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
And if you need even MORE, you can use the large number libraries posted on my blog
--
Adam Machanic
whoisactive -
Haha, for qualifying this as a shameless plug, you forgot to post the URL.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
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_NAME
WHERE T.TABLE_TYPE = 'BASE TABLE'
AND COLUMNPROPERTY (OBJECT_ID(T.TABLE_SCHEMA + '.' + T.TABLE_NAME), C.COLUMN_NAME, 'IsIdentity') = 1
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME, C.COLUMN_NAME
--Peter
-
It wasn't a shameless plug -- I would hope that no one would ever actually use it!
--
Adam Machanic
whoisactive
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply