Data Types (char, varchar, nchar, nvarchar, ...)

Could someone please help me by explaining which one is best to use and when? For example, storing the word "Corona Del Mar" - which Data Type would be suggested?

Thanks.

I would suggest Varchar(#).

ntype data types support Unicode (non-standard characters). So unless you're expecting complex input (or different languages) char, varchar, and text are what you'll typically be using.

char = fixed length string. If you create a char(10) field and enter "test" the field will actually stored "test" PLUS 6 blank spaces (4 characters in test + 6 blank spaces = 10). So when you output your field, you'll actually get "test ".

varchar = variable length string. This is the bread and butter of most databases. If you create varchar(10) and enter "test", it will store test. The limitation to this field is that it can only store up to 8,000 characters.

text = unlimited text field. Downsides: Text fields are stored OUTSIDE the database record (only a pointer is stored inside the record) so retrieving text fields is slower then retrieving a varchar record. It's also not compatible with some database commands (group etc.)

|||

Chris Pebble:

I would suggest Varchar(#).

ntype data types support Unicode (non-standard characters). So unless you're expecting complex input (or different languages) char, varchar, and text are what you'll typically be using.

char = fixed length string. If you create a char(10) field and enter "test" the field will actually stored "test" PLUS 6 blank spaces (4 characters in test + 6 blank spaces = 10). So when you output your field, you'll actually get "test ".

varchar = variable length string. This is the bread and butter of most databases. If you create varchar(10) and enter "test", it will store test. The limitation to this field is that it can only store up to 8,000 characters.

text = unlimited text field. Downsides: Text fields are stored OUTSIDE the database record (only a pointer is stored inside the record) so retrieving text fields is slower then retrieving a varchar record. It's also not compatible with some database commands (group etc.)

Thank you, that was a very good explaination.

|||

I have a list box that has a few different values "information a, information b, information c" and when any or all are chosen they are stored in my database in one column. What data type would you suggest for this? Thanks.

data types

I'm reading Sams TY SQL in 10 minutes and on the examples he uses CHAR for
practically everything, id's, addresses etc.
I upsized a small dbase from access and when I looked at a table, the
datatypes were a lot different. I noticed all ID fields were int and
nvarchar was used on most other fields. In the reserved words glossary, I
didn't see nvarchar. I guess it's missing a few types from the list.
I did a search for dbase schemas on google and found the Duwamish Book
example on MSDN. That dbase also uses nvarchar and int as the upsized access
dbase did.
Just curious why I should use nvarchar instead of char etc.
ThanksThe n in nvarchar indicates that it's for unicode. If you don't plan to use
unicode, you don't need to use nvarchar, and you can use varchar instead.
nvarchar is documented in the SQL Server Books Online.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"James" <no@.email.com> wrote in message
news:%23e1oE4TiDHA.1456@.TK2MSFTNGP11.phx.gbl...
> I'm reading Sams TY SQL in 10 minutes and on the examples he uses CHAR for
> practically everything, id's, addresses etc.
> I upsized a small dbase from access and when I looked at a table, the
> datatypes were a lot different. I noticed all ID fields were int and
> nvarchar was used on most other fields. In the reserved words glossary, I
> didn't see nvarchar. I guess it's missing a few types from the list.
> I did a search for dbase schemas on google and found the Duwamish Book
> example on MSDN. That dbase also uses nvarchar and int as the upsized
access
> dbase did.
> Just curious why I should use nvarchar instead of char etc.
> Thanks
>|||> I upsized a small dbase from access and when I looked at a table, the
> datatypes were a lot different. I noticed all ID fields were int and
> nvarchar was used on most other fields.
You shouldn't be using varchar unless you know you'll need to support
foreign alphabets / unicode characters.
For a description of the (n)(var)char/(n)text datatypes, see
http://www.aspfaq.com/2354
For an exhaustive discussion of differences between Access and SQL Server,
see http://www.aspfaq.com/2214
For some other issues with upsizing, see http://www.aspfaq.com/2182
> In the reserved words glossary,
Of a SAMS book? You should be using Books Online for this kind of thing,
IMHO. Also, see http://www.aspfaq.com/2080|||Thanks

Data Type that accept . for MSSQL

Hi,

I wish to enter some string data with period(.) in a column with char type. It doesn't accept period(.) What data type should I choose for this field ?

I'm using MSSQL Server 2000.

Please advice.varchar, nvarchar..?|||Thanks.

Data type question

Hello All
Can some one give me a quick lesson in data types - What is the basic
difference between a char data type and varchar data type and when does one
use one over the other and advantages and disadvantages of one over the
other.
Here is the reason I asked.
Initially I had a table to which I was importing data from a text file. The
majority of the columns in this table was of datatype varchar ranging from
size of 3 to size of 100. THe columns also consisted of decimal data types
and datetime datatypes. It was suggested that changing the data type to char
from varchar would speed up the insertion process. The file the data is
being inserted from is a fixed length ASCII text file which
Would using a char datatype over a varchar make a difference in select
statement (Select *)
Please advise
Thanks in advance"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
I think that using varchar in your situation is better solution. Varchars
are generally space savers and when distribution of your data ranges from 3
to 100 characters it is better that you use varchar instead of char. If you
use char you'll need to set it to the size of the largest string (100) and
each and every time you enter the value into this field it will take up all
100 chars even if actual data takes only 3 (the rest will be filled with
blanks). Internaly SQL Server stores varchar and char data types a little
bit different, but that shouldn't affect the performance of select
statements. The problem might appear when you update or insert data because
of possible page splitting if new value doesn't fit on the page so SQL
Server has to split page in two to get the space for this new data. This
process is expensive and you want to avoid it. I guess you'll just need to
know your data if you want to select best data type for it. I suggest you
that you use char if the size of your data dont range much. If it does use
varchar and you'll save some space on your hard drive.
Regards,
Tomislav Kralj
tomislav.kralj1@.zg.tel.hr|||I don't see where using a char in this case would give you any performance
gain and might even decrease it if the majority of the actual data are
relatively small in the number of bytes. While there is a little overhead
in maintaining a varchar over a char it is minimal and usually not worth the
extra space the char will take up when storing and retrieving. If you want
to get the fastest data load then make sure you have the proper conditions
to get a minimally logged load. Lookup BCP in BooksOnLine and there is a
section that explains how to do this.
--
Andrew J. Kelly
SQL Server MVP
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
> Please advise
> Thanks in advance
>|||With the most important already stated, a further point:
in case the column is part of an index, the impact could be significant.
With the fixed length, the index size would be much larger, taking more
space and making traverse of the index longer. Then, checking the
individual index value mostly also takes much longer than needed.
Quentin
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:u2jrgy1VDHA.2024@.TK2MSFTNGP12.phx.gbl...
> Hello All
> Can some one give me a quick lesson in data types - What is the basic
> difference between a char data type and varchar data type and when does
one
> use one over the other and advantages and disadvantages of one over the
> other.
> Here is the reason I asked.
> Initially I had a table to which I was importing data from a text file.
The
> majority of the columns in this table was of datatype varchar ranging from
> size of 3 to size of 100. THe columns also consisted of decimal data types
> and datetime datatypes. It was suggested that changing the data type to
char
> from varchar would speed up the insertion process. The file the data is
> being inserted from is a fixed length ASCII text file which
> Would using a char datatype over a varchar make a difference in select
> statement (Select *)
> Please advise
> Thanks in advance
>

Data type int to char

Hi again everyone,
I build a temp table with a data type of int on one column. I then use BCP
to export this table to a TXT file. I use the -c option and also the -t""
(no column delimiter).
The problem I am having is that I am limited to 5 characters for this column
by the company the data is being sent too (archaic I know, I think they are
reading it with Cobol) and the int exports to about 9 characters.
Is there a way to change an int to a char on the fly with INSERT? Something
like CAST?
Beating my brains out, thanks for any suggestions.
George
george collins wrote:
> Hi again everyone,
> I build a temp table with a data type of int on one column. I then
> use BCP to export this table to a TXT file. I use the -c option and
> also the -t"" (no column delimiter).
> The problem I am having is that I am limited to 5 characters for this
> column by the company the data is being sent too (archaic I know, I
> think they are reading it with Cobol) and the int exports to about 9
> characters.
> Is there a way to change an int to a char on the fly with INSERT?
> Something like CAST?
> Beating my brains out, thanks for any suggestions.
> George
You can optionally use a query with BCP. That would give you the
opportunity to convert the INT into a char(5). But you may have problems
if the values in the INT are greater than 99,999 since you'll run into 6
digit numbers.
David Gugick
Imceda Software
www.imceda.com
|||Yeah, I ran in to that kind of issue, what my main problem was the original
character type was a float which would not convert. I changed that to an
int and I am off and running.
Thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23enR57ooEHA.3464@.tk2msftngp13.phx.gbl...
> george collins wrote:
> You can optionally use a query with BCP. That would give you the
> opportunity to convert the INT into a char(5). But you may have problems
> if the values in the INT are greater than 99,999 since you'll run into 6
> digit numbers.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Data type int to char

Hi again everyone,
I build a temp table with a data type of int on one column. I then use BCP
to export this table to a TXT file. I use the -c option and also the -t""
(no column delimiter).
The problem I am having is that I am limited to 5 characters for this column
by the company the data is being sent too (archaic I know, I think they are
reading it with Cobol) and the int exports to about 9 characters.
Is there a way to change an int to a char on the fly with INSERT? Something
like CAST?
Beating my brains out, thanks for any suggestions.
Georgegeorge collins wrote:
> Hi again everyone,
> I build a temp table with a data type of int on one column. I then
> use BCP to export this table to a TXT file. I use the -c option and
> also the -t"" (no column delimiter).
> The problem I am having is that I am limited to 5 characters for this
> column by the company the data is being sent too (archaic I know, I
> think they are reading it with Cobol) and the int exports to about 9
> characters.
> Is there a way to change an int to a char on the fly with INSERT?
> Something like CAST?
> Beating my brains out, thanks for any suggestions.
> George
You can optionally use a query with BCP. That would give you the
opportunity to convert the INT into a char(5). But you may have problems
if the values in the INT are greater than 99,999 since you'll run into 6
digit numbers.
--
David Gugick
Imceda Software
www.imceda.com|||Yeah, I ran in to that kind of issue, what my main problem was the original
character type was a float which would not convert. I changed that to an
int and I am off and running.
Thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23enR57ooEHA.3464@.tk2msftngp13.phx.gbl...
> george collins wrote:
>> Hi again everyone,
>> I build a temp table with a data type of int on one column. I then
>> use BCP to export this table to a TXT file. I use the -c option and
>> also the -t"" (no column delimiter).
>> The problem I am having is that I am limited to 5 characters for this
>> column by the company the data is being sent too (archaic I know, I
>> think they are reading it with Cobol) and the int exports to about 9
>> characters.
>> Is there a way to change an int to a char on the fly with INSERT?
>> Something like CAST?
>> Beating my brains out, thanks for any suggestions.
>> George
> You can optionally use a query with BCP. That would give you the
> opportunity to convert the INT into a char(5). But you may have problems
> if the values in the INT are greater than 99,999 since you'll run into 6
> digit numbers.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Data type conversion

I have one column which contains numbers [but the column is in char format]
What will be the proper SQL query to sort that column ? If i use order by
clause, the records are sorted in characted method, i.e 40,50 not 40,41Arsalan,
Try:
ORDER BY LTRIM(RTRIM(column))
HTH
Jerry
"Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
news:%23o3VP5czFHA.3836@.TK2MSFTNGP10.phx.gbl...
>I have one column which contains numbers [but the column is in char format]
> What will be the proper SQL query to sort that column ? If i use order by
> clause, the records are sorted in characted method, i.e 40,50 not 40,41
>|||Doesnt work.
LTRIM and RTRIM will remove the space, but doesnt sort properly.
MY records are like this
40
41
42
.
..
..
50
when i use order by clause, its sorts like this --> 40,50 [bec if character
field]. How do I sort the numeric part of the character field properly?
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...
> Arsalan,
> Try:
>
> ORDER BY LTRIM(RTRIM(column))
> HTH
> Jerry
> "Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
> news:%23o3VP5czFHA.3836@.TK2MSFTNGP10.phx.gbl...
>|||create table #t(s char(4))
insert into #t(s) values('5')
insert into #t(s) values('50')
insert into #t(s) values('40')
insert into #t(s) values('41')
select s
from #t
order by s
select cast(s as int) p
from #t
order by p
drop table #t
Payson
Arsalan wrote:
> Doesnt work.
> LTRIM and RTRIM will remove the space, but doesnt sort properly.
> MY records are like this
> 40
> 41
> 42
> .
> ..
> ..
> 50
> when i use order by clause, its sorts like this --> 40,50 [bec if characte
r
> field]. How do I sort the numeric part of the character field properly?
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...|||order by cast(ltrim(rtrim(column as int)))
"Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
news:OKxzKOdzFHA.3924@.TK2MSFTNGP14.phx.gbl...
> Doesnt work.
> LTRIM and RTRIM will remove the space, but doesnt sort properly.
> MY records are like this
> 40
> 41
> 42
> .
> ..
> ..
> 50
> when i use order by clause, its sorts like this --> 40,50 [bec if
> character field]. How do I sort the numeric part of the character field
> properly?
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...
>|||Arsalan,
Worked on my server. Try the examples listed in the following posts with
CAST --> INT.
HTH
Jerry
"Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
news:OKxzKOdzFHA.3924@.TK2MSFTNGP14.phx.gbl...
> Doesnt work.
> LTRIM and RTRIM will remove the space, but doesnt sort properly.
> MY records are like this
> 40
> 41
> 42
> .
> ..
> ..
> 50
> when i use order by clause, its sorts like this --> 40,50 [bec if
> character field]. How do I sort the numeric part of the character field
> properly?
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23G6I3%23czFHA.3832@.TK2MSFTNGP10.phx.gbl...
>|||Thanks
"Jay" <msnews.microsoft.com> wrote in message
news:unWe6ddzFHA.3152@.TK2MSFTNGP10.phx.gbl...
> order by cast(ltrim(rtrim(column as int)))
>
> "Arsalan" <arsalan_aslam@.hotmail.com> wrote in message
> news:OKxzKOdzFHA.3924@.TK2MSFTNGP14.phx.gbl...
>

Data Type Confusion...

Which Datatype should i choose for Field "AGE"

Do i select "Int", "Char"?

SQL Server...in Table Desgin, The length for Int only can be max of 4 ?

Is this mean it can only store 4 digit ?

What if i want to store numbers but more then 4 digit ?

What will be the appropriate datatype to store numbers?For age, an appropriate data type would be 'int', you should however contemplate SmallDateTime if you need the full Date of Birth. An 'int' has a storage size of 4 bytes, hence 4, and can be of values from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

Numbers can be stored using different data types, depending on what they represent and will be used for. E.g, for money, you simply use the data type 'money'.

Below are all numerics available on MS Sql Server 2000

EXACT NUMERICS
============
Integers
-------
bigint = Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807).

int = Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).

smallint = Integer data from 2^15 (-32,768) through 2^15 - 1 (32,767).

tinyint = Integer data from 0 through 255.
-------
bit
-------
bit = Integer data with either a 1 or 0 value.
-------
decimal and numeric
-------
decimal = Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.

numeric = Functionally equivalent to decimal.
-------
money and smallmoney
-------
money = Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.

smallmoney = Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
-------
Approximate Numerics
-------
float = Floating precision number data from -1.79E + 308 through 1.79E + 308.

real = Floating precision number data from -3.40E + 38 through 3.40E + 38.|||Hie...

Thanks alot for the info...

I just realize the Length "4" for "int" doesn't meant the total number of 4 digit...?

But how bout the Datatype "Char" or "NvarChar", the length is the number of character is it?|||you can use tinyint for AGE|||:: you can use tinyint for AGE

If you do not expect the person to live longer than a century, yeah :-) .|||::But how bout the Datatype "Char" or "NvarChar", the length is the number of character is
::it?

This is documented in the - documentation, you know.

For char and varchar fields, this is indeed the number of chars you store / can store.