InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 00:18:24 GMT20http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]Tom.Thomson (5/13/2009)[/b][hr][quote][b]Manie Verster (5/11/2009)[/b][hr][quote][b]GilaMonster (5/11/2009)[/b][hr][quote][b]Manie Verster (5/11/2009)[/b][hr]I never see any articles about bad DBA's, no sirree. They are the good guys. The people that so arduosly defend their databases against developers that will come in like viruses and destroy their databases.[/quote]Maybe you didn't read my comments (Posted 5/8/2009 3:15:16 PM) - the first thing pointed out there is that the really serious errors that developers make are just the same as the really serious errors that DBAs make. ANd I make the particular point that more DBAs write row by row stuff than developers (because the developers never went on any of those really awful courses for DBAs that pay more attention to understanding cursors and all their beauties and complexities than to sets.I don't think that the editorial had any anti-developer bias: if I had thought that I woul have had a few rude things to say. I did think that some of the comments were rather anti-developer and showed a stupid attitude, but you can't blame the original editorial for that. Maybe the editor should have guessed that he would get some comments of that sort - but whatever you write is going to provoke some idiot responses as well as lgood responses, so is the answer never to write anything?[/quote]Tom, you did not read very well. I will quote myself below where I said that:[quote]Steve, you said that you did not mean to generalize or to degrade developers here (can't remember you exact words) but there are people (DBA's) that log on to this site and generalize and absolutely slaughter the developers. [/quote]and if you checked further on page 10 I posted an apology for my rants. Allow me to apologise again here. I am really sorry for the things I said. Someone said to me one day that if people anger you then they anger you and for a moment there I allowed these people to control me. I hope that I will remember this the next time before I allow people to anger me.Fri, 15 May 2009 14:48:49 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]Manie Verster (5/11/2009)[/b][hr][quote][b]GilaMonster (5/11/2009)[/b][hr][quote][b]Manie Verster (5/11/2009)[/b][hr]I never see any articles about bad DBA's, no sirree. They are the good guys. The people that so arduosly defend their databases against developers that will come in like viruses and destroy their databases.[/quote]Maybe you didn't read my comments (Posted 5/8/2009 3:15:16 PM) - the first thing pointed out there is that the really serious errors that developers make are just the same as the really serious errors that DBAs make. ANd I make the particular point that more DBAs write row by row stuff than developers (because the developers never went on any of those really awful courses for DBAs that pay more attention to understanding cursors and all their beauties and complexities than to sets.I don't think that the editorial had any anti-developer bias: if I had thought that I woul have had a few rude things to say. I did think that some of the comments were rather anti-developer and showed a stupid attitude, but you can't blame the original editorial for that. Maybe the editor should have guessed that he would get some comments of that sort - but whatever you write is going to provoke some idiot responses as well as lgood responses, so is the answer never to write anything?Wed, 13 May 2009 12:30:15 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]GilaMonster (5/11/2009)[/b][hr]If you want a rant - [url]http://sqlinthewild.co.za/index.php/2009/04/17/developers-vs-dbas/[/url][/quote]Very nice Gila, I like this way of thinking. I think everybody can go and have a look at this discussion. Working together rather than against each other makes sense.Wed, 13 May 2009 00:28:15 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxIf you want a rant - [url]http://sqlinthewild.co.za/index.php/2009/04/17/developers-vs-dbas/[/url]Mon, 11 May 2009 14:09:27 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]Bob Lee (5/8/2009)[/b][hr]Amen to the injection. Field validation in the applicaiton is a biggy.Databases are often used as spreadsheets. Just like developers are taught in school. Data access can be an art form too because all record sets are not sized equally. A DBA can help the developer here. We often know the data better than the application developer but are clueless about the application. It changes but those changes aren't uploaded to the DBA's KB.I think that we could spend some quality time with our developers and not looking at them as the impediment but as an opportunity to tout our benefits.SELECT * FROM DEVELOPERS WHERE CLUE IS NOT NULL;Best way to resolve the query above is to lend a hand or to "insinuate" yourself into the development process from the very begining. Involving the DBA with the business side of the application as well as the data is a winner. Bob[/quote]Gila, I have to apologize. There is some good DBA's out there and yes, I was wrong about the blood. I went back through this thread and read through the posts again until I came upon this jewel of a post which I had missed. This is what I have been saying in the past. I know there are in every profession those who have either not been properly trained or they just try to do the job to get it done regardless of what end users of their product/work have to endure.Bob, you said it. Developers that do it wrong need to be told they do it wrong and not cut out off the process. I will be the first one to admit that I had written some code in the past that makes my hair stand up. Fortunately, I had people that were willing to show me the way.Steve, sorry for my rants. I will count my words next time!:hehe::hehe::-D:blush:Mon, 11 May 2009 13:34:36 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxNo assumptions, bitbucket, on the success or failure of a project. This is at a lower level of where developers make mistake in the coding, not in interpreting the requirements or executing them.Mon, 11 May 2009 11:59:33 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote] Steve JonesThis thread was designed to highlight things that developers commonly do wrong[/quote]Let me define what I understand a Developer task - who who develops interface code NOT one who develops SQL code, defines tables, etc.Having been a developer, I strong resent the assumption that developers are at fault for a poorly executed DB project. I will state clearly that it is [b]management[/b] that makes the mistake. All too often management is composed of bean counters (accountants) or those with a degree in management science who have not a shred of technical understanding, or how to manage a technical project. Managers who allow coding and database design to proceed without a shred of documentation, starting with what the results of the project should be, who will be using the developed code/database, what will be the data input, etc., etc. In other words without a formal specification. They then compound the issue by not having specific software and database designs documented. Since they lack the necessary foundation upon which to build they never plan to hold review meetings, where developers and DBAs present and discuss their respective designs and modify each where necessary. To sum it up they fail to see that the project requires a team effort where developers and DBAs work together exchanging ideas and each part of the team does what they do best. Last, but not least they fail to appreciate that each group, developers and DBAs, learn the others strengths / weakness so that the next project proceeds to a successful completion at the least cost.Mon, 11 May 2009 11:54:59 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxFor the record, I'm an application developer first and a database developer second (as determined by time devoted to each category), but I took no offense. To the contrary, my personal experience has been that many, if not most, of my fellow developers aren't terribly adept at db development and maintenance. But that's not necessarily the problem. It's the ones who don't care (at the expense of their customers) that get my goat. Such developers are more prevalent than some want to believe. Those who don't fall into this category (i.e. developers who do care about proper db admin/development) have no reason to take offense at Steve's reasonably accurate assessment.Mon, 11 May 2009 10:27:59 GMTChris-232075http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI think I mentioned in the editorial that I appreciate what developers do. And I'll welcome a "problem with DBAs" guest editorial if you want to write one.And I know I make mistakes. This thread was designed to highlight things that developers commonly do wrong. I'm sure we could have a much longer, and more heated, discussion about what DBAs do wrong.Mon, 11 May 2009 09:26:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]GilaMonster (5/11/2009)[/b][hr][quote][b]Manie Verster (5/11/2009)[/b][hr]No, you're right, I just get the feeling that developers are getting slaughtered over here. I see blood all over the place and people seem to forget that developers also visits this site and also learn from this site.[/quote]Where's the blood? I saw a lot of posts talking about things that developers often forget/ignore about databases (probably because they're not DB specialists). The only insulting generalisation I saw was yours. Maybe I'm just reading the posts with a different point of view. I'm a developer. Jeff's a developer. Barry, iirc, classifies himself as a developer. There are a lot of developers around here, you're by far not the only one.[/quote]Gila, I think you must take those rose tinted glasses of yours off. Yes, not all the posts was about developers and yes, I agree there is bad developers or developers that forget as you say. I also saw a lot of slaughtering take place. Anyway, I also remember a couple of other developers in this thread that complained. I never see any articles about bad DBA's, no sirree. They are the good guys. The people that so arduosly defend their databases against developers that will come in like viruses and destroy their databases.Mon, 11 May 2009 02:35:20 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]Manie Verster (5/11/2009)[/b][hr]No, you're right, I just get the feeling that developers are getting slaughtered over here. I see blood all over the place and people seem to forget that developers also visits this site and also learn from this site.[/quote]Where's the blood? I saw a lot of posts talking about things that developers often forget/ignore about databases (probably because they're not DB specialists). The only insulting generalisation I saw was yours. Maybe I'm just reading the posts with a different point of view. I'm a developer. Jeff's a developer. Barry, iirc, classifies himself as a developer. There are a lot of developers around here, you're by far not the only one.Mon, 11 May 2009 01:29:40 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]lmu92 (5/10/2009)[/b][hr][quote][b]Manie Verster (5/10/2009)[/b][hr]...but there is one thing that stabs me straight to the heart and that is the generalization of developers. ...Now here is a generalization for you. I think DBA's are arrogant![/quote]Hi Manie,I think there's no generalization so far in this thread. It's just a summary of what has been seen from the "bad side of programming". Unfortunately, this looks like it refers to all developers.But that's definitely not the intention as far as I can see. If you look at my post earlier in this thread I described both programming extremes I've been faced with: the worst AND the best. And, if you look at the worst I described I'm pretty sure you wouldn't consider those developers as being qualified to be called so.The generalization regarding DBA's you put in your post most probably isn't your true opinion of DBA's rather than it's driven by your current mood - which I can understand to some extent.Since you've been around this forum for a while I'm sure you have noticed that there are all kind of folks around: DBA's, developers, consultants a.s.o.Maybe you'd feel better if there'd be a separate thread "Checking up on DBA's" followed by "Checking up on Consultants". Most probably you'd get the same amount of replies with the same scary stories and for the same reason: Nobody is perfect.So, what I take out of this thread (and would take out of the others if started) is simple: What do I have to look for during my daily business to make the "final product" better for the end user. It's more "mistakes to avoid (focussed on the subject)" than "developers are stupid (focussed on a group of people)".[/quote]No, you're right, I just get the feeling that developers are getting slaughtered over here. I see blood all over the place and people seem to forget that developers also visits this site and also learn from this site.Steve, you said that you did not mean to generalize or to degrade developers here (can't remember you exact words) but there are people (DBA's) that log on to this site and generalize and absolutely slaughter the developers. Next time please write a warning on the top of your article stating: "Developers might be slaughtered here. Not for developers to join." Why can't we have articles from which a person can learn or something where no human being will be degraded or slaughtered.I feel like I might have to salute the next time I walk past a DBA and say: "Sir, Yes Sir!"Thank you, I have ranted enough here now.Mon, 11 May 2009 00:48:50 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxGuys and gals,As first, I'm a developer. This thread really sounds like a "[i]all developers have no idea about databases[/i]"! I read almost all posts and I saw only two people (thanks to Gift Peddie and lmu92!!) who mentioned that there are different people on both sides.I've seen many of the noticed failures done by developers. Anyway I've seen great solutions in design and implementation done by developers.I've seen DBAs who had no idea about index maintenance (no joke!) and I've seen great DBAs keeping the system alive and working good. (Glad to have my current DBA ;-) )GreetsFloSun, 10 May 2009 14:31:59 GMTFlorian Reischlhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]Manie Verster (5/10/2009)[/b][hr]...but there is one thing that stabs me straight to the heart and that is the generalization of developers. ...Now here is a generalization for you. I think DBA's are arrogant![/quote]Hi Manie,I think there's no generalization so far in this thread. It's just a summary of what has been seen from the "bad side of programming". Unfortunately, this looks like it refers to all developers.But that's definitely not the intention as far as I can see. If you look at my post earlier in this thread I described both programming extremes I've been faced with: the worst AND the best. And, if you look at the worst I described I'm pretty sure you wouldn't consider those developers as being qualified to be called so.The generalization regarding DBA's you put in your post most probably isn't your true opinion of DBA's rather than it's driven by your current mood - which I can understand to some extent.Since you've been around this forum for a while I'm sure you have noticed that there are all kind of folks around: DBA's, developers, consultants a.s.o.Maybe you'd feel better if there'd be a separate thread "Checking up on DBA's" followed by "Checking up on Consultants". Most probably you'd get the same amount of replies with the same scary stories and for the same reason: Nobody is perfect.So, what I take out of this thread (and would take out of the others if started) is simple: What do I have to look for during my daily business to make the "final product" better for the end user. It's more "mistakes to avoid (focussed on the subject)" than "developers are stupid (focussed on a group of people)".Sun, 10 May 2009 13:19:52 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxLet me start by saying I am a developer and proud of it and have been for the last 10 years. 3 years ago I was thrown in the deep end when a client bought a new server and sql server 2005 and expected me to maintain and administer the database and the server. Since I haven't done DBA work before I had to learn very quickly and had to put some plans in place to do backups and other tasks on the database. It was at this point that I came upon SSC. Now, I have learnt a lot here and got a lot from this web site but there is one thing that stabs me straight to the heart and that is the generalization of developers. I think in any profession you get the people that tries to do their best to do their job very efficient and then you get the buffoons that are just doing the job as quick as they can and do not consider what trouble they cause their others. I have said this before and I will most probably say it again and that is that if you see someone (a developer?) do something that is not good for the database then TRAIN them! I'll bet there is some DBA's out there as well that are doing stuff that other people later on have to battle with.Now here is a generalization for you. I think DBA's are arrogant!Sun, 10 May 2009 12:45:42 GMTManie Versterhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI've edited out the "smart" comment.Apologies for those that took it the wrong way.Sat, 09 May 2009 09:53:53 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI'm surprised this one hasn't been mentioned: inadequate or in appropriate definition of business unique identifiers. This one can go to either extreme.The one extreme is to use a GUID or incremental integer for a system-generated PKID on every table, but then completely ignore the definition of actual data that makes for a correlating unique identifier. This allows for duplicates in the database even though the PK itself is not duplicated. It usually stems from the developer/dba (often the same person actually) not understanding the business model, or not fully understanding how to implement it in data.The other extreme (of which I was guilty for many years) is to never use a system-generated key and always used business values for keys. The potential problem with this approach is that concatenated keys will be required, sometimes to many levels, which causes data duplication in many tables, all of which need to be kept up to date. The issues related to changing primary key values and the dependencies on foreign keys are obvious.I've found the best approach to be:a) always use a system-generated key (I prefer a sequencer with an integer for human-readable benefit of being able to tell which records were created first, but this is not really of primary concern; using a GUID helps with other features such as replication and, later on, merging with other databases when the inevitable mergers and acquisitions happen),- and -b) define an alternate primary key (aka unique index) on the data fields that make up a unique identifier from a business standpoint.Using this combination allows for the FKs of other tables to refer to the static, non-changing, primary key, while the second index ensures uniqueness at the business level.And, in the common event that a business-duplicate does get defined and an exception is generated, it forces the data model and business process to undergo more scrutiny because, obviously, something was overlooked the first time around. It causes noise and aggravation, but it's better to get the dust out of the house than to sweep it under the rug where we forget about it. :-)Sat, 09 May 2009 09:46:55 GMTsubscriptions-524733http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxDynamic SQL! Why would you do it? Why?? It's because you hate the DBA, isn't it?On the bright side, the Cloud will magically fix all of these problems for us :-)Fri, 08 May 2009 17:26:50 GMTJames Stoverhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI have worked at a software company before and it boils down to 1 or more of the following which I believe all fall on the decission makers at these companies and not so much the developer who is trying to make a living. Thats not to say that some times developers aren't at least partially responsable such as when they convence the decission makers that they can do it all.1) [b]Ignorance[/b] - [size="3"] NOT understanding[/size] that a well designed application consists or more then good coding in ([i]insert favorite procedural programming language here[/i])2) [b]Arrogance[/b] - [size="3"]Not caring [/size]that a well designed application consists or more then good coding; seeing database design, user interface design & user accessability & understanding of the application as being superfolous. 3) [b]Greed [/b]- Being to [size="3"]cheap[/size] to do it right; putting of till a later Service Pack/ Relase/Update ([i]especially when its for paid support clients only[/i]) what should have been in the initial releaseOf these it seems to me like [b]#2[/b] & [b]#3[/b] take the lead (in frequency) with #1 being a distant second in the pack. The market that the vendor caters too seems to also weigh heavily on whcih of these is most prominent. For example vendors who make apps for the more technical markets are far less likely to be Ignorant as Arrogant or Cheap while those vendors catering to the less tech savvy are sometimes as ignorant to what should be done and how as the clients they cater to. [i]Q: How then can vendors like this sell tehir products and make profit?[/i][b]A: [/b]Unless a vendor has a solid lock on a market (as is the case if there is no competitor) they all know the importance of doing marketing & sales the right/proper way. I bet you won't find a single vendor who sells porrly designed products have their programmers making sales calls or passing out material at conferences.Fri, 08 May 2009 15:32:14 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxone of the common mistakes of the application side is:Create / Alter / Drop SQL objects on the fly.With the implementation of DDL of SQL2005, I had so many confrontations because of it.Another one is to drop / disable the FK constraints in order to update the parent tables key values, ...... re-create it back or just forget to put it back......Fri, 08 May 2009 15:28:20 GMTDavid Luhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxAs a developer and occasional "accidental DBA" I am intrigued and very interested in these types of topics. In my current role I am a senior developer and work new projects and optimizing existing procedures. I currently have one that we reduced from 14 hours to 4 hours in a "test" or "cert" database but unfortunately the first production run only reduced to 11 hours. This was with DBA input as well. I mention this because even with the best efforts by developers and DBA's, it sometimes takes a few iterations to really tune a procedure ... especially in a production environment ;-)Fri, 08 May 2009 14:23:55 GMTbrad.ashforthhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxIt seems like some of you folks had a chance to look at the 3rd-party code I'm struggling with at the moment...You'll find almost everything that's mentioned already, but within a single proc!And there's one thing that needs to be added to the list: using table variables where ever possible.Scenario:The original proc itself has just a little less than 1.000 lines (when you exclude the lines for documentation purposes, the number of lines will remain exactly the same ...), grabbing data of roughly 10 tables with a few hundred lines each.The output is a table structure with about 500 lines (note: it's a SP, not a function...).Inside the proc all subresults are stored in table variables, even if it's the result of a cross join (!) of three or more tables (some internal table vars go as far as a some ten thousand lines...) and, of course, the result is heavily used by other joins later on.There is no CTE or anything like it. Instead of this there is a cursor at the end of the proc, grabbing each value of an intermediate table var, doing some IF .. ELSE ..IF.. stuff on it together with a "need" while loop in between and inserting the result into yet another table variable having just one column more than the previous one - containing the value calculated in the cursor.It's mandatory to say that not a single base table has more than the primary key index.Result: Complains from the end users regarding performance of the new software. (approx. 10 seconds for a spreadsheet with the 500 values as from above...)That's definitely the worst thing I've ever seen being proposed as a solution from a Software Dev. company, but there's always one more thing that can make it worse... In this case it's the answer from the vendor regarding the performance complaints: "It's state of the art and since you requested SS2K5 together with SSRS and .NET framework there's nothing more you can ask for in terms of performance!"So, I've been asked to have a look at it without changing anything.By just modifying the procedure code via SSMS (T-SQL with no cursors, indexed temp tables where required, CTEs instead of repetitive huge joins a.s.o.) it went down from approx. 7 seconds to just a friction of a second to get the stuff done (including the output of the 500 rows in SSMS). The source code after two days is about one third of the original, keeping the code formatting structure. Note: There is no index on a base table yet. I'll keep that for later on...Did you see anything worse yet?I also have to say that I've been involved in software projects with programmers that really, really did know what they were doing. I had one project where I needed to schedule an immediate meeting with some of the programmers and I've been told that they cannot participate because they were going to a local SQL user group conference at the same time and they'd like to discuss some of the issues we had within this group. I decided to reschedule my meeting... The results proved me right...Bringing everything down to the point:It would be false to claim all programmers guilty by default (which, afaik, didn't happen in this thread). But I do see a reverse correlation between the price for a software project and the resulting code quality.PS: Thanx for letting me vent. I'm feeling better now!!!Fri, 08 May 2009 14:13:37 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxuser defined functions in WHERE clauses.Fri, 08 May 2009 13:43:53 GMTGRE (Gethyn Ellis)http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxTo add to the list, inappropriate domains and inappropriate physical representation of domains is a common design flaw. A case in point is the msdb jobhistory table.Inappropriate domain is a problem with column run_status, which has possible values of 0 = Failed, 1 = Succeeded, 2 = Retry, 3 = Canceled, and 4 = In progress. That is at least three different pieces of information in one column - Executing (a Boolean), Executing Status (many values), Successful Completion (a Boolean) and Successful Completion Reason (Failed or Cancelled). Inappropriate physical representation of domains is most famously known as the Y2K problem and can be seen in these three columns: run_date int NOT NULL , run_time int NOT NULL , run_duration int NOT NULL ,Column run_date has an internal format of CCYYMMDDColumn run_time has an internal format of HHMMSScolumn run_duration has an internal format of HHMMSSHere is Microsoft's description on how to calculate the end time: CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 )+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 ) / (86399.9964 ) AS End_DateTimeThis would be a lot simpler with 2 columns: run_timestamp datetimerun_duration_seconds integer (for 10 hours, a value of 3600 would be stored).Fri, 08 May 2009 13:37:17 GMTCarl Federlhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxHow about these little gems:INSERT INTO some_tableSELECT * FROM some_other_tableOr this method of calling procs that I have found in embedded far too many applications:EXEC dbo.some_proc 'parameter1', 'parameter2', 0, NULL, 'parameter5'Add an extra parameter in the middle of the parameter list of the proc and you get some interesting results...Lastly, it seems that quite a few developers just don't want to listen to us DBAs!!Fri, 08 May 2009 12:30:27 GMTj.a.chttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx"You wouldn't expect Lebron James to be able step into a Cleveland Browns uniform and play quarterback."Actually He does play for the Cleveland Browns...... in a commercial in his dreams :-DSeriously, I was a bit surprised when I saw Steve's quote about developers not knowing the difference between a PK and NCI. I also figured it was worded differently; that it wasn't meant to be derogatory.kenFri, 08 May 2009 12:16:25 GMTken.trockhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxBoth sides are full of crap the developer thinks the database is storage to the application so data is dumped into the database. The DBA says give the developer read only permissions. These are the reason for about 80 to 90 percent of software development and maintenance problem because both are not in touch with what is needed to develop scalable application. The DBA needs read only permissions to databases and the developer needs to see users timeout while accessing the storage database.I remember telling a bank IT security team to call the bank VP they have decided to close his online banking for security reasons. The reason these security people decided the Agent account proxy account should be disabled, one pesky problem the Agent populates online banking SQL Server with DB2 AS400 deposits.Third party application problems either corruption or control problems, IT control is impeding other department needs for tools and IT departments buying applications to keep someone's brother in-law employed.;-)Fri, 08 May 2009 12:04:38 GMTGift Peddiehttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI agree with you completely about litigation.[quote]As far as litigation, I'm don't know what to do here. Sales misleads customers often, and when things don't perform as they are advertised what is the alternative? Complain? Too many companies don't do anything about that and once you've installed a product you cana) re-tune some things, and possibly violate a support agreementb) live with itc) litigate[/quote]In these cases, where sales misleads customers, complaints about a product are ignored, and general attempts at mitigating issues with the product are ignored, then litigation is probably the only choice left. From the original editorial, I took what you were saying completely differently in regard to litigation.[quote]Apologies, but I don't believe I called all developers reckless.[/quote]No, you did not. I did. Some developers are reckless. Some developers will completely mess up tasks related to SQL because they don't care that they are doing them incorrectly.I do appreciate the advice given on this topic. I want to know when I'm doing something incorrectly. I don't want to mess up a database because I'm ignorant of how to do things correctly. Topics like this are useful to me. A few key comments in your editorial seemed a bit strong or not fleshed out enough. That was what incited my previous post, not the topic itself.Fri, 08 May 2009 12:02:22 GMTgjohnson-677409http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI will agree with some of the statements about litigation but not litigation purely because of performance of the product. The issue may begin with the performance of the product due to poor database interaction but when it gets to an appropriate time for litigation, the issue should have grown to more than just a technical one.If the (3rd party) product does not meet the stated performance abilities and the company does not address the issues when brought up thru the appropriate channels (support -> managers -> executives), then litigation may be a last resort but (to me) the issue then is more about the company being unresponsive and misrepresenting the product than simply bad database indexing.A company should stand behind the product and the people building it. Additionally, if litigation is involved, I would hope it is against the company and not the developers. Hopefully, if an issue gets to the point of litigation, they will not just turn around and berate the developers for their choices but look at why those database choices had to be made (no DBA to consult with, did not know that they would need a DBA to consult with, too cheap to hire a DBA or developers with good database skills, etc.)Fri, 08 May 2009 11:50:26 GMTDerek Bakerhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]Shane Petroff (5/8/2009)[/b][hr][quote]IMHO one of the biggest mistakes a development shop can make is failing to tap the power of the database engine by placing all the T-SQL in the application code rather than using stored procedures.[/quote]Any my pet peeve is the pervasive myth of stored proc performance due to pre-compiled caching.[/quote]I totally agree, and it detracts from the primary good reasons to use SPs, which is control, design and maintenance.Control, because you can use execute permissions to control access.Design, because SPs can be used to structure an interface layer and can be used from within each other to avoid redundancyMaintenance (by design), because SPs can be maintained independently of the application and that abstraction layer is a great place to provide consistent changes to behavior which can be applied in a single place.Fri, 08 May 2009 11:17:01 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]gjohnson (5/8/2009)[/b][hr]This editorial truly frustrated me.[/quote]Apologies, but I don't believe I called all developers reckless. Some of this was in fun, and you might step back and realize I said I appreciated developers' efforts, and this was an attempt to find out what things they need to learn, or that someone can help them learn better.[quote]Was it necessary to, simply put, call developers stupid if they don't have specific knowledge about database indexes? [/quote]I didn't call them stupid, though saying they aren't smart isn't much better. I should have said "ignorant" of the difference. and you As far as litigation, I'm don't know what to do here. Sales misleads customers often, and when things don't perform as they are advertised what is the alternative? Complain? Too many companies don't do anything about that and once you've installed a product you cana) re-tune some things, and possibly violate a support agreementb) live with itc) litigateI don't want to see a lot of litigation, but many companies do take advantage of customers and it's not as cut and dried as "just buy something else" A little liability (little with limits) might help. Lots of developers throw things together, sometimes because they're not good at things, sometimes because they're pressured to do so.I didn't tear down all developers. I thought I was being proactive, and dozens of people before you seem to have taken it that way. Perhaps you ought to re-examine how this struck you.Fri, 08 May 2009 11:03:11 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx...and another one. No defaults for mandatory fields.You're going along, adding values to a form. Click on the next page. Add a value to a field that you own. Hit "F2-Save" and bang, you're stuck with a "Field xxx requires a value..." error on a field you don't own. You can simply select something, go off and find the person who owns the field and ask what to add or hit "Esc" which wipes out your session.Fri, 08 May 2009 11:02:25 GMTCarlo Clausiushttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote]IMHO one of the biggest mistakes a development shop can make is failing to tap the power of the database engine by placing all the T-SQL in the application code rather than using stored procedures.[/quote]Any my pet peeve is the pervasive myth of stored proc performance due to pre-compiled caching. I don't have easy access to a recent version right now, so I checked it out in BOL for Sql2000 and sure enough:Search forcache execution planThen scroll down to title "SQL Stored Procedures", topic "SQL Server Architecture". Quoting directly:SQL Server 2000 and SQL Server version 7.0 incorporate a number of changes to statement processing that extend many of the performance benefits of stored procedures to all SQL statements. SQL Server 2000 and SQL Server 7.0 do not save a partially compiled plan for stored procedures when they are created. A stored procedure is compiled at execution time, like any other Transact-SQL statement. SQL Server 2000 and SQL Server 7.0 retain execution plans for all SQL statements in the procedure cache, not just stored procedure execution plans.To see the content of your cache execute:SELECT text, plan_handle, * FROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(sql_handle)CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp(Connor Cuningham's older blogs are down right now so I can't make any more precise attributions or refinements to the query here)If you use parameterized sql in your application code, you will see it in the cache and know that the "power of the database engine" is indeed at work. You will also note a distinct lack of stored proc code from less frequently used procedures, validating the BOL quote above.Fri, 08 May 2009 11:01:33 GMTShane Petroffhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxAs a developer (currently working mainly in T-SQL for backend processes), it's often better to not have a DBA at all than to have one who doesn't understand development or doesn't participate constructively in the database design.A LOT of DBAs don't know how to write much T-SQL, don't understand the relational model and don't know much about systems architecture - designing for maintenance, product lifecycle, and flexibility. They just handle backups, storage and service packs and escalate performance issues to the vendor or developer or senior DBA. I call these functional DBAs and I see this a lot - especially if there is not a lot of inhouse development. It makes it expecially hard to hire a DBA for a development intensive shop, because you are looking for a lot more experience and exposure in design and architecture - i.e. 5-10 years instead of the always advertised minimum 2-3 years.In all cases, I assume I will not be able to simply ask a DBA to tune my tables - I build the indexes up front (in addition to the PK and choice of CI) as part of the DB design. I tune the procs to perform with data and adjust indexes as merited.Fri, 08 May 2009 10:40:49 GMTCade Rouxhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxThis editorial truly frustrated me. I am primarily a developer, but I also have to work with SQL Server with some frequency. I do not claim to be a DBA. I know that I make some mistakes when writing queries and stored procedures, developing Reporting Services reports, and who knows what else. I appreciate the advice that database professionals post on sites like this one. I also realize that there are developers that are reckless when it comes to databases. Calling all developers reckless when it comes to databases is a broad generalization, and, in my opinion, this editorial when completely overboard though.[quote]Either they don't realize that the PK is an index, or they aren't smart enough to know the difference between a CI and an NCI.[/quote]Was it necessary to, simply put, call developers stupid if they don't have specific knowledge about database indexes? Is it okay for a developer to call a database professional stupid because he or she doesn't know the intricacies for C# or Java? I don't feel that it is. Thus, I don't feel that it was okay for Steve Jones to call developers to task for not knowing about indexes.[quote]If you're selling a product, I think you ought to know how to tune it for a database. If you don't, I'd like to see some recourse for clients. Maybe some common settlement in a lawsuit. A few of those and I bet you'd have more DBAs hired by software firms.[/quote]Really? It's a good idea to introduce litigation that could lose a developer a job and possibly a career? Unless the goal is to make development industry akin to the medical industry where developers and database professionals have to take out insurance to help protect them against litigation, this idea is foolish. If a customer has a problem with the way a product is built, they should take their complaints and ideas to the software company. Let's keep the computing industry as proactive as possible, not a place where we solve minor problems in court.Once again, I don't mind criticism, but keep it proactive. I don't appreciate anybody tearing me and my profession down. Even if your intentions were to give us developers advice, which I appreciate when helpful, the approach taken in this editorial was poor, and, in my opinion, equated to a very public flaming of developers. Take into consideration the situations that developers are put in. Not every developer has a DBA they can turn to. Some developers get thrown into situations where they are forced to do database work even if they are not qualified for it, and, no matter how much they try, can only gleam so much information from sites like this one. So, cut them some slack when appropriate. You wouldn't expect Lebron James to be able step into a Cleveland Browns uniform and play quarterback. So don't expect developers to be DBAs either.Fri, 08 May 2009 10:21:03 GMTgjohnson-677409http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI know this sounds simplistic, but a few things I found in a third party ERP.Multi value, multi type fields:Ex: "30 1LAP0 " Read it and weep! Each character represents either a field value or a flag setting! Spaces are either a missing values or space for more then one character values. :w00t:Inappropriate field location in a table. I have a field value that has a one to one relationship with a record, yet it's stored in a time stamp table. The result is that the same field value is multiplied over and over again (tens of thousands of instances). :crying:Fri, 08 May 2009 10:10:55 GMTCarlo Clausiushttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxvarchar PK's!?! If we could only carry guns in the office.Fri, 08 May 2009 10:06:42 GMTMark Cooper-1068662http://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxAs Cade alluded to earlier, and perhaps others I didn't have time to read all the replies, non-clustering indexes can perform better. On a large table with many columns, a small covering index on the identity/primary key can satisfy numerous queries that have high selectivity faster than the PK.Fri, 08 May 2009 09:55:10 GMTdigitaloxhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspxI will pass the links along to the lads. I probably passed the one before, I think, as it is quite familiar.Then they willc laim they had no time to read them. :-PFri, 08 May 2009 09:48:04 GMTFrank Buchanhttp://www.sqlservercentral.com/Forums/Topic712574-263-1.aspx[quote][b]Frank Buchan (5/8/2009)[/b][hr]I would love to see a real, simple, and developer-focused article or two about indexes. Drop all the terminology possible, and just shape an understanding so the developers can grasp what and why indexing is so useful. As it stands all the articles are either too superficial for them, but require terminology they don't necessarily have, or too specific.[/quote]Not really developer focused, but have a look at these (disclaimer, they're all mine)[url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/[/url][url]http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/[/url][url]http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/[/url]I'll put 'intro to indexes' on my list of things to write about.Fri, 08 May 2009 09:31:59 GMTGilaMonster