December 20, 2017 at 8:33 pm
Comments posted to this topic are about the item IDENT_CURRENT()
December 21, 2017 at 12:11 am
Good back to basics question thanks Steve.
...
December 21, 2017 at 3:50 am
Nice one, thanks Steve.
However, it appears option 2 & 3 are exactly the same.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
December 21, 2017 at 3:52 am
Stewart "Arturius" Campbell - Thursday, December 21, 2017 3:50 AMNice one, thanks Steve.
However, it appears option 2 & 3 are exactly the same.
My bad - missed the "My" in the3rd option (quickly wiping egg off face).....
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
December 21, 2017 at 6:52 am
This must be a MS Question 🙂 One of those splitting hairs (in my opinion)
The difference between Highest value and Last value - REALLY ?
Thanks for making be think 🙂
December 21, 2017 at 8:14 am
Thanks for the instructive question.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 21, 2017 at 11:27 am
Budd - Thursday, December 21, 2017 6:52 AMThis must be a MS Question 🙂 One of those splitting hairs (in my opinion)
The difference between Highest value and Last value - REALLY ?
Thanks for making be think 🙂
Not really the same thing considering the identity column can be reseeded to values less than the initial value when the column was created.
CREATE TABLE [dbo].[testident] (
testidentId INT IDENTITY(1,1)
, testvalue INT
);
INSERT INTO [dbo].[testident]([testvalue])
VALUES (1),(2),(10),(25);
SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');
DBCC CHECKIDENT([testident], RESEED, -10);
INSERT INTO [dbo].[testident]([testvalue])
VALUES (100),(200),(1000),(2500);
SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');
December 21, 2017 at 11:46 am
Lynn Pettis - Thursday, December 21, 2017 11:27 AMBudd - Thursday, December 21, 2017 6:52 AMThis must be a MS Question 🙂 One of those splitting hairs (in my opinion)
The difference between Highest value and Last value - REALLY ?
Thanks for making be think 🙂Not really the same thing considering the identity column can be reseeded to values less than the initial value when the column was created.
CREATE TABLE [dbo].[testident] (
testidentId INT IDENTITY(1,1)
, testvalue INT
);INSERT INTO [dbo].[testident]([testvalue])
VALUES (1),(2),(10),(25);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');DBCC CHECKIDENT([testident], RESEED, -10);
INSERT INTO [dbo].[testident]([testvalue])
VALUES (100),(200),(1000),(2500);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');
TRUE that is a possibility.
and maybe it would happen, perhaps in bizarreO world 🙂
I've never seen the need and fail to understand - WHY would something like that be needed.
But, I get it, it can happen.
December 21, 2017 at 12:27 pm
Budd - Thursday, December 21, 2017 11:46 AMLynn Pettis - Thursday, December 21, 2017 11:27 AMBudd - Thursday, December 21, 2017 6:52 AMThis must be a MS Question 🙂 One of those splitting hairs (in my opinion)
The difference between Highest value and Last value - REALLY ?
Thanks for making be think 🙂Not really the same thing considering the identity column can be reseeded to values less than the initial value when the column was created.
CREATE TABLE [dbo].[testident] (
testidentId INT IDENTITY(1,1)
, testvalue INT
);INSERT INTO [dbo].[testident]([testvalue])
VALUES (1),(2),(10),(25);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');DBCC CHECKIDENT([testident], RESEED, -10);
INSERT INTO [dbo].[testident]([testvalue])
VALUES (100),(200),(1000),(2500);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');TRUE that is a possibility.
and maybe it would happen, perhaps in bizarreO world 🙂
I've never seen the need and fail to understand - WHY would something like that be needed.
But, I get it, it can happen.
Not necessarily in bizarreO world. I have read numerous threads asking about reseeding identity columns, so it happens more frequently than you may realize. Do I agree with doing it? That would depend on why it was being done.
December 21, 2017 at 2:57 pm
Lynn Pettis - Thursday, December 21, 2017 12:27 PMBudd - Thursday, December 21, 2017 11:46 AMLynn Pettis - Thursday, December 21, 2017 11:27 AMBudd - Thursday, December 21, 2017 6:52 AMThis must be a MS Question 🙂 One of those splitting hairs (in my opinion)
The difference between Highest value and Last value - REALLY ?
Thanks for making be think 🙂Not really the same thing considering the identity column can be reseeded to values less than the initial value when the column was created.
CREATE TABLE [dbo].[testident] (
testidentId INT IDENTITY(1,1)
, testvalue INT
);INSERT INTO [dbo].[testident]([testvalue])
VALUES (1),(2),(10),(25);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');DBCC CHECKIDENT([testident], RESEED, -10);
INSERT INTO [dbo].[testident]([testvalue])
VALUES (100),(200),(1000),(2500);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');TRUE that is a possibility.
and maybe it would happen, perhaps in bizarreO world 🙂
I've never seen the need and fail to understand - WHY would something like that be needed.
But, I get it, it can happen.Not necessarily in bizarreO world. I have read numerous threads asking about reseeding identity columns, so it happens more frequently than you may realize. Do I agree with doing it? That would depend on why it was being done.
Another scenario where IDENT_CURRENT does not equal the highest value - - If you roll back an insert, the identity value(s) that would have been used in the transaction are skipped over. My $0.02
December 21, 2017 at 3:05 pm
WILLIAM MITCHELL - Thursday, December 21, 2017 2:57 PMLynn Pettis - Thursday, December 21, 2017 12:27 PMBudd - Thursday, December 21, 2017 11:46 AMLynn Pettis - Thursday, December 21, 2017 11:27 AMBudd - Thursday, December 21, 2017 6:52 AMThis must be a MS Question 🙂 One of those splitting hairs (in my opinion)
The difference between Highest value and Last value - REALLY ?
Thanks for making be think 🙂Not really the same thing considering the identity column can be reseeded to values less than the initial value when the column was created.
CREATE TABLE [dbo].[testident] (
testidentId INT IDENTITY(1,1)
, testvalue INT
);INSERT INTO [dbo].[testident]([testvalue])
VALUES (1),(2),(10),(25);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');DBCC CHECKIDENT([testident], RESEED, -10);
INSERT INTO [dbo].[testident]([testvalue])
VALUES (100),(200),(1000),(2500);SELECT * FROM [dbo].[testident] AS [t];
SELECT IDENT_CURRENT('dbo.testident');TRUE that is a possibility.
and maybe it would happen, perhaps in bizarreO world 🙂
I've never seen the need and fail to understand - WHY would something like that be needed.
But, I get it, it can happen.Not necessarily in bizarreO world. I have read numerous threads asking about reseeding identity columns, so it happens more frequently than you may realize. Do I agree with doing it? That would depend on why it was being done.
Another scenario where IDENT_CURRENT does not equal the highest value - - If you roll back an insert, the identity value(s) that would have been used in the transaction are skipped over. My $0.02
And that is a more likely scenario.
December 29, 2017 at 5:48 am
nice question to learn new things....😎😎😎😎
Manik
You cannot get to the top by sitting on your bottom.
January 2, 2018 at 8:48 am
WILLIAM MITCHELL - Thursday, December 21, 2017 2:57 PMAnother scenario where IDENT_CURRENT does not equal the highest value - - If you roll back an insert, the identity value(s) that would have been used in the transaction are skipped over. My $0.02
Yet another scenario is where a row is simply deleted.
An possible but unlikely scenario is that the row is transferred from current data to historical data, so that it's no longer in the current table - I don't really see this happening with the most recently assigned identity, but it is probably possible in the scrambled mess that is a typical database designed by non-data people.
Tom
January 2, 2018 at 9:37 pm
TomThomson - Tuesday, January 2, 2018 8:48 AMWILLIAM MITCHELL - Thursday, December 21, 2017 2:57 PMAnother scenario where IDENT_CURRENT does not equal the highest value - - If you roll back an insert, the identity value(s) that would have been used in the transaction are skipped over. My $0.02Yet another scenario is where a row is simply deleted.
An possible but unlikely scenario is that the row is transferred from current data to historical data, so that it's no longer in the current table - I don't really see this happening with the most recently assigned identity, but it is probably possible in the scrambled mess that is a typical database designed by non-data people.
This i can relate to - we recently "inherited" such a database from an analyst that resigned - no-one even knew about this till now....
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply