Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlconfused
Yak Posting Veteran
50 Posts |
 Posted - 2013-10-04 : 00:50:34
|
| Hello.I've a table with an ID column, it is an auto increment id column that increases by 1.ID--97249725When my webhost restarts SQL, or some other random act of God, this ID jumps by 1000. Today it leapt from 9725 to 10725, which just doesn't work for me. It results in my having to upload the last known good copy of the database.My host says they can't change the 1000 row buffer, MS apparently doesn't deem this an issue, and I'm quite frustrated. I used to be able to modify certain settings in SQL since I was using an SQL 2008 database under SQL Management Console but since my host upgraded to 2012, and I'm running XP, there is no forward compatibility and many options are no longer possible (eg. modifying a column, now it has to be done via clicking query and entering a SQL command rather than just right clicking on the column)So long story made short... is there a way to modify my table (named "places") to turn OFF the autoincrement Id and perform a simple means to figure out the next possible value?example:id, place, locstion, gps, v2, v3, v4, comment================================================9720, Bill's House, Toronto, 43.4453, y, n, c, This was a good place to eat9721, Dime a Dozen, Cambridge, 42.4453, n, e, d, A good place to take photosI'd have to pull the highest value of ID (+1 to it) and then do a standard INSERT into places (id, place, location, gps, v2, v3, v4, comment) values (9722, etc...)I guess my concern is if the autoidentity is turned off, will SQL allow a new value to be entered via INSERT? I'm also using the @@identity to find the last known ID when I want to direct the user to the entry that they've just created and I don't know what that # is just yet. Would the @@identity still work?Keep in mind I'm very new at SQL so saying, "do a trace flag 272 o" might not be as easy as you make it sound :)Thank you |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-10-04 : 01:11:20
|
| is there a way to modify my table (named "places") to turn OFF the autoincrement Id and perform a simple means to figure out the next possible value?you can do this. just alter column to remove IDENTITY property and then you can remove that auto incrementing property.Once this done1. You need to make sure you pass explicit values in INSERT as value doesnt get generated automatically anymore2. You need to devise an algorithm which works for bulk insert as well. so MAX(ID)+1 will work fine for single inserts but not for batch ones. for that you might need an intermediate temporary table with id column to generate values sequentially.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-10-04 : 01:13:24
|
| 1000 row buffer? Sounds like you need to switch hosting providers. This shouldn't be happening and sounds like they have a special script running to cause this. ALL of my identity columns increment by 1 when an INSERT occurs, regardless of SQL restarting or not. I've got hundreds of applications with thousands of ID columns. No issues on any of them.But perhaps show us the DDL of the table just in case.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-10-04 : 07:06:00
|
My first question is "Why does it matter?". Because if the value of the identity column means something, you are designing your solution the wrong way.To me, it sounds like the restarts are killing and donig a rollback of a lot of connections. When an insert is rolled back, the identity values is not reused.
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
sqlconfused
Yak Posting Veteran
50 Posts |
Posted - 2013-10-04 : 09:20:38
|
| It matters because the id's also tell me how many places exist in the database. It's a database of places people visit and take photographs of. When it's 9677 everyone knows there are 9677 entries. The ID is also shown in the URL (eg. www.mypage.com/location.asp?place=4433)I don't know how to generate a DDL, sorry (refer to my username).I can (and will) change the ID column to manually increment but unfortunately there are other columns in other tables that also use autoincrement and I think it would be a pain to do as these are batch photo upload scripts.Thanks for the help. I realize one could use a COUNT command but I don't want it jumping all over the place |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-10-04 : 13:21:08
|
| The adjustment of the ID value is a, possible, feature of SQL when the server is reset. Relying on the value to increment by one is bad design. There are other scenarios that can cause gaps in the sequence too. |
|
|
|
|
|
|
|
|
MS SQL incrementing issue (of 1000 gaps)