May 10, 2011 at 11:36 am
lemon squeezy nice and easy
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 7:33 am
Not so easy for me -- I got it wrong by picking 13. I thought that despite reseeding the INSERT would add one to the current max ID in the table (12), but from the discussion it seems that that would happen only if there were a primary key on the table. Is that correct?
Thanks,
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
May 24, 2011 at 7:46 am
webrunner (5/24/2011)
Not so easy for me -- I got it wrong by picking 13. I thought that despite reseeding the INSERT would add one to the current max ID in the table (12), but from the discussion it seems that that would happen only if there were a primary key on the table. Is that correct?Thanks,
webrunner
No, reseeding only changes the current value, the primary key will not automatically bump it up to the max+1, it just won't let you add the value to the table until it becomes max+1 (because of duplicate checks)
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
May 24, 2011 at 7:52 am
jcrawf02 (5/24/2011)
webrunner (5/24/2011)
Not so easy for me -- I got it wrong by picking 13. I thought that despite reseeding the INSERT would add one to the current max ID in the table (12), but from the discussion it seems that that would happen only if there were a primary key on the table. Is that correct?Thanks,
webrunner
No, reseeding only changes the current value, the primary key will not automatically bump it up to the max+1, it just won't let you add the value to the table until it becomes max+1 (because of duplicate checks)
Oh, OK, thanks. So if there isn't a primary key, and the column is reseeded to 2 where an ID of 12 is already in the table, is that sort of a ticking time bomb? For example, when rows are added and the ID gets to 11 and one more row is added?
Thanks again,
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
May 24, 2011 at 9:33 am
webrunner (5/24/2011)
Oh, OK, thanks. So if there isn't a primary key, and the column is reseeded to 2 where an ID of 12 is already in the table, is that sort of a ticking time bomb? For example, when rows are added and the ID gets to 11 and one more row is added?
Yep. That's exactly what was discussed about five posts back. See FargOUt's reply to Steve Malone just a bit above your own first. IDENTITY simply provides an incremented value. Any uniqueness or other constraints are a separate issue.
May 24, 2011 at 9:35 am
john.arnott (5/24/2011)
webrunner (5/24/2011)
Oh, OK, thanks. So if there isn't a primary key, and the column is reseeded to 2 where an ID of 12 is already in the table, is that sort of a ticking time bomb? For example, when rows are added and the ID gets to 11 and one more row is added?Yep. That's exactly what was discussed about five posts back. See FargOUt's reply to Steve Malone just a bit above your own first. IDENTITY simply provides an incremented value. Any uniqueness or other constraints are a separate issue.
Great. Thanks again.
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
May 24, 2011 at 9:55 am
Code to show duplicate values, without constraint (SS2K8R2)
DROP TABLE dbo.MyTable
CREATE TABLE mytable
( id INT IDENTITY(1,1)
, mychar VARCHAR(10)
)
GO
INSERT mytable SELECT 'A'
INSERT mytable SELECT 'B'
INSERT mytable SELECT 'C'
SELECT * FROM dbo.MyTable
SET IDENTITY_INSERT dbo.MyTable ON
GO
INSERT dbo.MyTable
( ID, myChar )
VALUES ( 8, -- myID - int
'H' -- myChar - varchar(20)
)
SET IDENTITY_INSERT dbo.MyTable OFF
SELECT * FROM dbo.MyTable
DBCC CHECKIDENT('mytable', RESEED, 4)
INSERT mytable SELECT 'E'
INSERT mytable SELECT 'F'
INSERT mytable SELECT 'G'
INSERT mytable SELECT 'H'
INSERT mytable SELECT 'I'
SELECT * FROM dbo.MyTable
June 13, 2011 at 12:50 am
read too fast, don't look at the RESEED options.
Nice question
June 23, 2011 at 8:02 pm
Awesome question Thanks
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply