I agree that it is all to easy to fall into the trap of using the same methods for everything. One reason I got into programming/DBA work was because of the opportunity to be continiually learning and finding better ways to solve every day problems.
I come to SQL Server Central every day because I am able to learn new ways from my peers. I am not in a position currently where I use SQL Server, but I am hoping to move the organization that direction and I use this site, and some others, to try to "keep up".
As they say, 3 Hah-Hah.
I always tell stories about Windows authentication. I am not even mentioning the Domain Trust.
I love cursors.
I set up my servers a certain way that a new DBA described as "No Special Configuration" on purpose that the same new DBA would be comfortable around this server. Forget putting log or backup files on the system drive if you have only 2 physical drives - I am not comfortable with it, I would better ship tr. log backups to the network share as soon as they are completed (unless we have performance issues - in this case I will request more hardware)
I totally agree about naming conventions - these even changed from Visual Studio 6 to Visual Studio.NET
What about the best practice 10 years ago in Database Design to have a database per action: a database for user authentication, a database for state management, a database for user data, a database with views for user access... I have a couple of applications set up like this. I don't think it is recommended anymore.
Some things though would never change and be the best practice: don't leave your password on a sticky note on your monitor.
Good article.
One time our DBA baggage really comes under scrutiny is when we change jobs and confront the accumulation of someone else's paradigms and best practices. It's good then to know why you believe what you believe, so you can justify change -- or accept change.
Lynn Hineman
Heh... I used to seriously question cursors… now, I LOVE cursors and RBAR While Loops, too! Even better than that, I LOVE code that looks set-based (no While loops) that actually has performance worse than cursors (usually some wonderful form of triangular join associated with a correlated sub-query). They're all really sweet deals and my friends that sell hardware get about 80% of their repeat business from folks that have performance issues and throw hardware at the problem. The Developer's (in-house and 3rd party) are loving it, too. They know the users will throw hardware at the problem so no need for the Developer's to actually think about writing performance enabled code. They can just crank out oodles of code in a highly productive manner without having to even think about performance. What a deal! And there's even built in obsolescence! As the data grows, so does the need for even more performance... so, about every two years, the customers order more new hardware to fix their lovely performance problems, all without having to actually touch any working code and without ever having to call the customer to see how they're doing! Sweet, huh? And, what the Developers really like is the fact that they no longer have to spend any time justifying cursors to the DBA even for the simplest of tasks… the hardware will take care of it!
The Systems DBA's are loving it, too! Long gone are the days when they have to review code for potential performance issues… they can just promote the code to production without even having to look at it. They know that it can all be handled with hardware! And, if a cursor is responsible for a deadlock, no problem!!! Just buy another server to run that process on!
Management likes hardware solutions a lot because, as everyone knows… hardware is a lot cheaper than good programmers! And, it makes justifying the floor-plan, air-conditioning, cooling, and power consumption budgets so much easier! In fact, Management just loves being able to tell their golfing buddies about the nifty set of 8, 16, and 32 processor boxes they just got… saved oodles by being able to get Developer's at a quarter of the cost because they don't actually have to understand RDBMS's anymore and they have a lot more time to write the GUI code that they're so good at. And, with things like Hibernate, they don't even need to be good at that anymore, either… it'll automatically write the performance challenged code for them! That means even the interviewing process becomes a lot easier and cheaper because you don't need to hire DBA's that know much, either! The savings just don't quit!
But, as much as I like cursors, you're all missing the holy grail of hardware justification… the very mention of it just sends hardware chills up and down my little data-troll spine in fevered anticipation… my hardware buddies are expecting hardware upgrades to triple or quadruple in just the next year or so because of it… AND, you can hire even cheaper programmers because the data just figures itself out... ya just gotta love XML for all the potential savings, don'cha?
Funny how this works... everyone seems to say that performance isn't all that important... but, why do they buy the high performance hardware if performance is not important? And why do so many of the job postings for DBA's on this site have things like the following in the job requirements?
• Assist with code enhancements to achieve reliable server performance and ensure database security and data integrity measures are in place
Hmmmm... must be a type-o... they must've meant "Able to buy a pot-wad of hardware without getting writer's cramps on the PO's".
Jeff, your point about cursors is taken, but there are situations where they are, in reality, the best solution to the problem at hand. Like almost everything else in the SQL Server world, it depends... Performance and speed is critical, but it isn't the most important consideration. Logical correctness is the most important thing. In arriving at the correct answers cursors are a tool, one that is often abused and misused, but a valuable tool nonetheless when the circumstances dictate.
Andy,
A very timely article, I switched jobs last year and one of the first things I did was to revise my SQL Server Standards document. This is a document that I started at least 8 years ago and have carried forward through those years with many additions and revisions along the way.
One of the most surprising (to me) changes I made was to the formatting section. I, like most people have always used all caps for SQL key words and functions. The developers in my new job had abandoned that practice. Their argument was that keyword highlighting made that practice unnecessary. After discussing it, I agreed to try going with lowercase for sql key words. I expected to not like it and find that readability somehow suffered, but after 6 months of using it, I have yet to find an example where I wished we'd followed the old standard. I really don't miss hitting the shift key all the time...
