|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Identity column based on other valuecalculates the next number based on some other field or value? For example, I have a table with the following fields: Company (PK, smallint, not null) ID (PK, smallint, not null) SomeValue (varchar(10), null) I want ID to increment for each instance of a unique value in Company. So I might have the following: Company ID 1 1 1 2 1 3 2 1 2 2 3 1 .... Is this possible, or is there no way to make an identity column start again at 1 if the value of another field is different? I hope this is readonably clear; many thanks for any help. Chris >> Is it possible to have a sequentially increasing identity column that only calculates the next number based on some other field [sic] or value? << Let's get back to the basics of an RDBMS. Rows are not records; fieldsare not columns; tables are not files; there is no sequential access or ordering in an RDBMS. In the future , coudl you post real DDL instead of a vague pseudo-code? >> I want ID [see ISO-11179 for proper naming rules] to increment for each instance of a unique value in Company. << Look at the "DENSE_RANK() OVER( ORDER BY company_id)" in the SQL Server2005 OLAP functions. It does exactly this kind of numbering. >> Is this possible, or is there no way to make an identity column start again at 1 if the value of another field [sic] is different? << You have a problem with basic concepts. Do not use proprietary exposedphysical locators like IDENTITY in an RDBMS for anything. >> You have a problem with basic concepts. Do not use proprietary exposed You have a problem moving theory into implementation.>> physical locators like IDENTITY in an RDBMS for anything. IDENTITY is used extensively for surrogate keys with great use and great scalability. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1153324660.330790.96530@i3g2000cwc.googlegroups.com... >>> Is it possible to have a sequentially increasing identity column that >>> only calculates the next number based on some other field [sic] or >>> value? << > > Let's get back to the basics of an RDBMS. Rows are not records; fields > are not columns; tables are not files; there is no sequential access or > ordering in an RDBMS. In the future , coudl you post real DDL instead > of a vague pseudo-code? > >>> I want ID [see ISO-11179 for proper naming rules] to increment for each >>> instance of a unique value in Company. << > > Look at the "DENSE_RANK() OVER( ORDER BY company_id)" in the SQL Server > 2005 OLAP functions. It does exactly this kind of numbering. > >>> Is this possible, or is there no way to make an identity column start >>> again at 1 if the value of another field [sic] is different? << > > You have a problem with basic concepts. Do not use proprietary exposed > physical locators like IDENTITY in an RDBMS for anything. > >> You have a problem moving theory into implementation. << "If it doesn't work in practice, you're using the wrong theory"- Immanuel Kant >> IDENTITY is used extensively for surrogate keys with great use and great scalability.<< Yes, it is; no, it does not scale. --CELKO-- > Yes, it is; no, it does not scale. What utter and complete BS!!!Do we really need to get into the argument of why using natural keys on references is very bad for concurrency and very bad for performance in terms of a) extra cache usage and cpu usage on the server, b) extra memory requirements of the client app and browser, c) more traffic between server and client. I NEVER want to be in the position where my database has been implemented with natural key data duplicated everywhere as teh foreign keys and then for me to have to make a change to the natural key data - it happens often. Surrogate key - 1 place to update and minimal transaction size and few concurrency problems Natural key - 1 + each foreign key reference makes for a very big transaction, if the data is expanding you will likely cause page splits - ouch....... dear dear dear.... But being an 'expert' with lots of 'experience' of 'real systems' you'd know all that already...... -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1153326856.035080.27810@s13g2000cwa.googlegroups.com... >>> You have a problem moving theory into implementation. << > > "If it doesn't work in practice, you're using the wrong theory" > - Immanuel Kant > >>> IDENTITY is used extensively for surrogate keys with great use and great >>> scalability.<< > > Yes, it is; no, it does not scale. > > --CELKO-- > >>I NEVER want to be in the position where my database has been implemented with natural key data duplicated everywhere as the foreign keys and then for me to have to make a change to the natural key data - it happens often. << Since you have no experience with ISO or ANSI or DIN or even BritishStandards, you would know about the 5-year lifecycle, the detailed migration paths, etc for the INFREQUENT changes in natural keys. VIN, ISBN, DUNS, SIN, SSN. UPC, EAN, -- shall I continue with a few tens of thousands of industry and legally required identifiers? Tony, you are such a 1970's "cowboy coder" working in a RDBMS world! When was the last time the CE calendar changed? (Longitude, latitude) changed? UPC? ISBN? VIN? The reason you write as poorily as you seem to from your postings is that you can do it fast and leave before it falls apart. If it runs fast now, but cannot port or scale, you are happy to get it out of QA. I have not gotten a consult gig in the UK in years, so I have not had to clean up your stuff. Let me drop this onto the Internet -- Anyone with a "Tony Rogerson" SQL database can hire me for expenses and US$750 per day to review and try to fix it. > Since you have no experience with ISO or ANSI or DIN or even British Cowboy Coder - LOL, at least I followed a training scheme and was mentored > Standards, you would know about the 5-year lifecycle, the detailed > migration paths, etc for the INFREQUENT changes in natural keys. VIN, > ISBN, DUNS, SIN, SSN. UPC, EAN, -- shall I continue with a few tens > of thousands of industry and legally required identifiers? Tony, you > are such a 1970's "cowboy coder" working in a RDBMS world! When was > the last time the CE calendar changed? (Longitude, latitude) changed? > UPC? ISBN? VIN? by senior programmers throuhout the early part of my career - I didn't just go to university and then start writing books! INFREQUENT is not enough! an INFREQUENT change even once on a 100GB database for the type of thing you advise would case masses of IO, totally fragment the database and would cause an outage of hours, whereas the surrogate key method, a method recognised by all the industrial experts aside from yourself there would be little problem. > The reason you write as poorily as you seem to from your postings is The reason I write well designed, scalable and efficient code is so that > that you can do it fast and leave before it falls apart. If it runs > fast now, but cannot port or scale, you are happy to get it out of QA. people can easily maintain it, understand it and it will work between versions and get the most out of the product the client has just invested their HARD EARNED CASH in - ever heard the concept of ROI? > I have not gotten a consult gig in the UK in years, so I have not had Wow - your cheap; I bill at £800 - £1,125 ex VAT a day and a lot of my work > to clean up your stuff. Let me drop this onto the Internet -- Anyone > with a "Tony Rogerson" SQL database can hire me for expenses and US$750 > per day to review and try to fix it. is repeat with relationships going back years! Your antics would not work here, people are more clued up now-a-days, your sort can't get away with BS anymore - you'd be out the door like a flash (not that you'd get in there in the first place!). -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "--CELKO--" <jcelko***@earthlink.net> wrote in message news:1153329342.747609.254600@75g2000cwc.googlegroups.com... >>>I NEVER want to be in the position where my database has been implemented >>>with natural key data duplicated everywhere as the foreign keys and then >>>for me to have to make a change to the natural key data - it happens >>>often. << > > Since you have no experience with ISO or ANSI or DIN or even British > Standards, you would know about the 5-year lifecycle, the detailed > migration paths, etc for the INFREQUENT changes in natural keys. VIN, > ISBN, DUNS, SIN, SSN. UPC, EAN, -- shall I continue with a few tens > of thousands of industry and legally required identifiers? Tony, you > are such a 1970's "cowboy coder" working in a RDBMS world! When was > the last time the CE calendar changed? (Longitude, latitude) changed? > UPC? ISBN? VIN? > > The reason you write as poorily as you seem to from your postings is > that you can do it fast and leave before it falls apart. If it runs > fast now, but cannot port or scale, you are happy to get it out of QA. > > > I have not gotten a consult gig in the UK in years, so I have not had > to clean up your stuff. Let me drop this onto the Internet -- Anyone > with a "Tony Rogerson" SQL database can hire me for expenses and US$750 > per day to review and try to fix it. > Hi Tony
Joe said: >> Anyone with a "Tony Rogerson" SQL database can hire me for expenses and And then your replied:>> US$750 >> per day to review and try to fix it. > Wow - your cheap; I bill at £800 - £1,125 ex VAT a day and a lot of my I too was wondering why Joe charges so little. And all I could figure was > work is repeat with relationships going back years! that he knows there won't be much work to do to clean up after you. :-) -- HTH Kalen Delaney, SQL Server MVP :) - thanks Kalen. I tend to only drink water while out onsite now, so even the need for > I too was wondering why Joe charges so little. And all I could figure was > that he knows there won't be much work to do to clean up after you. somebody to wash cups has gone ;) -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:O3DROb3qGHA.1368@TK2MSFTNGP05.phx.gbl... > Hi Tony > > Joe said: > >>> Anyone with a "Tony Rogerson" SQL database can hire me for expenses and >>> US$750 >>> per day to review and try to fix it. > > And then your replied: > >> Wow - your cheap; I bill at £800 - £1,125 ex VAT a day and a lot of my >> work is repeat with relationships going back years! > > I too was wondering why Joe charges so little. And all I could figure was > that he knows there won't be much work to do to clean up after you. > > :-) > -- > HTH > Kalen Delaney, SQL Server MVP > > >> I too was wondering why Joe charges so little. And all I could figure was that he knows there won't be much work to do to clean up after you. << LOL! That's is my "Tony Special" price, not my regular rates!Frankly, I would expect to have to do re-designs to brign things up to industry standards, and not just clean up code. "Tony Rogerson" wrote in message:
> Your antics would not work here, people are more clued up now-a-days, your Wow pilgrim.You find it necessary to attack someones livelihood to make a> sort can't get away with BS anymore - you'd be out the door like a flash > (not that you'd get in there in the first place!). point?? Could you get anymore personnal?Cut that &%&^ out. 'Your sort'...are you writing this from a pub? > Cowboy Coder - LOL, at least I followed a training scheme and was mentored Were you mentored by some classless wanker on how to spew personal venom> by senior programmers throuhout the early part of my career - I didn't just > go to university and then start writing books! or are you a self made man? Look, you don't get the Celko 'it' or his 'IT'.That's the way it goes.But don't go crucifying the guy as if your on mtv.Your hanging the messager and its making you look foolish.I don't agree with a lot of stuff here (including many of joes jiffy lubes) but I don't turn people into sushi and I don't tell them they're not worthy to make a living. You can find anything in BOL but class. Learn how to throw darts or use a scalpel if you must but stop the puke. On Wed, 19 Jul 2006 17:44:16 -0400, "Steve Dassin" <rac4sqlnospam@net> in <uTU#eU5qGHA.2***@TK2MSFTNGP03.phx.gbl> wrote: Show quote >"Tony Rogerson" wrote in message: Finally someone pointed that out.>> Your antics would not work here, people are more clued up now-a-days, your >> sort can't get away with BS anymore - you'd be out the door like a flash >> (not that you'd get in there in the first place!). > >Wow pilgrim.You find it necessary to attack someones livelihood to make a >point?? >Could you get anymore personnal?Cut that &%&^ out. >'Your sort'...are you writing this from a pub? > >> Cowboy Coder - LOL, at least I followed a training scheme and was mentored >> by senior programmers throuhout the early part of my career - I didn't >just >> go to university and then start writing books! > >Were you mentored by some classless wanker on how to spew personal venom >or are you a self made man? >Look, you don't get the Celko 'it' or his 'IT'.That's the way it goes.But >don't go >crucifying the guy as if your on mtv.Your hanging the messager and its >making >you look foolish.I don't agree with a lot of stuff here (including many of >joes jiffy lubes) >but I don't turn people into sushi and I don't tell them they're not worthy >to make a living. >You can find anything in BOL but class. >Learn how to throw darts or use a scalpel if you must but stop the puke. > Behave Steve, and I think with his attitude to others on here the bully is
more than capable of defending himself! The way this guy treats people on here he deserves everything I and other people throw at him. And class, I'm working class through and through mate. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Steve Dassin" <rac4sqlnospam@net> wrote in message news:uTU%23eU5qGHA.2440@TK2MSFTNGP03.phx.gbl... > "Tony Rogerson" wrote in message: >> Your antics would not work here, people are more clued up now-a-days, >> your >> sort can't get away with BS anymore - you'd be out the door like a flash >> (not that you'd get in there in the first place!). > > Wow pilgrim.You find it necessary to attack someones livelihood to make a > point?? > Could you get anymore personnal?Cut that &%&^ out. > 'Your sort'...are you writing this from a pub? > >> Cowboy Coder - LOL, at least I followed a training scheme and was >> mentored >> by senior programmers throuhout the early part of my career - I didn't > just >> go to university and then start writing books! > > Were you mentored by some classless wanker on how to spew personal venom > or are you a self made man? > Look, you don't get the Celko 'it' or his 'IT'.That's the way it goes.But > don't go > crucifying the guy as if your on mtv.Your hanging the messager and its > making > you look foolish.I don't agree with a lot of stuff here (including many of > joes jiffy lubes) > but I don't turn people into sushi and I don't tell them they're not > worthy > to make a living. > You can find anything in BOL but class. > Learn how to throw darts or use a scalpel if you must but stop the puke. > > Short version: Celko is arguing against the entire co-opting of an industry
by vendors.What you 'know' and 'do' comes from MS bol (or Oracle,DB2...) It is an industry based on expediency.As Gertrude Stein said: "there's no there there".At least he is proscribing some kind of external framework (be it problematic sometimes).Look around, the pharmaceutical companies,law enforcement and insurance companies now define medicine.Great. Your misunderstanding and superficial treatment of what he's about does not justify your rank dismissal of him. I support Dataphor (www.alphora.com) as a RAD and a true Relational dbms. I support MS Sql Server as a storage device for Dataphor. Those who have the strongest convictions have the least courage, those who have no convictions have the most courage, that is why we have a government of cowards and lunatics. Show quote "Tony Rogerson" <tonyroger***@sqlserverfaq.com> wrote in message news:ugSrg17qGHA.4192@TK2MSFTNGP04.phx.gbl... > Behave Steve, and I think with his attitude to others on here the bully is > more than capable of defending himself! > > The way this guy treats people on here he deserves everything I and other > people throw at him. > > And class, I'm working class through and through mate. > > -- > Tony Rogerson > SQL Server MVP > http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL > Server Consultant > http://sqlserverfaq.com - free video tutorials > > > "Steve Dassin" <rac4sqlnospam@net> wrote in message > news:uTU%23eU5qGHA.2440@TK2MSFTNGP03.phx.gbl... > > "Tony Rogerson" wrote in message: > >> Your antics would not work here, people are more clued up now-a-days, > >> your > >> sort can't get away with BS anymore - you'd be out the door like a flash > >> (not that you'd get in there in the first place!). > > > > Wow pilgrim.You find it necessary to attack someones livelihood to make a > > point?? > > Could you get anymore personnal?Cut that &%&^ out. > > 'Your sort'...are you writing this from a pub? > > > >> Cowboy Coder - LOL, at least I followed a training scheme and was > >> mentored > >> by senior programmers throuhout the early part of my career - I didn't > > just > >> go to university and then start writing books! > > > > Were you mentored by some classless wanker on how to spew personal venom > > or are you a self made man? > > Look, you don't get the Celko 'it' or his 'IT'.That's the way it goes.But > > don't go > > crucifying the guy as if your on mtv.Your hanging the messager and its > > making > > you look foolish.I don't agree with a lot of stuff here (including many of > > joes jiffy lubes) > > but I don't turn people into sushi and I don't tell them they're not > > worthy > > to make a living. > > You can find anything in BOL but class. > > Learn how to throw darts or use a scalpel if you must but stop the puke. > > > > > > No, there is nothing automatic like identity that works the way you
described. Sure would be handy to have such a feature, Celko not withstanding. Roy Harvey Beacon Falls, CT Show quote On Wed, 19 Jul 2006 16:43:14 +0100, "Chris Pratt" <n**@given.com> wrote: >Is it possible to have a sequentially increasing identity column that only >calculates the next number based on some other field or value? For example, >I have a table with the following fields: > >Company (PK, smallint, not null) >ID (PK, smallint, not null) >SomeValue (varchar(10), null) > >I want ID to increment for each instance of a unique value in Company. So I >might have the following: > >Company ID >1 1 >1 2 >1 3 >2 1 >2 2 >3 1 >... > >Is this possible, or is there no way to make an identity column start again >at 1 if the value of another field is different? > >I hope this is readonably clear; many thanks for any help. > >Chris > "Roy Harvey" <roy_har***@snet.net> wrote in message Implemented in MySql as part of auto_increment (ie identity):news:sr2tb293vvf5if3uasl5r6dhcdbdbgh8ce@4ax.com... > No, there is nothing automatic like identity that works the way you > described. Sure would be handy to have such a feature, Celko not > withstanding. http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html www.rac4sql.net I support Dataphor (www.alphora.com) as a RAD and a true Relational dbms. I support MS Sql Server as a storage device for Dataphor. You could probably do it in an INSTEAD of trigger by working out the MAX(
id ) for a given Company - be a bit fiddly though. Tony. -- Show quoteTony Rogerson SQL Server MVP http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL Server Consultant http://sqlserverfaq.com - free video tutorials "Chris Pratt" <n**@given.com> wrote in message news:uRhoIo0qGHA.2180@TK2MSFTNGP05.phx.gbl... > Is it possible to have a sequentially increasing identity column that only > calculates the next number based on some other field or value? For > example, I have a table with the following fields: > > Company (PK, smallint, not null) > ID (PK, smallint, not null) > SomeValue (varchar(10), null) > > I want ID to increment for each instance of a unique value in Company. So > I might have the following: > > Company ID > 1 1 > 1 2 > 1 3 > 2 1 > 2 2 > 3 1 > ... > > Is this possible, or is there no way to make an identity column start > again at 1 if the value of another field is different? > > I hope this is readonably clear; many thanks for any help. > > Chris > Chris,
If data is only added, never deleted, from this table, one possibility is not to store this ID column at all, but generate it in a view as needed, and treat that view as your table, hiding an identity column that keeps track of sequence in a real table. Here's a sketch: create table hiddenMyCompanyStuff ( CompanyID int not null, seqNum int identity(1,1) not null, otherStuff varchar(20), primary key (CompanyID, seqNum) ) go create view MyCompanyStuff as select CompanyID, rank() over (partition by CompanyID order by seqNum) as subID, otherStuff from hiddenMyCompanyStuff go insert into MyCompanyStuff(CompanyID, otherStuff) values(1,'gabc') insert into MyCompanyStuff(CompanyID, otherStuff) values(3,'fabc') insert into MyCompanyStuff(CompanyID, otherStuff) values(2,'eabc') insert into MyCompanyStuff(CompanyID, otherStuff) values(2,'dabc') insert into MyCompanyStuff(CompanyID, otherStuff) values(1,'cabc') insert into MyCompanyStuff(CompanyID, otherStuff) values(1,'babc') insert into MyCompanyStuff(CompanyID, otherStuff) values(2,'aabc') go select * from MyCompanyStuff go drop view MyCompanyStuff drop table hiddenMyCompanyStuff -- Steve Kass -- Drew University -- www.stevekass.com Show quote "Chris Pratt" <n**@given.com> wrote in message news:uRhoIo0qGHA.2180@TK2MSFTNGP05.phx.gbl... > Is it possible to have a sequentially increasing identity column that only > calculates the next number based on some other field or value? For > example, I have a table with the following fields: > > Company (PK, smallint, not null) > ID (PK, smallint, not null) > SomeValue (varchar(10), null) > > I want ID to increment for each instance of a unique value in Company. So > I might have the following: > > Company ID > 1 1 > 1 2 > 1 3 > 2 1 > 2 2 > 3 1 > ... > > Is this possible, or is there no way to make an identity column start > again at 1 if the value of another field is different? > > I hope this is readonably clear; many thanks for any help. > > Chris > I never thought such a seamingly simple post could spark such a reaction!
Thank you to those who offered useful suggestions - most helpful thank you - and slightly less thanks to those (naming no names CELKO) who wasted my valuable time by making me read the verbalisation of their poorly disguised sexual frustrations. My knowledge of SQL Server may not be as water-tight as yours, but I am sure there are plenty of things you are not particularly good at either (civility, for example). If you were to post a message onto a newsgroup for which your expertise was less than that of some other participants, would you expect an aggressive and patronising response? It is exactly that sort of elitest attitude that gains people in the computing industry their bad name. Show quote "Chris Pratt" <n**@given.com> wrote in message news:uRhoIo0qGHA.2180@TK2MSFTNGP05.phx.gbl... > Is it possible to have a sequentially increasing identity column that only > calculates the next number based on some other field or value? For > example, I have a table with the following fields: > > Company (PK, smallint, not null) > ID (PK, smallint, not null) > SomeValue (varchar(10), null) > > I want ID to increment for each instance of a unique value in Company. So > I might have the following: > > Company ID > 1 1 > 1 2 > 1 3 > 2 1 > 2 2 > 3 1 > ... > > Is this possible, or is there no way to make an identity column start > again at 1 if the value of another field is different? > > I hope this is readonably clear; many thanks for any help. > > Chris > "Chris Pratt" <n**@given.com> wrote in message Good, somebody who knows a subject in depth.news:eGRLcHCrGHA.4812@TK2MSFTNGP04.phx.gbl... > ... slightly less thanks to those (naming no names CELKO) who wasted my > valuable time by making me read the verbalisation of their poorly disguised > sexual frustrations. I had this same problem once, and it took me FOREVER to figure out.
Here are the links to my group posts that eventually lead me to my answer, hope they help! http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/1de32e402b88a212/5055554b270a3a4c?lnk=st&q=&rnum=12&hl=en#5055554b270a3a4c http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/ae41b00e47ee68bc/cb48b642699f230f?lnk=st&q=&rnum=40&hl=en#cb48b642699f230f Chris Pratt wrote: Show quote > Is it possible to have a sequentially increasing identity column that only > calculates the next number based on some other field or value? For example, > I have a table with the following fields: > > Company (PK, smallint, not null) > ID (PK, smallint, not null) > SomeValue (varchar(10), null) > > I want ID to increment for each instance of a unique value in Company. So I > might have the following: > > Company ID > 1 1 > 1 2 > 1 3 > 2 1 > 2 2 > 3 1 > ... > > Is this possible, or is there no way to make an identity column start again > at 1 if the value of another field is different? > > I hope this is readonably clear; many thanks for any help. > > Chris  |
|||||||||||||||||||||||
Other interesting topics