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

untouched.

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:

• Cursors
• RAID 5
• Dynamic SQL
• Triggers
• 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.