InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 17:34:25 GMT20http://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]Jeff Moden (2/17/2011)[/b][hr][quote][b]YSLGuru (2/24/2009)[/b][hr][quote][b]corey lawson (12/5/2007)[/b][hr]So... the major problem with your article is that it posts no solutions for the problem at hand, or no pointers to where to look further to increase one's kungfu skills in this area.I can't find one of my JCelko books that talks about calc'ing running sums in SQL and how he or some of his readers do it, but that would be one good* place to look ("SQL for Smarties" etc). (* I realize that some people out there have a definite love-hate..no, hate relationship with JCelko's scribblings...but I don't, so feh. But if you have other resources, do add them to the thread).[/quote]Just curious but are you saying that if one knows of a problem they shouldn't tell anyone else unless they also have the solution? I hope you don't apply that to everyting.[/quote]Ya know... I never did thank you for that thought. Thanks, YSL... :-)[/quote]My pleasure. I do not like people who can contorbute nothing but sarcasm to a conversation. Besides I think you've done more then your fair share of assistance on SSC.com. Tue, 22 Feb 2011 15:23:30 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]YSLGuru (2/24/2009)[/b][hr][quote][b]corey lawson (12/5/2007)[/b][hr]So... the major problem with your article is that it posts no solutions for the problem at hand, or no pointers to where to look further to increase one's kungfu skills in this area.I can't find one of my JCelko books that talks about calc'ing running sums in SQL and how he or some of his readers do it, but that would be one good* place to look ("SQL for Smarties" etc). (* I realize that some people out there have a definite love-hate..no, hate relationship with JCelko's scribblings...but I don't, so feh. But if you have other resources, do add them to the thread).[/quote]Just curious but are you saying that if one knows of a problem they shouldn't tell anyone else unless they also have the solution? I hope you don't apply that to everyting.[/quote]Ya know... I never did thank you for that thought. Thanks, YSL... :-)Thu, 17 Feb 2011 20:09:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]corey lawson (12/5/2007)[/b][hr]So... the major problem with your article is that it posts no solutions for the problem at hand, or no pointers to where to look further to increase one's kungfu skills in this area.I can't find one of my JCelko books that talks about calc'ing running sums in SQL and how he or some of his readers do it, but that would be one good* place to look ("SQL for Smarties" etc). (* I realize that some people out there have a definite love-hate..no, hate relationship with JCelko's scribblings...but I don't, so feh. But if you have other resources, do add them to the thread).[/quote]Just curious but are you saying that if one knows of a problem they shouldn't tell anyone else unless they also have the solution? I hope you don't apply that to everyting.Tue, 24 Feb 2009 14:47:51 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]LeeBear35 (2/3/2009)[/b][hr]I'm sure however that they will bring in consultants to address the issues, because that funding comes out of a different bucket.[/quote]There is nothing that says you couldn't become that consultant... I did that same thing last year. I was happy, the company was happy (got a raise out of it, too!), and the poor slobs that I left behind were happy.Wed, 04 Feb 2009 19:18:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxWell I could not prove that it was malicious. Could be just incompetence or the "many cooks" syndrome.What I say is: If it walks like a duck and quacks like a duck then shoot the sun of a #$%^ and let me get back to sleep!Wed, 04 Feb 2009 09:15:46 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]Charles Kincaid (2/3/2009)[/b][hr][quote][b]Joe Celko (2/3/2009)[/b][hr]Hey! I teach SQL, not ethics! :)[/quote]:w00t: And here I keep buying your books to wave around in our ethics meetings! :w00t:Good, fast, cheep. Pick any two. Works for software and your dates.I was tasked to document an existing system. We found a routine that opened the database connection, twenty lines of rem'd out code, start loop, more rem'd code, read record number 5, a few hundred lines of rem'd code from some other project, increment loop counter, more rem'd code, if count less than 5000 then loop, more rem'd code, close connection, more rem'd code, return success. If you took out all the remarks and comments the routine opened the database, read record five 5000 times, closed the connection, and said "I did it". No error trapping. If there was a failure it never got reported. The record number was hard coded. No data from the record was used. The client was charged for I/O and CPU time.[/quote]Sounds like a snippet that the owner of the system (the one who GOT paid for that CPU/IO usage) paid a developer to put into the system to generate more revenues. :hehe: I wouldn't mind getting into that racket myself - just like printing money!! heheheWed, 04 Feb 2009 08:21:22 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]Joe Celko (2/3/2009)[/b][hr]Hey! I teach SQL, not ethics! :)[/quote]:w00t: And here I keep buying your books to wave around in our ethics meetings! :w00t:Good, fast, cheep. Pick any two. Works for software and your dates.I was tasked to document an existing system. We found a routine that opened the database connection, twenty lines of rem'd out code, start loop, more rem'd code, read record number 5, a few hundred lines of rem'd code from some other project, increment loop counter, more rem'd code, if count less than 5000 then loop, more rem'd code, close connection, more rem'd code, return success. If you took out all the remarks and comments the routine opened the database, read record five 5000 times, closed the connection, and said "I did it". No error trapping. If there was a failure it never got reported. The record number was hard coded. No data from the record was used. The client was charged for I/O and CPU time.Tue, 03 Feb 2009 13:54:13 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxLOL! In the early days of SQL, we wrote MAX(DISTINCT x) to force a sort that would slow down performance. Likewise, we did SELECT DISTINCT when we knew a SELECT would work, etc. The program would lrun like glue; we would change a few keywords and be heroes! Hey! I teach SQL, not ethics! :)Tue, 03 Feb 2009 13:06:46 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxActually I did make a version that replaced the call to up_CalculateEmailPoints_Insert, it was more a real life example of RBAR and how someone did what they could to avoid a cursor, but ended up going further down the wrong road.Cursors, Goto...there are those things that should be avoided, but there is nothing like coding a program on Advanced PICK (for anyone that knows it anymore) of 10 GOTO 10 - just hangs the whole system. :PTue, 03 Feb 2009 12:11:13 GMTLeeBear35http://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxCould you make a version of up_CalculateEPoints_Insert (say up_CalculateEPoints_Insert_All) that would incorporate the selection logic query so that the whole solution could be set based?Tue, 03 Feb 2009 10:50:03 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxLynn,I think in many ways it is a blessing, but I do not like the timing. Seems like everyone out there sees this as a time to get senior experiance for entry level or at most mid-grade experiance. I would not mind but that is a big cut.Anyhow, I have corrected a number of processes like this and dropped the overall processing from hours to minuted. Infact I have one process that has been refactored and using a million row set takes 6.5 minutes to run, but I have to confirm it against the original process that has been running for 4 hours and I think has 12 to 24 more hours to go.Oh, and one of the solutions was to generate all of the EXEC statements then call SQLCMD. One of the other potential solutions is using a varchar(max) in SQL 2005, but there is a limit of about 8 million rows.[code]DECLARE @SQL AS VARCHAR(MAX)DECLARE @CR AS CHAR(1)SET @SQL = ''SET @CR = CHAR(10) -- Just to make things readableSELECT @SQL = @SQL + @CR + 'EXEC up_CalculateEPoints_Insert ' + CONVERT(VARCHAR(19), EID)FROM [dbo].[Es] (nolock)WHERE [ProjectID] = @ProjectIDAND [Scanned] = 0EXEC (@SQL)[/code]But that is SQL 2005 and beyond...Tue, 03 Feb 2009 10:42:09 GMTLeeBear35http://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]LeeBear35 (2/3/2009)[/b][hr][quote][b]Lynn Pettis (2/3/2009)[/b][hr][quote][b]LeeBear35 (2/3/2009)[/b][hr]Not that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:[code]DECLARE @Count INT SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 WHILE @Count IS NOT NULL BEGIN EXEC up_CalculateEPoints_Insert @Count SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count END[/code]Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.[/quote]Looks like an opportunity to train someone in a better way to do something, not a reason to "get rid of" someone. Now, if such training and mentoring has been provided and they just don't get "it", that would be different.Counterpoint -- who wants to work for a company that looks for reasons to get rid of people they feel can't do the job because no one has taken the time to show someone a better way to do something?[/quote]I have been trying to train him, but he still continues to generate this kind of code. In the mean time I am the one that they are letting go of because I am a senior resource and he is entry level. I'm sure however that they will bring in consultants to address the issues, because that funding comes out of a different bucket.I agree with you whole heartedly I would never seek to have a company cut someone especially if they just need better training, and a more watchful eye (code review). Oh well, we never realize just how much we don't know until we learn something new.[/quote]If they are letting you go because you are a senior resource (meaning you get paid more, and are probably more knowledgable regarding your companies systems) and keeping him since he is an entry level resource (lower pay, less knowledge), then they are being short-sighted. If they then have to bring in a consultant (maybe even you) to fix things at a higher short-term cost, what are they saving? It is obvious the entry-level person may consistently write substandard code requiring constant rewrites.Hmm, who would want to keep working for that company? This may be a blessing in disguise. I know it was for me four years ago.Tue, 03 Feb 2009 10:25:03 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[code]SELECT 'EXEC up_CalculateEPoints_Insert ' + EID AS Command FROM Es WHERE ProjectID=@ProjectID AND Scanned=0 ORDER BY EID[/code]Save the result to a text file and run it back though SQLCMD maybe? :w00t:No cursors and no loops. It's set based. Well, sort of. :Whistling:Tue, 03 Feb 2009 10:16:31 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]Lynn Pettis (2/3/2009)[/b][hr][quote][b]LeeBear35 (2/3/2009)[/b][hr]Not that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:[code]DECLARE @Count INT SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 WHILE @Count IS NOT NULL BEGIN EXEC up_CalculateEPoints_Insert @Count SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count END[/code]Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.[/quote]Looks like an opportunity to train someone in a better way to do something, not a reason to "get rid of" someone. Now, if such training and mentoring has been provided and they just don't get "it", that would be different.Counterpoint -- who wants to work for a company that looks for reasons to get rid of people they feel can't do the job because no one has taken the time to show someone a better way to do something?[/quote]I have been trying to train him, but he still continues to generate this kind of code. In the mean time I am the one that they are letting go of because I am a senior resource and he is entry level. I'm sure however that they will bring in consultants to address the issues, because that funding comes out of a different bucket.I agree with you whole heartedly I would never seek to have a company cut someone especially if they just need better training, and a more watchful eye (code review). Oh well, we never realize just how much we don't know until we learn something new.Tue, 03 Feb 2009 10:14:40 GMTLeeBear35http://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]LeeBear35 (2/3/2009)[/b][hr]Not that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:[code]DECLARE @Count INT SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 WHILE @Count IS NOT NULL BEGIN EXEC up_CalculateEPoints_Insert @Count SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count END[/code]Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.[/quote]Looks like an opportunity to train someone in a better way to do something, not a reason to "get rid of" someone. Now, if such training and mentoring has been provided and they just don't get "it", that would be different.Counterpoint -- who wants to work for a company that looks for reasons to get rid of people they feel can't do the job because no one has taken the time to show someone a better way to do something?Tue, 03 Feb 2009 10:07:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxNot that I want to complain, but does anyone see a problem with a company that would retain a person that consistenly produces code like this:[code]DECLARE @Count INT SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 WHILE @Count IS NOT NULL BEGIN EXEC up_CalculateEPoints_Insert @Count SELECT @Count=MIN(EID) FROM Es(NOLOCK) WHERE ProjectID=@ProjectID AND Scanned=0 AND EID > @Count END[/code]Please note that is would be on a table that in most cases contains more than 4 million rows, when confronted the responce was that "well at least I did not use cursors!" My thought is that cursors should have far better performance, but I have not the time and desire to confirm that point.Tue, 03 Feb 2009 09:59:41 GMTLeeBear35http://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]David.Poole (1/28/2009)[/b][hr][quote]Why do the Britts always say 'Ten minutes Time'? Are there any other kind of Minutes?"[/quote]A minute amount of time might be defined as ten minutes according to the minutes of the meeting. And a small lizard might be my newt....sorry, I'll get my coat.[/quote]Heh... here... let me get it for you... door, too! :PWed, 28 Jan 2009 12:08:39 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote]Why do the Britts always say 'Ten minutes Time'? Are there any other kind of Minutes?"[/quote]A minute amount of time might be defined as ten minutes according to the minutes of the meeting. And a small lizard might be my newt....sorry, I'll get my coat.Wed, 28 Jan 2009 11:56:08 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx>> We could be arrogant and say "Ignorant prole, you should be grateful we allow you to buy from us, you and your ugly kids will take we can be bothered to offer" , but I suspect we wouldn't sell much with that attitude (unless we were, oh I don't know, a large international bank). <<Actually International Banks are one of the strongest supporters of Standardize data and procedures; look at SWIFT.Tue, 27 Jan 2009 09:32:50 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxThis is a nice case:Someone called the fire depatement to help on this situation:They have a nice x64 4proc (each 4 cores) going totally somewhere I didn't want it to go, because of RBAR queries.Bizar thing about it is that the same lovely query (5 unions joining 4 to 5 tables each) sometimes completed in 6 minutes, and sometimes needed more than 3 hours. (isolation level read uncommitted).That may be a 'living' content issue.I've optimized it a little and now it is consuming 2 minutes.One of the critical things were correlated subqueries.Altering them into a join solved this behaviour.Thank you once again ..... :D[code] SELECT DISTINCT 'OFU22 - Shipped' as Rules, getdate() as 'Check Date', 'Yes' as 'Blocking', CM.ref_commande_fournisseur as 'Supplier Ref.', LC.num_poste_fournisseur as 'Supplier Item', LC.statut_ligne_cde as 'Status', CL.raison_sociale as 'Sold To Name', CL.id_business as 'Sold To', LC.code_fournisseur as 'Managing Plant', LC.code_site as 'Finishing Line', '' as 'Batch', LC.date_reception as 'Last update', LC.ref_commande_fournisseur_2 + '/' + LC.num_poste_fournisseur_2 as 'Mill Order Reference' FROM fcs_ligne_commande LC inner join fcs_commande CM on LC.id_commande = CM.id_commande inner join boc_client CL on CL.id_boc_client = CM.id_boc_client_donneur_ordre WHERE LC.qte_exp is not null[b] AND ( select sum( CO.poids_brut) from fcs_colis CO where CO.ref_commande_fournisseur = CM.ref_commande_fournisseur and CO.num_poste_fournisseur = LC.num_poste_fournisseur and CO.statut_colis in ( '08' ) ) <> LC.qte_exp[/b] AND LC.statut_ligne_cde in ( '01', '05' )[/code]Altered to[code] SELECT distinct 'OFU22 - Shipped' as Rules, getdate() as 'Check Date', 'Yes' as 'Blocking', CM.ref_commande_fournisseur as 'Supplier Ref.', LC.num_poste_fournisseur as 'Supplier Item', LC.statut_ligne_cde as 'Status', CL.raison_sociale as 'Sold To Name', CL.id_business as 'Sold To', LC.code_fournisseur as 'Managing Plant', LC.code_site as 'Finishing Line', '' as 'Batch', LC.date_reception as 'Last update', LC.ref_commande_fournisseur_2 + '/' + LC.num_poste_fournisseur_2 as 'Mill Order Reference' FROM fcs_ligne_commande LC inner join fcs_commande CM on LC.id_commande = CM.id_commande inner join boc_client CL on CL.id_boc_client = CM.id_boc_client_donneur_ordre[b] inner join ( /* 324732 rows in total */ select ref_commande_fournisseur, num_poste_fournisseur, sum( poids_brut) as sum_poids_brut from fcs_colis where statut_colis = '08' group by ref_commande_fournisseur, num_poste_fournisseur ) CO on CO.ref_commande_fournisseur = CM.ref_commande_fournisseur and CO.num_poste_fournisseur = LC.num_poste_fournisseur and CO.sum_poids_brut <> LC.qte_exp[/b] WHERE LC.qte_exp is not null AND LC.statut_ligne_cde in ( '01', '05' )[/code]Altering the [i]distinct [/i] into a [i]group by [/i] didn't change the behaviour, so for the moment that will just be an extra comment.Tue, 27 Jan 2009 07:09:48 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxThanks David... the article was first published over a year ago. Seems like some different folks jumped in this time and dang near doubled the length of the comments. Heh... odd part is, this second wave of comments started because of a totally different article I also wrote about a year ago. Then, the ANSI folks jumped in and all hell has broken loose. Some pretty good conversations going on, so far... just a little flame here and there.Tue, 27 Jan 2009 06:24:34 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxGood article, as usual, Jeff :)You sure know how create these long threads ;)p.s. Keep banging that drum, they'll either get it or go deaf :hehe:Tue, 27 Jan 2009 02:13:59 GMTDavid Burrowshttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxASCII silly question, get a silly ANSI.Able: "Why do the Britts always say 'Ten minutes Time'? Are there any other kind of Minutes?"Cain: "Define the word 'parsec'."Able: "Ah, now I get it."Mon, 26 Jan 2009 16:31:00 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]David.Poole (1/23/2009)[/b][hr]I'm just wondering what situation exists where you have 1 billion records with a tiny proportion having a bit field set to 1 AND that field being important enough to have an index on it.Doesn't imply that the tiny proportion of records should exist in their own table?On the subject of standards guys, look at the world around you. We have the 3 Abrahamic faiths perpared to commit satanic acts against each other to prove their loyalty to exactly the same God where 99% of the key teachings of their religions agree. In a world in which such craziness achieves nothing other than to prove that stupidity is the only renewable resource an international standard has been agreed. Don't look a gift horse in the mouth!If the proprietary and standard way do exactly the same thing with no detriment then you might as well use the standard way.Ditto ISO standard data. If there is an international standard use it.If there is no international standard conform to an industry standardI'm sick of seeing CountryID as an proprietary int field and having external companies kick back because they expect the ISO standard country code.Once a company grows beyond a certain point it has to talk to the outside world and proprietary stuff just gets in the way.[/quote]I know this is a late answer to your question, but at a previous employer we had an Invoice Header table with close to 500,000 records. In the header was a bit field, Historical. If it was 0 the invoice was current, and if 1 it was historical. More than 90% of the queries I ran were for records that were current. An index on the bit field definitely enhanced the performance of my queries considering there were only about 20,000 current invoices.Mon, 26 Jan 2009 13:07:46 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxI work for a company that sources its data from many hundred different data providers and supplies it to many different data consumers.We could be arrogant and say "Ignorant prole, you should be grateful we allow you to buy from us, you and your ugly kids will take we can be bothered to offer" , but I suspect we wouldn't sell much with that attitude (unless we were, oh I don't know, a large international bank).For us it makes sense to say, look guys, our data conforms to the the following published standards. It wouldn't be profitable for us to try and provide our customers with data in their format and it actually makes things easier for our customers in the medium to long term because they don't have to source their data with us. It opens up a whole new range of possibilities for them.Yes, standards change but they are still standards. One of the ANSI-SQL standards had a 30 character limit on object names. Fantastic, a bone fide excuse to kill the ridiculous practice of putting tbl in front of tables and vw in front of views.The next ANSI standard increased the limit to 128 characters. Again, this was beneficial for databases with huge numbers of objects because we weren't stuck trying to abbreviate a meaningful object name into text speak.When it comes to sharing data adhering to standards is a bit like marriage. Marriage has its trials but celibacy has no joys.Mon, 26 Jan 2009 12:42:33 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxI have a rubber stamp for ISO-8601 dates and use that format on my checks; I also set my household appliances, cell phones, computers, etc to "24 hours time". But I had the advantage of being an Army Brat and growing up with that stuff.Mon, 26 Jan 2009 12:27:16 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxMy absolute favorite standard is "MIL-TP-41".... "Make It Like The Print for once". :PMon, 26 Jan 2009 10:44:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxI guess that I got bit by the standards bug. The other day I wrote a check to our food delivery driver. He claimed that I fouled up the date on it. Nope, it was right. 20090115 just happens to be ISO-8601. I threw a hissy when they published a new schema layout that had a UPC column that was NVARCHAR(10). I guess "Sunrise 2005" was a waste. I objected to 10 and calling the column "UPC".I'm old enough to have used a ton of serial connections. I still have NULL MODEM cable with 25 to 9 adapters. We used to laugh at RS-232 knowing that RS means [b][i]R[/i][/b]ecommended [i][b]S[/b][/i]tandard. Not even an adopted standard. Just one that had been [u]recommended[/u].Mon, 26 Jan 2009 08:15:34 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxSo, what do you do without standards ?Some won't do business with you if you're not certified ISO...Which one to pick is up to the company.IMO things like SOx or Hippa are standards that have generated billions of $$ of investment to accomplish what ? Well, coding standards are a start.Having good guidelines and documented exceptions will help out in the (near) future.Mon, 26 Jan 2009 00:18:10 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]TheSQLGuru (1/23/2009)[/b][hr]>>Ditto ISO standard data. If there is an international standard use it.If there is no international standard conform to an industry standard>>I will take the contrary position here. Some (many??) standards those such as Joe would have us adhere to are incredibly onerous and complicated, which would require lots of lost time and productivity to learn and then follow. What about older code that must be maintained - should we convert it as we have to touch it? Doesn't that increase error chances? Oh, and standards are NEVER that - take ANSI SQL and Microsoft SQL Server. Which flavor? What level of compatability is baked into which version of which RDBMS your product must operate on?? Which standards do you adopt?? There are plenty out there. All of them, and if not why would one win and another lose?? What about conflicts between standards?Yada-yada-yadaSorry, 80+% of the dev shops out there have no need nor desire and would in my opinion suffer much more than than would gain from trying to implement standards across the board. This is yet another thing those of us on the forum will just have to agree to disagree about. There is nothing I can say to convince Joe et al that his standards would cause more harm than good if they were forced upon all and nothing he et al can say to convince me otherwise. :blink:[/quote]On this, I will agree with you.Fri, 23 Jan 2009 21:38:34 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx>>Ditto ISO standard data. If there is an international standard use it.If there is no international standard conform to an industry standard>>I will take the contrary position here. Some (many??) standards those such as Joe would have us adhere to are incredibly onerous and complicated, which would require lots of lost time and productivity to learn and then follow. What about older code that must be maintained - should we convert it as we have to touch it? Doesn't that increase error chances? Oh, and standards are NEVER that - take ANSI SQL and Microsoft SQL Server. Which flavor? What level of compatability is baked into which version of which RDBMS your product must operate on?? Which standards do you adopt?? There are plenty out there. All of them, and if not why would one win and another lose?? What about conflicts between standards?Yada-yada-yadaSorry, 80+% of the dev shops out there have no need nor desire and would in my opinion suffer much more than than would gain from trying to implement standards across the board. This is yet another thing those of us on the forum will just have to agree to disagree about. There is nothing I can say to convince Joe et al that his standards would cause more harm than good if they were forced upon all and nothing he et al can say to convince me otherwise. :blink:Fri, 23 Jan 2009 14:48:26 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxHave to agree with you on that Joe. Just want to be sure that people who follow standards understand that doing so will not be a panacea, and will not prevent having to rewrite code.Standards are a good way to "learn from other people's mistakes". You just have to understand their limitations.Fri, 23 Jan 2009 13:59:21 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxI think people forgot another advantage of Standards after portability, maintenance, and readability. Standards have a migration path when they change. If you have any DB friends in retail or the book industry ask about how they switched from a 10-digit UPC barcode to a 13-digit EAN barcode. I started off with FORTRAN in the 1960's and moved to FORTRAN II, FORTRAN IV, Fortran 66, Fortran 77, Fortran 90, Fortran 95 and Fortran 2003 standards. It was a very easy migration path. Proprietary stuff from a vendor tends to disappear when the vendor is out of business or makes a change. Even worse, personalize code schemes are not well-designed. A quick example is a bit flag for gender I once saw -- "is_male" with the expected meaning. Then they got a corporate customer -- "is_corp" flag. Opps! Add another bit flag instead of using the ISO sex codes. Opps! Add a CHECK() constraint to be sure that the combination of bit flags were consistent. Opps! Try to clean up application code in 300+ programs. Designing and maintain an industry standard is done by an outside agency that does nothing else and gets all kinds of feedback. Life is short; let someone else do the work. Fri, 23 Jan 2009 13:51:04 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxStandards are a good thing. Just keep in mind, they do change over time, and something that is 100% compliant today may be 0% compliant tomorrow.Take a look at Imperial vs Metric vs SI, for a great example.If your thermometer says it's 80 degrees out, is it a bit warm, dangerously hot, or insta-death-cold? The "standard" has evolved so much in just a few decades that it makes that much difference.I had to take a class in Fortran 74 when I was in college in 1988 (required credit). That was "the standard", and was expected (by some) to stay "the standard" indefinitely, but boy is it useless to me now!So, yeah, comply to standards. It's often helpful. But it won't accomplish what the fanatics say it will, no matter how much they insist it will. In all things, moderation (extreme Buddhism!).Fri, 23 Jan 2009 12:43:25 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxI'm just wondering what situation exists where you have 1 billion records with a tiny proportion having a bit field set to 1 AND that field being important enough to have an index on it.Doesn't imply that the tiny proportion of records should exist in their own table?On the subject of standards guys, look at the world around you. We have the 3 Abrahamic faiths perpared to commit satanic acts against each other to prove their loyalty to exactly the same God where 99% of the key teachings of their religions agree. In a world in which such craziness achieves nothing other than to prove that stupidity is the only renewable resource an international standard has been agreed. Don't look a gift horse in the mouth!If the proprietary and standard way do exactly the same thing with no detriment then you might as well use the standard way.Ditto ISO standard data. If there is an international standard use it.If there is no international standard conform to an industry standardI'm sick of seeing CountryID as an proprietary int field and having external companies kick back because they expect the ISO standard country code.Once a company grows beyond a certain point it has to talk to the outside world and proprietary stuff just gets in the way.Fri, 23 Jan 2009 12:34:52 GMTDavid.Poolehttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]Peso (1/22/2009)[/b][hr]With SQL Server 2008CREATE NONCLUSTERED INDEX IX_MyBitField ON MyTable (MyBitField)WHERE MyBitField = 1[/quote]I was wondering if someone would bring up filtered indexes for this scenario. It certainly seems like a good candidate for them to me also. BTW, this was a near real-world situation. IIRC was actually several million records with several hundred bit=1 rows, but the point was the same. Fri, 23 Jan 2009 06:05:38 GMTTheSQLGuruhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]TheSQLGuru (1/22/2009)[/b][hr][quote][b]Joe Celko (1/22/2009)[/b][hr]Did you know that CURRENT_TIMESTAMP has worked in SQL Server for several release now? The only excuse for talking like a hillbilly is "job security programming" or ignorance.[/quote]1) Yes, I did know that. And I don't care. Did you know that Microsoft has not deprecated getdate(), therefore there is absolutely no reason for myself nor any of the other 99.873% of the Microsoft SQL Server developers out there who ARE NOT CREATING CROSS-PLATFORM CODE to make a shift to CURRENT_TIMESTAMP? 2) I will let the personal attack slide. You are't worth a reply on that one.[/quote]As always, there is a proper way and there is a propriatary way.It sure pays off knowing the proper way, but it is fun to know the propriatary one.If you don't have a standard "use case" to solve requests, you will end up with hunderds of flavours to accomplish the same thing.(not only) Juniors will struggle with that so it is prone to errors.For any company, it is a good investment to have a guideline to apply.And it is so hard to change a bad habit :hehe:Fri, 23 Jan 2009 00:05:03 GMTALZDBAhttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspxWith SQL Server 2008CREATE NONCLUSTERED INDEX IX_MyBitField ON MyTable (MyBitField)WHERE MyBitField = 1Thu, 22 Jan 2009 23:25:47 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]LeeBear35 (1/22/2009)[/b][hr][quote][b]TheSQLGuru (1/22/2009)[/b][hr]>>Any DBA who thinks an index on a bit field is a good idea.You have a billion row table, with from 0 to 100 rows at any one time having mybitfield = 1. You routinely run queries with a WHERE clause that is or includes mybitfield = 1. Would you want an index on mybitfield?[/quote]You have a billion rows that are going to have the bit as either 0 or 1, ideally the analyser will skip using the index and result to a table scan because the index sets will be too large. Remember if you have to read the index, then the table you have done two i/o's (actually when you account for paging, it is much more) to get a record. When creating indexes you want to get a value that will get you to a row or smaller set of rows.[/quote]Be sure o read the question. IF I had a table with one billion rows, and ONLY 0 to 100 of those rows had a bit field, called mybitfield, set to 1 and I routinely ran queries that included in the WHERE clause mybitfield = 1, you can bet that an index on that field would be of GREAT benefit. The index would quickly get me to those 0 to 100 records out the total 1 billion records.Thu, 22 Jan 2009 19:32:15 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic430004-203-1.aspx[quote][b]Joe Celko (1/22/2009)[/b][hr]Did you know that CURRENT_TIMESTAMP has worked in SQL Server for several release now? The only excuse for talking like a hillbilly is "job security programming" or ignorance.[/quote]1) Yes, I did know that. And I don't care. Did you know that Microsoft has not deprecated getdate(), therefore there is absolutely no reason for myself nor any of the other 99.873% of the Microsoft SQL Server developers out there who ARE NOT CREATING CROSS-PLATFORM CODE to make a shift to CURRENT_TIMESTAMP? 2) I will let the personal attack slide. You are't worth a reply on that one.Thu, 22 Jan 2009 17:18:30 GMTTheSQLGuru