I read a very interesting post on Kimberly Tripp's blog which was about indexing, but she had a very interesting set of requirements for good indexing that I think apply to everything when designing, tuning, backing up, etc., any database. What strikes me the most are 1) and 2) - how can you tune any sort of system if you don't really know what the data *is* and how it is used? As Kim says:

"So, what is “finding the right balance” in indexing? In my opinion, there are three requirements/pre-requisites:

1. knowing the data
2. knowing how the users use the data
3. knowing how the underlying structures and database stores/manipulates and uses indexes"

I can't count how many times I've run into clients where I'm asked to tune or create a disaster recovery plan or secure for a database where no one is really sure what things mean and how it is used. Everyone should start here, and I'd take it to the step of actually know not just the data, but the information; that is, what is the business meaning behind the data. It's a mantra to adopt - "Know thy data".

I know we've all beaten the discussion of NULL to death on this blog, but I had an epiphany of sorts when flying to Germany for the European PASS conference. Thus, I'm going to pick up the topic just one more time...

It occurred to me that the word "Null" has a very specific meaning depending on the language you are speaking - TSQL, "academic"-SQL, English or...German, where null literally means zero. Thus, I would naturally expect some confusion when seeing the word "NULL" come up in a result if it is shown to your average German users.

That brings me to what I think is the fundamental problem with the term NULL. In a very practical sense in SQL Server NULL is:

• A recording in the NULL Bitmap for a specific row that a specific field (a tuple in the academic lingo) has the property of NULL, i.e., the tuple has a property, it has nothing to do with the value or lack of value in the column

The problem is that language comes in to play. No one likes trying to explain NULL to users in an academic sense, so we come up with definitions. Here are some of the more popular ones (correct or incorrect):

• "The absence of value" - I think Codd would have approved of this, as I think C.J. Date and others would endorse. The problem I have with this definition is that there is a connotation with this phrase. To me (and others that I've experienced), the implication is "Why is there the absence of value? Did someone ignore the field? Did the clear it out deliberately? Is it not applicable? Did the consumer not supply an answer to the question?" Thus, NULL chaos ensues, where people take it to mean whatever the conceive of (regardless of the intent of the DBA or developer). Here we get reports supplying questionable information.
• "Empty Set" - I wouldn't agree that this is an accurate definition, but again it implies - "Why is the set empty?" There must be some reason why there is a lack of data within the "tuple". Thus we get the practical affect of converting NULL to an empty string ('') or a zero (0). Was the logic of the NULL transferred properly from the DBAs and Developers to the end user - no. Again, what I call NULL chaos ensues.
• "Nothing" - Again I would disagree a bit with the definition, but the connotations remain the same - "Why is it nothing? Did no one enter data? Did they remove data? Is the "tuple" not applicable?" All of these things come to mind naturally to me (althought one could question whether I'm an accurate representation of a "normal" person).
• "Unknown" - As far as SQL Server goes, this is the best definition. The connotations are almost child like - "It is unknown. - Why is it unknown? - I don't know why....Why doing you know why?...ad infinitum". "Unknown" is also a very practical definition when it comes to understanding things like mathematics where NULLs are involved (6+NULL=NULL -->6 + "unknown" must equal "unknown"

In any event, we need to keep in mind as database professionals that the actual description used for NULL will naturally carry with it specific connotations, for good or for bad. Language plays a great role in the understanding and ultimate usage of NULL - let's remain aware of that.

It was a fantastic conference, and thanks to all of you who plastered me with questions after (both) of my sessions. I'll try to get to as many answers as I can on the blog site, but if I miss one, just email me (you can email me right through the blog site).

Of those topics coming:

*More on Full-Text Search and Backup/Restore (especially moving the location of the FT Catalog)

*Possible LSN chain breaks during a failover process, plus how to handle backup/restore during failovers to the mirror (lots of gotchas there)

Again, thanks for being cordial hosts to a humble American (with a German last name yet speaks extremely poor German).

If you're in the area and available, I'll be giving my first 2008 presentation on Tuesday April 8th. It's a general overview of the new features in SQL Server 2008, starting at 4:30 (I think the presentation starts at about 5:30). It will be held at the Microsoft office in Waukesha, WI. The address is:

N19W24133 Riverwood Dr., Suite 150 
Waukesha, WI 53188 

If you are in the area, it will be worth the trip. I'll be going over new features with my own skeptical/cynical/paranoid speaking style :) Not just a trumpeting of what's great, but also a warning of misuse...and a certain level of silliness :)

I just received a new item to add to my catalog of phonetic pronunciations of my name!

"L12"

I think that puts me at about 2,052 distinct pronunciations...

Since it is leap day, I thought something semi-serious would be in order. See if this sounds familiar. I often tell a joke like this to a group before I speak and see lots of heads nodding. If this is your life, sound off!

[Queue film-noir music]: "My name is James. I'm a DBA. I was barely in to work one day when the phone rang..."

[Sully-Business Application Specialist]: "Hi James, sorry to say it, but it looks like this patch we installed last night caused some problems. I'm on the phone with support right now. Just a heads up"

[Queue film-noir music]: "Sully was a good guy. Didn't know the database side of things, so when we're testing a patch for his application he relies on my heavily. But, he takes responsibility if there's a problem with the app. He's a rare breed of IT staff..."

[Phone Rings again - Roger, Business Area Liason] - "James, there's a big problem with the database."

[Queue film-noir music]: "Roger was smart about the business side of things, not much of a clue about the database"

"Yeah, I know there's a problem, Sully is working on it. It's because of the patch we loaded."

[Roger]: "Well when will you fix the database?"

"It isn't a database issue, it's something to do with the patch. Sully is working with tech support"

[Roger]: I know, he called me this morning to tell me about it. So when will things be back up?

"You already knew about it? Why are you calling me? And if you want an estimated time of availability, you have to talk to Sully first."

[Roger]: I just thought you could find the database problem before Sully got an answer. Just get the database up, we need that application..."

"But there's nothing wrong with the database..."

[Dial Tone]:

[Queue film-noir music]: Roger hung up on me. He'll pay for that.

[Phone Rings]

[Queue film-noir music]: "It was Bob, my immediate supervisor..."

[Bob]: "I got a call that the database is down, when will it be up?"

"It isn't down, there's a problem with the applicaiton"

[Bob] "Well didn't they test and sign off on the application changes in the QA environment"

"Yeah, but apparently they missed something"

[Bob] "Well, just get that database fixed...I'll check in later."

"There's nothing wrong with the database..."

[Dial Tone]

[Phone Rings]

[Queue film-noir music]:"It was Bob, Bob's manager"

[Bob managing Bob]: "The database is down. When will it be up?"

"It isn't down, it's an application problem..."

[Bob managing Bob]: "Just get it fixed, and keep me in the loop"

"Isn't Bob talking to you as well"

[Bob managing Bob]: "I don't trust him - you know that database, not Bob"

"But there is no database problem..."

[Dial Tone]

[Phone Rings]

[Queue film-noir music]: "It was Bob, the CIO, who manages Bob who managers Bob"

[Bob managing Bob managing Bob]: "Hi James, how are you today? I hear the database is down"

"No, it isn't a database issue, it's a problem with the patch."

[Bob managing Bob managing Bob]: "Well we have to get them to test and sign off on those patches then..."

"We do...apparently the missed something."

[Bob managing Bob managing Bob]: "I'll look into that"

[Queue film-noir music]: Bob managing Bob managing Bob was a good guy, but he had a drive to find root causes as soon as possible, even before solving the  problem at hand..."

[Phone Rings]:

[Queue film-noir music]: "It was Roger, and he was mad."

[Roger]: "What are you ratting me out to Bob for?"

"Which Bob? Bob or Bob managing Bob"

[Roger]: "The main Bob. Quit putting the blame on me and fix that database."

"But I didn't blame you at all - plus the database is fine..."

[Dial Tone]

[Phone Rings]

[Queue film-noir music]: It's a user of the application, someone I've never met nor heard of...I wonder how they got my number...."

[Unknown User]: "When will the database be up"

[Queue film-noir music]: At this point I've given up. "At least two hours, maybe more."

[Phone Rings]

"Hold on, I have another call"

[Sully]: "Good news, I have a patch from the company, things are fixed."

"OK, but do I need to bounce the database"

[Sully]: "Yeah, how long will that take"

"2 hours"

[Sully]: "Ouch. I'll relay the message"

[Queue film-noir music]: I keep the database down and leave for lunch. It's currently 8:30. Maybe I'll come back, maybe not.

So I generally speak at PASS every year. Unless I've offended the powers that be, I will again. So, I have an open question to colleagues and readers and those that have come to my sessions - what would you like to know more about? Perhaps everyone could use this post as a way to decide on topics. PASS is a very user-group oriented conference focusing just on SQL Server (with about 2000 attendees - it's big) if you've never been to one. I'm interested in getting input ahead of time from potential topics, based in reality, your every day jobs. What issues do you face? What features vex you? What gremlins haunt your servers? (yeah, I'm a little strange, I admit it).

Let's hear it - sound off everyone. The conference is for you, so the more input you give ahead of time, the better it will be.