January 23, 2019 at 9:05 pm
Comments posted to this topic are about the item The Jumping Identity
January 23, 2019 at 10:20 pm
Nice question, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
January 24, 2019 at 1:38 am
Ouch!
Yes, the answer is obvious but I managed to get it wrong...
January 24, 2019 at 4:03 am
Dear sir
I got answer as 21
January 24, 2019 at 9:15 am
ravikbpspl - Thursday, January 24, 2019 4:03 AMDear sirI got answer as 21
That is incorrect.
January 24, 2019 at 9:22 am
was convinced i knew what the answer was going to be....i did not,
good question
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
January 24, 2019 at 12:27 pm
January 25, 2019 at 6:17 am
To be honest, I got tripped up by misunderstanding the info on the DBCC CHECKIDENT online page: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017. "SQL Server 2008 R2 and earlier" and my brain went on the wrong direction of the timeline (I tested on SQL 2016 after I picked the wrong answer and then saw my error).
January 31, 2019 at 1:00 pm
Bob Razumich - Friday, January 25, 2019 6:17 AMTo be honest, I got tripped up by misunderstanding the info on the DBCC CHECKIDENT online page: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql?view=sql-server-2017. "SQL Server 2008 R2 and earlier" and my brain went on the wrong direction of the timeline (I tested on SQL 2016 after I picked the wrong answer and then saw my error).
Hi there. Actually, the documentation is quite incorrect about there being a behavior change between the versions. There is no change in behavior. the documentation is also missing the fact that no rows due to a DELETE operation will make the next inserted row get "new_seed_value" + "increment value", not simply "new_seed_value". For example, try the following:
IF (OBJECT_ID(N'tempdb..#SalesOrderHeader') IS NOT NULL)
BEGIN
DROP TABLE #SalesOrderHeader;
END;
CREATE TABLE #SalesOrderHeader
( OrderKey INT IDENTITY(1, 7)
, CustomerName VARCHAR(30)
)
GO
DBCC CHECKIDENT('#SalesOrderHeader', RESEED, 55)
INSERT #SalesOrderHeader (CustomerName) VALUES ('Andy')
INSERT #SalesOrderHeader (CustomerName) VALUES ('Brian')
INSERT #SalesOrderHeader (CustomerName) VALUES ('Steve')
SELECT * FROM #SalesOrderHeader;
GO
DELETE FROM #SalesOrderHeader;
DBCC CHECKIDENT('#SalesOrderHeader', RESEED, 55)
INSERT #SalesOrderHeader (CustomerName) VALUES ('Andy')
INSERT #SalesOrderHeader (CustomerName) VALUES ('Brian')
INSERT #SalesOrderHeader (CustomerName) VALUES ('Steve')
SELECT * FROM #SalesOrderHeader;
GO
TRUNCATE TABLE #SalesOrderHeader;
DBCC CHECKIDENT('#SalesOrderHeader', RESEED, 55)
INSERT #SalesOrderHeader (CustomerName) VALUES ('Andy')
INSERT #SalesOrderHeader (CustomerName) VALUES ('Brian')
INSERT #SalesOrderHeader (CustomerName) VALUES ('Steve')
SELECT * FROM #SalesOrderHeader;
GO
The first row in those 3 results sets is: 55, 62, and 55. The two "55" rows were when the table hadn't had any inserts yet, and after the TRUNCATE TABLE. But after the DELETE, the initial value is 62.
So the explanation of the correct answer for this question is incorrect, or at least misleading, when saying "because there are no rows in the table". The real reason is: "because there never were any rows in the table..."
I just posted about it here:
How Does DBCC CHECKIDENT Really Work When Resetting the Identity Seed (RESEED)?
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply