SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Revisit What You Know

By Andy Warren,

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.

Total article views: 3513 | Views in the last 30 days: 4
Related Articles




How would you get rid of this cursor?!

This cursor takes about half an hour to run. How would you make a set based solution?


SQL Server Security Basics: Why Would I Use SQL Server Authentication?

The standard best practice answer when it comes to connecting to SQL Server is to use Windows authen...



How do cursor work?


Query About SQL 2000 SP3