August 27, 2011 at 6:55 pm
What is the following clause, placed in front of an INSERT statement, accomplishing? Specifically wondering about lines 3,4,5.
DECLARE @Identity INT
DECLARE @IdentityStart INT = 1000000
SELECT @Identity = ISNULL(MAX(ApprovalModelId),1) FROM mgo.ApprovalModel WHERE ApprovalModelId < @IdentityStart
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, @Identity)
GO
This clause preceeds an INSERT statement that creates a table with three rows, and the first row's identity is a 2. I would like to RESEED it so that first row to insert has an identity value of 1 (not 2!) and I have tried this
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, 1)
GO
but this doesn't reset the first row to 0.
Thank you.
Helen
August 27, 2011 at 7:36 pm
figured it out. I need to delete rows from table before I can reseed.
as follows
delete from tablename
August 27, 2011 at 7:39 pm
:w00t: Actually, still have this question:
What is the following clause, placed in front of an INSERT statement, accomplishing? Specifically wondering about lines 3,4,5.
DECLARE @Identity INT
DECLARE @IdentityStart INT = 1000000
SELECT @Identity = ISNULL(MAX(ApprovalModelId),1) FROM mgo.ApprovalModel WHERE ApprovalModelId < @IdentityStart
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, @Identity)
GO
August 28, 2011 at 11:37 pm
My guess would be the original author is trying to avoid creating gaps in the assigned id range. An identity value is incremented even if an insert that was attempted, was rolled back. i.e. the value that got assigned to the row that was rolled back will normally not be re-used. This construct seems to make sure that the identity value is always reset to the highest actually assigned value before attempting the next insert.
I would still recommend to remove the code though As it is now, this is a real killer for concurrency and it is not correct, thus still no guarantee that no gaps will ever exist. Worse yet, it could be the source for strange intermittent errors. There must be better ways to accomplish what this code intends to support without reverting to nasty workarounds like this.
August 29, 2011 at 6:43 am
R.P. Rozema. I am very interested in understanding what you are saying better. Would you mind telling me line by line (yes, just brief words are fine) what each line does? This would help me understand your explanation better. BTW, this particular block of code is used throughout merge scripts, so it will not be run unless we are populating our target database.
DECLARE @Identity INT
DECLARE @IdentityStart INT = 1000000
SELECT @Identity = ISNULL(MAX(ApprovalModelId),1) FROM mgo.ApprovalModel WHERE ApprovalModelId < @IdentityStart
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, @Identity)
GO
Helen
August 29, 2011 at 8:24 am
🙂 no problemo.
I'll skip 1 & 2, these are clear, aren't they?
Line 3:
SELECT @Identity = ISNULL(MAX(ApprovalModelId),1)
FROM mgo.ApprovalModel
WHERE ApprovalModelId < @IdentityStart
Assigns into @Identity the highest value from all values in ApprovalModelId in the mgo.ApprovalModel table that have a ApprovalModelId smaller than 1000000. If there is a null value in any of the rows, this will be replaced by 1 and then checked to see if it is the highest value. Please note that the author probably intended to put a 1 in there when no value was found, but that is not what this code does... To achieve this (s)he should have written:
SELECT @Identity = ISNULL((
SELECT MAX(ApprovalModelId)
FROM mgo.ApprovalModel
WHERE ApprovalModelId < @IdentityStart
), 1);
Line 4:
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, @Identity)
Sets the identity value for table [mgo.ApprovalModel] to the value determined in line 3. Documentation on DBCC CHECKIDENT can be found online and in your local books online. Please note that here, the author probably intended to specify table [mgo].[ApprovalModel], not [mgo.ApprovalModel] which most likely is not found in your database. Also, I have not found documentation what will happen if @Identity is set to NULL when calling DBCC CHECKIDENT (, RESEED, ), but again it is unlikely to be what the author actually intended.
Do you see why I think you'd be better off removing the code? Seeing that your code will most likely not be run when other processes are concurrently accessing the same tables, the lack of concurrency possibilities is probably of less concern to you. But would this sort of code be run in a production environment, you should prepare for some serious "time-out" and/or "the system is slow" complaints when more than a single process/user is working on the same system.
hxkresl (8/29/2011)
R.P. Rozema. I am very interested in understanding what you are saying better. Would you mind telling me line by line (yes, just brief words are fine) what each line does? This would help me understand your explanation better. BTW, this particular block of code is used throughout merge scripts, so it will not be run unless we are populating our target database.DECLARE @Identity INT
DECLARE @IdentityStart INT = 1000000
SELECT @Identity = ISNULL(MAX(ApprovalModelId),1) FROM mgo.ApprovalModel WHERE ApprovalModelId < @IdentityStart
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, @Identity)
GO
Helen
August 29, 2011 at 2:17 pm
Overall, R.P.R. got the overall answer correct, however I felt compelled to add a minor correction:
The two versions of the select max statement R.P.R. included are equivalent.
The way to write the query that replaces on a row by row basis (which I don't think your author intended) is:
SELECT @Identity = MAX(ISNULL(ApprovalModelId,1) )
FROM mgo.ApprovalModel
WHERE ApprovalModelId < @IdentityStart
The code supplied by the original poster does indeed set @identity to 1 only when there are no rows in the table. (And can never result in @identity being set to null)
This is the correct part:
Sets the identity value for table [mgo.ApprovalModel] to the value determined in line 3. Documentation on DBCC CHECKIDENT can be found online and in your local books online. Please note that here, the author probably intended to specify table [mgo].[ApprovalModel], not [mgo.ApprovalModel] which most likely is not found in your database. Also, I have not found documentation what will happen if @Identity is set to NULL when calling DBCC CHECKIDENT (, RESEED, ), but again it is unlikely to be what the author actually intended.
Do you see why I think you'd be better off removing the code? Seeing that your code will most likely not be run when other processes are concurrently accessing the same tables, the lack of concurrency possibilities is probably of less concern to you. But would this sort of code be run in a production environment, you should prepare for some serious "time-out" and/or "the system is slow" complaints when more than a single process/user is working on the same system.
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
August 29, 2011 at 10:33 pm
Thanks for that addition, you are right, line 3 needs some more clarification. However, the both statements are not equivalent. I said null was assigned, which is indeed not true: instead null is left in @Identity. Because if not a single line matches the criteria set in the where clause (which is the case if the table is empty) there is no assigment at all.
Have a look at this example, that should demonstrate it clearly:
declare @x int;
select @x = 1;
select @x;
select @x = 2 where 1 = 0;
select @x;
The outcome is :
-----------
1
(1 row(s) affected)
-----------
1
(1 row(s) affected)
Note that the 2nd select outputs 1 and not 2. likewise, your statement will leave null in @Identity (the default for "no value assigned yet") when no rows exist in the table.
The correct way to always assign a value is by not putting a where clause on the assignment. i.e. using the statement I gave you:
SELECT @Identity = ISNULL((
SELECT MAX(ApprovalModelId)
FROM mgo.ApprovalModel
WHERE ApprovalModelId < @IdentityStart
), 1);
Dan's version indeed replaces row-by-row null values with 1's and if no row exists no assignment takes place:
SELECT @Identity = MAX(ISNULL(ApprovalModelId,1) )
FROM mgo.ApprovalModel
WHERE ApprovalModelId < @IdentityStart
And the original version replaces only the highest value by 1, if this happens to be null, and if no rows exists matching the criteria, no assignment at all takes place (as a side note: many aggregation functions like max() never return null, so the isnull is useless at all):
SELECT @Identity = ISNULL(MAX(ApprovalModelId),1)
FROM mgo.ApprovalModel
WHERE ApprovalModelId < @IdentityStar
August 30, 2011 at 11:48 am
The strange behavior that occurs when I leave this code in my migration scripts is that it populates the first row of the ApprovalModel column with a 2. Therefore there are three rows in it, with identity values 2,3,4. Because a second table is referencing this table with an FK ApprovalModelID, it throws a constraint error.
My workaround was to replace this:
/*DECLARE @Identity INT
DECLARE @IdentityStart INT = 1000000
SELECT @Identity = ISNULL(MAX(ApprovalModelId),1) FROM mgo.ApprovalModel WHERE ApprovalModelId < @IdentityStart
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, @Identity)
GO
*/
with this:
DBCC CHECKIDENT ([mgo.ApprovalModel], RESEED, 0)
and now the migration script is working perfect, because it reseeds the table. This also works fine when migrating into a populated source DB because the identity values there, already begin with 1.
Is there any reason not to remove all the code altogether?
August 30, 2011 at 2:51 pm
Have a read at: http://msdn.microsoft.com/en-us/library/ms176057.aspx
An excerpt of this page:
"DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value )
Current identity value is set to the new_reseed_value.
If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value."
The bold printed part explains why your inserted rows start at 2 and not at 1. i.e. You would have to reseed to 0 to have the new records start at 1.
August 30, 2011 at 2:57 pm
I have to ask - why is it so important to reseed at all?
I am a little concerned that you are trying to reuse the ID's, which may or may not be assigned in a predictable fashion to rows you insert. This could be disastrous if you're using that ID as a foreign key (which I thought I heard from you), since that would mean that this process is scrambling your reference data (the row that USED to be 1 is now 6, 2 is now 1, etc...).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 31, 2011 at 1:30 am
yes Matt I am glad you are insinuating that these scripts do no good. Why reseed at all when in 2 tables these are FKs referencing a identity PK in a look up table, and yes it has caused problems ie. giving constraint errors. This has been manifesting in the past two days as this thread has developed. Since I inherited this code I wanted to proceed slowly with removing what looks wrong until I am sure i understand what all it was originally intended to achieve. Thanks to Rozema, especially, for helping to pick apart the clause for me. At this point I do not see value and have commented out until I get further feedback from tester.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply