January 16, 2008 at 11:09 pm
I have a table with the following specifications:
[FutureArticleId] [bigint] IDENTITY(1,1) NOT NULL,
[cFutureArticleTitle] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[cDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[gCategoryId] [bigint] NOT NULL,
[cKeyword] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[bIsDeleted] [bit] NOT NULL CONSTRAINT [DF_tbl_FutureArticle_bIsDeleted] DEFAULT ((0)),
[tOnCreated] [datetime] NOT NULL,
[tOnUpdated] [datetime] NULL,
[gCreatedBy] [bigint] NOT NULL,
[gUpdatedBy] [bigint] NULL,
After some insertions and deletion from the UI, obviously there are gaps in the FutureArticleId column which is an identity column.
However, sometimes while inserting the records the records are being inserted into the gap and not in the next available Identity value.
Is there any table setting which I need to do, to stop this.
Please let me know
Thanks
Ankit
January 16, 2008 at 11:51 pm
- did someone execute dbcc checkident 'yourtable', reseed ?
- it should not be a problem if you use the identity column as a surogate key and therefor don't deduct anything else from it's vaule. (age, first, last, ...)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 18, 2008 at 10:52 am
Logically you shouldn't be using that field for anything but a surrogate key. But yes, you can get the current maximum from that field by using:
SELECT MAX(YourIdentityField) + 1 FROM YourTable.
Then you can put your answer in the "Indentity Seed" attribute in the design of your table and it will start incrementing from that number.
January 18, 2008 at 10:57 am
I prefer to reset the right value by using:
dbcc checkident('MyTable', reseed, 0)
dbcc checkident('MyTable', reseed)
The reseed with no arguments will change the seed to the next higher number not used in the DB IF the current seed isn't already higher than the highest currently in the table.
----------------------------------------------------------------------------------
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?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply