December 7, 2010 at 10:41 pm
Hi everyone
I need to create a table with a column that store comments from the user.
I have no idea how large the comment column should be.
I know that is realy depend on each case, but if I set the size to nvarchar(400), may be someone want to put more than that.
any data type is flexible
thx!
December 7, 2010 at 11:15 pm
You can use VARCHAR(MAX) or NARCHAR(MAX) if you are using sql server 2005 and above.
Generally when you are inserting the comments from the front end you can use some validations to limit the text that the user is entering and infom if it exceeds it. so you can plan your things.
December 8, 2010 at 12:56 am
Are you expecting unicode data?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2010 at 3:13 pm
thank you ppl reply the post!
You can use VARCHAR(MAX) or NARCHAR(MAX) if you are using sql server 2005 and above.
I just checked, we are using 2000
Generally when you are inserting the comments from the front end you can use some validations to limit the text that the user is entering and infom if it exceeds it. so you can plan your things.
yes, we got the front end, the front end only insert bulk of records (1000 or more each time), it takes time to insert, so if that is the way, i will do that.
the comment column is always empty, it got data sometime, but short, not many comment over the limit.
its like a design issue, if i set the size to large, is it going to waste the space? what i know when the data type and size is set, even it is empty, it still take the space, am i right?
December 8, 2010 at 3:14 pm
GilaMonster (12/8/2010)
Are you expecting unicode data?
what is unicode data
December 8, 2010 at 3:31 pm
http://www.google.com/search?q=%22SQL+Server%22+unicode
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2010 at 4:08 pm
GilaMonster (12/8/2010)
Are you expecting unicode data?
if yes, how it going to help me to set the column size
December 8, 2010 at 4:15 pm
If you are not needing Unicode data then you would not use nvarchar, you would use varchar. The difference is that if you are not usig Unicode data, then nvarchar will consume 2x the bytes and 1/2 of them will be throw away because you are not taking advantage of them.
December 8, 2010 at 5:38 pm
Using varchar(max), or nvarchar, will not waste space. It will use what space you require.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 8, 2010 at 5:46 pm
What I meant was is that the Datalength of a nvarchar(max) with 'Ben' inside, is 6 bytes, and Datalength of a varchar(max) with 'Ben' inside is 3 bytes, so because I am not going to store characters that will require 2 bytes per character, then the space used will be double that of a varchar.
Sorry for any confusion.
December 8, 2010 at 10:01 pm
thank ppl so much to reply the post
i think i got it, let me make sure i understand,
e.g.
nvarchar(max)
"hello world" is 11 Bytes,
"hello" is 5 Bytes,
varchar(max)
"hello world" is 22 Bytes,
"hello" is 10 Bytes,
am i right?
Datalength of a varchar(max) with 'Ben' inside is 3 bytes, so because I am not going to store characters that will require 2 bytes per character, then the space used will be double that of a varchar.
what does " I am not going to store characters that will require 2 bytes per character" mean?
what characters?
if i am using sql server 2000, what can i do, does it support nvarchar(max)?
thx!!
December 8, 2010 at 10:09 pm
:crazy: i double post, my bad
thank ppl to reply the post.
if iam using sql server 2000, does it support nvarchar(max)??
what if iam using nvarchar(200), every filed will take 200Bytes, no matter how many bytes been used. e.g. "hello world" is 11 bytes, it still takes 200bytes to store, am i correct?
thx!!
December 8, 2010 at 10:28 pm
dlam 18073 (12/8/2010)
e.g.nvarchar(max)
"hello world" is 11 Bytes,
"hello" is 5 Bytes,
varchar(max)
"hello world" is 22 Bytes,
"hello" is 10 Bytes,
am i right?
No. Wrong way around.
Datalength of a varchar(max) with 'Ben' inside is 3 bytes, so because I am not going to store characters that will require 2 bytes per character, then the space used will be double that of a varchar.
what does " I am not going to store characters that will require 2 bytes per character" mean?
what characters?
Did you do that search that I sent you the URL for? Did you read any of the articles? From your questions, I'm guessing not.
if i am using sql server 2000, what can i do, does it support nvarchar(max)?
Firstly you can post questions in the SQL 2000 forums. Second, lookup TEXT/NTEXT in SQL's Books Online. Oh and again, are you going to be storing unicode data? If not, there's no point in using a unicode data type.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2010 at 6:43 pm
unicode: the data size is 2 times of the data length
my question is: the comment column sets to nvarchar(200), I think is not big enough, sometime comment is longer than that. if I set the column to nvarchar(1000), am i waste the space, buz not many comment over the limit.
what my understand is when the size limit been set, no matter that field is empty or not, the field still take nvarchar(200)(in this example).
if yes, then iam not going to change the column size, buz not many row will over the limit.
if the field only takes the size of the actual data length or 2 times of it, then I will change to a large size like 1000, buz if the data lenght in the field is 6bytes, it will not take 1000 bytes.
e.g. comment column datatype=nvarchar(200)
data is "hello", the size it take is 10bytes only.
can you tell me which one is correct?
thx!
December 9, 2010 at 7:19 pm
You should look at the links that were given to you.
But basically, you have to understand the var part of varchar or nvarchar. The 'var' is variable length, and the column will store only the amount of data used. So 'hello' in nvarchar(1000) will take only 10 bytes even though the definition would take 2000 bytes.
So understand that the variable nature of the varchar datatypes will store variable lengths not consume the entire length as defined.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply