December 30, 2008 at 11:32 pm
i have a problem in using the identity column in a table
when i try to insert some data into a table, which is having 2 columns.
first column is identity and second column is unique.
i inserted 2 rows (1,101) , (2,102)
then i tried to insert 1 more row with same value "102", as it violates the unique constraint this row wont' be inserted.
then i tried to insert 1 more row with value "103", here i observed that the identity column value got incremeted, even when the insertion got failed in the third insert statment. Is there any way to get rid of this kind of situation. Is there any way to find out the unutilized numbers (like 3 here in the given example)
create table temp3(a int identity(1,1) , b int unique)
insert into temp3(b) values(101)
insert into temp3(b) values(102)
insert into temp3(b) values(102)
insert into temp3(b) values(103)
select * from temp3
December 31, 2008 at 4:35 am
If you want to reset the identity or just see the next number you can use DBCC checkident statement (you can check about it in books on line), but take into consideration that identity is supposed to guarantee that you’ll have unique numbers in the column. It isn’t suppose to make sure that you have consecutive numbers without any gap. Identity columns should be used only when the values that you get are not important and the only thing that is important is that it will be unique. If in your case you need to reuse numbers that are not in the table (which can happen for few reasons for example, records were deleted, transactions were rolled back, etc’), then you should use your own mechanism instead of identity.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 31, 2008 at 8:31 am
Adi is right that IDENTITY certainly will not prevent gaps, and the reason is that it needs to protect the integrity of the column. So while the 3rd insert you describe is happening, there is nothing preventing a separate session from also doing an insert into that table. To stop duplicate values from happening in the IDENTITY column, it has to consume the value 3 when you do your 3rd insert even though that statement gets rolled back because of the unique constraint violation.
I would like to convince you though that trying to come up with your own scheme for generating a sequence of numbers is a bad idea, and probably more difficult than you may think. Doing a SELECT MAX(ID) in an AFTER INSERT trigger can be a performance issue, and having a separate table to hold your last used ID value would require you to have explicit transactions around each INSERT statement and could cause excessive blocking. It's probably better to live with a skipped number every now and then in your IDENTITY column which is designed to handle concurrent transactions with good performance.
December 31, 2008 at 9:07 am
Why do you need the values to be sequential.
As stated above it is done to prevent duplicates. The database doesn't care or require the Identity column to be sequential.
If you want your data to have a value that is sequential for display purposes when you query look up the rank function and input that into your query.
Edit: Added Sample
create table #mytable (pk int identity, MyValue varchar(200))
Create unique Nonclustered Index MyUniqueIndex on #Mytable(MyValue)
insert into #Mytable (MyValue)
Values ('Test')
insert into #Mytable (MyValue)
Values ('Run')
insert into #Mytable (MyValue)
Values ('Run')
Insert into #Mytable (MyValue)
Values ('Done')
select Row_Number() Over(Order by PK) as SeqNumber, pk, MyValue
from #Mytable
Drop Table #Mytable
December 31, 2008 at 11:14 am
It seems strange to me that you're spending time trying to force sequential numbers on an identity column. As you have discovered, your identity column creates a unique sequential number for each insert attempted. So it's working as designed. If you need to enforce "non-gap" numbering then an identity column shouldn't be your choice unless you can guarantee that inserts will never fail.
If that doesn't work for you, there is a way to force a skipped identity value into the table. Lookup "SET IDENTITY_INSERT" in BOL for the full detail. The high level overview is...
- it can only be set for one table at a time for the session
- it must follow the constraints for the column (normally a unique constraint)
- the user must be in one of 3 fixed roles (sysadmin, db_owner or db_ddladmin)
Syntax:
set identity_insert [db.][schema.] some_table ON;
insert some_table (RowID, ColumnA) values (3, 103);
set identity_insert [db.][schema.] some_table OFF;
In any event, I wouldn't spend time trying to rebuild the identity property using the above syntax, I'd simply build a non-gap scalar function to return the desired value.
I hope this helps.
--Paul Hunter
January 1, 2009 at 2:48 am
Hi
You can reset the identity to a defined value using following command
DBCC checkident (tablex, reseed, N)
where tablex - replace your able name
N - the number you want to reset
Hope this would work for you
Venkat
January 2, 2009 at 7:35 am
Hi Harsh,
The thing which Chris Harshman mentioned is good and pretty valid also. The thing which he mentioned can be written in code as follows:
create table #temp3(a int , b int unique)
insert into #temp3(b) values(101)
insert into #temp3(b) values(102)
//This will throw an error but the insertion will continue
insert into #temp3(b) values(102)
insert into #temp3(b) values(103)
DECLARE @intNextId INT
SET @intNextId = 0
SELECT @intNextId = MAX(a) + 1
FROM #temp3
IF (@intNextId IS NULL) //if table does not have a max identity.
SET @intNextId = 0
/*This will update the table with incremental values of @intNextId and hence a.*/
UPDATE #temp3
SET @intNextId = a = @intNextId + 1
select * from #temp3
drop table #temp3
So instead of creating a table with identity column, create without one and then update the same with incremental values as shown above.
I hope this will be of some help to you.
Samar
January 2, 2009 at 8:10 am
paulhunter (12/31/2008)
It seems strange to me that you're spending time trying to force sequential numbers on an identity column. As you have discovered, your identity column creates a unique sequential number for each insert attempted. So it's working as designed. If you need to enforce "non-gap" numbering then an identity column shouldn't be your choice unless you can guarantee that inserts will never fail.
As rightly stated, i was just thinking of another scenario, what if you're going easy with sequential generation of IDs, and business needs to delete existing records, this will indeed create gaps.
January 2, 2009 at 8:27 am
Yes ofcourse ps it will create gaps. So here we can nullify all the identity entries and rebuilt them.
January 2, 2009 at 8:40 am
dhaval.samaranayeke (1/2/2009)
Yes ofcourse ps it will create gaps. So here we can nullify all the identity entries and rebuilt them.
By nullify, do you mean delete all identity values and insert afresh sequentially? This would be one of the worst ideas.
take a scenario. You have a table TABLE1 which generates identity values in a column called ID. You have TABLE2, TABLE3 and TABLE4 which depend on ID from TABLE1(referential integrity). Do you mean you'll manually insert newly created IDs in all those tables? This would be defeating the very purpose of having Identity columns and Foreign Keys. (and i'm not saying this is not possible, but this is not a good solution at all)
Let database take care of Identity columns, Gaps should not be a problem.
January 2, 2009 at 8:50 am
Oh yes if the table has some dependency then it is a very poor idea. Moreover if there is a primary key-foreign key constraint then it wont even allow to nullify the values and rebuild them. It would be better to live with it rather than change it. 🙂
January 2, 2009 at 9:03 am
dhaval.samaranayeke (1/2/2009)
So here we can nullify all the identity entries and rebuilt them.
How? Aren't identity columns usually set up with uniqueness?
Why? The sole purpose of an identity column is to identify the row of the table. There's a strong hint in the name. Would you arbitrarily change the identifier (name) of a table because you didn't like it? How about a column? What makes the identifier of a row any different?
They are there for SQL Server to use, not for the business to muck about with. If the business needs an incremental integer column with or without gaps, then create one for them. Then they can do whatever they like with it - and they usually will.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 2, 2009 at 10:03 am
Hi Mr. Chris Morris,
I guess you are taking my sentence all wrong. I am trying to remove gaps within a column which has incremental values and not that of an the identity column. Identity columns cannot be edited. Also i am saying that it would not be possible to fill the gap of a column by nullifying it and again incrementing it if the table has a primary key - foreign key constraint on any other table for that column. These gaps then can only be removed by adding a row to that particular deleted value. Please note that the column should not be an identity column for this to be a reality. And i guess it would be really nice if we could just stop this discussion here.
Samar
January 2, 2009 at 10:18 am
dhaval.samaranayeke (1/2/2009)
Hi Mr. Chris Morris,I guess you are taking my sentence all wrong. I am trying to remove gaps within a column which has incremental values and not that of an the identity column. Identity columns cannot be edited. Also i am saying that it would not be possible to fill the gap of a column by nullifying it and again incrementing it if the table has a primary key - foreign key constraint on any other table for that column. These gaps then can only be removed by adding a row to that particular deleted value. Please note that the column should not be an identity column for this to be a reality. And i guess it would be really nice if we could just stop this discussion here.
Samar
Hi Samar
No offence intended, merely striving for accuracy 😎
Please note that identity columns can be edited. See above posts by Venkat and paulhunter. Your discussion indicates a very sound reason why they shouldn't be edited.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply