July 5, 2011 at 4:25 pm
Hey Guys..
Is there any way to reset the identity key to 1 without using DBCC CHECKIDENT? I have a temp table which I use twice by deleting the data from it..so after deleting it for the first time and again reloading it, the identity column gives me weird values..and I am not allowed to use DBCC CHECKIDENT coz of the company rules..so is there any other way to reset it?
July 5, 2011 at 4:27 pm
truncate should the the trick.
drop / recreate too.
2nd temp table.
row_number instead of identity.
The choices are many :w00t:.
Drop will cause recompiles which may or may not be an issue depending of a slue of factors so I'd use that option only has last resort.
July 5, 2011 at 4:28 pm
identity insert too :w00t:.
ok I think you have enough :hehe:.
July 5, 2011 at 4:35 pm
Truncate instead of delete will set the identity back to 0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2011 at 4:38 pm
GilaMonster (7/5/2011)
Truncate instead of delete will set the identity back to 0.
Sorry miss (mam?), I spoke out of turns :-D.
July 5, 2011 at 4:46 pm
GilaMonster (7/5/2011)
Truncate instead of delete will set the identity back to 0.
Narm?
http://msdn.microsoft.com/en-us/library/aa260621(v=SQL.80).aspx
The counter used by an identity for new rows is reset to the seed for the column.
And, not exactly, post truncate in 2k8:
Checking identity information: current identity value 'NULL', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
IF OBJECT_id( 'testid') IS NOT NULL
DROP TABLE TestID
GO
CREATE TABLE testID ( tID INT IDENTITY( 1, 1), somedata varchar(5))
GO
DBCC CHECKIDENT ("testid", NORESEED)
GO
INSERT INTO testID (somedata) VALUES ( 'abc')
GO
SELECT * FROM testid
GO
DBCC CHECKIDENT ("testid", NORESEED)
GO
TRUNCATE TABLE testid
GO
DBCC CHECKIDENT ("testid", NORESEED)
GO
However, in either case, truncate allows the numbering seed, if originally set to be 1, to restart at 1, thus still a valid answer.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2011 at 4:51 pm
Pedantic....
😉 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 5, 2011 at 4:58 pm
GilaMonster (7/5/2011)
Pedantic....😉 😀
Craig or me? :w00t:
July 5, 2011 at 5:08 pm
Ninja's_RGR'us (7/5/2011)
GilaMonster (7/5/2011)
Pedantic....😉 😀
Craig or me? :w00t:
That'd be me. I pulled a brainfart and thought from your response she was commenting on your stuff, not answering the question long after responses were put up... when I do the same thing leaving a window open for over an hour. 😀
I shall now offer a facepalm to myself, because at this point I should have known Gail better... :blush:
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2011 at 5:12 pm
Craig Farrell (7/5/2011)
Ninja's_RGR'us (7/5/2011)
GilaMonster (7/5/2011)
Pedantic....😉 😀
Craig or me? :w00t:
That'd be me. I pulled a brainfart and thought from your response she was commenting on your stuff, not answering the question long after responses were put up... when I do the same thing leaving a window open for over an hour. 😀
I shall now offer a facepalm to myself, because at this point I should have known Gail better... :blush:
Sorry guys, I'll stop using the force... it's too much for ya'll :-P.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply