I think I speak on behalf of all developers when I say: Whatever

PingBack from http://scarydba.wordpress.com/2008/11/25/sign-me-up/

Do we have a nifty logo with bullet holes or something?

Just wear the tee-shirt, the bullet-holes will come.

PingBack from http://sqlbatman.com/2008/11/join-the-battle/

"Do attribute to malice that which can be explained by stupidity" -- attributed to various sources

I have had a theory about most bad SQL for years.  It comes from good "something else" that just does not work in RDBMS.   People like to have what  Michael Shermer calls "patternicity" in their mindset.  (http://www.sciam.com/article.cfm?id=patternicity-finding-meaningful-patterns).  They do  not make attempts at programming randomly, like monkeys on a typewriter.  

When you learn a new spoken language, you keep your old pronunciations for awhile.  You keep your old grammar (lots of fun when you go from "subject-verb-object" to topic languages with particle markers like Japanese) for awhile.  Learning is hard, but UN-learning is harder.  

A computer language will take care of the syntax by spitting out error messages until you get something that compiles. But it cannot look at your DDL and see that it mimics a sequential tape with bit flags and record numbers.  Recently in one SQL Server newsgroup a newbie posted a model of the first disk file systems invented over 40 years ago.  

He had pre-allocated the table (contiguous disk file) and filled it with dummy rows (records) to hold the space.  The flag he used was '9999', which old farts will recognize as 1950's COBOL programming to force the punch cards to sort to the end of the file.  He even made this numeric data element into a string, just like a punch card.

Since each account had dummy rows, there was no key possible.  If you had grown up with early mainframe computers, you knew what he was doing on sight.  

When I teach, I want to tell the student that they are wrong, but I also want to tell them how they came to the wrong answer.  

PS: can i get a tee shirt?  It will look fun with my black 3-piece suits, but what the heck!

Recently we had a project that needed to be completed ASAP (don't they all?), so the developers attempted to keep me out of the loop so they could design the database using one table. Yes, just one.  Of course, I knew about it and it really made me uncomfortable.  But in the end, I just had to let it go. It was, after all, just a MySQL database.

Where can I buy the t-shirt?

I'm in.  Do I need to get the haircut? I'm almost there already.

You've got to make a poster of this for DBA cubicles.

I can do the stern posture too.  My wife calls it "Command Mode".
One of the remnants of 20 years in the USAF.

XL for the T-shirt.

LOL

" They do  not make attempts at programming randomly, like monkeys on a typewriter."

With all due respect - you've obviously not seen some of the stuff I've seen over the years

I was once called in as a consultant to fix a web application that was mysteriously losing data and thus losing lots and lots of money. Diving into the database I saw the lovely combination of using SELECT MAX(SEQ)+1 as a serial number generator PLUS not putting any kind of constraints. Primary keys? Who needs them!

Does the T Shirt come in blue? I like blue.

Rather than deal with NULL, the company I am currently at decided to default all foreign keys to Zero and not to enforce referential integrity. At least I have moved them into using stored procedures and thank goodness they are finally moving out of Access 97 as a GUI for the database. Unfortunately, they are going to Cold Fusion as the back end. Another "good" decision.

3XL for the shirt. I am all for it and willing to join at any time.

Where are the software database developers?  Do they really exist?

I, like most software developers, came across the database as a necessity, not a luxury.  I started with dBase files, then to BTrieve for five glorious years, then reluctantly (and regrettably) dropped back to Paradox files, before finally being force fed SQL Server.  I guess I was fortunate, because even in the dBase days (which was high school, actually), I learned the benefit of using keys (and they weren't always sequentially incremented identity values).

I was reluctant to embrace SQL Server, because years earlier I was involved with a prototype port from Paradox to SQL Server 6.5.  The application was SO much slower on SQL Server 6.5 that it just blew me away.  And Paradox is dog slow, especially compared to BTrieve.  Today I'm convinced it was because the port still used a flat-file style database design, rather than efficient SQL statements.  (Although SQL Server 6.5's performance certainly wasn't helping.)

I learned a lot about SQL Server 2000 the first three years.  And then we lost our senior database guy (another application developer who decided on his own to "own" SQL, if you will), and the privilege landed in my lap.  Learning what not to do with SQL Server, the hard way, is not fun (or cheap).  I would have never thought an index or a cached stored procedure execution plan could ever be a bad thing.  :(

Four years later, management agrees to hire a dedicated database developer.  The only problem is that they don't exist.  At least, not around here.  All we could find were DBAs who knew very little about application design and development, or developers who only wrote SQL statements because they wanted a paycheck.  We finally hired a DBA.  But a year later, he still struggles with the software development aspect.  Unfortunately, that's what we do.  We don't develop databases.  We develop software that, coincidentally, stores a lot of information in this mysterious software called SQL Server that has a mind of its own and was written as though it is the only product installed on the box other than the OS (which isn't real world).  In ways, it wants to be the OS too.  But I digress, that's another blog post altogether.

I had eight years of software development experience before joining a project that used SQL Server.  Since then, I've spent eight years of my life learning how to develop applications that use SQL Server as a backend.  It isn't the same as developing applications that use a different RDBMS, and certainly not the same as applications that use flat-file storage engines.

What I think the industry needs are individuals with strong database development skills, who understand the software development needs of a database and the production administration needs of a database, and are skilled at creating solid, balanced, database solutions for applications.

Perhaps they're out there.  I've never met one.  :(

Sorry for the long post and the rant.  But thanks for getting to the end.  ;)

Oh, and your post doesn't apply just to .NET developers.  I think it applies to most software developers.  :)

I have been programmer as well as DBA for 7 years now. Earlier I was a programmer. After I switched over to DBA one year down the line I started to get grey hairs, now I can count the black hairs (pre-mature aging).

I have some peculiar groups that I worked with. A developers group created index and in all their application code they used the WITH INDEX to force SQL Server to use it. No SP's. Result - code that could take 3 seconds now runs for 25 to 35 minutes. Again non parameterized. Ahhh! I cannot take any more

MS CRM - I should not touch the database or even create a new index in it. Hmm. I created one and got my fingers burnt badly. Why am I even monitoring the server I dont know. It is running much worse than my dbase application that I wrote on a pc-386 machine during my student life.

One more group avoided using text data type. Seems difficult to search text in it. Made use of varchar(....) in SQL 2000.

One group is trying to do without any SQL code. Using LINQ. No SP's. No parameterized queries. Cool!

Another one using Cursor based application. Users complain to me that the database is slow. I have a 3% CPU and less than 1% disk utilization

Give me a red color t-shirt with an Orange text. I want it to give a striking appearance. Enough of grey hairs. Let atleast few black hairs remain with me till I reach 50.

I am a database developer, and I totally agree.  I came from a C/C++ and OO background, but at some point I had to learn Access for a backend database and when I grok'ed SQL, that was it - from SQL Server 6.5 till now, I have been a firm advocate of a strong database which is well designed and protects its perimeter and a strong client design which respects the database boundary as a service in a design-by-contract scenario.

Every team should have an experienced tech lead who understands RDBMS AND whatever client platform(s) the project is using.

The absolutely silliest thing about the state of RDBMS expertise on most projects is that a LOT of the information on good DB design is transferable - even more so than the other technologies (Python <> Ruby <> ASP etc.).  So you only need a solid SQL grounding in one platform and you'll be far better situated on Oracle, SQL Server, MySQL or whatever than most people on the team.

And a SQL Development DBA is far more flexible and scalable than a production or what we used to call "functional" DBA - I use a lot of automation, code generation and generic tools that really could boost a variety of larger teams.

I've already got the pose....now I just need the T-Shirt and I will be fully prepared to fight the good fight.

I wholeheartedly agree that bad database design and practices can crater a project and that having a good DBA is integral to success.  And Jon Robertson is correct - this is not only true of .Net and SQL Server development, but of all projects that have a database component.

With Microsoft you are unfortunately swimming upstream against their marketing machine.  I have sat through too many Microsoft presentations where managers are told that SQL Server is so simple and so easy to use that there is no need to have trained staff.  They drag out their pretty visual tools and go through some canned demo that has no real world flavor at all but looks pretty and sell the database as a black box that will run of its own accord.

I am working on a project where we recently lost our project DBA and they are not going to replace her.  Keep in mind that this is in a place that has Oracle DBA's, but management does not see the need for a SQL Server DBA because "SQL Server isn't nearly as complex as Oracle."  Fortunately I am just a contractor - but I have seen this mindset in far too many places.

As a developer I recognize the need for good database standards and practices.  However, until you can get the "authority" in the matter to start being a little more candid in their presentation of the product - this idea that SQL Server databases just run of their own accord and that any IT resource can design and manage them is going to be a tough obstacle to overcome.

JonRobertson:

"Where are the software database developers?  Do they really exist? "

*stands up. clears throat. steps up to the mic.*

My name is Adam, and I'm a database-focused software developer.

We're a rare breed to be sure, but we're out there if you look hard enough (and pay us enough to leave our current gigs <g>)...

From (stupid but very commonly used) it-manager perspective: it's way to cheap to get another server/disk/... for sqlserver than to invest in best practises.

From a more dba perspective:
- keep pointing to design flaws when you extinguish the next performance fire

Especialy now, having budgets cut to 0, DBAs will have to get comfortable being fire fighters. As long as you can cope with getting it back on track, you'll keep your job.

JonRobertson said:
Where are the software database developers?

<waves> Hi. I'm a database developer.
--

The best I've seen was at a bank (isn't it always) where the developers had been working long hours trying to get something done with far too little time and far too few resources. They were good developers, and one was fairly good with databases, so this wasn't too much of a worry.

Not that I'd been asked to help out, of course. This was a C# development. No need to involve the DBA team. I only found out about the development a week before it was due to be deployed. (par for the course at this company)

Per my boss's request, and to make him feel comfortable, I glanced over a couple of the scripts late one afternoon (3 days before deployment).

Within 10 minutes I'd found 5 logic problems in stored procedures, 2 potential data integrity issues, 4 procedures with major performance issues and a large security hole.

I spent the next 2 days (14 hours a day) reviewing the rest of the code. (139 stored procedures, 12 tables, 25 views and a bunch of functions)

Of course, the developers didn't have time to fix any but the worst of the problems (and they weren't impressed at having to fix those either) and the development was deployed to production on schedule.

The architect came to me a week later (after the first of the potential data integrity issues appeared in reality) to discuss a rewrite. (Phase 2 he called it). That was about 10 months ago.

p.s. Tony, I want one of those T-shirts. :-)

I need the shirt, even though I spend most of my time in DB2.  Issues are the same.  

There is new hope, in the form of new hardware:  solid state disks.  Sun and Intel have new products.  Tim Bray has a recent blog with stats.  

The reason for hope is that a SSD machine running a 3/4NF database will blow the doors off any flat-file inspired application.  And it could easily be cheaper to build anew rather than accrete further cruft to legacy systems.  It will be interesting times.

<tentatively raising hand> Uh, I'm a database developer. 1/2 a career as a straight developer. 1/2 a career as a dba & database designer. We exist.

My favorite is still the system where the developers built an entire object oriented database out of multi-statement user defined functions. It was beautiful and elegant, like a crystal wine glass. Just as fragile too. As soon as there were more than 10 rows and more than two people accessing the system it ground to a halt like an engine with a failed bearing.

I've got the hair cut. I just need the t-shirt. I'll post a scary picture too.

I totally agree.  I have experience in SQL Server especially in writing stored procs and designing databases for over 10 years.  My IT department within the state public sector has been fully restructured.  I was told that I am being unplaced employee due to unmatched with my current job and personal specification.  They have full OO programmers in the .NET application environment working with the transactional databases.   There is not at least one experienced tech person who specialised in both SQL Server and RDBMS environments in that department, nothing!  They are replacing the mainframe with the .NET application with the back-end of SQL Server databases.  Good luck to them.

Let us assume this philosophy for an automobile...particularly Porsche manufacturing. So let's say that Porsche, since they now own VW decided to start putting Passat engines in their 911's - why?  Well they decided that the 911 engine designers and mechanics were too expensive - it's all aesthetics anyway - right? So they take some of their exterior design engineers and make them engine, linkage, transmission specialists, etc...  Of course out on the street they don;t tell anyone and all of the Porsche shops keep their normal mechanics of course staffed to the same levels.

After afew weeks or months the 'new' Porsche's start rolling in - let's call them VolksPor; if it's body work the aesthetics (Exterior) guys have no issues, everything fits and time lines are met. But the engine mechanics are seeing the cars twice as often now and know they are working on POS engines, the chassis is the same but all the other fun stuff is now junk - they want to work on the good stuff like they used too.  So they move on to other employers and lower class mechanics start moving into the Porsche dealerships.

A Structural Design Engineer specializes for a reason, yes he could do Mechanical and Performance Engineering - but doesn't; and vice versa holds true as well.  This is the same for developers and DBA's.  I can throw together VB.Net, C#, hell even assembly, apps - amougnst other languages, but don't, other than for my own use.  I also know many developers that can write some mean T-SQL and design decent databases, but when it comes to enterprise class systems we stick to our specialties and when that process is followed you end up with a high performance machine, and if you don't, whether that be personally or as a business then you end up with a VolksPor.  If you can afford to pay that price and don't care about performance - keep it up, but don't expect the Porsche mechanic to come around and clean up your mess.

There are two kinds of laws: laws of nature, such as Newton's ones, and human laws.
For even the best practice to become a law, we need to either have a referendum or vote for our representatives who then can create laws. I'm sorry, but this is not the case with database design best practices, they are not laws, to my best knowledge we have not voted in any way. The following also sounds strange to me: "any application built on SQL Server will succeed or fail according to how effectively it uses the database". From where I sit, many applications succeed or fail because of core competency of the business. If the core competence is not storing and retrieving data, then "any application built on SQL Server will succeed or fail" depending mostly on something else, not on the database.

I have had experience as a Sybase/SQL Server DBA for a while now and have worked with some really great application developers (some poor ones, too).  Anyone who's been to school, or taken a class re: RDBMs, regardless of flavor, is familiar with the concept of normal forms.  What I have always stressed to our developers is: instead of chasing ever higher normal forms, focus on modeling reality.  I work in the health care industry and we store Provider data, contract data, discounts, etc.  When looking at how to store a "person" in the DB, I ask the developers to look at what defines a "person," what charactersitics does the bunsiness need to store that are solely attributed to that "person."  As they go through this exercise they can ID candidate keys, columns, logic or check constraints, etc.  Does it really make sense to take a "person" and normalize him/her "up" to a firstName table, a lastName table, a middleInitial table, a suffix table, etc. and have the "person" DB entity merely join the keys from these other tables to represent the "person?"  We have found that the "model reality" method has served us (as a business) very well over the years and has produced very flexible database designs that are easily modified/extended/enhanced to accomodate what the business asks us to store.

TTUEagle:Does it really make sense to take a "person" and normalize him/her "up" to a firstName table, a lastName table, a middleInitial table, a suffix table, etc. and have the "person" DB entity merely join the keys from these other tables to represent the "person?"

Not in the slightest. That's 6th normal form. It's over-normalisation and, I'd like to think that, no good database developer/designer would ever suggest that for a real system. (with maybe a couple of exceptions)

Generally either 3rd or Boyse-Codd normal form is as far as things are taken. That removes most common data anomalies and inconsistencies and doesn't require a million joins to put back together

Arguments about what constitutes "proper, sufficient" normal form almost always devolve into:  coder says, "I prefer to enforce integrity and consistency in my code, so I don't care about normal form"; while database developer says, "I prefer to put the ACID in the database and leave only display and input to the coder."  Needless to say, that doesn't much middle ground.

As to the number of joins, that SSD machine I've mentioned before will moot the cavil.

Everyone here knows what a nested loop join is, right? However, I am still willing to bet that if you...

I am also one of the rare DB developers. I have been part of both the application development side and the DBA side. Now, I am almost totally a SQL developer in my daytime job. Of course, with my own company and software that I sell, I wear all the hats. Being able to talk both side has always helped me with getting developmers to use a normalized DB and to get things into production through the DBAs.

I like the one where they wrote a new invoiceing sytem in an OO db system to replace the billing system that I had developed as a "stop gap" measure. They were never able to produce even a simple invoice without having to go in and manually do things to the system. They finally killed the replacement project and used the Access application to do all of it. That is until the whole company imploded, ENRON.

War - when enough is enough.

The war is on. Developers are not operational folk, they deal in the future, but they deal it into the present. DBAs are the front line protection for all things data well beyond the database structure including servers, connected systems, building operations, communications, security, operational protocols and policies, compliance adherence, disaster preparedness, and responsibility for the stability of every aspect and every dependency of these systems, present and future.

Who gets called when things go wrong? The DBA. Who knows what lurks in the doings of developers. The DBA. Who knows both development AND its practical implementation in the real world. The DBA. Who crosses the border between the think-tank-developers and the in-the-trenches operational staff? The DBA. In no small way the DBA is the Rambo of information technology workers.

When enough is enough, then the issue has already gone too far and has already caused unacceptable negative impact on resources human and machine, already stunted the growth of personal and corporate productivity, already stole away with tangible profit and has already dimmed the hopes we set every day for our future, and the future of the business.

Serving in the armed infantry taught me that intelligence work, "Intel", is the path to defeating the opposing force. So here's how I have time and again brought some incredibly smart developers to a more humble and accepting attitude:

+ Gather the obvious issues at hand and identify the core cause.
+ Prove this information with tangible and understandable presentation.
+ Bring forth external corroborating sources that affirm the inefficiency.
+ Iterate the specific protocol that was not followed and the reason it would have alleviated current issues.
+ Iterate the company policies and objectives that strive for cohesive and productive work.
+ Iterate compliance related protocol that if followed would have alleviated the current issues.
+ State clearly the cause of the current problems as the lack of implemented protocol identified above. It needs to be stated this is the reason for why we are where we are.
+ Create a written protocol and submit it along with the proposal to fix the current issues.
+ Forecast the different effect to the dba, the staff, the customers and the business between following or not the (widely accepted but not yet implemented here) protocols.

The rest is inertia.

Each of these steps could use a good amount of explanation and advise, but this is the way to win a war, by winning the battles that mean the most, and development is a meaningful future-driving process and worthy battle. Those that would compromise development, compromise our very future.

Thats it. The job is done but the battle persists. Not against any person or group, but against the organization's tendency to thwart and avoid change at its own peril. This we can all understand and it is not specific to anyone, but to all which if explained clearly can usher sympathy and agreement faster than struggling with individuals and their traits. Thus making change widely acceptable and not personal. If someone then stands up against changing for the better, know it will not be long that they stand, for they stand against all who now understand the grave issue at hand has a non-personal resolution.

When change does come about, and before that, it will be the one who had the guts to stand and deliver the bad news, but wrapped in solutions and hope, that will be remembered and credited.

This is the way to win against those that are maybe smarter, but are lazy. Developers, you know who I'm talking about. Come in at 6am, leave at 3pm, 3 weeks vacation, think they invented data in college and programming as an undergrad. Autonomous and introverted pretty much doing whatever they want for few if any know their job better than they, and so persist without being questioned or audited, even guided, let alone managed uniformly. No one's fault, but someone needs to be the hero for all involved.

Who can win against operational efficiency opposing forces, gather the best intelligence data for best practices and new protocols, stand up as a peer with something to lose, and bring forth the evidence that may rub some harshly but without intended malice? But of course, the one who's job it is and is depended upon to be on top of all things data related, 24/7, without fail. The one who's reputation and job depends on the stability and perceived efficiency in the data systems:

The DBA.

So when enough is enough, risk being proactive, start gathering the intelligence work that will explain why the war needs to be fought, and won, now. If what you say is clear, many will listen, more importantly, the CEO will act.

Success,

Alek Kirstein
DBA

Assuming that their is a need to database: Single user, Asynchronous and data structure agnostic systems aside.

The anti-pattern of having a 'flexible', data model agnostic, database with an application layer containing the data model needs to be removed from the collective consciousness.

I am tired of the discussion that follows the form:

Personally I don't think that (insert: concurrency, integrity, indexing/growth) will ever be an issue for my system given (insert: size of data/users,cost of CPU/storage, moore's law, Divine Intervention).  

The (insert: users, admins, developers) need control of the data model and don't need to understand (inset: concurrency, integrity, indexing/growth) in order to change it.

I can do a much better job at considering and implementing (insert the missing from prior: concurrency ,integrity, indexing/growth) than the generations of people building those archaic high Volume, concurrent, scalable and timely systems before I was born.


Why does this concept of a database structure as being 'inflexible' persist ?  
Scary as the concept sounds maybe these flexibility hungry Dev's need to be introduced to DDL and this concept of inflexible database can go away.

It isn't relevant whether the db design drives the application design or its inverse but rather that the indicies, data model, and integrity need to be implemented in the same layer by the same group.

Personally I would re-use a layer that has been designed, tested and used for decades to make this efficient to build and operate but I would be lying to say I have never reinvented a square wheel.

I understand the points, the semantics and the "fight".  Developers versus DBAs, robbers versus cops, jocks versus nerds, nerds versus other nerds, the battles are ongoing.  It's a lot of fun, and I'm bald.

PingBack from http://www.keyongtech.com/2558943-replies-by-celko/2

I finally took a picture & posted it.
http://scarydba.wordpress.com/2009/03/01/bald-d/