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

Hi everyone,
Which is better to use for datatypes nvarchar or varchar?
In access it is a text datatype. For instance i use in access an column with
datatype text, length 100. When upsizing sql server convert this into
nvarchar(100).
Should i leave it this way or is it better to change it to a suitable
datatype.nvarchar uses twice the storage space but allows for storage of more
characters / symbols to support various languages.
If your access db is in a non enlgish language, it's likely best for you to
use nvarchar.
Regards,
Greg Linwood
SQL Server MVP
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:eUnabfTyDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi everyone,
> Which is better to use for datatypes nvarchar or varchar?
> In access it is a text datatype. For instance i use in access an column
with
> datatype text, length 100. When upsizing sql server convert this into
> nvarchar(100).
> Should i leave it this way or is it better to change it to a suitable
> datatype.
>|||nvarchar can handle unicode characters
varchar cannot
if you need to deal with these characters for example
french and spanish have accent marks and these are fairly
common within people names and place names, then you need
nvarchar. The downside is that it is twice as large (in
storage terms) as varchar because each character required
two bytes, varchar characters require 1 byte.
regards & Merry Christmas,
Mark Baekdal
www.dbghost.com
>--Original Message--
>Hi everyone,
>Which is better to use for datatypes nvarchar or varchar?
>In access it is a text datatype. For instance i use in
access an column with
>datatype text, length 100. When upsizing sql server
convert this into
>nvarchar(100).
>Should i leave it this way or is it better to change it
to a suitable
>datatype.
>
>.
>|||Hi Mark,
Thx for the explaination. Does the performance sql server decreases if
diskspace is not a problem or does sql not work that way?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:07da01c3c941$52d93650$a301280a@.phx.gbl...
> nvarchar can handle unicode characters
> varchar cannot
> if you need to deal with these characters for example
> french and spanish have accent marks and these are fairly
> common within people names and place names, then you need
> nvarchar. The downside is that it is twice as large (in
> storage terms) as varchar because each character required
> two bytes, varchar characters require 1 byte.
> regards & Merry Christmas,
> Mark Baekdal
> www.dbghost.com
> >--Original Message--
> >Hi everyone,
> >
> >Which is better to use for datatypes nvarchar or varchar?
> >In access it is a text datatype. For instance i use in
> access an column with
> >datatype text, length 100. When upsizing sql server
> convert this into
> >nvarchar(100).
> >
> >Should i leave it this way or is it better to change it
> to a suitable
> >datatype.
> >
> >
> >.
> >|||Performance will decrease... The reason is that rows may be twice as long,
therefore half as many rows will fit on a page... This means,
1. Twice as much IO is required to get the same number of rows
2. The rows take up twice as much space in memory, which means OTHER data
gets bumped out of memory, which means OTHER folks have to do more IO as
well..
Use Nvarchar IF you need to store information in ANY language, But if you
only need to store data in latin based languages,,, varchar would be better.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:eUnabfTyDHA.1996@.TK2MSFTNGP12.phx.gbl...
> Hi everyone,
> Which is better to use for datatypes nvarchar or varchar?
> In access it is a text datatype. For instance i use in access an column
with
> datatype text, length 100. When upsizing sql server convert this into
> nvarchar(100).
> Should i leave it this way or is it better to change it to a suitable
> datatype.
>|||Usually you will have a slight decrease in performance
using nvarchar although this should be neglible.
regards,
Mark Baekdal
www.dbghost.com
>--Original Message--
>Hi Mark,
>Thx for the explaination. Does the performance sql
server decreases if
>diskspace is not a problem or does sql not work that way?
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:07da01c3c941$52d93650$a301280a@.phx.gbl...
>> nvarchar can handle unicode characters
>> varchar cannot
>> if you need to deal with these characters for example
>> french and spanish have accent marks and these are
fairly
>> common within people names and place names, then you
need
>> nvarchar. The downside is that it is twice as large (in
>> storage terms) as varchar because each character
required
>> two bytes, varchar characters require 1 byte.
>> regards & Merry Christmas,
>> Mark Baekdal
>> www.dbghost.com
>> >--Original Message--
>> >Hi everyone,
>> >
>> >Which is better to use for datatypes nvarchar or
varchar?
>> >In access it is a text datatype. For instance i use in
>> access an column with
>> >datatype text, length 100. When upsizing sql server
>> convert this into
>> >nvarchar(100).
>> >
>> >Should i leave it this way or is it better to change
it
>> to a suitable
>> >datatype.
>> >
>> >
>> >.
>> >
>
>.
>

data type nvarchar(MAX) in SQL Server 2005

nvarchar(max)

I have a nvarchar(MAX) “Description” field in my SQL Server 2005 database. Somewhere I think I read that nvarchar(MAX) is the size of 4000.

My question is in my Insert Stored Proc

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

ALTERPROCEDURE [dbo].[syl_CodeScheduleTypeInsert]

@.ScheduleName nvarchar(50),

@.ScheduleDescription nvarchar(????)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

BEGINTRY

INSERTI

What number should replace the ???? with? nvarchar(4000) is that correct?

I tried nvarchar(MAX) and nvarchar but both versions only input one character into my database table.

Please help a newbie out, cheers!

No NVarchar (max) is more than 4000 because it can grow to 2 gig so the ?? can be replaced with any thing between 4000 and 2gig. There are restriction using this data type like no Order By and some other clauses because it is bigger than one SQL Server page which is NVarchar 4000. Try the links below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

http://msdn2.microsoft.com/en-us/library/ms187752.aspx

|||

newbie06:

I tried nvarchar(MAX) and nvarchar but both versions only input one character into my database table.

This sounds strange. How did you use the @.ScheduleDescription nvarchar(max) parameter in your stored procedure?

Data Type Conversion

I have a SQL Server 200 database and I need to change the data types in a few fields in a table.

The fields are currently are NVARCHAR and need to be datetime

I get an error message when trying to do this:

****
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
****

My regional settings show as English UK and date format of dd/mm/yyyy.

Can anyone help on this issue?

Thanks in advance.

Neil.The obvious suggestion (and therefore probably wrong) would be to identify rows where the nvarchar data is not actually a valid date time.

I'm not a purist when it comes to cursors. My first approach would be to use a cursor. Scroll through the records and attempt to insert them (just the date/time column) into a new table. If you encounter an error, output sufficient data to clearly identify a row.

Remember that it takes only a single bad data point to trash the conversion process. There may not be that many bad data points.

Something else to remember about what SQL is doing in the background: it is actually creating a new temporary table with the date/time column, inserting the data, then dropping the old table and renaming the new temporary table to the old table name. Just food for thought.

Regards,

hmscott|||My first approach would be:

select *
from YourTable
where IsDate(YourTextDate) = 0

That should show you all the rows with invalid dates.

blindman|||Thanks!

I think I've solved the problem now.

Some of the dates are 2003 and some are 03 so if I change the 03's to 2003 and then convert the data type it works!

Thanks anyway!|||Oh, ugh. I better go back to the SQL BOL. I thought IsDate was only a VBScript function.

Sigh. So much to learn, so little time.

Thanks,

hmscott|||There's a lot of functions and tools out there. I frequently see people reference usefull things on this forum that I have to go look up.

It's too bad that when a new version comes out the help system doesn't have a section titled "Just the stuff you don't already know."

blindman