My premise for this article is that there is a lot more gray in our
profession than black and white. Beginners don't like Zen answers like 'it
depends' and so they settle on a philosophy without having fully explored the
issue. The truth is most of us learned to be a DBA on the job and success was
defined as accomplishing whatever task we were given without regard to elegance
or best practice. That's eminently pragmatic and I'm not here to question that,
but to challenge you to make sure you've fully explored some of the 'best
practices' and controversial areas of our profession and more importantly, to
get in the habit of looking a little deeper into things before deciding which
practices you'll adopt and recommend.
Let's start with a common example. If I asked you which is the best practice,
using Windows authentication or SQL logins, I bet most of you would say Windows
authentication. MS does, right? My follow up question is - are there any caveats
to that best practice? To that question I suspect I wouldn't get many answers,
maybe something along the lines of make sure users are required to have a strong
password, to change passwords frequently, etc. Good advice and certainly should be part of the package, but there is a bigger gap that usually is left
The problem? Windows authentication grants the user the right
to do certain things in SQL Server. How many of you are comfortable with your
users executing stored procedures directly without the benefit of an application
or business layer wrapped around it? Or running queries against tables directly
if you have granted table access? I would guess not many DBA's are going to be
in favor of that, but that's what Windows authentication allows.
We fall back on security by obscurity, hoping that among all our users who have access none will discover the name of the SQL Server and have the skills to....oh, maybe start up MS Query, or run a few lines of VBScript that they found on the web. So what would my answer be? Windows authentication when used from a web server or application server, but never grant anyone outside of IT the ability to access
the server using their credentials and if at all possible, deny them access to
data servers entirely by means of firewall rules.
I think that's a good example, but probably not the kind to spark the debate
that other topics would, such as:
- RAID 5
- Dynamic SQL
- Identity columns
- Unique identifiers as a primary key
- XML stored as XML/BLOB
- Blobs in the database
Let's take cursors, briefly! Let's say that we all agree that trying to do
old style record based processing in a cursor is not a good idea. Do we believe
that set based solutions are always the best way to solve a problem? I had a
friend recently show me a query where performance improved when they
broke up a set based query to use a cursor driven loop. Could the original set
based solution be rewritten a different way to match or exceed the performance
of the cursor solution? I would venture to say probably, but until the query
performance falls out of the envelope again I suspect my friend will work on
more import matters.
Do you know what a cursor costs? Are you pragmatic enough to use a cursor at least as a stop gap, or do you insist on finding the right set based solution no matter how long it takes? Note that I'm not trying to convince you that cursors are good, but to ask if you if really understand them, or just took someone's word for the performance implications?
How about uniqueidentifiers? There are a lot of negatives through about, but a deal breaker for many DBA's was that it was essentially random which resulting in a lot of fragmentation and page splitting. SQL 2005 changed the rules by allowing us to generate sequential guids. Does that change your mind about when they might make sense? Or dynamic SQL - used badly you can end up with a lot of very similar plans being generated in SQL 2000, but we in SQL 2005 we have forced parameterization and some better rules about plan caching as of SP2, would that be enough to change your mind in favor of it?
Let me change direction for a minute. Recently I read Celko's style book and
while I didn't agree with all of it, I could also see several places where the
style I had evolved for column names wasn't very good, even pretty bad maybe!
I've been working with SQL for about 10 years now; shouldn't I have mastered
column naming before now?
Here's my challenge to you - revisit these often contested topics and take a
fresh look at the other side of the issue and see if you can see there is some
gray instead of black and white. Look to see if you've fallen into one trap by
avoiding another (my favorite example is using a while loop instead of a cursor,
it's still a loop). Much of the gray is easier to see if you remember that pure
performance isn't always the goal, or at least the most important goal.