February 27, 2008 at 10:16 am
Oh no - I'm no longer "allowed" to play MMORPG's.....:D. I have to steer clear of those if i plan on ever getting any sleep. Funny thing - my wife actually wants to spend some time with me on occasion:)
I stick to single-user ones (NWN, Morrowind etc...). That way - she can always lock the CD's away if I go over my time allotment!
----------------------------------------------------------------------------------
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?
February 27, 2008 at 2:22 pm
michael.rosquist (2/27/2008)
Identity is evil.Using identity as PK is bad.
Having a table like this:
CREATE TABLE Countries
(CountryID INT IDENTITY PRIMARY KEY,
CountryName VARCHAR(50) NOT NULL)
If I were Joe Celko I would ask you how do you
prevent your table from containing
CountryID CountryName
1 Canada
2 Canada
3 Sweden
4 Sweden
etc
If you want an incremental counter use:
SELECT @nextid = coalesce(MAX(id), 0) + FROM tbl (UPDLOCK)
This must be done in a transaction to prevent two processes getting
the same ID.
/m
If I were anyone but Joe Celko I would say you can easily solve this problem with:
CREATE TABLE Countries
(CountryID INT IDENTITY PRIMARY KEY,
CountryName VARCHAR(50) NOT NULL UNIQUE);
Oh wait a minute, I'm not Joe Celko!
February 28, 2008 at 1:12 am
Hi guys 'n girls,
I have a problem that you might be able to help me with and do not want to use identity. I have a table (let's call it FinTrans) on which I have different types of financial transactions (by the way, I work on SQL Server 2005) like Invoices, Journals etc. Another table (Company) keeps my Next Invoice Number etc. In the clients office is two clerks that both do invoices and gets invoice numbers from the Company table's Next Invoice Number field. My code looks like this:
select @nextinvoice = [next invoice number] from company
update company set [next invoice number] = isnull([next invoice number],0)+1
I then use @nextinvoice to insert the FinTrans table. Now it so happens that these two clerks click the save button at exactly the same time and both of the get exactly the same invoice number. I use Begin Transaction, Commit Transaction and Rollback Transaction (in the event of errors).What can I do to prevent this?
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 28, 2008 at 2:40 am
😎 I have to agree that identity columns are indeed evil! Especially when someone before you created a database and now you sit with the problems. Example: I have a table called BankStatements and these people that created this table was to lazy to make provision for a proper primary key so they used an indentity key. Very nice and you have a unique number with which you can link your bankstatements (imported via the bank's website) and your transactions to the gl accounts. Now!! the users import the bankstatement and the next day that same statement changed and the bank balances differ and you have to now with text (varchar) fields compare and I promise you it's a nightmare.
Don't be lazy!!! Use proper primary keys for your tables.
Manie
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 28, 2008 at 6:02 am
Manie,
You need to supply a lock hint when selecting from the invoice number:
select @nextinvoice = [next invoice number] from company with(updlock)
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 28, 2008 at 6:21 am
Thanks Jack,
I have been searching but clearly not enough. Thanks a span, you helped me save a lot of work.:D:D:D:D:P:P
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 28, 2008 at 6:39 am
Manie Verster (2/28/2008)
😎 I have to agree that identity columns are indeed evil! Especially when someone before you created a database and now you sit with the problems. Example: I have a table called BankStatements and these people that created this table was to lazy to make provision for a proper primary key so they used an indentity key. Very nice and you have a unique number with which you can link your bankstatements (imported via the bank's website) and your transactions to the gl accounts. Now!! the users import the bankstatement and the next day that same statement changed and the bank balances differ and you have to now with text (varchar) fields compare and I promise you it's a nightmare.Don't be lazy!!! Use proper primary keys for your tables.
Manie
From your previous question just before the one above, are you suggesting that a sequence table is the way to go? If not, what are you suggesting that you would use in the BankStatements table problem of yours?
--Jeff Moden
February 28, 2008 at 7:47 am
...and how exactly is this better than an identity? Sequence tables are evil in my book...:D
----------------------------------------------------------------------------------
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?
February 28, 2008 at 8:34 am
Matt Miller (2/28/2008)
...and how exactly is this better than an identity? Sequence tables are evil in my book...:D
now Matt, there are instances where a sequence table is necessary:D. For instance where I used to work BOL (bills of lading, not Books on Line:P) numbers had to sequential and without gaps, so we could not use identity because if there was an error on insert identity was incremented. Of course if you are allowed to have gaps I prefer identity.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 28, 2008 at 8:41 am
Ah - but that's not what should be used as a pk/fk. Using a sequence as a clustered key (What Manie seems to be looking to do) is usually the most direct route to "badly behaving database ville".
I never really understood the "no gaps thing", which is a wrong-headed control measure. Having to resequence your bills of lading because someone calls and cancels the order 6 hours after the warehouse folks started picking the order is - well - ludicrous. Cancellations happen.
----------------------------------------------------------------------------------
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?
February 28, 2008 at 8:57 am
Manie,
Your code >>
select @nextinvoice = [next invoice number] from company
update company set [next invoice number] = isnull([next invoice number],0)+1
How I would change it >>
update dbo.company set
[next invoice number] = @nextinvoice = isnull([next invoice number], 0) + 1
set @nextinvoice = @nextinvoice - 1
If [next invoice number] was [last invoice number], you could drop this: set @nextinvoice = @nextinvoice - 1
Just a thought.
😎
February 28, 2008 at 9:04 am
Matt Miller (2/28/2008)
I never really understood the "no gaps thing", which is a wrong-headed control measure. Having to resequence your bills of lading because someone calls and cancels the order 6 hours after the warehouse folks started picking the order is - well - ludicrous. Cancellations happen.
I did not say I understood or liked the "no gaps thing", I just said I had to enforce it. It's one of the things accountants and government likes!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 28, 2008 at 9:10 am
Jack Corbett (2/28/2008)
Matt Miller (2/28/2008)
I never really understood the "no gaps thing", which is a wrong-headed control measure. Having to resequence your bills of lading because someone calls and cancels the order 6 hours after the warehouse folks started picking the order is - well - ludicrous. Cancellations happen.I did not say I understood or liked the "no gaps thing", I just said I had to enforce it. It's one of the things accountants and government likes!
I hear ya. I've had to "code with the ax on my neck" too....
----------------------------------------------------------------------------------
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?
February 29, 2008 at 12:05 am
Shoot, I hadn't expect such a reply to my Bankstatement comment. Guys, I have to apologize profusely to the people the created the bank statement table and to you. I was in a bad mood because these people sprung this bankstatement buggerup on me and it is always risky because your matches can go wrong. So, let's start again.
Jeff, no I do not suggest a sequence table and actually, even though I am a great campaigner against identity keys, in this case it was the best way to go. I mulled over this problem all of last night (it is now 08:37 in South Africa and you guys might be sleeping now) and realised that in this case there is no other way to go. I'll give you an example:
The file we get from the bank (comma delimited) looks like this:
BankId 0784, (the banks identifier of a day's statement)
Date 2202, (format ddmm)
Instruction ACB, (some code the bank uses)
Amount -6645.07, (transaction amount)
Description Payment, (short description like type of transaction)
Detail J.Doe invoice 1234, (reference used to allocate this money to an account)
This basically what you get so what to do now. The only way to go is either a sequencial table or an identity which is a sequential table made easier. The upside is that each bankstatement item has a UNIQUE id which can be referenced to the FinTrans (live account transactions) table. So, if someone gives me the identity columns number (records 1 through 100000) e.g. 98754 I can hit it directly.
To come back to the sequence table, it is not so difficult to resequence it should you have a "no gap" rule. I used this in a Journal table wherein I sequenced the line number of a journal. For every journal you would have a unique set if line nrs. For instance:
Journal 1234 Lines 1,2,3,4,5,6,7
Should line 5 get deleted my code (in the delete procedure) wil look like this:
update journals set linenr = linenr-1 where journalnr = @journalnr and linenr > @linenr
It worked every time! But you should know that better than me now!
:-PManie Verster
Developer
Johannesburg
South Africa
I can do all things through Christ who strengthens me. - Holy Bible
I am a man of fixed and unbending principles, the first of which is to be flexible at all times. - Everett Mckinley Dirkson (Well, I am trying. - Manie Verster)
February 29, 2008 at 4:00 am
No problem, Manie... and, heh, yeaup... I know how it is when someone sort'a hits you between the eyes with a sharp one. 😀
One difference between the way you do it and the way I do it... once I have data in a permanent table, I never delete. I might mark it as a deactivated row or add a sister row to counter-act the transaction, but I never delete once a row has made it (either by GUI or staging table) to a permanent table... it's part of the banking/accounting/SOX thing. And, yes, it's ok to move old data to an archive... but I never delete from a permanent table. Boy, has it ever saved my butt! I've never failed an audit and I always come up smelling like a rose if there's an investigation of any type.
--Jeff Moden
Viewing 15 posts - 106 through 120 (of 129 total)
You must be logged in to reply to this topic. Login to reply