June 5, 2009 at 11:39 am
nchar(1) is the one. I agree with all explanations above.
Alberto
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 5, 2009 at 11:44 am
HEAR ! HEAR ! Markus B!
June 5, 2009 at 11:49 am
Alvin Ramard (6/5/2009)
cs_troyk (6/5/2009)
Actually, varchar(1) is the "best" choice from the standpoint that when people blindly follow advice like this without doing their research, the resulting system will be rife with opportunities for improvement (you know there will be other problems, too). This equals more money in my pocket as I'm called in to fix such systems.WHAT?!?!?
Alvin,
He's just being a bit snarky, saying that bad advice to corporate drones leads to more work and therefore more money for him as a consultant.
BTW, although we have learned that the QOTD is not jurored, I too am surprised that no one at SSC (Steve?) checked this one out or asked for a citation that a varchar(1) or nvarchar(1) doesn't allocate the two length bytes. Might have saved author VM some (seven pages so far) embarrassment.
June 5, 2009 at 11:55 am
an interesting statement in the explanation:
...thought of this question considering larger scenarios where data types need to be chosen with care.
I think a bit more care should have been put into the decision making process as NCHAR (UNICODE) would be the only choice. Although a system designed and built today may only utilize 8-bit ASCII characters, there is nothing to say that the situation could change next week. It's a global world with mergers and acquisitions happening all of the time. GM's Hummer division is now owned by a Chinese company. Talk about a plain, 7-bit ASCII database now needing to support UNICODE!
June 5, 2009 at 12:02 pm
DBA Cabuloso (6/5/2009)
The question is about the space allocated to a variable like
DECLARE @onechar as varchar(1)DECLARE @onechar as char(1)
not to a table.
And the space allocated to both is equal.
Pay attention to the question !!!
While I agree that most seem to have missed that this is in regards to a variable declaration, and not a column declaration, the more fundamental problem remains: what is "best" isn't defined (and unless the person is on the SQL Server team, I doubt that he knows how much memory is allocated for any of the possible declarations, if in fact any memory is allocated at all).
June 5, 2009 at 12:09 pm
Rutger (6/5/2009)
I was thinking beyond US-ASCII, so I chose nchar(1) 🙂
Me, too!
Since the question asks what is "the best" to use, and it doesn't specify the character set (so it could be Chinese, for all you know), I selected nchar(1). I think the answer is incorrect.
June 5, 2009 at 12:21 pm
Try this on.
While BOL says that it's +2 to store it, but that refers to in memory storage as well.
try this on.
declare @char char(1)
declare @varchar varchar(1)
declare @nchar nchar(1)
declare @nvarchar nvarchar(1)
SET @char = 'A'
SET @varchar = 'A'
SET @nchar = N'A'
SET @nvarchar = N'A'
SELECT
DATALENGTH(@char),
DATALENGTH(@varchar),
DATALENGTH(@nchar),
DATALENGTH(@nvarchar)
declare @tab table (
char1 char(1),
varchar1 varchar(1),
nchar1 nchar(1),
nvarchar1 nvarchar(1)
)
insert into @tab values ('A','A',N'A',N'A')
SELECT
DATALENGTH(char1),
DATALENGTH(varchar1),
DATALENGTH(nchar1),
DATALENGTH(nvarchar1)
FROM @tab
CREATE TABLE #test(
char1 char(1),
varchar1 varchar(1),
nchar1 nchar(1),
nvarchar1 nvarchar(1)
)
insert into #test values ('A','A',N'A',N'A')
SELECT
DATALENGTH(char1),
DATALENGTH(varchar1),
DATALENGTH(nchar1),
DATALENGTH(nvarchar1)
FROM #test
DROP TABLE #test
Notice datalength is the same for a variable, table variable, and a temp table. While the +2 bytes is there in storage, nothing will tell you that when you're using the built in functions. Without knowing what kind of character you need this variable for, it still needs to be nchar(1) as that covers all of them.
June 5, 2009 at 12:26 pm
This question to too vague. Best for what?
June 5, 2009 at 1:04 pm
DBA Cabuloso (6/5/2009)
The question is about the space allocated to a variable like
DECLARE @onechar as varchar(1)DECLARE @onechar as char(1)
not to a table.
And the space allocated to both is equal.
Pay attention to the question !!!
This is an interesting assertion, and is strongly made ("Pay attention...."). So, can you please tell us Where is the MS documentation saying that a varchar(1) variable does NOT carry the two bytes of length overhead? I see nothing in the datatype documentation in BOL for SQL2005 that makes such a distinction.
June 5, 2009 at 1:08 pm
mtassin (6/5/2009)
Try this on.While BOL says that it's +2 to store it, but that refers to in memory storage as well.
-snip-
Notice datalength is the same for a variable, table variable, and a temp table. While the +2 bytes is there in storage, nothing will tell you that when you're using the built in functions. Without knowing what kind of character you need this variable for, it still needs to be nchar(1) as that covers all of them.
Change your assigments from 'A' to '' and see what happens to Datalength. It's not a representation of how much memory is being used to track the variable, it's how much data is in the variable. That's not the same thing at all, at this point you have to start thinking outside of SQL and into machine code, pointers, registers, byte boundaries and so forth.
How much memory is used for the variable is something that only someone on the SQL team would really know (various optimizations make external testing imprecise).
June 5, 2009 at 1:13 pm
nChar(1) or Char(1) would be a better answer depending on your character set. Both Varchar(1) or nVarchar(1) have extra overhead to handle variable length strings which is not required per the question. Either char(1) or varchar(1) may be used, but this is clearly not the best answer for a single fixed length character.
This was a good question, just the wrong answer.
June 5, 2009 at 1:30 pm
I base my char(1) answer off of this from bol:
Use char when the sizes of the column data entries are consistent.
Use varchar when the sizes of the column data entries vary considerably.
Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/282cd982-f4fb-4b22-b2df-9e8478f13f6a.htm
June 5, 2009 at 1:40 pm
In the real world, where all to often we do not get to ask follow up questions to design specs that may have been handed to us, the "BEST" answer will always be the most extensible.
If you cannot ask any questions to further define a requirement, then you need to provide an answer that covers all the questions you would have asked.
in this case:
one character of data
I have a variable that will hold one character of data and will never be a null or an empty space. Which of the following is the best data type to declare that variable?
nchar(1) = most extensible, covers the questions that the majority of the forum have correctly posed.
June 5, 2009 at 4:27 pm
Tricky Question.
I had chosen char(1) instead of varchar(1) because thats what it actually is, a fixed length string.
And I had chosen char(1) instead of nchar(1) because I think it is very unlikely that someone would want to store a sincle unicode character in a variable. In the end it will most likely be some code like "V" for view or "T" for table. But I agree that this is subject to the requirements.
This reminds me that too often people use unicode fields where not really necessary. Example would be the 3 character currency code (EUR, USD etc) which definitively does not need to be unicode...
Best Regards,
Chris Büttner
June 5, 2009 at 4:39 pm
Here's to "wrong" correct answers, a seemingly simple question led to quite an interesting discussion!
BTW, I wonder how many of us created char(1) and varchar(1) variables and executed DATALENGTH on them (since this supposedly returns the number of bytes and not the length) to be surprised that both return 1 rather than 1 and 3?
Viewing 15 posts - 61 through 75 (of 182 total)
You must be logged in to reply to this topic. Login to reply