| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2012-01-11 : 07:32:25
|
| We have an Sproc that inserts a batch of rows into a table.It has a parameter to operate in "Test mode". This does the INSERT as normal, displays the results from the target table, and then does a ROLLBACK. This gives the operator the chance to check how it is all going to look, make adjustments if necessary, and when they are happy do it "for real".Typically the operator will adjust things and make an average of about 10 "test runs". This obviously leaves a big gap in the IDENTITY numbers allocated.I realise that gaps don't matter, and the application certainly doesn't care, but I just have this nagging feeling that leaving all these gaps is not a brilliant idea. Only rational thought I can think of is that the presence of GAPs could signify some real problem, and that will be masked by the fact that the application is allowing this to happen "as a matter of course", so we won't be able to use the presence of "gaps" as an indicator of some other type of problem.I could change the Sproc to "reset" the IDENTITY when it does a rollback.Any thoughts / comments? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2012-01-11 : 07:56:42
|
| In test mode, can you use a temp table or table variable in place of actual table and later insert into actual table so that you dont worry about gaps?MadhivananFailing to plan is Planning to fail |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-11 : 07:59:42
|
| Its a good idea, only issue I have is that we "do it for real" so that there are no surprises - otherwise there is risk that Test Mode is not "identical" to Real Life mode. For example, it would fail with an error if we got a duplicate-unique-key or anything like that.Well ... that was the thinking when we designed it! |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-11 : 08:13:57
|
| In theory you can reset the identity seed when you rollback, but there is something that I cannot explain that bothers me about it. Sort of a feeling in the gut that that is not the right thing to do.Can you store the rolled-back identity numbers in a separate table? The thought is that the union of values in your real table and the values in this "discarded identities" table should have no gaps. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-11 : 09:02:54
|
| For sure I could work around the problem (temp tables or something else), but we have a rule here which we like to stick to which is: "do not test using a hybrid system", which is why I am using the real, live, tables for the "test insert" (and then doing a ROLLBACK).Perhaps you could all tell me that no one can think of any reason to worry about the "gaps" and maybe that is the answer? |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-01-11 : 09:34:18
|
"There is no reason to worry about the gaps". There! I said it!!  (really, I think there is no reason to worry about gaps)What I was proposing was not a hybrid system that mixes production and test environment. I was thinking of a new table in the production environment which would be "DiscardedIdentities". When the operator (in production environment) runs in the evaluate mode (where you do the rollback), the identity range that was used up by the evaluation mode activities would get inserted into this DiscardedIdentities table. It would just sit there and have no purpose other than if you needed to do some kind of auditing or if you wanted to keep track of gaps. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-11 : 12:57:00
|
quote:
Originally posted by sunitabeck
"There is no reason to worry about the gaps". There! I said it!! (really, I think there is no reason to worry about gaps)
Yeah, I said that too ... didn't stop me worrying about it though !!!!quote:
What I was proposing was not a hybrid system that mixes production and test environment. I was thinking of a new table in the production environment which would be "DiscardedIdentities". When the operator (in production environment) runs in the evaluate mode (where you do the rollback), the identity range that was used up by the evaluation mode activities would get inserted into this DiscardedIdentities table. It would just sit there and have no purpose other than if you needed to do some kind of auditing or if you wanted to keep track of gaps.
Ah! Now I've got it  Yes, that would enable me to differentiate real-gaps from the ones caused by my deliberate ROLLBACKs. Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-11 : 12:57:32
|
quote:
Originally posted by X002548
Worse than..."There is no reason to worry"Is that you are thinking about it at all
Yeah, I wish I wasn't a Detail-Person sometimes ... |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-11 : 13:31:13
|
| OK, but not sure I'm understanding you, sorry.The ID numbers are completely arbitrary, and no weight is attached to them being contiguous etc. They are, as you rightly recommend, "just used for JOINs"However, as an old-hack certain things ring alarm bells with me. The amount / size of "gaps" can suggest that there are more rollbacks than would be "normal"; there might be a goofy process running amok, or frequent deadlocks, or ...if I am causing huge gaps by doing a ROLLBACK deliberately then am I losing the "value" of possibly being ably to spot something being amiss? - by using my "6th sense", acquired over many years of DBA-ing. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-01-11 : 14:10:03
|
| *wipes hand through the air*These aren't the missing ID values you are looking for. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-11 : 15:47:35
|
| Pity. I've looked everywhere else ... Is there support for a multi-universe version? or am I in Denial for the next version? Which reminds me ... I was reading "How to teach your dog Quantum Mechanics" over the holidays (as you do ... as you do ...) and it occurs to me that when we have a Quantum Databases I won't even KNOW whether IDENTITY values have even been allocated, or not ... so perhaps I should stop worrying about ones that I know have NOT been allocated?!! |
|
|
|
Tom85
Starting Member
13 Posts |
Posted - 2012-01-12 : 10:12:32
|
quote:
Originally posted by Kristen
We have an Sproc that inserts a batch of rows into a table.It has a parameter to operate in "Test mode". This does the INSERT as normal, displays the results from the target table, and then does a ROLLBACK. This gives the operator the chance to check how it is all going to look, make adjustments if necessary, and when they are happy do it "for real".Typically the operator will adjust things and make an average of about 10 "test runs". This obviously leaves a big gap in the IDENTITY numbers allocated.I realise that gaps don't matter, and the application certainly doesn't care, but I just have this nagging feeling that leaving all these gaps is not a brilliant idea. Only rational thought I can think of is that the presence of GAPs could signify some real problem, and that will be masked by the fact that the application is allowing this to happen "as a matter of course", so we won't be able to use the presence of "gaps" as an indicator of some other type of problem.I could change the Sproc to "reset" the IDENTITY when it does a rollback.Any thoughts / comments?
Hello Kristen,If its not too much trouble or against your company policies, can I ask you to provide a sample of described procedure?I've been asked by my superiors to learn SQL in 6 months and get myself on a decent level in that time. Procedure you described seems to be doing exactly the same thing like the one I've done as my first project (with the difference that mine is bulk inserting from CSV file), it works fine and does the job but when I found your post it made me wonder how did I do compared to a pro.Sorry for inconvenience,thank you,Regards,Tom |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-12 : 12:32:57
|
| This is just a generic question Tom, so there isn't any specific code that goes with it.You'd be better off starting a new topic, explaining what you need help with, and folk can then provide advice.If you are wanting to learn I suggest you explain that and most folk will then "guide" you rather than providing an answer (which will assume you know what you are doing) or NOT answering because they think you are trying to get some homework done without putting in any effotr! |
|
|
|
Tom85
Starting Member
13 Posts |
Posted - 2012-01-13 : 03:49:31
|
| Morning Kristen,thank you for answer,I browse this forum on daily basis at work whenever I can, I prefer reading through examples of queries and trying to understand them rather than reading my SQL book which I'm, politely put, not very impressed with. I try not to bother anyone with newbie questions too much but what you described looked exactly like the problem I've been dealing with so as I said... it made me wonder how is it done by folks who know what are they doing.Anyways, when I get the chance I'll try to strip my proc of specific object names and paste it here for you guys to have a look at.I have a strong feeling that you are not gonna like it very much, even though its quite simple :)Thank you,Regards,Tom |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-01-13 : 08:17:13
|
| Most of the links in my signature are helpful for those just starting to learn SQL:http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Tom85
Starting Member
13 Posts |
Posted - 2012-01-13 : 09:14:47
|
| Thank you DonAtWork,I actualy belive I've got the basics mostly covered,I started with a webpage very similar to those you have listed in your signature. Sort of 'all in one' SQL queries manual and started experimenting with queries. Then I got a book: A beginners guide Microsoft SQL server 2008 by Dusan Petkovic, which I am currently about half way through.That been said... This book is doing my head in, I'm not impressed at all and if you have any suggestion regarding better studying materials I would appreciate it.Thank you,Regards,Tom |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-01-18 : 06:49:00
|
Just trolling browsing through this site would be helpful. I cannot count how much i have learned from the folk here.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Tom85
Starting Member
13 Posts |
Posted - 2012-01-18 : 07:16:39
|
quote:
Originally posted by DonAtWork
Just trolling browsing through this site would be helpful. I cannot count how much i have learned from the folk here.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp
Hi DonAtWork, how is your day going?As I already said in the post before, which you probably haven't noticed, I browse SQL team forum daily :)But thank you for trying to help.Regards,Tom |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-01-18 : 07:37:14
|
| For a beginner's book on T-SQL - Itzik Ben-Gan's t-SQL Fundamentals 2008--Gail ShawSQL Server MVP |
|
|
|
Next Page
|
Reset IDENTITY after a ROLLBACK