Sunday, November 26, 2006
DBA Job Interview Question #3: When Should You Use CHAR/NCHAR?
Here's our third Question and Answer in our series on DBA Job Interview Questions:
Let's start with the basic answer that most interviewers would want to hear: "You would use CHAR/NCHAR anytime you needed to store a fixed amount of text/string data. Deciding between CHAR/NCHAR is just a matter of determining which types of and/or how many different characters need to be stored."
That's the right answer technically. However I think you'll find that these fixed datatypes are used a lot more in college classes on databases than they are in real life, at least that's been my experience. I don't think I've ever seen a fixed datatype column of more than 10 or 12 characters used correctly. It just isn't all that often that you have fixed data that is truly always "x" characters and "x" is larger than 10 or 12.
"What about social security numbers - those are fixed 12-character values?" you ask. If you don't know, Social Security Numbers (SSNs) are a United States government-issued way to identify people in the US and are usually in the form of nnn-nn-nnnn (like 123-45-6789). The problem is that, in the 20s, 30s, and 40s, the government was known to reuse numbers and assign them in the form of nnn-nn-nnnnA, nnn-nn-nnnnB, etc therefore we cannot guarantee that an SSN will always be 12 characters - it might be 13 or 12 but we know it's one of those two. Some people think that, because it is "kind of" fixed, that it should be CHAR/NCHAR but I disagree. Since we cannot guarantee a fixed width, I would say that this should be a VARCHAR column but that's my opinion...
Let's look at an example using social security numbers (SSNs):
Let's look at the first query and answer the question, "Will the space at the end of the first query cause it not to find the row?" Remember: we're talking about 12 characters being stored inside a CHAR(13) datatype therefore there are 13 characters stored: 12 "real" characters and one "blank space" character. Therefore, since the space is stored inline with the data, this query returns the correct row.
Let's look at the second query and answer this question: "Will not having a space at the end of our literal string '123-45-6789' cause it to fail to find a matching row?" Now we are comparing 12 characters against 13 so, logically, you would not expect a match, right? After all, "123-45-6789 " is not the same as "123-45-6789" but, in SQL, this query is correct as well.
So both queries "work" and give us the correct answer but why? The reason is that, since the underlying column is a CHAR(13), all string data that is compared against the column is implicitly converted to CHAR(13) before the comparison takes place. Therefore, even though you entered "123-45-6789" into your expression, SQL Server converts it to "123-45-6789 " before testing it against the actual column.
What would happen if you changed the CHAR(13) to VARCHAR(13)? You get the same rows and everything is the same way except now, "123-45-6789 " is converted to "123-45-6789" since the variable length datatypes "truncate" any trailing spaces.
Well, I suppose this post went far, far deeper than just the basics of when you should use the fixed-length datatypes but I hope it left you with a better understanding of how they work.
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
When is it appropriate to use the CHAR/NCHAR data types?As an interviewer, I love to ask questions that assume knowledge (as this one does). To answer this question you must already know what the CHAR and NCHAR data types are as well as what the difference is between them.
Let's start with the basic answer that most interviewers would want to hear: "You would use CHAR/NCHAR anytime you needed to store a fixed amount of text/string data. Deciding between CHAR/NCHAR is just a matter of determining which types of and/or how many different characters need to be stored."
That's the right answer technically. However I think you'll find that these fixed datatypes are used a lot more in college classes on databases than they are in real life, at least that's been my experience. I don't think I've ever seen a fixed datatype column of more than 10 or 12 characters used correctly. It just isn't all that often that you have fixed data that is truly always "x" characters and "x" is larger than 10 or 12.
"What about social security numbers - those are fixed 12-character values?" you ask. If you don't know, Social Security Numbers (SSNs) are a United States government-issued way to identify people in the US and are usually in the form of nnn-nn-nnnn (like 123-45-6789). The problem is that, in the 20s, 30s, and 40s, the government was known to reuse numbers and assign them in the form of nnn-nn-nnnnA, nnn-nn-nnnnB, etc therefore we cannot guarantee that an SSN will always be 12 characters - it might be 13 or 12 but we know it's one of those two. Some people think that, because it is "kind of" fixed, that it should be CHAR/NCHAR but I disagree. Since we cannot guarantee a fixed width, I would say that this should be a VARCHAR column but that's my opinion...
Let's look at an example using social security numbers (SSNs):
CREATE TABLE #Employees (Name VARCHAR(128), SSN CHAR(13))Which of the two queries will return the row for the '123-45-6789'? I don't know if the font makes it clear but the first query is testing for "123-45-6789" and then a space whereas the second query is just testing for the 11 characters "123-45-6789". Since SSN is a CHAR(13) datatype, which of these queries will return the right row?
INSERT #Employees
SELECT 'Scott', '123-45-6789'
SELECT * FROM #Employees WHERE SSN = '123-45-6789 '
SELECT * FROM #Employees WHERE SSN = '123-45-6789'
Let's look at the first query and answer the question, "Will the space at the end of the first query cause it not to find the row?" Remember: we're talking about 12 characters being stored inside a CHAR(13) datatype therefore there are 13 characters stored: 12 "real" characters and one "blank space" character. Therefore, since the space is stored inline with the data, this query returns the correct row.
Let's look at the second query and answer this question: "Will not having a space at the end of our literal string '123-45-6789' cause it to fail to find a matching row?" Now we are comparing 12 characters against 13 so, logically, you would not expect a match, right? After all, "123-45-6789 " is not the same as "123-45-6789" but, in SQL, this query is correct as well.
So both queries "work" and give us the correct answer but why? The reason is that, since the underlying column is a CHAR(13), all string data that is compared against the column is implicitly converted to CHAR(13) before the comparison takes place. Therefore, even though you entered "123-45-6789" into your expression, SQL Server converts it to "123-45-6789 " before testing it against the actual column.
What would happen if you changed the CHAR(13) to VARCHAR(13)? You get the same rows and everything is the same way except now, "123-45-6789 " is converted to "123-45-6789" since the variable length datatypes "truncate" any trailing spaces.
Well, I suppose this post went far, far deeper than just the basics of when you should use the fixed-length datatypes but I hope it left you with a better understanding of how they work.
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
