Wednesday, December 20, 2006
SQL Server 2000 Lifecycle
I was cruising the SQL Server site on microsoft.com today and saw this: SQL Server 2000 Support Lifecycle
It looks like most support stops April 8, 2008 but the extended support (you pay extra for this) stops in 2013. In my opinion, that's plenty of time to move to SQL Server 2005!
More After the New Year
I'll resume our series on interview questions on January 3rd (or thereabouts). Here in the US, many of us take the weeks between Dec 25 and Jan 1 off and I will be as well.
Have a wonderful holiday season and I'll see you next year!
Have a wonderful holiday season and I'll see you next year!
Wednesday, December 13, 2006
DBA Interview Question #7: How would you implement an optional foreign key?
Here's question #7 in our series on DBA Job Interview Questions:
1) Step 1: Create a new column in the child table and make it non-null
2) Step 2: Create a foreign key constraint from this new column to the parent's primary key
After you do these two steps, you will have a mandatory foreign key; any inserts/updates to the child column will require a matching parent. Is it possible to insert a value at the child that has no matching parent? No. What if you tried to insert a NULL value into the child column - would it succeed or fail? It would fail - this is a non-nullable column.
Now, on to the optional foreign key - the words "optional foreign key" are a bit ambiguous, aren't they? Does that mean that the child records will not always be verified against the parent? Does "optional" mean that sometimes the child values are checked and sometimes they aren't? It's confusing, right?
Basically an optional foreign key means this: if a known value is inserted into the child column(s), then it must have a matching parent value. If an unknown value is inserted into the table, then it is allowed. That's all there is to it - nothing more. So, to answer the question succintly and move on to the next question, I would just say, "All you have to do is to make the child column(s) nullable."
Hope this helps!
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
How would you implement an optional foreign key?This, of course, depends on your understanding of how primary key/foreign key relationships work and, if you have any gaps in your knowledge, this type of question will expose them to your interviewer. The answer to this question is actually ultra-simple but it requires a little introduction. Let's first review how to implement mandatory foreign keys:
1) Step 1: Create a new column in the child table and make it non-null
2) Step 2: Create a foreign key constraint from this new column to the parent's primary key
After you do these two steps, you will have a mandatory foreign key; any inserts/updates to the child column will require a matching parent. Is it possible to insert a value at the child that has no matching parent? No. What if you tried to insert a NULL value into the child column - would it succeed or fail? It would fail - this is a non-nullable column.
Now, on to the optional foreign key - the words "optional foreign key" are a bit ambiguous, aren't they? Does that mean that the child records will not always be verified against the parent? Does "optional" mean that sometimes the child values are checked and sometimes they aren't? It's confusing, right?
Basically an optional foreign key means this: if a known value is inserted into the child column(s), then it must have a matching parent value. If an unknown value is inserted into the table, then it is allowed. That's all there is to it - nothing more. So, to answer the question succintly and move on to the next question, I would just say, "All you have to do is to make the child column(s) nullable."
Hope this helps!
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
Tuesday, December 05, 2006
DBA Interview Question #-1: Who is the new coach of the USA?
It's Juergen Klinsmann! What a record this man has - won the World Cup in 1990, two UEFA Cups and a Euro Cup in addition to being England's POY. By the way - I'm a h-u-g-e USA National Team soccer fan so sorry... I know this isn't an interview question but I'm excited :)
Monday, December 04, 2006
DBA Job Interview Question #6: Describe the goals of Normalization
Here's question #6 in our series on DBA Job Interview Questions and this question falls under the category of "Relational Theory":
Let's talk about the basics: What is normalization?
What? You actually think I'm going to waste my typing time telling you? Google it! Seriously - that's waaaayyyy too big a topic to cover in a single blog topic.
The goals of normalization are pretty easy and you can sum it up quickly: essentially, a long time ago (30+ years ago), some very smart people realized that, if you designed a database in a certain way, it would run faster, contain less wasted space and fewer (if any) duplicate items/data. So that's the 30 second answer but it really cuts to the main goals of normalization, doesn't it? All normalization really is is a standardized approach to designing databases, right? It's sort of like a design pattern if you will.
To break down further the various Normal Forms in this blog post is just tediuos; there's tons of material out there that you can read:
Of course there are more normal forms but, from a junior-level DBA's perspective, you ONLY need to familiarize yourself with these first three. Heck - 99.9% of all DBAs out there couldn't tell you what 4th or 5th Normal Form do anyway so just focus on 1-3.
I would also suggest that you be able to go deeper if asked and be able to explain examples of using each. The wikipedia.org samples above are good about providing examples.
Hope this helps!
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
Describe the goals of NormalizationThis isn't really a tough question at all and, if I'm asking this of a Junior Admin-level candidate, I don't need/expect too many details. If you understand the purpose of normalization and can throw in a few buzzwords and/or one of the forms, then I'm happy and won't ask you any further questions!
Let's talk about the basics: What is normalization?
What? You actually think I'm going to waste my typing time telling you? Google it! Seriously - that's waaaayyyy too big a topic to cover in a single blog topic.
The goals of normalization are pretty easy and you can sum it up quickly: essentially, a long time ago (30+ years ago), some very smart people realized that, if you designed a database in a certain way, it would run faster, contain less wasted space and fewer (if any) duplicate items/data. So that's the 30 second answer but it really cuts to the main goals of normalization, doesn't it? All normalization really is is a standardized approach to designing databases, right? It's sort of like a design pattern if you will.
To break down further the various Normal Forms in this blog post is just tediuos; there's tons of material out there that you can read:
Of course there are more normal forms but, from a junior-level DBA's perspective, you ONLY need to familiarize yourself with these first three. Heck - 99.9% of all DBAs out there couldn't tell you what 4th or 5th Normal Form do anyway so just focus on 1-3.
I would also suggest that you be able to go deeper if asked and be able to explain examples of using each. The wikipedia.org samples above are good about providing examples.
Hope this helps!
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
